MySQL Bugs: #104963: can't set auto_inc value to 1 in an empty table in MySQL8.0

Bug #104963	can't set auto_inc value to 1 in an empty table in MySQL8.0
Submitted:	16 Sep 2021 20:13	Modified:	27 Sep 2021 6:43
Reporter:	Zongzhi Chen (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In MySQL 8.0, InnoDB can't set auto_inc value to 1 in an empty table. 
Howeveer it's possible to set to 2, 3 or some other values.

And In MySQL 5.6, the same command can set the auto_inc to 1.

How to repeat:
create table t1 (id int auto_increment primary key ) auto_increment=100;

alter table t1 auto_increment = 1;
show create table t1;

This is the output

```
mysql> create table t1 (id int auto_increment primary key ) auto_increment=100;
Query OK, 0 rows affected (0.02 sec)

mysql> alter table t1 auto_increment = 1;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 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=100 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table t1 auto_increment = 10;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> 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=10 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
```

Hello chen,

Thank you for the report and test case.
My apologies for the delay, I quickly tried 5.6(first GA i.e. 5.6.10 and last GA in 5.6.x series i.e. 5.6.51) along with current GA's 5.7/8.0 - behavior is consistent across these releases i.e. auto_inc is set to 1 after "alter table t1 auto_increment = 1;" but it is not displayed since by default, the starting value for AUTO_INCREMENT is 1 but for >1 values it is displayed as you can see in below test results.  

--
- 5.6.10(first GA)/5.6.51(last GA) and same is in 5.7.35 (in 8.0 - you have to set set information_schema_stats_expiry=0; otherwise I_S will list old values)
 
mysql> create database if not exists test;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> use test;
Database changed
mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t1 (id int auto_increment primary key ) auto_increment=100;
Query OK, 0 rows affected (0.01 sec)

mysql> 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=100 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table t1 auto_increment = 1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 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 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT `AUTO_INCREMENT` FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND   TABLE_NAME   = 't1';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

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

mysql> 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=10 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT `AUTO_INCREMENT` FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND   TABLE_NAME   = 't1';
+----------------+
| AUTO_INCREMENT |
+----------------+
|             10 |
+----------------+
1 row in set (0.00 sec)

- 8.0.26

bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> set information_schema_stats_expiry=0;
Query OK, 0 rows affected (0.00 sec)

mysql> create database if not exists test;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1 (id int auto_increment primary key ) auto_increment=100;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> SELECT `AUTO_INCREMENT` FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND   TABLE_NAME   = 't1';
+----------------+
| AUTO_INCREMENT |
+----------------+
|            100 |
+----------------+
1 row in set (0.00 sec)

mysql> alter table t1 auto_increment = 1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> SELECT `AUTO_INCREMENT` FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND   TABLE_NAME   = 't1';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

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

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> SELECT `AUTO_INCREMENT` FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND   TABLE_NAME   = 't1';
+----------------+
| AUTO_INCREMENT |
+----------------+
|             10 |
+----------------+
1 row in set (0.00 sec)

regards,
Umesh