alter table... drop partition resets auto_increment if table becomes empty

[Rick Pizzi]

If you have a partitioned table and you drop the oldest partition, and no other rows are in the table, the auto_increment gets reset to 1. This is not the expected behaviour.

See below...

Server version: 5.6.23-72.1-log Percona Server (GPL), Release 72.1, Revision 0503478

 auto_increment_increment = 2
 auto_increment_offset = 2

CREATE TABLE `rick_test` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `TIMESTAMP` datetime NOT NULL,
  `ID2` bigint(20) DEFAULT NULL,
  `ID3` int(11) DEFAULT NULL,
  `JM_ROUTE` varchar(20) NOT NULL,
  `TRACE_ID` varchar(150) NOT NULL,
  `SPAN_ID` varchar(25) NOT NULL,
  `PARENT_SPAN_ID` varchar(25) NOT NULL,
  `ID_DRIVER` varchar(50) NOT NULL,
  `ID_BUSINESS_PROFILE` varchar(50) NOT NULL,
  `LANGUAGE` varchar(2) NOT NULL,
  `ID_5` varchar(3) NOT NULL,
  `REQUEST_XML` mediumtext NOT NULL,
  `RESPONSE_XML` mediumtext NOT NULL,
  PRIMARY KEY (`ID`,`TIMESTAMP`),
  KEY `TIMESTAMP` (`TIMESTAMP`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (TO_DAYS(TIMESTAMP))
(PARTITION p37 VALUES LESS THAN (736487) ENGINE = InnoDB,
 PARTITION p38 VALUES LESS THAN (736488) ENGINE = InnoDB,
 PARTITION p39 VALUES LESS THAN (736489) ENGINE = InnoDB,
 PARTITION p40 VALUES LESS THAN (736490) ENGINE = InnoDB,
 PARTITION p41 VALUES LESS THAN (736491) ENGINE = InnoDB,
 PARTITION p42 VALUES LESS THAN (736492) ENGINE = InnoDB,
 PARTITION p43 VALUES LESS THAN (736493) ENGINE = InnoDB,
 PARTITION p44 VALUES LESS THAN (736494) ENGINE = InnoDB,
 PARTITION p45 VALUES LESS THAN (736495) ENGINE = InnoDB,
 PARTITION p46 VALUES LESS THAN (736496) ENGINE = InnoDB,
 PARTITION p47 VALUES LESS THAN (736497) ENGINE = InnoDB,
 PARTITION p48 VALUES LESS THAN (736498) ENGINE = InnoDB,
 PARTITION p49 VALUES LESS THAN (736499) ENGINE = InnoDB,
 PARTITION p50 VALUES LESS THAN (736500) ENGINE = InnoDB,
 PARTITION p51 VALUES LESS THAN (736501) ENGINE = InnoDB,
 PARTITION p52 VALUES LESS THAN (736502) ENGINE = InnoDB,
 PARTITION p53 VALUES LESS THAN (736503) ENGINE = InnoDB,
 PARTITION p54 VALUES LESS THAN (736504) ENGINE = InnoDB,
 PARTITION p55 VALUES LESS THAN (736505) ENGINE = InnoDB,
 PARTITION p56 VALUES LESS THAN (736506) ENGINE = InnoDB,
 PARTITION p57 VALUES LESS THAN (736507) ENGINE = InnoDB) */
Query OK, 0 rows affected (0.04 sec)

mysql>insert into rick_test (id, timestamp) values (NULL, '2016-01-01 00:00:00');
Query OK, 1 row affected, 10 warnings (0.00 sec)

mysql>insert into rick_test (id, timestamp) values (NULL, '2016-01-01 00:00:00');
Query OK, 1 row affected, 10 warnings (0.00 sec)

mysql>insert into rick_test (id, timestamp) values (NULL, '2016-01-01 00:00:00');
Query OK, 1 row affected, 10 warnings (0.00 sec)

mysql>insert into rick_test (id, timestamp) values (NULL, '2016-01-01 00:00:00');
Query OK, 1 row affected, 10 warnings (0.00 sec)

mysql>insert into rick_test (id, timestamp) values (NULL, '2016-01-01 00:00:00');
Query OK, 1 row affected, 10 warnings (0.00 sec)

mysql>select * from rick_test;
+----+---------------------+------------+-----------+-----------+----------+---------+----------------+-----------+---------------------+----------+------------+-------------+--------------+
| ID | TIMESTAMP | ID2 | ID3 | JM_ROUTE | TRACE_ID | SPAN_ID | PARENT_SPAN_ID | ID_DRIVER | ID_BUSINESS_PROFILE | LANGUAGE | ID5 | REQUEST_XML | RESPONSE_XML |
+----+---------------------+------------+-----------+-----------+----------+---------+----------------+-----------+---------------------+----------+------------+-------------+--------------+
| 2 | 2016-01-01 00:00:00 | NULL | NULL | | | | | | | | | | |
| 4 | 2016-01-01 00:00:00 | NULL | NULL | | | | | | | | | | |
| 6 | 2016-01-01 00:00:00 | NULL | NULL | | | | | | | | | | |
| 8 | 2016-01-01 00:00:00 | NULL | NULL | | | | | | | | | | |
| 10 | 2016-01-01 00:00:00 | NULL | NULL | | | | | | | | | | |
+----+---------------------+------------+-----------+-----------+----------+---------+----------------+-----------+---------------------+----------+------------+-------------+--------------+
5 rows in set (0.00 sec)

mysql>show create table rick_test\G
*************************** 1. row ***************************
       Table: rick_test
Create Table: CREATE TABLE `rick_test` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `TIMESTAMP` datetime NOT NULL,
  `ID2` bigint(20) DEFAULT NULL,
  `ID3` int(11) DEFAULT NULL,
  `JM_ROUTE` varchar(20) NOT NULL,
  `TRACE_ID` varchar(150) NOT NULL,
  `SPAN_ID` varchar(25) NOT NULL,
  `PARENT_SPAN_ID` varchar(25) NOT NULL,
  `ID_DRIVER` varchar(50) NOT NULL,
  `ID_BUSINESS_PROFILE` varchar(50) NOT NULL,
  `LANGUAGE` varchar(2) NOT NULL,
  `ID5` varchar(3) NOT NULL,
  `REQUEST_XML` mediumtext NOT NULL,
  `RESPONSE_XML` mediumtext NOT NULL,
  PRIMARY KEY (`ID`,`TIMESTAMP`),
  KEY `TIMESTAMP` (`TIMESTAMP`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (TO_DAYS(TIMESTAMP))
(PARTITION p37 VALUES LESS THAN (736487) ENGINE = InnoDB,
 PARTITION p38 VALUES LESS THAN (736488) ENGINE = InnoDB,
 PARTITION p39 VALUES LESS THAN (736489) ENGINE = InnoDB,
 PARTITION p40 VALUES LESS THAN (736490) ENGINE = InnoDB,
 PARTITION p41 VALUES LESS THAN (736491) ENGINE = InnoDB,
 PARTITION p42 VALUES LESS THAN (736492) ENGINE = InnoDB,
 PARTITION p43 VALUES LESS THAN (736493) ENGINE = InnoDB,
 PARTITION p44 VALUES LESS THAN (736494) ENGINE = InnoDB,
 PARTITION p45 VALUES LESS THAN (736495) ENGINE = InnoDB,
 PARTITION p46 VALUES LESS THAN (736496) ENGINE = InnoDB,
 PARTITION p47 VALUES LESS THAN (736497) ENGINE = InnoDB,
 PARTITION p48 VALUES LESS THAN (736498) ENGINE = InnoDB,
 PARTITION p49 VALUES LESS THAN (736499) ENGINE = InnoDB,
 PARTITION p50 VALUES LESS THAN (736500) ENGINE = InnoDB,
 PARTITION p51 VALUES LESS THAN (736501) ENGINE = InnoDB,
 PARTITION p52 VALUES LESS THAN (736502) ENGINE = InnoDB,
 PARTITION p53 VALUES LESS THAN (736503) ENGINE = InnoDB,
 PARTITION p54 VALUES LESS THAN (736504) ENGINE = InnoDB,
 PARTITION p55 VALUES LESS THAN (736505) ENGINE = InnoDB,
 PARTITION p56 VALUES LESS THAN (736506) ENGINE = InnoDB,
 PARTITION p57 VALUES LESS THAN (736507) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql>alter table rick_test drop partition p37;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>select * from rick_test;
Empty set (0.01 sec)

mysql>show create table rick_test\G
*************************** 1. row ***************************
       Table: rick_test
Create Table: CREATE TABLE `rick_test` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `TIMESTAMP` datetime NOT NULL,
  `ID2` bigint(20) DEFAULT NULL,
  `ID3` int(11) DEFAULT NULL,
  `JM_ROUTE` varchar(20) NOT NULL,
  `TRACE_ID` varchar(150) NOT NULL,
  `SPAN_ID` varchar(25) NOT NULL,
  `PARENT_SPAN_ID` varchar(25) NOT NULL,
  `ID_DRIVER` varchar(50) NOT NULL,
  `ID_BUSINESS_PROFILE` varchar(50) NOT NULL,
  `LANGUAGE` varchar(2) NOT NULL,
  `ID5` varchar(3) NOT NULL,
  `REQUEST_XML` mediumtext NOT NULL,
  `RESPONSE_XML` mediumtext NOT NULL,
  PRIMARY KEY (`ID`,`TIMESTAMP`),
  KEY `TIMESTAMP` (`TIMESTAMP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (TO_DAYS(TIMESTAMP))
(PARTITION p38 VALUES LESS THAN (736488) ENGINE = InnoDB,
 PARTITION p39 VALUES LESS THAN (736489) ENGINE = InnoDB,
 PARTITION p40 VALUES LESS THAN (736490) ENGINE = InnoDB,
 PARTITION p41 VALUES LESS THAN (736491) ENGINE = InnoDB,
 PARTITION p42 VALUES LESS THAN (736492) ENGINE = InnoDB,
 PARTITION p43 VALUES LESS THAN (736493) ENGINE = InnoDB,
 PARTITION p44 VALUES LESS THAN (736494) ENGINE = InnoDB,
 PARTITION p45 VALUES LESS THAN (736495) ENGINE = InnoDB,
 PARTITION p46 VALUES LESS THAN (736496) ENGINE = InnoDB,
 PARTITION p47 VALUES LESS THAN (736497) ENGINE = InnoDB,
 PARTITION p48 VALUES LESS THAN (736498) ENGINE = InnoDB,
 PARTITION p49 VALUES LESS THAN (736499) ENGINE = InnoDB,
 PARTITION p50 VALUES LESS THAN (736500) ENGINE = InnoDB,
 PARTITION p51 VALUES LESS THAN (736501) ENGINE = InnoDB,
 PARTITION p52 VALUES LESS THAN (736502) ENGINE = InnoDB,
 PARTITION p53 VALUES LESS THAN (736503) ENGINE = InnoDB,
 PARTITION p54 VALUES LESS THAN (736504) ENGINE = InnoDB,
 PARTITION p55 VALUES LESS THAN (736505) ENGINE = InnoDB,
 PARTITION p56 VALUES LESS THAN (736506) ENGINE = InnoDB,
 PARTITION p57 VALUES LESS THAN (736507) ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql>insert into rick_test (id, timestamp) values (NULL, '2016-01-01 00:00:00');
Query OK, 1 row affected, 10 warnings (0.00 sec)

mysql>select * from rick_test;
+----+---------------------+------------+-----------+-----------+----------+---------+----------------+-----------+---------------------+----------+------------+-------------+--------------+
| ID | TIMESTAMP | ID2 | ID5 | JM_ROUTE | TRACE_ID | SPAN_ID | PARENT_SPAN_ID | ID_DRIVER | ID_BUSINESS_PROFILE | LANGUAGE | ID5 | REQUEST_XML | RESPONSE_XML |
+----+---------------------+------------+-----------+-----------+----------+---------+----------------+-----------+---------------------+----------+------------+-------------+--------------+
| 2 | 2016-01-01 00:00:00 | NULL | NULL | | | | | | | | | | |
+----+---------------------+------------+-----------+-----------+----------+---------+----------------+-----------+---------------------+----------+------------+-------------+--------------+
1 row in set (0.00 sec)

[Rick Pizzi (pizzi)]

This is an upstream bug. Raising it there as well

[Sveta Smirnova (svetasmirnova)]

Thank you for the report.

Verified as described.

[Laurynas Biveinis (laurynas-biveinis)]

The upstream resolution is "not a bug". Rick, Sveta, do you agree?

[Rick Pizzi (pizzi)]

Of course I do NOT agree. I have added a comment on the upstream bug.
They seem to not have understood where the problem lies. Their example uses TRUNCATE and we all know that using truncate would reset the autoinc. I did not mention truncate. I am not truncating. If autoinc refers to the table as a whole (which holds true), then this is a bug.

Please see my answer there. I really feel there is need to have a coherent behaviour of autoinc when a partition is dropped regardless how many rows are in the table.

Thanks
Rick

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1719