MySQL Bugs: #1830: Identity gaps

Bug #1830	Identity gaps
Submitted:	13 Nov 2003 12:26	Modified:	15 Nov 2003 1:19
Reporter:	Jevgenij Obzigailov	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.16	OS:	Windows (Windows 2000 Server)
Assigned to:		CPU Architecture:	Any

Description:
I've noticed strange behaviour of MyISAM table auto_increment field (used as primary key): suppose we have 344500, and next generated number is not 344501 but something like 900654. This is very annoying, appears chaotically and is unpredictable. Table is relatively small (270k records) and very slowly updated by concurrent 5-6 users: about 100-150 records daily. Users work with MS Access 2k SP3 with latest Jet SP and MDAC installed. Similar case is described in this SyBase-related article: http://www.sypron.nl/idgaps_txt.html

How to repeat:
I don't know how to repeat this error, it appears chaotically on both 10 records and 270000 records tables.

Suggested fix:
I suggest MySQL developer team to add option like "slow insert": each time before inserting new record engine reads maximum value from disk. It takes milliseconds on indexed field and will not interrupt work too much in many cases. Of course there must be possibility to programmatically enable this variable without the need to restart DB server.

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

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.

Please provide repeatable test case if possible.
There is no way to test and fix chaotically occuring problem.

Make sure that no session inserts/updates value higher than current Last_insert_id for table.

Suppose you have Last_insert_id = 100
Suppose there is something (application or user) which does:
INSERT ... ID=150
DELETE ... WHERE ID=150;

Now Last_insert_id is set to 150

Your suggestion to read MAX value can't be implemented as it will break above rule and will lead to reusable values which is undesired for Auto_increment.