| Bug #71906 | SHOW CREATE TABLE FOR PARTITIONED TABLES: output slightly wrong | ||
|---|---|---|---|
| Submitted: | 3 Mar 2014 9:47 | Modified: | 10 Mar 2014 8:06 |
| Reporter: | Simon Mudd (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | 5.6.15 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | partitioning, SHOW CREATE TABLE | ||
[3 Mar 2014 10:13]
Mark Leith
Appears to be related to the format passed in to the ALTER TABLE statement, for instance, this looks fine:
mysql> create table t999 (id int primary key) engine = myisam;
Query OK, 0 rows affected (0.05 sec)
mysql> alter table t999 partition by range (id) (partition p values less than MAXVALUE) ;
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t999\G
*************************** 1. row ***************************
Table: t999
Create Table: CREATE TABLE `t999` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (id)
(PARTITION p VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
However:
mysql> drop table t999;
Query OK, 0 rows affected (0.06 sec)
mysql> create table t999 (id int primary key) engine = myisam;
Query OK, 0 rows affected (0.15 sec)
mysql> alter table t999 partition by range ( id ) (partition p values less than MAXVALUE) ;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t999\G
*************************** 1. row ***************************
Table: t999
Create Table: CREATE TABLE `t999` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( id)
(PARTITION p VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
1 row in set (0.00 sec)
[3 Mar 2014 13:08]
MySQL Verification Team
related: http://bugs.mysql.com/bug.php?id=46968
[4 Mar 2014 12:46]
Mattias Jonsson
Simon, currently the partition expression is also stored as the given string found during parsing, which is later given back as output. We could change it to output the parsed expression instead, which would be more inline with the rest of the output from mysql.
Doing this I'm also thinking of removing the 'ENGINE = <engine>' per partition and always treat [sub]partition names as other identifiers so they will also always be quoted (and according to SQL_MODE: ANSI_QUOTES).
Example:
create table t1 ( id int primary key ) engine = myisam;
alter table t1 partition by range ( id ) ( partition p values less than MAXVALUE ) ;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION `p` VALUES LESS THAN MAXVALUE) */
DROP TABLE t1;
And the output with slightly more complex expression, for showing the effect only :)
CREATE TABLE t1 (a INT, b INT)
PARTITION BY RANGE (a * 3 + b)
SUBPARTITION BY HASH (b %5 + a%7) SUBPARTITIONS 3
(PARTITION p0 VALUES LESS THAN (-1000),
PARTITION p1 VALUES LESS THAN (0),
PARTITION p2 VALUES LESS THAN (1000),
PARTITION p3 VALUES LESS THAN (3000));
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (((`a` * 3) + `b`))
SUBPARTITION BY HASH (((`b` % 5) + (`a` % 7)))
SUBPARTITIONS 3
(PARTITION `p0` VALUES LESS THAN (-1000),
PARTITION `p1` VALUES LESS THAN (0),
PARTITION `p2` VALUES LESS THAN (1000),
PARTITION `p3` VALUES LESS THAN (3000)) */
ALTER TABLE t1 ADD PARTITION
(PARTITION p4 VALUES LESS THAN (6000) (subpartition sp40, subpartition sp41, subpartition sp42));
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (((`a` * 3) + `b`))
SUBPARTITION BY HASH (((`b` % 5) + (`a` % 7)))
(PARTITION `p0` VALUES LESS THAN (-1000)
(SUBPARTITION `p0sp0`,
SUBPARTITION `p0sp1`,
SUBPARTITION `p0sp2`),
PARTITION `p1` VALUES LESS THAN (0)
(SUBPARTITION `p1sp0`,
SUBPARTITION `p1sp1`,
SUBPARTITION `p1sp2`),
PARTITION `p2` VALUES LESS THAN (1000)
(SUBPARTITION `p2sp0`,
SUBPARTITION `p2sp1`,
SUBPARTITION `p2sp2`),
PARTITION `p3` VALUES LESS THAN (3000)
(SUBPARTITION `p3sp0`,
SUBPARTITION `p3sp1`,
SUBPARTITION `p3sp2`),
PARTITION `p4` VALUES LESS THAN (6000)
(SUBPARTITION `sp40`,
SUBPARTITION `sp41`,
SUBPARTITION `sp42`)) */
SET SQL_MODE="ANSI_QUOTES";
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE "t1" (
"a" int(11) DEFAULT NULL,
"b" int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ((("a" * 3) + "b"))
SUBPARTITION BY HASH ((("b" % 5) + ("a" % 7)))
(PARTITION "p0" VALUES LESS THAN (-1000)
(SUBPARTITION "p0sp0",
SUBPARTITION "p0sp1",
SUBPARTITION "p0sp2"),
PARTITION "p1" VALUES LESS THAN (0)
(SUBPARTITION "p1sp0",
SUBPARTITION "p1sp1",
SUBPARTITION "p1sp2"),
PARTITION "p2" VALUES LESS THAN (1000)
(SUBPARTITION "p2sp0",
SUBPARTITION "p2sp1",
SUBPARTITION "p2sp2"),
PARTITION "p3" VALUES LESS THAN (3000)
(SUBPARTITION "p3sp0",
SUBPARTITION "p3sp1",
SUBPARTITION "p3sp2"),
PARTITION "p4" VALUES LESS THAN (6000)
(SUBPARTITION "sp40",
SUBPARTITION "sp41",
SUBPARTITION "sp42")) */
Do the above match your request for consistency?
Regarding 'odd' character, in such case they needs to be quoted in the create statement and should be handled correctly.
[10 Mar 2014 8:06]
Simon Mudd
A couple of thoughts: (1) while it's not possible to have different engines for each partition then removing this duplicate information would seem like a good idea as it's useless. (It might be nice to be able to choose the engine per partition but that's out of the scope of this FR.) You said: " I'm also thinking of removing the 'ENGINE = <engine>' per partition and always treat [sub]partition names as other identifiers so they will also always be quoted (and according to SQL_MODE: ANSI_QUOTES)." Yes, I think this make sense.
Description: The partitioning output seems to be inconsistent with other SHOW CREATE TABLE OUTPUT. While not a big issue, if you process the output of SHOW CREATE TABLE you have to take these inconsistencies into account. How to repeat: mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> create table t999 ( id int primary key ) engine = myisam; Query OK, 0 rows affected (0.07 sec) mysql> alter table t999 partition by range ( id ) ( partition p values less than MAXVALUE ) ; Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t999\G *************************** 1. row *************************** Table: t999 Create Table: CREATE TABLE `t999` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE ( id) (PARTITION p VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ 1 row in set (0.00 sec) mysql> select @@version; +------------+ | @@version | +------------+ | 5.6.14-log | +------------+ 1 row in set (0.05 sec) mysql> Suggested fix: 1. Note: the PARTITION BY RANGE LINE shows a space before the id column name, but not after it. 2. Most other columns are shown as back-ticks but the output of this RANGE ( xxx ) does not use that style. Again both of these are minor things but it would be nice to be consistent. So I'd be tempted to think the output of this line should be: /*!50100 PARTITION BY RANGE ( `id` ) or /*!50100 PARTITION BY RANGE (`id`) It's not clear what would happen if there are odd characters such as spaces in the column names for example.