Description:
When importing a mysqldump backup in which the table definition does NOT have the COLLATION specified, the collation used is "default_collation_for_utf8mb4" rather than "collation_database".
As per the documentation https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
"The default_collation_for_utf8mb4 system variable is for internal use by MySQL Replication only."
In the above case not only it's not used for replication but can break an import or cause issues due to the difference in collation and the inability to set this parameter through the configuration file.
How to repeat:
I have the following configuration:
mysql> show global variables like '%coll%';
+-------------------------------+--------------------+
| Variable_name | Value |
+-------------------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
And the following file:
# cat test.dmp
CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Importing the file with the following command:
mysql -u root -p test < test.dmp
Will create the following table definiton with the collation from default_collation_for_utf8mb4:
CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Setting:
set global default_collation_for_utf8mb4 = "utf8mb4_general_ci";
Will make the import result with the correct collation (utf8mb4_general_ci)
Note that executing the CREATE statement manually (rather than through an import) will use "collation_database" value for the table collation
Suggested fix:
Although the workaround is to set an explicit COLLATION in the backup, the above difference in configuration can introduce differences in the expected outcome or even result in errors.
Ideally the value of "collation_database" should be honoured for an import. And the possibility for setting "default_collation_for_utf8mb4" in the configuration is highly desirable as well to keep consistency with the other values.
