Bug #76328 Generated columns not printed correctly in SHOW CREATE TABLE
Submitted: 15 Mar 2015 20:53 Modified: 29 Apr 2015 13:29
Reporter: Mario Beck Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S2 (Serious)
Version:5.7.6, 5.7.8, 8.0.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: character set, generated columns, mysqldump, SHOW CREATE TABLE

[15 Mar 2015 20:53] Mario Beck
Description:
Auto generated columns in MySQL 5.7.6 that have a CHARACTER SET defined, are displayed wrongly in the SHOW CREATE TABLE statement. This means that dumpfiles cannot be loaded! No backup possible.

How to repeat:
mysql> CREATE TABLE t (i CHAR(3), n CHAR(20) CHARACTER SET utf8 GENERATED ALWAYS AS (md5(i)));
Query OK, 0 rows affected (0,13 sec)

mysql> SHOW CREATE TABLE t;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `i` char(3) DEFAULT NULL,
  `n` char(20) GENERATED ALWAYS AS (md5(i)) VIRTUAL CHARACTER SET utf8
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

Then try to re-run the CREATE statement:

mysql> CREATE TABLE `t` (
    ->   `i` char(3) DEFAULT NULL,
    ->   `n` char(20) GENERATED ALWAYS AS (md5(i)) VIRTUAL CHARACTER SET utf8
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
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 'CHARACTER SET utf8
) ENGINE=InnoDB DEFAULT CHARSET=latin1' at line 3

Or do a mysqldump of table t and try to import the dump file. Same error appears. 

Suggested fix:
SHOW CREATE TABLE must generate correct SQL code. In the example above this would be

 CREATE TABLE `t` (
  `i` char(3) DEFAULT NULL,
  `n` char(20) CHARACTER SET utf8 GENERATED ALWAYS AS (md5(i)) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CHARACTER SET is part of the datatype.
[16 Mar 2015 5:30] Umesh Shastry
Hello Mario Beck,

Thank you for the report and test case.

Thanks,
Umesh
[16 Mar 2015 5:31] Umesh Shastry
// 5.7.8

root@localhost [test] > show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.7.8                                                   |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.7.8-rc-enterprise-commercial-advanced                 |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

root@localhost [test] > CREATE TABLE t (i CHAR(3), n CHAR(20) CHARACTER SET utf8 GENERATED ALWAYS AS (md5(i)));
Query OK, 0 rows affected (0.01 sec)

root@localhost [test] > SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `i` char(3) DEFAULT NULL,
  `n` char(20) GENERATED ALWAYS AS (md5(i)) VIRTUAL CHARACTER SET utf8
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

root@localhost [test] > drop table t;
Query OK, 0 rows affected (0.01 sec)

root@localhost [test] >  CREATE TABLE `t` (
    ->   `i` char(3) DEFAULT NULL,
    ->   `n` char(20) GENERATED ALWAYS AS (md5(i)) VIRTUAL CHARACTER SET utf8
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
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 'CHARACTER SET utf8
) ENGINE=InnoDB DEFAULT CHARSET=latin1' at line 3
root@localhost [test] >
[16 Mar 2015 5:33] Umesh Shastry
// 5.8.0
commit: a9992220039b548fc6529ecbeae75f4afcc048fc
date: 2015-03-14 06:45:26 +0100
build-date: 2015-03-14 17:51:23 +0100
short: a999222
branch: mysql-trunk

MySQL source 5.8.0

root@localhost [test] > show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.8.0                                                   |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.8.0-m17-enterprise-commercial-advanced                |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

root@localhost [test] > CREATE TABLE t (i CHAR(3), n CHAR(20) CHARACTER SET utf8 GENERATED ALWAYS AS (md5(i)));
Query OK, 0 rows affected (0.00 sec)

root@localhost [test] > SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `i` char(3) DEFAULT NULL,
  `n` char(20) GENERATED ALWAYS AS (md5(i)) VIRTUAL CHARACTER SET utf8
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

root@localhost [test] >  CREATE TABLE `t` (
    ->   `i` char(3) DEFAULT NULL,
    ->   `n` char(20) GENERATED ALWAYS AS (md5(i)) VIRTUAL CHARACTER SET utf8
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
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 'CHARACTER SET utf8
) ENGINE=InnoDB DEFAULT CHARSET=latin1' at line 3
root@localhost [test] >
[29 Apr 2015 13:29] Paul Dubois
Noted in 5.7.8, 5.8.0 changelogs.

SHOW CREATE TABLE did not correctly display generated columns that
had a character set defined.
[18 Jun 2016 21:24] Omer Barnir
Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0