7

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?

Craig
  • 2,093
  • 3
  • 28
  • 43
  • Possible duplicate: http://stackoverflow.com/questions/5655396/why-insert-ignore-increments-the-auto-increment-primary-key – Bryan Menard Sep 27 '11 at 03:02
  • Thank you for the link, I have looked at this and edited my question to include what I have tried. – Craig Sep 27 '11 at 03:26
  • 5
    why do you *care* if there are gaps? – SingleNegationElimination Sep 27 '11 at 03:29
  • 1
    Agree with @TokenMacGuy... it sounds like you're using the ID to count or calculate something. If so, you should be using a different approach. – harpo Sep 27 '11 at 03:31
  • 2
    No I care because there are millions of records and it just looks messy im doing 10k inserts where only about 50-60 get inserted, just seems like it will cause problems down the road. – Craig Sep 27 '11 at 03:43
  • The big question is which will be a bigger performance cost, having to use BIGINT as a PK (and having to update all related references). Or disabling innodb_autoinc_lock_mode, and maintaining a lower auto increment value. (if you are already having to use BIGINT it may not even matter). – Chad Cache May 14 '19 at 16:11

2 Answers2

15

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.

Bryan Menard
  • 13,234
  • 4
  • 31
  • 47
-1

I had the same issue and I set the InnoDB auto_inc_lock_mode to 0 and it worked just once but I encountered the same problem again. Luckily I found a way to solve it.

ALTER TABLE `nameOfTable` AUTO_INCREMENT=1;

You can enter this query to reset the jumping indices or better still the GUI of phpMyAdmin has an Operation tab. Go to Operations > Table Options and change the AUTO_INCREMENT value manually.

You can also do this:

SET  @num := 0;

UPDATE your_table SET id = @num := (@num+1);

ALTER TABLE your_table AUTO_INCREMENT =1;

From this source .

lator
  • 29
  • 1
  • 6
  • While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/late-answers/31970825) – lemon Jun 10 '22 at 10:07