37

I have a mysql 8.0 that I exported using mysqldump. I am trying to import it onto a Mariadb 10.4 database with phpmyadmin, both are the most current versions. Each time I do it though, I get:

Error: Unknown collation utf8mb4_0900_ai_ci

Then I went back into the sql file and replaced utf8mb4_0900_ai_ci with utf8mb4_general_ci, but then it says there are errors with various CREATE VIEW sql statements. Are there any other quick, error free fixes I can try?

0

5 Answers 5

22

I've solved my issue by replacing all occurrences of utf8mb4_0900_ai_ci with utf8mb4_unicode_520_ci in my SQL dump file.

1
20

In MariaDB use utf8mb4_unicode_520_ci instead, or you may even use uca1400_as_ci if you have a newer version of MariaDB.

Don't use "_general_ci", as it does not correctly sort or compare according to any version of Unicode and have been superseded decades ago.

When MySQL introduced utf8mb4_0900_ai_ci based on comparison and sorting rules in Unicode 9.0, MariaDB chose not to follow at the time. The new rules did not change much as Unicode's sorting and comparison rules have been pretty stable for several generations of Unicode now. Choosing the "unicode_520_ci" rules will in almost all situations have the same result and this collation can be used in both MySQL and MariaDB.

MariaDB is currently introducing collation rules from Unicode 14.0 - called simply uca1400_as_ci (you don't need the "utf8mb4_" part anymore). You can upgrade to these if you have a new version of MariaDB.

Make sure you use "utf8mb4" as the character encoding (and not "utf8mb3" or "utf8", which represent the older flawed/deprecated implementation of UTF8).

0
3

I replaced all utf8mb4_0900_ai_ci with utf8mb4_unicode_ci and it works.

As FlipMcF stated, unicode is the better choice – general collation is outdated for it has flawed sorting.

0
0
sed -i 's/utf8mb4_0900_ai_ci/utf8_general_ci/g' YOUR_SQL_FILE_NAME.sql 

sed -i 's/CHARSET=utf8mb4/CHARSET=utf8/g' YOUR_SQL_FILE_NAME.sql

Note : After runing above command you are getting similar this sed: 1: "YOUR_SQL_FILE_NAME.sql": invalid command code Y

Open your file vi editor

sudo vi YOUR_SQL_FILE_NAME.sql

Pres esc and type bellow command press enter

:%s/utf8mb4_0900_ai_ci/utf8_general_ci/g

:%s/CHARSET=utf8mb4/CHARSET=utf8/g

Note : Above command search and replace in vi editor we are searching utf8mb4_0900_ai_ci and replacing with utf8_general_ci

1
  • Its better to keep mb4 part of character type convertion because when you remove that part it changes some behaviour and may cause unexpected behavior and datatype errors. I recommend to use it this way: sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' YOUR_SQL_FILE_NAME.sql Jul 6, 2023 at 22:56
-2

According to my experience, the destination's MySQL server is an older version than the source. The required database collation is not present on the destination server.

To fix this, we can make a small change to the backup file. Replace "utf8mb4 0900 ai ci" with "utf8mb4 general ci" and "CHARSET=utf8mb4" with "CHARSET=utf8" in the database backup file.

Replace the below string:

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

with:

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Save your file and restore the database.

1
  • " Don't use any of the "general_ci" variants, as they do not correctly sort or compare according to any version of Unicode and have been superseded decades ago. " - @thomasrutter above
    – FlipMcF
    Feb 23, 2023 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.