MySQL Bugs: #91038: AUTO_INCREMENT does not increase automatically

Bug #91038	AUTO_INCREMENT does not increase automatically
Submitted:	28 May 2018 3:48	Modified:	5 Jun 2018 10:16
Reporter:	KO WEI LEE	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:	8.0.11	OS:	Windows (WIN 10)
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
I exported a table from MYSQL 5.7.15 (using MySQL Workbench with mysqldump.exe 5.7.15.0), and import into MYSQL 8.0.11 Database.

The id(Primary Key) of the table "temp_order"(attached file) is AUTO_INCREMENT using InnoDB.

I use the script below to check the value of AUTO_INCREMENT:

SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'temp_order'
AND table_schema = DATABASE();

Then I inserted a record, and then check AUTO_INCREMENT again.

The id is correct, since it increases by 1.

But AUTO_INCREMENT does not increase, even after I inserted many records, the AUTO_INCREMENT is still the same value.

That is to say, the id will greater than the AUTO_INCREMENT.

How to repeat:
Firstly, check the current AUTO_INCREMENT. Then to insert a record, and then check AUTO_INCREMENT again. You'll see the AUTO_INCREMENT does not increase.

A creating table script

Attachment: mysql-bug-data-91038.zip (application/x-zip-compressed, text), 1.70 MiB.

Hello Lee!

Thank you for the report and test case.

Thanks,
Umesh

-- 8.0.11 - affected

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.11: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test < mysql-bug-data-91038/temp_order.sql
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.11: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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> select max(id) from temp_order;
+---------+
| max(id) |
+---------+
|   68840 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'temp_order' AND table_schema = DATABASE();
+----------------+
| AUTO_INCREMENT |
+----------------+
|          68841 |
+----------------+
1 row in set (0.10 sec)

mysql> show create table temp_order\G
*************************** 1. row ***************************
       Table: temp_order
Create Table: CREATE TABLE `temp_order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
.

) ENGINE=InnoDB AUTO_INCREMENT=68841 DEFAULT CHARSET=utf8 COMMENT='旅团订购'
1 row in set (0.00 sec)

mysql> INSERT INTO `temp_order` VALUES (NULL,'TO68841','C15CBR0724','LADEL','67220','DTCBR','XXXNA','2015-07-24 00:00:00','MR','Kevin','Hui','Hana Yu','ACTV1',7,0,0,0,0,0,0,0,24.30000,20.00000,0.00000,0.00000,0.00000,NULL,0.00000,NULL,0,'20150723120316',0.00000,0.00000,'','2015-07-23 12:03:16',0.00000,35.00000,NULL,'\0',NULL,NULL,NULL,NULL,NULL,'',0,'',0.00000);
Query OK, 1 row affected (0.02 sec)

mysql> select max(id) from temp_order;
+---------+
| max(id) |
+---------+
|   68841 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'temp_order' AND table_schema = DATABASE();
+----------------+
| AUTO_INCREMENT |
+----------------+
|          68841 |
+----------------+
1 row in set (0.01 sec)

^^ Which sounds unsual since in general this is value+1, where value is the largest value for the column currently in the table. 

mysql> show create table temp_order\G
*************************** 1. row ***************************
       Table: temp_order
Create Table: CREATE TABLE `temp_order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
.

) ENGINE=InnoDB AUTO_INCREMENT=68842 DEFAULT CHARSET=utf8 COMMENT='旅团订购'
1 row in set (0.01 sec)

-- 5.7.22 - looks fine

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.22-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> 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> select max(id) from temp_order;
+---------+
| max(id) |
+---------+
|   68840 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'temp_order' AND table_schema = DATABASE();
+----------------+
| AUTO_INCREMENT |
+----------------+
|          68841 |
+----------------+
1 row in set (0.00 sec)

mysql> show create table temp_order\G
*************************** 1. row ***************************
       Table: temp_order
Create Table: CREATE TABLE `temp_order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
.

) ENGINE=InnoDB AUTO_INCREMENT=68841 DEFAULT CHARSET=utf8 COMMENT='旅团订购'
1 row in set (0.00 sec)

mysql> INSERT INTO `temp_order` VALUES (NULL,'TO68841','C15CBR0724','LADEL','67220','DTCBR','XXXNA','2015-07-24 00:00:00','MR','Kevin','Hui','Hana Yu','ACTV1',7,0,0,0,0,0,0,0,24.30000,20.00000
    -> ,0.00000,0.00000,0.00000,NULL,0.00000,NULL,0,'20150723120316',0.00000,0.00000,'','2015-07-23 12:03:16',0.00000,35.00000,NULL,'\0',NULL,NULL,NULL,NULL,NULL,'',0,'',0.00000);
Query OK, 1 row affected (0.00 sec)

mysql> show create table temp_order\G                                                                                                                                                      *************************** 1. row ***************************
       Table: temp_order
Create Table: CREATE TABLE `temp_order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL COMMENT '旅团订购编号',
.
) ENGINE=InnoDB AUTO_INCREMENT=68842 DEFAULT CHARSET=utf8 COMMENT='旅团订购'
1 row in set (0.00 sec)

