What happens when autoincrement clashes with existing data in MySQL?

Question

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.

Answer 1

After your insert, you could set the auto increment to a value above the now-highest id:

ALTER TABLE tbl AUTO_INCREMENT = 9000;

Answer 2

If you only have one legacy table with no dependencies on the ids, then what I'd do is create a temporary table to insert all your new data into (with the IDs 5000+). Then run this:

INSERT INTO `myrealtable` (column1, column2, column3)
SELECT column1, column2, column3
FROM `temptable`;

DROP TABLE `temptable`;

...where none of the columnX columns are the primary auto_increment id.

Answer 3

Try it out on a test database and see what happens, with lampp/xampp etc.

Answer 4

I believe MySQL checks if you insert on an auto-incremented column and will update AUTO_INCREMENT so that AUTO_INCREMENT > MAX(id), but I need to look through the docs. You should still follow Andrew Duffy's suggestion, to be safe.

Answer 5

MySQL's doesn't allow you to set the internal auto_increment "value" lower than the current highest ID.

Therefore, if you add 1000 rows starting at 5000, the increment is set to 6000. You can still add rows with IDs that don't exist yet (e.g. 4500) but it's not worth bothering, really. There are plenty of numbers between 6000 and 4 billion.

Answer 6

If you insert data with already assigned primary keys into MyISAM, the value of the AUTO_INCREMENT column for the next insert will be max(column) + 1, so it will work.

However you are not using MyISAM since this is important data, you are using InnoDB, which needs the ALTER TABLE statement quoted above.

Answer 7

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.