ERROR 1273 (HY000) at line 22: Unknown collation: 'utf8mb4_0900_ai_ci' when importing from MySQL 8 to MariaDB 10.3

Hello,

I am moving server from DigitalOcean due to poor service and support from them. I set up a new server at Hetzner. I exported our MySQL 8 database. It is in prodsales.backup file. I installed MariaDB on my new server. But, I get strange problem and error. Not sure what to do. My command:

mysql prodsales < prodsales.backup 

Error:

ERROR 1273 (HY000) at line 22: Unknown collation: 'utf8mb4_0900_ai_ci'

I thought MariaDB is drop-in replacement for MySQL. Anyway, how can I fix this?

Recommended charset MariaDB:

utf8mb4

Collation for MariaDB

utf8mb4_unicode_ci

ENGINE for MariaDB:

InnoDB

So find those in your .sql file and replace with proper one.

Fixing unknown collation: ‘utf8mb4_0900_ai_ci’

Simple sed should work:

sed 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' prodsales.backup > new.prodsales.backup 

Repeat process for rest of databases charset or collation. Then try to import it:

mysql prodsales < new.prodsales.backup 

If you don’t know how to use sed or Unix prompt

  1. Open the sql file in your text editor.
  2. Search: utf8mb4_0900_ai_ci Replace all: utf8mb4_unicode_ci
  3. Save the sql file. Then import as usual on the new server.
1 Like

It worked. So simple.


Linux sysadmin blog - Linux/Unix Howtos and Tutorials - Linux bash shell scripting wiki