| Bug #81848 | alter table... drop partition resets auto_increment if table empty after alter | ||
|---|---|---|---|
| Submitted: | 14 Jun 2016 13:06 | Modified: | 15 Jun 2016 13:15 |
| Reporter: | Riccardo Pizzi | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S2 (Serious) |
| Version: | 5.6/5.7 | OS: | CentOS (6.7) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | innodb partitioning | ||
[14 Jun 2016 22:27]
MySQL Verification Team
Thank you for the bug report. It's documented when a drop partition is executed all data stored is removed (truncated) so the auto_increment reset has nothing to do with partition: https://dev.mysql.com/doc/refman/5.6/en/alter-table-partition-operations.html "DROP PARTITION can be used to drop one or more RANGE or LIST partitions. This statement cannot... Any data that was stored in the dropped partitions named in the partition_names list is discarded. " C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > " Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.6.32 Source distribution PULL: 2016-MAY-09 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.6 > create database s; Query OK, 1 row affected (0.00 sec) mysql 5.6 > use s Database changed mysql 5.6 > 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 DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.33 sec) mysql 5.6 > insert into rick_test (id, timestamp) values (NULL, '2016-01-01 00:00:00'); Query OK, 1 row affected, 10 warnings (0.09 sec) mysql 5.6 > insert into rick_test (id, timestamp) values (NULL, '2016-01-01 00:00:00'); Query OK, 1 row affected, 10 warnings (0.04 sec) mysql 5.6 > insert into rick_test (id, timestamp) values (NULL, '2016-01-01 00:00:00'); Query OK, 1 row affected, 10 warnings (0.07 sec) mysql 5.6 > insert into rick_test (id, timestamp) values (NULL, '2016-01-01 00:00:00'); Query OK, 1 row affected, 10 warnings (0.07 sec) mysql 5.6 > insert into rick_test (id, timestamp) values (NULL, '2016-01-01 00:00:00'); Query OK, 1 row affected, 10 warnings (0.04 sec) mysql 5.6 > select id, timestamp from rick_test; +----+---------------------+ | id | timestamp | +----+---------------------+ | 1 | 2016-01-01 00:00:00 | | 2 | 2016-01-01 00:00:00 | | 3 | 2016-01-01 00:00:00 | | 4 | 2016-01-01 00:00:00 | | 5 | 2016-01-01 00:00:00 | +----+---------------------+ 5 rows in set (0.00 sec) mysql 5.6 > truncate rick_test; Query OK, 0 rows affected (0.38 sec) mysql 5.6 > insert into rick_test (id, timestamp) values (NULL, '2016-01-01 00:00:00'); Query OK, 1 row affected, 10 warnings (0.05 sec) mysql 5.6 > select id, timestamp from rick_test; +----+---------------------+ | id | timestamp | +----+---------------------+ | 1 | 2016-01-01 00:00:00 | +----+---------------------+ 1 row in set (0.00 sec)
[15 Jun 2016 7:33]
Riccardo Pizzi
Sorry, you seem to have missed the content of this bug report. What is documented, is that when you TRUNCATE a table, the autoinc is reset. We all know this. But I am not truncating anything. I am merely dropping a partition. Not dropping the table. Not truncating the table. Just removing some rows. A partition is not a table; it is a part of it. Auto increment, on the other hand, refers to the table as a whole, and not to the partition. So this is definitely not documented, and definitely a bug in our view. Mind you: in a non partitioned (normal) table, if you delete all rows, the auto_increment is NOT reset. In a partitioned table, if you delete all rows, it is. The behaviour should be the same in both situations. Main problem for us is, since we are archiving older data of this partitioned table into a DWH platform, the reset of autoinc creates duplicate keys there. This only happens if the table happens to be empty at some point in time. We think we should rather have a homogeneous behaviour of auto increment regardless if we have rows in the table or not. Thank you Rick
[15 Jun 2016 11:49]
MySQL Verification Team
Thank you for the feedback.
[15 Jun 2016 12:55]
Marko Mäkelä
Posted by developer: As far as I can tell, the AUTO_INCREMENT sequence is being reset to MAX(autoinc_col) after DROP PARTITION as well as TRUNCATE PARTITION. So, we are always resetting the AUTO_INCREMENT based on the table contents, not only when the table becomes empty. If DROP PARTITION or TRUNCATE PARTITION are considered to be special cases of DELETE, their behaviour is inconsistent with DELETE, which will not reset the AUTO_INCREMENT sequence. Based on this reasoning, DROP/TRUNCATE PARTITION should leave the AUTO_INCREMENT alone. The matter is somewhat complicated by a prior decision that TRUNCATE TABLE (and TRUNCATE PARTITION ALL) should reset AUTO_INCREMENT sequence, for compatibility reasons.
[15 Jun 2016 13:15]
Riccardo Pizzi
Hi, thanks for looking into this. I verified and what you have written above: "As far as I can tell, the AUTO_INCREMENT sequence is being reset to MAX(autoinc_col) after DROP PARTITION" does not hold true. The reset of auto_inc does NOT happen if you drop a partition and there are still rows in the table. Even when the remaining rows have a MAX(id) which is lower than the rows that you drop, the auto_increment remains untouched. See below. mysql> select * from rick_test; +----+---------------------+------+------+----------+----------+---------+----------------+-----------+---------------------+----------+------+-------------+--------------+ | ID | TIMESTAMP | ID2 | ID3 | JM_ROUTE | TRACE_ID | SPAN_ID | PARENT_SPAN_ID | ID_DRIVER | ID_BUSINESS_PROFILE | LANGUAGE | ID_5 | REQUEST_XML | RESPONSE_XML | +----+---------------------+------+------+----------+----------+---------+----------------+-----------+---------------------+----------+------+-------------+--------------+ | 1 | 0000-00-00 00:00:00 | NULL | NULL | | | | | | | | | | | | 2 | 0000-00-00 00:00:00 | NULL | NULL | | | | | | | | | | | | 3 | 0000-00-00 00:00:00 | NULL | NULL | | | | | | | | | | | | 4 | 0000-00-00 00:00:00 | NULL | NULL | | | | | | | | | | | | 7 | 2016-06-15 15:02:37 | NULL | NULL | | | | | | | | | | | | 8 | 2016-06-15 15:02:38 | NULL | NULL | | | | | | | | | | | | 10 | 2016-06-15 15:04:22 | NULL | NULL | | | | | | | | | | | +----+---------------------+------+------+----------+----------+---------+----------------+-----------+---------------------+----------+------+-------------+--------------+ 7 rows in set (0.00 sec) mysql> select * from rick_test partition (p46); +----+---------------------+------+------+----------+----------+---------+----------------+-----------+---------------------+----------+------+-------------+--------------+ | ID | TIMESTAMP | ID2 | ID3 | JM_ROUTE | TRACE_ID | SPAN_ID | PARENT_SPAN_ID | ID_DRIVER | ID_BUSINESS_PROFILE | LANGUAGE | ID_5 | REQUEST_XML | RESPONSE_XML | +----+---------------------+------+------+----------+----------+---------+----------------+-----------+---------------------+----------+------+-------------+--------------+ | 7 | 2016-06-15 15:02:37 | NULL | NULL | | | | | | | | | | | | 8 | 2016-06-15 15:02:38 | NULL | NULL | | | | | | | | | | | | 10 | 2016-06-15 15:04:22 | NULL | NULL | | | | | | | | | | | +----+---------------------+------+------+----------+----------+---------+----------------+-----------+---------------------+----------+------+-------------+--------------+ 3 rows in set (0.00 sec) mysql> alter table rick_test drop partition p46; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from rick_test; +----+---------------------+------+------+----------+----------+---------+----------------+-----------+---------------------+----------+------+-------------+--------------+ | ID | TIMESTAMP | ID2 | ID3 | JM_ROUTE | TRACE_ID | SPAN_ID | PARENT_SPAN_ID | ID_DRIVER | ID_BUSINESS_PROFILE | LANGUAGE | ID_5 | REQUEST_XML | RESPONSE_XML | +----+---------------------+------+------+----------+----------+---------+----------------+-----------+---------------------+----------+------+-------------+--------------+ | 1 | 0000-00-00 00:00:00 | NULL | NULL | | | | | | | | | | | | 2 | 0000-00-00 00:00:00 | NULL | NULL | | | | | | | | | | | | 3 | 0000-00-00 00:00:00 | NULL | NULL | | | | | | | | | | | | 4 | 0000-00-00 00:00:00 | NULL | NULL | | | | | | | | | | | +----+---------------------+------+------+----------+----------+---------+----------------+-----------+---------------------+----------+------+-------------+--------------+ 4 rows in set (0.01 sec) mysql> insert into rick_test values (); Query OK, 1 row affected, 11 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 | ID_5 | REQUEST_XML | RESPONSE_XML | +----+---------------------+------+------+----------+----------+---------+----------------+-----------+---------------------+----------+------+-------------+--------------+ | 1 | 0000-00-00 00:00:00 | NULL | NULL | | | | | | | | | | | | 2 | 0000-00-00 00:00:00 | NULL | NULL | | | | | | | | | | | | 3 | 0000-00-00 00:00:00 | NULL | NULL | | | | | | | | | | | | 4 | 0000-00-00 00:00:00 | NULL | NULL | | | | | | | | | | | | 15 | 0000-00-00 00:00:00 | NULL | NULL | | | | | | | | | | | +----+---------------------+------+------+----------+----------+---------+----------------+-----------+---------------------+----------+------+-------------+--------------+ 5 rows in set (0.00 sec)
[29 Dec 2023 4:35]
Aristotle Po
Just for additional in this bug report as I know it will not be fixed as MySQL 5.7.44 is the final release of the MySQL 5.7 series.
> [15 Jun 2016 12:55] Marko Mäkelä
> Posted by developer:
> As far as I can tell, the AUTO_INCREMENT sequence is being reset to MAX(autoinc_col) after DROP PARTITION as well as TRUNCATE PARTITION.
> So, we are always resetting the AUTO_INCREMENT based on the table contents, not only when the table becomes empty.
The issue does not happen if partition the table after data was already inserted. See my test 2 below :
####################
# -- test 1 : partition during table creation
####################
-- AUTO_INCREMENT got reset
drop table t1 ;
CREATE TABLE `t1` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`ID`)
)
PARTITION BY RANGE (ID)
(PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (200),
PARTITION others VALUES LESS THAN MAXVALUE)
;
insert into t1 values() ;
insert into t1 values(150 ) ;
show create table t1 \G
alter table t1 drop partition p2 ;
show create table t1 \G
####################
# -- test 2 : Partition the table after data was already inserted.
####################
-- AUTO_INCREMENT did not reset
drop table t1 ;
CREATE TABLE t1 ( ID int auto_increment,primary key(ID)) ;
insert into t1 values() ;
insert into t1 values(150 ) ;
ALTER TABLE t1
PARTITION BY RANGE (ID)
(PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (200),
PARTITION others VALUES LESS THAN MAXVALUE)
;
select * from t1;
show create table t1 \G
alter table t1 drop partition p2 ;
show create table t1 \G
####################
# Test 2 : output
####################
Server version: 5.7.44-log MySQL Community Server (GPL)
mysql [localhost:5744] {msandbox} (test) > select * from t1;
+-----+
| ID |
+-----+
| 1 |
| 150 |
+-----+
2 rows in set (0.00 sec)
mysql [localhost:5744] {msandbox} (test) > show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=151 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (ID)
(PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (200) ENGINE = InnoDB,
PARTITION others VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
mysql [localhost:5744] {msandbox} (test) > alter table t1 drop partition p2 ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:5744] {msandbox} (test) > show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=151 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (ID)
(PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
PARTITION others VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
Description: In a partitioned table (in this case by RANGE), if you drop an older partition (eg to enforce data retention) and the table has zero rows after the drop, auto_increment value gets reset. This is not the expected behaviour. How to repeat: mysql> 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 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.05 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 | ID_5 | REQUEST_XML | RESPONSE_XML | +----+---------------------+------+------+----------+----------+---------+----------------+-----------+---------------------+----------+------+-------------+--------------+ | 1 | 2016-01-01 00:00:00 | NULL | NULL | | | | | | | | | | | | 2 | 2016-01-01 00:00:00 | NULL | NULL | | | | | | | | | | | | 3 | 2016-01-01 00:00:00 | NULL | NULL | | | | | | | | | | | | 4 | 2016-01-01 00:00:00 | NULL | NULL | | | | | | | | | | | | 5 | 2016-01-01 00:00:00 | NULL | NULL | | | | | | | | | | | +----+---------------------+------+------+----------+----------+---------+----------------+-----------+---------------------+----------+------+-------------+--------------+ 5 rows in set (0.00 sec) mysql> alter table rick_test drop partition p37; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 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, `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 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.01 sec) mysql> select * from rick_test; Empty 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 | ID3 | JM_ROUTE | TRACE_ID | SPAN_ID | PARENT_SPAN_ID | ID_DRIVER | ID_BUSINESS_PROFILE | LANGUAGE | ID_5 | REQUEST_XML | RESPONSE_XML | +----+---------------------+------+------+----------+----------+---------+----------------+-----------+---------------------+----------+------+-------------+--------------+ | 1 | 2016-01-01 00:00:00 | NULL | NULL | | | | | | | | | | | +----+---------------------+------+------+----------+----------+---------+----------------+-----------+---------------------+----------+------+-------------+--------------+ 1 row in set (0.00 sec) Suggested fix: auto_increment should NOT be reset by a drop partition statement