We've recently noticed the following scenario happening in many of our databases:
- A record is inserted, and receives an AUTO_INCREMENT primary key.
- The record is deleted.
- Another record is inserted, and receives the same AUTO_INCREMENT id as the deleted one.
I'm aware that this will happen for InnoDB tables (which is what these are) if MySQL restarts between setps 2 and 3, but it is not restarting. Is there any other event that will cause the AUTO_INCREMENT counter to be re-initialised? The time period between 2 and 3 can be a few minutes or up to a month.
Over the past month we've had some performance issues that we have so far resolved my increasing table_open_cache. The server has up to 8000 databases in use at any one time, and we found that we had a lot of processes in the "Opening tables" and "Closing tables" states. Could this be related?
Edit in response to Rick James' comment regarding FLUSH TABLES
The MySQL documentation (under "InnoDB AUTO_INCREMENT Counter Initialization") states that:
If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk.
This is the counter that is initialised to
MAX(id) + 1 on restart. However, the glossary entry for the data dictionary states that:
This metadata is physically located in the InnoDB system tablespace.
This in turn links to the glossary entry for the system tablespace, which states that it is stored in "One or more data files (ibdata files)". This is contradictory but I'm inclined to believe the InnoDB AUTO_INCREMENT documentation. If the counter were stored on disk in the system tablespace, there would be no need to initialise it with
MAX(id) + 1.
If this is the case, then FLUSH TABLES would have no effect, but if the counter is persisted to the system tablespace then it should work. Does anybody know which part of the documentation is correct?