Description:
The character set and collation should be included in output from SHOW CREATE TABLE, mysqldump and mysqlpump. As it is now, the collation will not be printed if it is the default collation. This in turn causes problems when doing dump/restore from 5.7 to 8.0
How to repeat:
Do the following in 5.7:
mysql> CREATE TABLE test (col1 VARCHAR(255), PRIMARY KEY(col1)) CHARACTER SET utf8mb4;
# In case the second character isn't printed correctly in the bug report, it should be 'LATIN SMALL LETTER AE'
mysql> INSERT INTO test VALUES ("ae"), ("æ");
Dump this table using mysqldump or mysqlpump:
shell> mysqldump test test > mydump.sql
You'll get the following CREATE TABLE:
CREATE TABLE `test` (
`col1` varchar(255) NOT NULL,
PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Note that the collation is not included! If you try to restore this dump in 8.0, you'll get the following error:
shell> mysql test < mydump.sql
ERROR 1062 (23000) at line 37: Duplicate entry 'æ' for key 'PRIMARY'
This is because 8.0 has a different default collation for utf8mb4.
Suggested fix:
Always output the collation for table/column/schema. This will cause less problems when doing dump/restore from old to new versions.
Description: The character set and collation should be included in output from SHOW CREATE TABLE, mysqldump and mysqlpump. As it is now, the collation will not be printed if it is the default collation. This in turn causes problems when doing dump/restore from 5.7 to 8.0 How to repeat: Do the following in 5.7: mysql> CREATE TABLE test (col1 VARCHAR(255), PRIMARY KEY(col1)) CHARACTER SET utf8mb4; # In case the second character isn't printed correctly in the bug report, it should be 'LATIN SMALL LETTER AE' mysql> INSERT INTO test VALUES ("ae"), ("æ"); Dump this table using mysqldump or mysqlpump: shell> mysqldump test test > mydump.sql You'll get the following CREATE TABLE: CREATE TABLE `test` ( `col1` varchar(255) NOT NULL, PRIMARY KEY (`col1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Note that the collation is not included! If you try to restore this dump in 8.0, you'll get the following error: shell> mysql test < mydump.sql ERROR 1062 (23000) at line 37: Duplicate entry 'æ' for key 'PRIMARY' This is because 8.0 has a different default collation for utf8mb4. Suggested fix: Always output the collation for table/column/schema. This will cause less problems when doing dump/restore from old to new versions.