Support lowering AUTO_INCREMENT in ALTER TABLE t AUTO_INCREMENT = N

[mjonss]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t (id bigint unsigned not null auto_increment primary key, c varchar(255));
insert into t (c) values ("first"), ("second");
insert into t values (10000000, "10M");
delete from t where id = 10000000;
alter table t auto_increment = 10;
show create table t;
insert into t (c) values ("last");
select * from t;

This can be useful when some one accidentally added a very high auto_increment value, or after testing AUTO_RANDOM and want to go back to AUTO_INCREMENT (after removing those AUTO_RANDOM values).

2. What did you expect to see? (Required)

mysql> create table t (id bigint unsigned not null auto_increment primary key, c varchar(255));
Query OK, 0 rows affected (0,01 sec)

mysql> insert into t (c) values ("first"), ("second");
Query OK, 2 rows affected (0,01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t values (10000000, "10M");
Query OK, 1 row affected (0,00 sec)

mysql> delete from t where id = 10000000;
Query OK, 1 row affected (0,00 sec)

mysql> alter table t auto_increment = 10;
Query OK, 0 rows affected (0,00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `c` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql> insert into t (c) values ("last");
Query OK, 1 row affected (0,00 sec)

mysql> select * from t;
+----+--------+
| id | c      |
+----+--------+
|  1 | first  |
|  2 | second |
| 10 | last   |
+----+--------+
3 rows in set (0,00 sec)

MySQL will lower the AUTO_INCREMENT value to the minimum of (provided number, max(id)+1)

3. What did you see instead (Required)

tidb> create table t (id bigint unsigned not null auto_increment primary key, c varchar(255));
Query OK, 0 rows affected (0,01 sec)

tidb> insert into t (c) values ("first"), ("second");
Query OK, 2 rows affected (0,00 sec)
Records: 2  Duplicates: 0  Warnings: 0

tidb> insert into t values (10000000, "10M");
Query OK, 1 row affected (0,00 sec)

tidb> delete from t where id = 10000000;
Query OK, 1 row affected (0,00 sec)

tidb> alter table t auto_increment = 10;
Query OK, 0 rows affected (0,00 sec)

tidb> show create table t;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `c` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=12000001 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

tidb> insert into t (c) values ("last");
Query OK, 1 row affected (0,00 sec)

tidb> select * from t;
+----------+--------+
| id       | c      |
+----------+--------+
|        1 | first  |
|        2 | second |
| 12000001 | last   |
+----------+--------+
3 rows in set (0,00 sec)

4. What is your TiDB version? (Required)

Release Version: v5.5.0-alpha-240-g23f7e51ae0
Edition: Community
Git Commit Hash: 23f7e51ae01287fa7f811e1462d8987e4d7727a4
Git Branch: master
UTC Build Time: 2022-01-27 15:36:52
GoVersion: go1.17
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[bb7133]

I think we have supported the requirement in: #25400

[bb7133]

Close this issue because of #25400.