MySQL Bugs: #94278: Updated auto_increment value is not shown.

Bug #94278	Updated auto_increment value is not shown.
Submitted:	11 Feb 2019 14:54	Modified:	13 Feb 2019 11:43
Reporter:	Jeroen Venderbosch	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	Server version: 8.0.13 - MySQL Community	OS:	MacOS
Assigned to:		CPU Architecture:	Any

Description:
After the auto_increment value of a table is changed by using an 'alter table' statement, the new value is not shown. 

How to repeat:
SET @@SESSION.`information_schema_stats_expiry` = 0;
SHOW TABLE STATUS LIKE 'my_table' => Auto_increment = 45
ALTER TABLE my_table AUTO_INCREMENT = 49
SHOW TABLE STATUS LIKE 'my_table' => Auto_increment = 45
INSERT INTO my_table ..... => auto increment column gets value 49
SHOW TABLE STATUS LIKE 'my_table' => Auto_increment = 45

Thank you for the bug report. Looks like same behavior/root of https://bugs.mysql.com/bug.php?id=93748, please check.

Both 'analyze table my_table' as the SET @@SESSION.`information_schema_stats_expiry` = 0; have no effect. still the cached value is shown.

I checked with 8.0.13 and current GA build 8.0.15 - working as expected:

rm -rf 94278
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/94278 --log-error-verbosity=3
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/94278 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/94278/log.err --log-error-verbosity=3 2>&1 &

SHOW VARIABLES LIKE 'information_schema_stats_expiry';
CREATE DATABASE db1;
USE db1;
CREATE TABLE table1 (
  id int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
);

SET @@SESSION.`information_schema_stats_expiry` = 0;
ALTER TABLE table1 AUTO_INCREMENT = 45;
SHOW TABLE STATUS LIKE 'table1'; -- Auto_increment = 45
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 = 49;
SHOW TABLE STATUS LIKE 'table1'; -- Auto_increment = 45
SELECT t.table_name, t.auto_increment FROM information_schema.tables AS t WHERE t.table_schema = 'db1' AND t.table_name = 'table1';
INSERT INTO table1 values(); -- auto increment column gets value 49
SHOW TABLE STATUS LIKE 'table1'; -- Auto_increment = 45
SELECT t.table_name, t.auto_increment FROM information_schema.tables AS t WHERE t.table_schema = 'db1' AND t.table_name = 'table1';

## 8.0.15/8.0.13
 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.15 MySQL Community Server - GPL

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> SHOW VARIABLES LIKE 'information_schema_stats_expiry';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| information_schema_stats_expiry | 86400 |
+---------------------------------+-------+
1 row in set (0.01 sec)

mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.00 sec)

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

mysql> SET @@SESSION.`information_schema_stats_expiry` = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'information_schema_stats_expiry';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| information_schema_stats_expiry | 0     |
+---------------------------------+-------+
1 row in set (0.00 sec)

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

mysql> SHOW TABLE STATUS LIKE 'table1'; -- Auto_increment = 45
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| 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 |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| table1 | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |             45 | 2019-02-12 12:32:42 | NULL        | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.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 = 'table1';
+------------+----------------+
| TABLE_NAME | AUTO_INCREMENT |
+------------+----------------+
| table1     |             45 |
+------------+----------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE table1 AUTO_INCREMENT = 49;
Query OK, 0 rows affected (0.00 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 = 'table1';
+------------+----------------+
| TABLE_NAME | AUTO_INCREMENT |
+------------+----------------+
| table1     |             49 |
+------------+----------------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'table1'; -- Auto_increment = 45
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| 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 |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| table1 | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |             49 | 2019-02-12 12:33:28 | NULL        | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> INSERT INTO table1 values();
Query OK, 1 row affected (0.01 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     |             50 |
+------------+----------------+
1 row in set (0.00 sec)

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.