Description:
For some table collations, when columns inherit the default one from table, show create table prints explicit column COLLATE definition wrongly.
Then when the same shown definition is repeated, the column is really set to use explicit charset and collation.
How to repeat:
mysql> CREATE TABLE test (
-> id int NOT NULL AUTO_INCREMENT,
-> a int,
-> b varchar(10),
-> PRIMARY KEY (`id`)) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.02 sec)
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int NOT NULL AUTO_INCREMENT,
`a` int DEFAULT NULL,
`b` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)
ibd2sdi /var/lib/mysql/test/test.ibd |grep -A40 '"name": "b"'|grep collation
"collation_id": 224,
"is_explicit_collation": false
mysql> drop table test;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE `test` (
-> `id` int NOT NULL AUTO_INCREMENT,
-> `a` int DEFAULT NULL,
-> `b` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.02 sec)
ibd2sdi /var/lib/mysql/test/test.ibd |grep -A40 '"name": "b"'|grep collation
"collation_id": 224,
"is_explicit_collation": true
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int NOT NULL AUTO_INCREMENT,
`a` int DEFAULT NULL,
`b` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)
Suggested fix:
When collation is not set explicitly for a column, it should stay that way ;)
Description: For some table collations, when columns inherit the default one from table, show create table prints explicit column COLLATE definition wrongly. Then when the same shown definition is repeated, the column is really set to use explicit charset and collation. How to repeat: mysql> CREATE TABLE test ( -> id int NOT NULL AUTO_INCREMENT, -> a int, -> b varchar(10), -> PRIMARY KEY (`id`)) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; Query OK, 0 rows affected (0.02 sec) mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int NOT NULL AUTO_INCREMENT, `a` int DEFAULT NULL, `b` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.00 sec) ibd2sdi /var/lib/mysql/test/test.ibd |grep -A40 '"name": "b"'|grep collation "collation_id": 224, "is_explicit_collation": false mysql> drop table test; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE `test` ( -> `id` int NOT NULL AUTO_INCREMENT, -> `a` int DEFAULT NULL, -> `b` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; Query OK, 0 rows affected (0.02 sec) ibd2sdi /var/lib/mysql/test/test.ibd |grep -A40 '"name": "b"'|grep collation "collation_id": 224, "is_explicit_collation": true mysql> show create table test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int NOT NULL AUTO_INCREMENT, `a` int DEFAULT NULL, `b` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.00 sec) Suggested fix: When collation is not set explicitly for a column, it should stay that way ;)