MySQL Bugs: #104330: SHOW TABLE STATUS returns incorrect AUTO

Bug #104330	SHOW TABLE STATUS returns incorrect AUTO_INCREMENT value
Submitted:	16 Jul 2021 5:56	Modified:	16 Jul 2021 11:45
Reporter:	Michal Ševčenko	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.25	OS:	Any
Assigned to:		CPU Architecture:	x86 (Win64 on x86_64 (MySQL Community Server - GPL))
Tags:	auto_increment

Description:
When attempting to read AUTO_INCREMENT state of a table A, using SHOW TABLE STATUS, wrong value is returned (old, i.e. lower value, or even NULL).

SHOW CREATE TABLE returns correct AUTO_INCREMENT value.

Worked fine in 5.7, does not work in 8.

How to repeat:
1. create table

CREATE TABLE `A` (
	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`)
);

2. insert some values

insert into A (id) values(default)

3. read AUTO_INCREMENT

SHOW TABLE STATUS WHERE NAME='A';

or use

SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='A'

both would return NULL AUTO_INCREMENT

4. compare it with SHOW CREATE TABLE A;

This would return the correct value in AUTO_INCREMENT

Applies to InndoDB engine.

Likely a duplicate of #91038.

Hi Mr.  Ševčenko,

Thank you for your bug report.

And you are quite right that this report is a duplicate of the mentioned bug report.

And as it says nicely, this is not a bug.

In 8.0, some changes have been introduced and all that you need to do is set the variable named information_schema_stats_expiry to 0 and you will get better results.

Not a bug.

Hi Mr.  Ševčenko,

Thank you for your bug report.

And you are quite right that this report is a duplicate of the mentioned bug report.

And as it says nicely, this is not a bug.

In 8.0, some changes have been introduced and all that you need to do is set the variable named information_schema_stats_expiry to 0 and you will get better results.

Not a bug.