alter table... drop partition resets auto_increment if table becomes empty
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.5 |
Triaged
|
Medium
|
Unassigned | |||
5.6 |
Triaged
|
Medium
|
Unassigned | |||
5.7 |
Triaged
|
Medium
|
Unassigned |
Bug Description
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
auto_increment
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_
`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(
(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
*******
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_
`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(
(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
*******
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_
`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(
(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)
This is an upstream bug. Raising it there as well