The current auto_increment behavior in the InnoDB engine is sub-optimal. As it currently functions, the auto_increment value is stored until the server shuts down or resets, and then is rebuilt based on values in the table when it starts up again. Furthermore, in 5.6 this ought to become even worse, because tables can be evicted from the InnoDB data dictionary cache. We may get a too low auto-increment value even without shutdown/restart. When a table is evicted, InnoDB will forget the current auto-increment value, and it will do SELECT MAX(auto_inc_column) next time when the table is accessed.

Plan (in high level):

— create data dictionary table to store auto_increment column and its current value 1 week
— create functions to automatically create auto_increment object/drop auto increment object when 2 days
table is created/dropped
— create a procedure to update persistent auto increment column values at dictionary 2 days
— create functions to read, store, peek and set auto_increment column value persistently 3 days
— create a function to migrate current dictionary to dictionary containing persistent auto increments 2 days
— create a function/tool/script to read current database and create necessary persistent auto_increment fields
(this might not be 100% correct on all possible cases) 1 week
— add test cases to test suite + testing 1 week

I might be here little bit pessimistic but those are not calendar days, they are work days i.e. total work cost ~5 weeks.

Concerns:

— there is some cost to store auto-increment value to persistent storage and this cost will be then per row operation (inserts mostly)