mysql> SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'temp_order' AND table_schema = DATABASE();                                                                 +----------------+
| AUTO_INCREMENT |
+----------------+
|          68842 |
+----------------+
1 row in set (0.00 sec)

Which sounds all good since in general this is value+1, where value is the largest value for the column currently in the table. 

mysql> select max(id) from temp_order;
+---------+
| max(id) |
+---------+
|   68841 |
+---------+
1 row in set (0.00 sec)

The behavior seem to be correct, based on the default setting
for information_schema_stats_expiry=86400 secs (= 1 day). This is
introduced in 8.0 and not present in 5.7. The expectation stated
in bug page can be observed with setting information_schema_stats_expiry=0.

Please refer following page for more info,
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_in
formation_schema_stats_expiry

Test results that demonstrates the behavior:
````````````````````````````````````````````
Note that the INFORMATION_SCHEMA.TABLES.AUTO_INCREMENT value remains same
until we set information_schema_stats_expiry=0. This will enable I_S to
stop using the cached statistics value and always get latest statistics.

...
CREATE TABLE `temp_order` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT
PRIMARY KEY,
`code` varchar(20) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=68842 ;
INSERT INTO `temp_order` VALUES (NULL, 'abc');
select max(id) from temp_order;
max(id)
68842
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name =
'temp_order' AND table_schema = DATABASE();
AUTO_INCREMENT
68843
INSERT INTO `temp_order` VALUES (NULL, 'abc');
select max(id) from temp_order;
max(id)
68843
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name =
'temp_order' AND table_schema = DATABASE();
AUTO_INCREMENT
68843
INSERT INTO `temp_order` VALUES (NULL, 'abc');
select max(id) from temp_order;
max(id)
68844
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name =
'temp_order' AND table_schema = DATABASE();
AUTO_INCREMENT
68843
set session information_schema_stats_expiry=0;
INSERT INTO `temp_order` VALUES (NULL, 'abc');
select max(id) from temp_order;
max(id)
68845
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name =
'temp_order' AND table_schema = DATABASE();
AUTO_INCREMENT
68846
INSERT INTO `temp_order` VALUES (NULL, 'abc');
select max(id) from temp_order;
max(id)
68846
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name =
'temp_order' AND table_schema = DATABASE();
AUTO_INCREMENT
68847
INSERT INTO `temp_order` VALUES (NULL, 'abc');
select max(id) from temp_order;
max(id)
68847
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name =
'temp_order' AND table_schema = DATABASE();
AUTO_INCREMENT
68848
DROP TABLE `temp_order`;
...

In my test case the error persists:

* Windows 10

Z:\>mysql
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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> DROP TABLE IF EXISTS `temp_order`;
Query OK, 0 rows affected (0.27 sec)

mysql> CREATE TABLE IF NOT EXISTS `temp_order` (
    ->   `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.49 sec)

mysql> SELECT AUTO_INCREMENT
    -> FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_NAME = 'temp_order'
    -> AND TABLE_SCHEMA = DATABASE();
+----------------+
| AUTO_INCREMENT |
+----------------+
|           NULL |
+----------------+
1 row in set (0.05 sec)

mysql> SHOW CREATE TABLE `temp_order`\G
*************************** 1. row ***************************
       Table: temp_order
Create Table: CREATE TABLE `temp_order` (
  `id` int(10) unsigned 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> INSERT INTO `temp_order`
    -> VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);
Query OK, 6 rows affected (0.04 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT MAX(`id`)
    -> FROM `temp_order`;
+-----------+
| MAX(`id`) |
+-----------+
|         6 |
+-----------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE `temp_order`\G
*************************** 1. row ***************************
       Table: temp_order
Create Table: CREATE TABLE `temp_order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 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_NAME = 'temp_order';
+----------------+
| AUTO_INCREMENT |
+----------------+
|           NULL |
+----------------+
1 row in set (0.00 sec)

* Linux

See https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=31b3589425c24a9b869cb05bcef1fda6

Posted by developer:
 
Hi William,

Even after setting information_schema_stats_expiry = 0 , like Gopal has suggested above? It did not show from your repro....

It's correct, after setting information_schema_stats_expiry = 0 everything works as expected. See https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b21ee67940aca7816f528f8f9f527e58.

Posted by developer:
 
OK, so it works as documented then.

setting information_schema_stats_expiry = 0 only works for the current session. If I need to access the uncached value every time I query or every time my application queries information_schema, how would I achieve this?

Posted by developer:
 
As you can see in https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_information_sc... , there is a global and session scope for this variable.
To get the global scope you can use SET GLOBAL, and if you want the setting to survive a server restart you can use SET PERSIST, see https://dev.mysql.com/doc/refman/8.0/en/set-variable.html .

So to get a permanent setting for the server you can use: SET PERSIST information-schema-stats-expiry=0

I tried using both GLOBAL and PERSIST, and neither of those worked.

Bug #96124 marked as duplicate of this one

Related - Bug #103197