| 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: | |
| 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 | ||
[16 Mar 2015 5:30]
MySQL Verification Team
Hello Mario Beck, Thank you for the report and test case. Thanks, Umesh
[16 Mar 2015 5:31]
MySQL Verification Team
// 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]
MySQL Verification Team
// 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
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.