Fun with MySQL autoincrement

Imagine the following scenario: you have a table with an autoincrement primary key. Let’s name it id and assume it is an UNSIGNED INT. See for details.  id can have values between 0 and 4294967295.

Let’s set up some a test table in a test database named mytest:

`val` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT ”,

If we set the id to the max possible value of 4294967295, MySQL throws an exception for duplicate id when trying to insert the next row.

With the following select we get the value for auto increment:

SELECT auto_increment FROM `TABLES` WHERE table_schema = ‘mytest’ and table_name = ‘test_first’;

Now we have a table running out of ids. Let’s imagine you are not allowed to run an alter table but that the table is not completely filled. Imagine there are gaps between rows. Assume also, that something like a defragmentation of the table with rewriting the data is out of the question. Further assume that your diskspace is limited and the (will take a looong time to do ) copy data to a table with BIGINT index and do RENAME TABLE is also out of the question.

What can we do? Dropping data with higher ids and eventually restarting the mysql demon resets the autoincrement counter to a lower value. Seems there’s no way to reset this counter to zero and fill up all the empty ids. Oh, btw, INFORMATION_SCHEMA is read only, so no chance to reset auto_increment by hand.

Using auto increment and forcing data with preset id into the table will screw up if the id in question is reached by auto increment. You will get a duplicate entry exception. But if the table is out of ids anyhow, you can try to fill out the missing rows. You just have to find a way to reliably get the missing ids.

Workaround experiment with a stored procedure:

drop procedure if exists getfreeid;
delimiter //

CREATE PROCEDURE getfreeid( p1 INT(11) )
SET @i = p1;
TRUNCATE test_second;
SET @i = @i + 1;
UNTIL not exists( select id from test_first where id = @i)
INSERT INTO test_second (id, value) VALUES( @i, @i );
delimiter ;

Table test_second  has the same structure like table test_first and is only for containing the last empty id we found for table test_first. There’s probably a better way to handle this but not at midnight after finishing half a bottle of wine ;).

The procedure is called with

CALL getfreeid( <last used id goes here> );

Possible drawbacks: performance issues, race conditions.

Think about if INT really suits your needs. Apart from that: if you have a table running out of ids where only a let’s say 20% amount of available ids is set you really should check your code ;). Probably there’s some smelly code. Extensive delete and insert is no good when using auto increment.

This entry was posted in mysql and tagged , . Bookmark the permalink.

Leave a Reply