MySQL Bugs: #74112: Create table loses AUTO_INCREMENT=N options when table_open

Bug #74112	Create table loses AUTO_INCREMENT=N options when table_open_cache is full
Submitted:	26 Sep 2014 22:05	Modified:	3 Oct 2014 17:05
Reporter:	Gavin Towey	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.6.19, 5.6.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Where there are enough open tables to fill up the table cache, newly created tables like the following:

create table test ( a int unsigned not null auto_increment primary key) AUTO_INCREMENT=100;

Do not get opened as part of table creation.  The next time the table is used, ha_innobase::open is called, which calls innobase_initialize_autoinc().  This sets the auto increment by selecting the max PK value from the table.  Since the table is empty, the correct table option is lost.

How to repeat:

Run a script to create & use tables until the cache is full:
#!/usr/bin/env bash
for i in {1..4096}
do 
  mysql test -e \
  "
drop table if exists test$i;
create table test$i ( a int unsigned not null auto_increment primary key) AUTO_INCREMENT=100;
select * from test$i;"
done

mysql> SHOW OPEN TABLES;
+----------------+-----------------------------+--------+-------------+
| Database       | Table                       | In_use | Name_locked |
+----------------+-----------------------------+--------+-------------+
| test           | test2931                    |      0 |           0 |
...
| test           | test359                     |      0 |           0 |
+----------------+-----------------------------+--------+-------------+
2007 rows in set (0.01 sec)

mysql> create table test ( a int unsigned not null auto_increment primary key) AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.08 sec)

mysql> show create table test \G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

Suggested fix:
There are probably a couple ways to handle this:

1. The real problem is that there is info in the table structure which is never persisted and can be different the next time the table is opened.  dict_table_t->autoinc can be set to an arbitrary value by the user, but it will be lost if the table is closed. This would not be a trivial problem to fix though.

2. Newly created tables should at least remain open without the need to call ha_innobase::open.  I didn't dig into the table cache deeply but since this issue doesn't occur when the table cache isn't full, it seems that there may be an issue with how tables are evicted from the table cache.  I would think that a new table create would cause an old entry to be evicted if the table cache is full.

Hello Gavin Towey,

Thank you for the report and test case.

Thanks,
Umesh

// 5.6.22

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.22                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.22-enterprise-commercial-advanced-log               |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

// with 1..1024 count in provided script

mysql> create table test ( a int unsigned not null auto_increment primary key) AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> drop database test;
Query OK, 1025 rows affected (8.83 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

//// with 1..2048 count in provided script

mysql> use test
Database changed
mysql> create table test ( a int unsigned not null auto_increment primary key) AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

I have some additional info.  It looks like this has less to do with the table_open_cache and more the table_definition_cache.  By doing SET GLOBAL table_definition_cache=10000; the issue goes away.  This still should be considered a bug because it can "data loss" in terms of the table definition.