Skip to content

Instantly share code, notes, and snippets.

@caarmen
Created June 28, 2023 11:05
Show Gist options
  • Save caarmen/cf5eb18a7583c19a4b2f230273c0756b to your computer and use it in GitHub Desktop.
Save caarmen/cf5eb18a7583c19a4b2f230273c0756b to your computer and use it in GitHub Desktop.
mysql/mariadb: fix weird errors about fk constraints, when the real problem is database charset

Dump the whole database:

mysqldump -u myuser my_database -p > dump.sql

Open the dump.sql file.

Do a search and replace: for example replace all occurrences of utf8mb3 with utf8mb4. Save the file.

Open mysql:

mysql -u myuser -p

Delete the database, recreate it, and use it:

drop database my_database;
create database my_database;
use my_database;

Import the dump:

source dump.sql
@e-a-genymobile
Copy link

Or simply tell mariadb/mysql to use another encoding before doing the migration so it sticks to the used one:
ALTER DATABASE my_database COLLATE = 'utf8mb3_general_ci';
This will make sure that the created table(s) use the same collation

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment