Changes MySQL Up to 5.5.0 database collation before importing .sql file in command line
Take a database dump from a Drupal site installed on MySQL up to 5.5.0 using
mysqldump. Try to import on MariaDB 10.2. from .sql file and the error is appear:
ERROR 1273 (HY000) at line 1300: Unknown collation: 'utf8mb4_unicode_ci'
That means that in your database server does not exist the reference to this collation and you have to change the reference inside your .sql file to other collation to resolve the conflict.
You can use the "sed" utility for replace the collation unknown. Be sure that you have the utility installed, then write the following command in your command terminal line.
sed 's/utf8mb4_unicode_ci/utf8mb4_unicode_ci/' filename.sql
Then you can try to import without problem the file again from you command line.
Another solution, but not very clear is opened the dump.sql file in Notepad++ and hit CTRL+H to find and replace the string “utf8mb4_0900_ai_ci” and replaced it with “utf8mb4_general_ci“.
It worked for somebody else, hope it will work for u as well.
Changing the Collation after creating a database
if you have to change the collation of some database after it creation you have to write these commands inside mysql server command line:
To change the collation of database...
ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_general_ci;
To change the collation of all of the tables and columns of database...
SELECT CONCAT("ALTER TABLE `", TABLE_NAME,"` convert to character set utf8 COLLATE utf8_general_ci;") AS mySQL FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="databasename" AND TABLE_TYPE="BASE TABLE";
Thanks you for reading...