Bug #94278 Updated auto_increment value is not shown.
Submitted: 11 Feb 14:54 Modified: 13 Feb 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:Mac OS X
Assigned to: CPU Architecture:Any

[11 Feb 14:54] Jeroen Venderbosch
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
[11 Feb 15:39] Miguel Solorzano
Thank you for the bug report. Looks like same behavior/root of https://bugs.mysql.com/bug.php?id=93748, please check.
[11 Feb 16:00] Jeroen Venderbosch
Both 'analyze table my_table' as the SET @@SESSION.`information_schema_stats_expiry` = 0; have no effect. still the cached value is shown.
[13 Feb 11:43] Umesh Shastry
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.