What happens when autoincrement clashes with existing data in MySQL?

mysql

Solution

Autoincrement will use the next available id for both InnoDB and MyISAM tables.

I have tested this for MySQL 4.1.22 running on Windows Vista. I created two simple tables, one using InnoDB and one using MyISAM. Each had an autoincrementing primary key called 'id' and a varchar column called 'description'.

I ran the following commands (with no errors):

INSERT INTO MyIsamTest (description)     VALUES ('autoincrement id insert'); 
INSERT INTO MyIsamTest (id, description) VALUES (100, 'manual id insert');
INSERT INTO MyIsamTest (description)     VALUES ('autoincrement id insert');

SELECT * FROM MyIsamTest;

I got the following result, which shows that the 'id' column was correctly autoincremented:

+=====+=========================+
| id  | description             |
+=====+=========================+
|   1 | autoincrement id insert |
+-----+-------------------------+
| 100 | manual id insert        |
+-----+-------------------------+
| 101 | autoincrement id insert |
+-----+-------------------------+

I repeated the experiment on my InnoDbTest table with the same outcome.

Problem

I have a MySQL table with an autoincremented id column. The id started from 1 and is now in the 4000s. However, I also need to port some legacy data into this table from an old version of the application. The ids of this data start from 5000 and must be preserved for auditing purposes. What happens if I insert an entry after my autoincrement counter is up to 4999? Is autoincrement smart enough to look for the next available id, or will it crash because it tries to insert id 5000, which already exists? While advice on how to work around this problem is very helpful, I'd also like to understand what MySQL would do in this situation and if I need to intervene at all.

Original source