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:
None 
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:37] Guoji Ma
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))
[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 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!