Description:
When using partitioning, SHOW CREATE TABLE is inconsistent before and after FLUSH STATUS (notice the AUTO_INCREMENT=3 difference):
-----
mysql> CREATE TABLE `t` (`i` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB AUTO_INCREMENT=3 PARTITION BY HASH (i) PARTITIONS 3;
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO t VALUES(1),(-1);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE t;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`i` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (i)
PARTITIONS 3 */ |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> FLUSH TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW CREATE TABLE t;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`i` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (i)
PARTITIONS 3 */ |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-----
The same is *not* the case when not using partitioning:
-----
mysql> CREATE TABLE `t` (`i` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB AUTO_INCREMENT=3;
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO t VALUES(1),(-1);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE t;
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`i` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-----
Nor when using only positive autoinc's:
-----
mysql> CREATE TABLE `t` (`i` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB AUTO_INCREMENT=3 PARTITION BY HASH (i) PARTITIONS 3;
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO t VALUES(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE t;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`i` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (i)
PARTITIONS 3 */ |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-----
For MyISAM, the results are exactly the same in all 3 circumstances.
How to repeat:
CREATE TABLE `t` (`i` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB AUTO_INCREMENT=3 PARTITION BY HASH (i) PARTITIONS 3;
INSERT INTO t VALUES(1),(-1);
SHOW CREATE TABLE t;
FLUSH TABLES;
SHOW CREATE TABLE t; /* And Compare */
Suggested fix:
Fix behavior.
