Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
SHOW CREATE TABLE produces a CREATE TABLE statement where any CHARACTER SET clauses are incorrectly placed, making the SQL invalid. SHOW CREATE TABLE is used by mysqldump.
mysql> create table t1 (f1 char(32), v2 char(32) character set ucs2 AS (f1) VIRTUAL);
mysql> show create table t1\G
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`f1` char(32) DEFAULT NULL,
`v2` char(32) AS (f1) VIRTUAL CHARACTER SET ucs2
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
- 1. row ***************************
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
This SQL is not valid – CHARACTER SET was placed after VIRTUAL, even though it must be between char(32) and AS.
All of the following must come before the AS () virtual column definition:
- field type and dimensions;
- unsigned
- zerofill
- character set
- collation
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Hi,
I read 'Fixed' but this is still present in 5.5.40,
a non default collation added in the Table definition is automatically added to the PERSISTENT column definition which is not yet supported thou.
MariaDB [test]> status
--------------
bin/mysql Ver 15.1 Distrib 5.5.40-MariaDB, for Linux (x86_64) using readline 5.1
- no collation specified
DROP TABLE table1;
CREATE TABLE table1 ( a INT NOT NULL, b VARCHAR(32), c INT AS (a mod 10) VIRTUAL, d VARCHAR(5) AS (left(b,5)) PERSISTENT);
- no problem
SHOW CREATE TABLE table1;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| table1 | CREATE TABLE `table1` ( `a` int(11) NOT NULL, `b` varchar(32) DEFAULT NULL, `c` int(11) AS (a mod 10) VIRTUAL, `d` varchar(5) AS (left(b,5)) PERSISTENT ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- specify only character set
DROP TABLE table2;
CREATE TABLE table2 ( a INT NOT NULL, b VARCHAR(32), c INT AS (a mod 10) VIRTUAL, d VARCHAR(5) AS (left(b,5)) PERSISTENT) character set utf8;
Query OK, 0 rows affected (0.05 sec)
- no problem
SHOW CREATE TABLE table2;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------table2 CREATE TABLE `table2` (
`a` int(11) NOT NULL,
`b` varchar(32) DEFAULT NULL,
`c` int(11) AS (a mod 10) VIRTUAL,
`d` varchar(5) AS (left(b,5)) PERSISTENT
) ENGINE=InnoDB DEFAULT CHARSET=utf8-------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
- specify charset and collation
DROP TABLE table3;
CREATE TABLE table3 ( a INT NOT NULL, b VARCHAR(32), c INT AS (a mod 10) VIRTUAL, d VARCHAR(5) AS (left(b,5)) PERSISTENT) character set utf8 collate utf8_unicode_ci;
Query OK, 0 rows affected (0.06 sec)
- problem: implicitly added the collaton to PERSISTENT column definition, which is not supported yet
SHOW CREATE TABLE table3;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------table3 CREATE TABLE `table3` (
`a` int(11) NOT NULL,
`b` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`c` int(11) AS (a mod 10) VIRTUAL,
`d` varchar(5) COLLATE utf8_unicode_ci AS (left(b,5)) PERSISTENT
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci-------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE `table3` (
-> `a` int(11) NOT NULL,
-> `b` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
-> `c` int(11) AS (a mod 10) VIRTUAL,
-> `d` varchar(5) COLLATE utf8_unicode_ci AS (left(b,5)) PERSISTENT
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS (left(b,5)) PERSISTENT
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unic' at line 5
- removing the explicit collation definition, fixes it
DROP TABLE table4;
CREATE TABLE `table4` ( `a` int(11) NOT NULL, `b` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, `c` int(11) AS (a mod 10) VIRTUAL, `d` varchar(5) AS (left(b,5)) PERSISTENT ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.05 sec)
- of course it is readded
SHOW CREATE TABLE table4;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+table4 CREATE TABLE `table4` (
`a` int(11) NOT NULL,
`b` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`c` int(11) AS (a mod 10) VIRTUAL,
`d` varchar(5) COLLATE utf8_unicode_ci AS (left(b,5)) PERSISTENT
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci-------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Launchpad bug id: 610890