I have the need to get last id (primary key) of a table (InnoDB), and to do so I perform the following query:
SELECT (SELECT `AUTO_INCREMENT` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = 'mySchema' AND `TABLE_NAME` = 'myTable') - 1;
which returns the wrong AUTO_INCREMENT. The problem is the TABLES table of information_schema is not updated with the current value, unless I run the following query:
ANALYZE TABLE `myTable`;
Why doesn't MySQL update information_schema automatically, and how could I fix this behavior?
Running MySQL Server 8.0.13 X64.
Q: Why doesn't MySQL update information_schema automatically, and how could I fix this behavior?
A: InnoDB holds the auto_increment value in memory, and doesn't persist that to disk.
Behavior of metadata queries (e.g. SHOW TABLE STATUS) is influenced by setting of innodb_stats_on_metadata and innodb_stats_persistent variables.
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata
Forcing an ANALYZE everytime we query metadata can be a drain on performance.
Other than the settings of those variables, or forcing statistics to be collected by manually executing the ANALYZE TABLE, I don't think there's a "fix" for the issue.
(I think that mostly because I don't think it's a problem that needs to be fixed.)
To get the highest value of an auto_increment column in a table, the normative pattern is:
SELECT MAX(`ai_col`) FROM `myschema`.`mytable`
What puzzles me is why we need to retrieve this particular piece of information. What are we going to use it for?
Certainly, we aren't going to use that in application code to determine a value that was assigned to a row we just inserted. There's no guarantee that the highest value isn't from a row that was inserted by some other session. And we have LAST_INSERT_ID() mechanism to retrieve the value of a row our session just inserted.
If we go with the ANALYZE TABLE to refresh statistics, there's still a small some time between that and a subsequent SELECT... another session could slip in another INSERT so that the value we get from the gather stats could be "out of date" by the time we retrieve it.