MySQL Bugs: #93748: AUTO_INCREMENT Changes only once

Bug #93748	AUTO_INCREMENT Changes only once
Submitted:	27 Dec 2018 13:54	Modified:	4 Jan 2019 14:30
Reporter:	dbForge Team	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:	8.0.11	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
AUTO_INCREMENT Changes only once.

How to repeat:
CREATE DATABASE db1;

USE db1;

CREATE TABLE table1 (
  id int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
);

ALTER TABLE table1 AUTO_INCREMENT = 10;
SELECT t.table_name, t.auto_increment FROM information_schema.tables AS t WHERE t.table_schema = 'db1' AND t.table_name = 'table1';

ALTER TABLE table1 AUTO_INCREMENT = 2;
SELECT t.table_name, t.auto_increment FROM information_schema.tables AS t WHERE t.table_schema = 'db1' AND t.table_name = 'table1';

Expected:
AUTO_INCREMENT must be set 2

Actual:
AUTO_INCREMENT equal 10

Suggested fix:
AUTO_INCREMENT must contain the last modified value.

Thank you for the bug report.
C:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > " --default-character-set=utf8mb4
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.15 Source distribution BUILD: 2018-DEC-11

Copyright (c) 2000, 2019, 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 8.0 > CREATE DATABASE db1;
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > USE db1;
Database changed
mysql 8.0 > CREATE TABLE table1 (
    ->   id int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql 8.0 > ALTER TABLE table1 AUTO_INCREMENT = 10;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 8.0 > SELECT t.table_name, t.auto_increment FROM information_schema.tables AS t WHERE t.table_schema = 'db1' AND t.table_name = 'table1';
+------------+----------------+
| TABLE_NAME | AUTO_INCREMENT |
+------------+----------------+
| table1     |             10 |
+------------+----------------+
1 row in set (0.01 sec)

mysql 8.0 > SHOW CREATE TABLE table1\G
*************************** 1. row ***************************
       Table: table1
Create Table: CREATE TABLE `table1` (
  `id` int(11) 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 8.0 > ALTER TABLE table1 AUTO_INCREMENT = 2;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 8.0 > SELECT t.table_name, t.auto_increment FROM information_schema.tables AS t WHERE t.table_schema = 'db1' AND t.table_name = 'table1';
+------------+----------------+
| TABLE_NAME | AUTO_INCREMENT |
+------------+----------------+
| table1     |             10 |
+------------+----------------+
1 row in set (0.00 sec)

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

mysql 8.0 > exit
Bye

C:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > " --default-character-set=utf8mb4
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.15 Source distribution BUILD: 2018-DEC-11

Copyright (c) 2000, 2019, 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 8.0 > SELECT t.table_name, t.auto_increment FROM information_schema.tables AS t WHERE t.table_schema = 'db1' AND t.table_name = 'table1';
+------------+----------------+
| TABLE_NAME | AUTO_INCREMENT |
+------------+----------------+
| table1     |             10 |
+------------+----------------+
1 row in set (0.01 sec)

mysql 8.0 > SHOW CREATE TABLE db1.table1\G
*************************** 1. row ***************************
       Table: table1
Create Table: CREATE TABLE `table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql 8.0 > use db1;
Database changed
mysql 8.0 > insert into table1 values (NULL);
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > SELECT * FROM table1;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

mysql 8.0 > SELECT t.table_name, t.auto_increment FROM information_schema.tables AS t WHERE t.table_schema = 'db1' AND t.table_name = 'table1';
+------------+----------------+
| TABLE_NAME | AUTO_INCREMENT |
+------------+----------------+
| table1     |             10 |
+------------+----------------+
1 row in set (0.00 sec)

https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

Note

ALTER TABLE ... AUTO_INCREMENT = N can only change the auto-increment counter value to a value larger than the current maximum.

This is because the information_schema statistics are cached:

information_schema_stats_expiry = 86400

If you run:

mysql> analyze table table1;
+------------+---------+----------+----------+
| Table      | Op      | Msg_type | Msg_text |
+------------+---------+----------+----------+
| db1.table1 | analyze | status   | OK       |
+------------+---------+----------+----------+
1 row in set (0.82 sec)

mysql> SELECT t.table_name, t.auto_increment FROM information_schema.tables AS t WHERE t.table_schema = 'db1' AND t.table_name = 'table1';
+------------+----------------+
| TABLE_NAME | AUTO_INCREMENT |
+------------+----------------+
| table1     |              2 |
+------------+----------------+
1 row in set (0.03 sec)

See this test:

mysql> SET  information_schema_stats_expiry=10;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> CREATE TABLE table2 (
    ->   id int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.96 sec)

mysql> 
mysql> ALTER TABLE table2 AUTO_INCREMENT = 10;
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT t.table_name, t.auto_increment FROM information_schema.tables AS t WHERE t.table_schema = 'db1' AND t.table_name = 'table2';
+------------+----------------+
| TABLE_NAME | AUTO_INCREMENT |
+------------+----------------+
| table2     |             10 |
+------------+----------------+
1 row in set (0.04 sec)

mysql> 
mysql> ALTER TABLE table2 AUTO_INCREMENT = 2;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT t.table_name, t.auto_increment FROM information_schema.tables AS t WHERE t.table_schema = 'db1' AND t.table_name = 'table2';
+------------+----------------+
| TABLE_NAME | AUTO_INCREMENT |
+------------+----------------+
| table2     |             10 |
+------------+----------------+
1 row in set (0.00 sec)

mysql> select sleep(10);
+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.00 sec)

mysql> SELECT t.table_name, t.auto_increment FROM information_schema.tables AS t WHERE t.table_schema = 'db1' AND t.table_name = 'table2';
+------------+----------------+
| TABLE_NAME | AUTO_INCREMENT |
+------------+----------------+
| table2     |              2 |
+------------+----------------+
1 row in set (0.06 sec)

Posted by developer:
 
Based on the explanation from Lefred, I am closing as "not a bug"