How do I stop auto increment on ignored inserts that make no changes in MySQL?

Question

I am inserting say 500 rows using INSERT IGNORE, if the row already exists (based on a unique field) then it simply does nothing, if not it inserts the row.

My problem is that say only 10 do NOT exist the auto increment still increases for every insert so it goes up by 500 so I end up with gaps in my id's. How do I stop this?

I have tried:

SET global innodb_autoinc_lock_mode = 0;

But I get the error:

1238 - Variable 'innodb_autoinc_lock_mode' is a read only variable

How do I change this?

Answer 1

According to MySQL's documentation, the innodb_autoinc_lock_mode is not a dynamic variable and, thus cannot be changed while MySQL is running.

You'll either have to set it in the command line

--innodb_autoinc_lock_mode=0

or in the configuration file (mysql.ini)

innodb_autoinc_lock_mode=0

It should be noted that you should not (read: never) rely on a continuous id sequence. Rollbacked or failed transactions may result in gaps.