Description:
The behavior of auto-increment value after ALTER TABLE EXCHANGE PARTITION is inconsistent.
How to repeat:
CREATE TABLE t1 (a int unsigned auto_increment primary key, b varchar(64));
CREATE TABLE t2 (
a int UNSIGNED PRIMARY KEY AUTO_INCREMENT,
b varchar(64)
) PARTITION BY LIST (a) (
PARTITION p1 VALUES IN (1, 3, 5, 7, 9),
PARTITION p2 VALUES IN (4, 6, 8));
INSERT INTO t1 VALUES (1, "t1"), (5, "t1");
INSERT INTO t2 VALUES (3, "p1"), (4, "p2"), (7, "p1"), (8, "p2");
Here, if we directly run `ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;` and check the table definition of table `t1`, the result is:
mysql-8.0.26 > SHOW CREATE TABLE t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`a` int unsigned NOT NULL AUTO_INCREMENT,
`b` varchar(64) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
The auto-increment value of table `t1` is 8.
But if we run `ALTER TABLE t2 ADD PARTITION (PARTITION p3 VALUES IN (0, 2));` before the EXCHANGE PARTITION command. The result will be different:
mysql-8.0.26 > SHOW CREATE TABLE t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`a` int unsigned NOT NULL AUTO_INCREMENT,
`b` varchar(64) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
The auto-increment value of table `t1` will be 9, which is inconsistent with the above result.
Description: The behavior of auto-increment value after ALTER TABLE EXCHANGE PARTITION is inconsistent. How to repeat: CREATE TABLE t1 (a int unsigned auto_increment primary key, b varchar(64)); CREATE TABLE t2 ( a int UNSIGNED PRIMARY KEY AUTO_INCREMENT, b varchar(64) ) PARTITION BY LIST (a) ( PARTITION p1 VALUES IN (1, 3, 5, 7, 9), PARTITION p2 VALUES IN (4, 6, 8)); INSERT INTO t1 VALUES (1, "t1"), (5, "t1"); INSERT INTO t2 VALUES (3, "p1"), (4, "p2"), (7, "p1"), (8, "p2"); Here, if we directly run `ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1;` and check the table definition of table `t1`, the result is: mysql-8.0.26 > SHOW CREATE TABLE t1; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `a` int unsigned NOT NULL AUTO_INCREMENT, `b` varchar(64) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ The auto-increment value of table `t1` is 8. But if we run `ALTER TABLE t2 ADD PARTITION (PARTITION p3 VALUES IN (0, 2));` before the EXCHANGE PARTITION command. The result will be different: mysql-8.0.26 > SHOW CREATE TABLE t1; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `a` int unsigned NOT NULL AUTO_INCREMENT, `b` varchar(64) DEFAULT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ The auto-increment value of table `t1` will be 9, which is inconsistent with the above result.