14

I have got a table with auto increment primary key. This table is meant to store millions of records and I don't need to delete anything for now. The problem is, when new rows are getting inserted, because of some error, the auto increment key is leaving some gaps in the auto increment ids.. For example, after 5, the next id is 8, leaving the gap of 6 and 7. Result of this is when I count the rows, it results 28000, but the max id is 58000. What can be the reason? I am not deleting anything. And how can I fix this issue.

P.S. I am using insert ignore while inserting records so that it doesn't give error when I try to insert duplicate entry in unique column.

Sourabh
  • 1,757
  • 6
  • 21
  • 43
  • possible duplicate of http://stackoverflow.com/questions/16348925/strictly-auto-increment-value-in-mysql – Ian Kenney May 16 '13 at 08:44
  • possible duplicate of [MySql upsert and auto-increment causes gaps](http://stackoverflow.com/questions/3679611/mysql-upsert-and-auto-increment-causes-gaps) – Barmar May 16 '13 at 08:46
  • 1
    I wrote up an innodb gap answer [Over Here](http://stackoverflow.com/a/38363271) – Drew Jul 14 '16 at 00:11

5 Answers5

24

This is by design and will always happen.

Why?

Let's take 2 overlapping transaction that are doing INSERTs

  • Transaction 1 does an INSERT, gets the value (let's say 42), does more work
  • Transaction 2 does an INSERT, gets the value 43, does more work

Then

  • Transaction 1 fails. Rolls back. 42 stays unused
  • Transaction 2 completes with 43

If consecutive values were guaranteed, every transaction would have to happen one after the other. Not very scalable.

Also see Do Inserted Records Always Receive Contiguous Identity Values (SQL Server but same principle applies)

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • I don't understand, why will this happen? Because of insert ignore or something else? And whats the point of having auto increment if its not in correct order? – Sourabh May 16 '13 at 08:42
  • 2
    The only requirement of auto increment is that they be unique, they don't have to be consecutive. – Barmar May 16 '13 at 08:44
  • 1
    @Sourabh they are in the right order, later inserts will have higher Ids, they are just not consecutive. If you need sequential numbers there is a trigger based solution here: http://stackoverflow.com/questions/3292197/emulate-auto-increment-in-mysql-innodb – Ian Kenney May 16 '13 at 08:53
  • 1
    It even happens on temporary tables that cannot be used simultaneously in two different transactions. – Barry Kelly May 25 '16 at 13:10
  • I wrote up an innodb gap answer [Over Here](http://stackoverflow.com/a/38363271) . You can make them not happen, but at a cost. – Drew Jul 14 '16 at 00:10
2

This is a problem in the InnoDB, the storage engine of MySQL.

It really isn't a problem as when you check the docs on “AUTO_INCREMENT Handling in InnoDB” it basically says InnoDB uses a special table to do the auto increments at startup

And the query it uses is something like 

SELECT MAX(ai_col) FROM t FOR UPDATE;

This improves concurrency without really having an affect on your data.

To not have this use MyISAM instead of InnoDB as storage engine

Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
cjds
  • 8,268
  • 10
  • 49
  • 84
2

You can create a trigger to handle the auto increment as:

CREATE DEFINER=`root`@`localhost` TRIGGER `mytable_before_insert` BEFORE INSERT ON `mytable` FOR EACH ROW 
BEGIN
  SET NEW.id = (SELECT IFNULL(MAX(id), 0) + 1 FROM mytable);;
END
Mackraken
  • 495
  • 4
  • 5
  • is this a good practice? how about in situation where concurrent insert happens, will it cause any error later on? – exiang Apr 19 '19 at 03:56
  • This is bad practice. If keys are removed from the end of the list they will be reused. This will not be an issue internally as foreign keys will prevent damage to referential integrity, but any external references to your data (perhaps the key gets used as an article ID on a website, or you export the data to another system that merges it into its own) could be "broken". Internal referential integrity can be broken too if you don't have proper FKs defined too where you need them. Also this will be less efficient than using the built-in increment support. – David Spillett Sep 30 '20 at 14:48
  • referencing articles is a good point but does not apply as this will trigger before records are inserted, not when they do exists. MySql does not reuse ids if records are not inserted. If you cancel a 40k records insert transaction, it will leave a 40k gap when the next record is inserted. Although is not ideal for every situation its a valid solution to batch import a large set of records periodically. – Mackraken Oct 02 '20 at 08:36
1

Perhaps (I haven't tested this) a solution is to set innodb_autoinc_lock_mode to 0. According to http://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html this might make things a bit slower (if you perform inserts of multiple rows in a single query) but should remove gaps.

qbolec
  • 5,374
  • 2
  • 35
  • 44
  • 3
    You seemingly did not read the page that you linked (??) which states "In all lock modes (0, 1, and 2), if a transaction that generated auto-increment values rolls back, those auto-increment values are “lost”." – Thomas Clowes Oct 31 '17 at 15:39
  • You are probably right. I understood the initial question was about gaps in numbering even when there are no errors/rollbacks. At least that was my experience and the reason I found this question and answer – qbolec Nov 01 '17 at 20:55
0

You can try insert like :

insert ignore into table select (select max(id)+1 from table), "value1", "value2" ;

This will try

  • insert new data with last unused id (not autoincrement)
  • if in unique fields duplicate entry found ignore it
  • else insert new data normally

    ( but this method not support to update fields if duplicate entry found )
a55
  • 376
  • 3
  • 13