14

I would like to take a dump from Mysql 8.0.11 and restore it into 5.7.27.

When I tried to restore it I got the error:

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

Then I tried to use the compatible flag to make it easier on an older MySQL DB.

mysqldump --compatible=mysql4 --add-drop-table -u r00t -h xxx.eu-north-1.rds.amazonaws.com -p radius_db > ~/radius.sql

But that doesn't seem to work either:

mysqldump: Couldn't execute '/*!40100 SET @@SQL_MODE='MYSQL40' */': Variable 'sql_mode' can't be set to the value of 'MYSQL40' (1231)

Any advice would be appreciated.

Houman
  • 64,245
  • 87
  • 278
  • 460
  • 6
    Upvoted for the awesome title, I love me a little accidental humor on SO :) – N.B. Aug 22 '19 at 10:41
  • 1
    Does this answer your question? [MySQL Import error - Unknown collation: 'utf8mb4\_unicode\_520ci\_'](https://stackoverflow.com/questions/41401436/mysql-import-error-unknown-collation-utf8mb4-unicode-520ci) – Channa Mar 14 '21 at 06:46

2 Answers2

11

Simply put, use as a DEFAULT "utf8" and as COLLATE "utf8_general_ci".

One way to solve your problem is to change in your import .sql-Files from

  • "utf8mb4" to "utf8"
  • and "utf8mb4_0900_ai_ci" (or something else) to "utf8_general_ci"

Hint: Don't forget to backup your files just in case ;-)

Go to your (.sql) import files and do these changes. From:

ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8_general_ci;

to:

ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Reimport again.

Birol Efe
  • 1,605
  • 16
  • 14
  • 2
    Thanks, I ended up changing all occurrences of `utf8mb4_0900_ai_ci ` to `utf8mb4_general_ci ` and it worked. – Houman Aug 23 '19 at 11:54
4

You can use the next code to move your database from mysql 8.x to mysql 5.x.

mysqldump db > db.sql

sed -i s/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g db.sql

mysql db < db.sql

db is your database name

Vadim Romanko
  • 61
  • 2