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 http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html for details.  id can have values between 0 and 4294967295.

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

CREATE TABLE IF NOT EXISTS `test_first` (
`id`   INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`val` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT ”,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

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) )
BEGIN
SET @i = p1;
TRUNCATE test_second;
REPEAT
SET @i = @i + 1;
UNTIL not exists( select id from test_first where id = @i)
END REPEAT;
INSERT INTO test_second (id, value) VALUES( @i, @i );
END//
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> );
SELECT @i;

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