Description:
AUTO_INCREMENT Property is expected to add 1 once,but in the situation below it Increases wrong value.
How to repeat:
CREATE TABLE t1 (f1 INT);
INSERT INTO t1 VALUES (1), (3);
CREATE TABLE t2(f1 int, f2 int auto_increment unique key) AS SELECT * FROM t1;
show create table t3;
context will be :
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t3 | CREATE TABLE `t3` (
`f2` int NOT NULL AUTO_INCREMENT,
`f1` int DEFAULT NULL,
UNIQUE KEY `f2` (`f2`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
we can find the AUTO_INCREMENT=4, but we expect AUTO_INCREMENT=3.
Suggested fix:
null
