MySQL Bugs: #92717: show table status -> auto

Bug #92717	show table status -> auto_increment is not updated
Submitted:	9 Oct 2018 8:45	Modified:	9 Oct 2018 13:41
Reporter:	jan Declercq	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:		OS:	Windows (Microsoft windows server version 1607 (OS Build 14393.2430))
Assigned to:		CPU Architecture:	Other (X86_64)
Tags:	auto_increment, show table status

Description:
I create a new table in Mysql, add some rows, yet the Auto_increment field of show tables still returns NULL.

The mysql manual says: this field should return: "The next Auto_increment value"

https://dev.mysql.com/doc/refman/8.0/en/show-table-status.html

How to repeat:
create schema myTest;
use myTest;

create table `test` (
  `id` int(5) not null auto_increment,
  `name` varchar(256),
  PRIMARY KEY(`id`)
);

insert into test values(null,'name1');
insert into test values(null,'name2');
insert into test values(null,'name3');

insert into test(name) values('name4');
insert into test (name) values('name5');
insert into test (name) values('name6');

select * from test;  
the id field has vaules 1 to 6

show table status where name like 'test';
==> the auto_increment field has value NULL

When restoring a backup (from a previous version), the auto_increment value has te value from the moment the backup was taken, and is also never updated.

Note:

if the first insert is of the type:

insert into test(name) values('name');

instead of:
insert into test values(null,'name');

then this problem does not occur.

Suggested fix:
update the auto_increment field correctly

It is important you don't retry this in an a previous existing schema..

Hello jan Declercq,

Thank you for the report and test case.
Quoting from Dev's response in Bug#91038:

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.

## 8.0.12

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.12 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> show variables like 'information_schema_stats_expiry';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| information_schema_stats_expiry | 86400 |
+---------------------------------+-------+
1 row in set (0.01 sec)

mysql> create schema myTest;
use myTest;

create table `test` (
  `id` int(5) not null auto_increment,
  `name` varchar(256),
  PRIMARY KEY(`id`)
);

insert into test values(null,'name1');
insert into test values(null,'name2');
insert into test values(null,'name3');Query OK, 1 row affected (0.05 sec)

mysql> use myTest;
Database changed
mysql>
mysql> create table `test` (
    ->   `id` int(5) not null auto_increment,
    ->   `name` varchar(256),
    ->   PRIMARY KEY(`id`)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> insert into test values(null,'name1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(null,'name2');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(null,'name3');
Query OK, 1 row affected (0.05 sec)

mysql> show table status where name like 'test'\G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 4 <--------------------- this is correct
    Create_time: 2018-10-09 12:25:21
    Update_time: 2018-10-09 12:25:22
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.04 sec)

mysql> insert into test(name) values('name4');
Query OK, 1 row affected (0.06 sec)

mysql> insert into test (name) values('name5');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test (name) values('name6');
Query OK, 1 row affected (0.02 sec)

mysql> show table status where name like 'test'\G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 4 <-------------not incremented
    Create_time: 2018-10-09 12:25:21
    Update_time: 2018-10-09 12:25:22
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> -- ^^ Auto_increment not updated, with information_schema_stats_expiry = 0 should update
mysql> set information_schema_stats_expiry = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> show table status where name like 'test'\G
*************************** 1. row ***************************
           Name: test
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 6
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 7 <---------- this is correct
    Create_time: 2018-10-09 12:25:21
    Update_time: 2018-10-09 12:25:48
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Could you please check with set information_schema_stats_expiry = 0; and confirm if you are still seeing this issue? Thank you!

regards,
Umesh

Changing the variable does fix this issue.

Apparantly the first time the statistics are updated is when the "show table status" is executed.   So the first time it has correct info. The second time it isn't updated..  confusing at least.
This should require at least a 'note' on this page: https://dev.mysql.com/doc/refman/8.0/en/show-table-status.html

C:\Users\user>mysql -uroot -p --table --verbose < bug92717.sql
Enter password: *****************
--------------
show variables like 'information_schema_stats_expiry'
--------------

+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| information_schema_stats_expiry | 86400 |
+---------------------------------+-------+
--------------
create schema mytest
--------------

--------------
create table `test` (
    `id` int(5) not null auto_increment,
    `name` varchar(256),
        PRIMARY KEY(`id`)
)
--------------

--------------
insert into test values(null,'name1')
--------------

--------------
insert into test values(null,'name2')
--------------

--------------
insert into test values(null,'name3')
--------------

--------------
show table status where name like 'test'
--------------

+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| test | InnoDB |      10 | Dynamic    |    3 |           5461 |       16384 |               0 |            0 |         0 |              4 | 2018-10-09 15:32:15 | 2018-10-09 15:32:16 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
--------------
insert into test values(null,'name3')
--------------

--------------
show table status where name like 'test'
--------------

+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| test | InnoDB |      10 | Dynamic    |    3 |           5461 |       16384 |               0 |            0 |         0 |              4 | 2018-10-09 15:32:15 | 2018-10-09 15:32:16 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
--------------
drop schema mytest
--------------

C:\Users\user>mysql -uroot -p --table --verbose < bug92717.sql
Enter password: *****************
--------------
show variables like 'information_schema_stats_expiry'
--------------

+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| information_schema_stats_expiry | 0     |
+---------------------------------+-------+
--------------
create schema mytest
--------------

--------------
create table `test` (
    `id` int(5) not null auto_increment,
    `name` varchar(256),
        PRIMARY KEY(`id`)
)
--------------

--------------
insert into test values(null,'name1')
--------------

--------------
insert into test values(null,'name2')
--------------

--------------
insert into test values(null,'name3')
--------------

--------------
show table status where name like 'test'
--------------

+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| test | InnoDB |      10 | Dynamic    |    3 |           5461 |       16384 |               0 |            0 |         0 |              4 | 2018-10-09 15:32:49 | 2018-10-09 15:32:49 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
--------------
insert into test values(null,'name3')
--------------

--------------
show table status where name like 'test'
--------------

+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| test | InnoDB |      10 | Dynamic    |    4 |           4096 |       16384 |               0 |            0 |         0 |              5 | 2018-10-09 15:32:49 | 2018-10-09 15:32:49 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
--------------
drop schema mytest
--------------

The Schema cache is welcome feature, but if code depends on realtime autoincrement value then it is problem. Also we have most of project hosted on shared environment so we cannot change this setting at server level. is there any alternative to pick non cache value where needed.