| Bug #95801 | show create table output can't be executed | ||
|---|---|---|---|
| Submitted: | 14 Jun 2019 1:37 | Modified: | 16 Jul 2019 14:25 |
| Reporter: | Guoji Ma (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 8.0.15, 8.0.16 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution | ||
[14 Jun 2019 1:39]
Guoji Ma
patch (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: 95081.diff (application/octet-stream, text), 661 bytes.
[14 Jun 2019 1:59]
Guoji Ma
patch was send
[14 Jun 2019 4:58]
MySQL Verification Team
Hello Guoji Ma, Thank you for the report and contribution. Verified as described with 8.0.16 build. regards, Umesh
[14 Jun 2019 5:05]
MySQL Verification Team
Please ensure to re-send the patch via "Contributions" tab. Otherwise we would not be able to accept it. regards, Umesh
[14 Jun 2019 7:38]
Guoji Ma
patch (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: 95081.diff (application/octet-stream, text), 661 bytes.
[14 Jun 2019 7:47]
Guoji Ma
Hello Umesh,
Patch is resent to Contributions tab.
Thanks,
Guoji MA
[14 Jun 2019 7:59]
MySQL Verification Team
Thank you, Guoji Ma. regards, Umesh
[14 Jun 2019 9:03]
Peter Laursen
Well .. obviously binary data don't use a collation at all! -- Peter -- not a MySQL/Oracle person
[16 Jul 2019 14:25]
Paul DuBois
Posted by developer: Fixed in 8.0.18. For columns defined with the binary character set, SHOW CREATE TABLE could generate CREATE TABLE statements that produced a syntax error when executed.
[28 Apr 2020 9:33]
Ståle Deraas
Posted by developer: We missed acknowledging that we used the contribution from Guoji Ma as inspiration for the fix. So a late "Thank You" for the contribution!
Description: when table has columns that was defined by specifing expcitly binary charset, the output of show create table about the table can't be executed. mysql> CREATE TABLE `test1` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `etype` enum('a','b','c') CHARACTER SET binary COLLATE binary DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'binary DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb' at line 3 How to repeat: mysql> create database db1; Query OK, 1 row affected (0.01 sec) mysql> use db1; Database changed mysql> create table test1(id int auto_increment primary key, etype enum('a','b','c') character set binary); Query OK, 0 rows affected (0.07 sec) mysql> show create table test1\G *************************** 1. row *************************** Table: test1 Create Table: CREATE TABLE `test1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `etype` enum('a','b','c') CHARACTER SET binary COLLATE binary DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> drop table test1; Query OK, 0 rows affected (0.06 sec) mysql> CREATE TABLE `test1` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `etype` enum('a','b','c') CHARACTER SET binary COLLATE binary DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'binary DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb' at line 3 Suggested fix: exclude my_charset_bin in function 'store_create_info' in file sql_show.cc change if (!(field->charset()->state & MY_CS_PRIMARY) || column_has_explicit_collation || (field->charset() == &my_charset_utf8mb4_0900_ai_ci && share->table_charset != &my_charset_utf8mb4_0900_ai_ci)) to if (!(field->charset()->state & MY_CS_PRIMARY) || (column_has_explicit_collation && field->charset() != &my_charset_bin) || (field->charset() == &my_charset_utf8mb4_0900_ai_ci && share->table_charset != &my_charset_utf8mb4_0900_ai_ci))