MySQL Duplicate ID

Question

Could it be that MySQL twice generates the same auto-increment identifier?

We have the following situation:

• Account created with id = 100;

• then it was deleted;

• then another account was created, and it has the same identifier = 100;

Table structure:

CREATE TABLE `bill` ( `id` int(11) NOT NULL auto_increment, `user` int(11) NOT NULL, `date` datetime NOT NULL, `state` int(11) NOT NULL, `adv` bit(1) NOT NULL default b'0', `weight` int(11) default NULL, PRIMARY KEY (`id`), KEY `FK2E2407EC768806` (`user`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE `bill` ADD CONSTRAINT `FK2E2407EC768806` FOREIGN KEY (`user`) REFERENCES `user` (`id`); 

Could there be some kind of race condition or does MySQL guarantee unique auto-increment identifiers?

UPDATE: we cannot reproduce this situation, but we registered it.

Answer 1

Auto-increment is handled differently using different storage mechanisms. For example, with MyISAM, the following auto-increment value is stored, so if you restart the MySQL server, it will save that auto-increment value.

However, InnoDB does not save the next auto-increment value, so if you restart the MySQL server, it will calculate the current maximum value and the increment from there.

This is relevant for you since you are using InnoDB. Thus, if 100 was the maximum id value in your table, then you deleted this row and then restarted the MySQL server and then reused 100 in the next insert.

Here is a simple example illustrating this point:

 mysql> CREATE TABLE `bill` ( -> `id` int(11) NOT NULL auto_increment, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.12 sec) mysql> -- start at 99 to force next value to 100 mysql> insert into bill values (99); Query OK, 1 row affected (0.01 sec) mysql> -- use auto-increment, should be 100 mysql> insert into bill values (null); Query OK, 1 row affected (0.00 sec) mysql> select * from bill; +-----+ | id | +-----+ | 99 | | 100 | +-----+ 2 rows in set (0.00 sec) mysql> -- delete max value mysql> delete from bill where id = 100; Query OK, 1 row affected (0.00 sec) mysql> -- use auto-increment, should be 101 mysql> insert into bill values (null); Query OK, 1 row affected (0.00 sec) mysql> select * from bill; +-----+ | id | +-----+ | 99 | | 101 | +-----+ 2 rows in set (0.00 sec) mysql> -- delete max value mysql> delete from bill where id = 101; Query OK, 1 row affected (0.00 sec) mysql> mysql> /*** RESTART MYSQL ***/ mysql> mysql> -- use auto-increment, should be 100 mysql> insert into bill values (null); Query OK, 1 row affected (0.01 sec) mysql> select * from bill; +-----+ | id | +-----+ | 99 | | 100 | +-----+ 2 rows in set (0.00 sec) 

Answer 2

Perhaps the deletion was done before the transaction? I believe that in this case, the record will never update the internal counters. This is a little hunch, but can you try to reproduce it by putting the insert, select, and delete instructions in the SQL script and surrounding each pair of inserts, select and delete with a transaction?