Bug #46911 Inconsistent SHOW CREATE TABLE result before/after FLUSH STATUS w/ partitioning
Submitted: 25 Aug 2009 6:56 Modified: 8 Sep 2009 8:44
Reporter: Roel Van de Paar (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1.37 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[25 Aug 2009 6:56] Roel Van de Paar
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.
[25 Aug 2009 6:57] Roel Van de Paar
Verifying on D2. Can repeat on Windows and Linux.
[8 Sep 2009 8:44] Mattias Jonsson
Cannot repeat in mysql-51-bugteam after bug#45823 was pushed. Closing as duplicate or that bug.