MySQL Bugs: #118893: "SELECT AUTO_INCREMENT FROM information_schema.TABLES" reports a value that is reduced by one

Bug #118893	"SELECT AUTO_INCREMENT FROM information_schema.TABLES" reports a value that is reduced by one
Submitted:	26 Aug 8:31	Modified:	1 Sep 8:57
Reporter:	Jonathan Haas	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	9.4.0-1.el9	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Requesting the auto increment value after creating a table without doing anything else in-between results in an off-by-one value.

If you execute

CREATE TABLE `data_test` (`pid` int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`pid`)) AUTO_INCREMENT=42;

and then

SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA='test_db' AND TABLE_NAME='data_test';

if (incorrectly) returns 41

if you do a 
SHOW TABLE STATUS LIKE 'data_test';
in-between SELECT AUTO_INCREMENT returns 42 (correctly)

The issue also happens in older versions such as 8.0.40

How to repeat:
git clone https://gitlab.com/haasjona/mysql-test and execute the test.bash. It should print 41.

Then uncomment the SHOW TABLE STATUS LIKE 'data_test'; line in db-init/10-data_test.schema.sql and it should print 42 instead.

Suggested fix:
SHOW TABLE STATUS should be a reading operation and not change the returned auto increment value.

The auto increment value should resemble the next inserted id and should always display 42 in that testcase.

Inserting a data set already correctly uses the id 42.

I don't have such behavior:

 SQL  select version();
+-----------+
| version() |
+-----------+
| 9.4.0     |
+-----------+
1 row in set (0.0004 sec)

 MySQL   localhost   test  2025-08-26 11:20:45  
 SQL  show create table data_test \G
*************************** 1. row ***************************
       Table: data_test
Create Table: CREATE TABLE `data_test` (
  `pid` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0008 sec)

 MySQL   localhost   test  2025-08-26 11:20:47  
 SQL  SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA='test' AND TABLE_NAME='data_test';
+----------------+
| AUTO_INCREMENT |
+----------------+
|             42 |
+----------------+
1 row in set (0.0016 sec)

@Frederic Descamps

I suspect the issue doen't happen in interactive environment due to caching/timing issues or something like that. The docker test project I linked prints

```
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Waiting for user setup etc...
The auto increment value is:
mysql: [Warning] Using a password on the command line interface can be insecure.
41
Shutting down...
```

and I don't see it running anything else than these commands.

For having a cache somewhere, it would have meant that the value would have been 41 at a moment, which is not the case in this scenario.

What is the value of information_schema_stats_expiry ?

Hello from MySQL User group.

Did you try information_schema_stats_expiry=0?
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_information_sc...

> Did you try information_schema_stats_expiry=0?

Doesn't change anything. Neither does setting a large value explicitly.

Thanks,

I originally understood that you are getting a higher value (mysql eating one) than expected and that is expected behavior but I see now that you get lower value than expected and that definitely looks like a bug. Thanks for the test case.