CODEWITHSUNDEEP
mysql
thatidiotguy
thatidiotguy

Reputation: 9011

Skipped IDs in MySQL Database

So somehow on entering data with an AUTO_INCREMENT field called id I have found out that the data is in fact missing some rows. That is there will be rows like:

 ID   COL1   COL2
2000  data   data
2001  data   data
2003  data   data

My question is not how this skipping occurred (I will be looking into my code later to find that bug), but rather how to find the skipped rows and fix this. So the questions are:

1) What SQL command could I use to find the skipped IDs so I at least know where they are?

2) Is there any SQL voodoo I can use to move up all rows after a skip by one ID so that the missing ID is filled in? ie. move row 2003 to 2002 and 2004 to 2003 etc etc

EDIT:

This is not a question of how the gaps happened but how they can be fixed. I assure you there have been no deletions on this table only inserts by another program. I know there must be a a bug there. The question is how do I fix the data on my currently live system.

Upvotes: 0

Views: 2827

Answers (4)

user14519309
user14519309

Reputation: 1

For Question 1:

Hmm.. you could do a simple query to count rows of the id. Something like:

SELECT COUNT(id) FROM your_table WHERE id BETWEEN 2000 AND 2003;

From that query, the result should return 4.

For Question 2:

A simple alter table auto increment would do.

Upvotes: 0

spencer7593
spencer7593

Reputation: 108500

There's several reasons an auto_increment column can have "skipped" values... a failed insert, an INSERT IGNORE ..., an INSERT ... ON DUPLICATE KEY, a value specified in an insert that is higher than the next value, rows inserted and subsequently deleted, an ALTER TABLE ... AUTO_INCREMENT = statement, there's all sorts of reasons.

What AUTO_INCREMENT guarantees you is a unique value; it doesn't guarantee that none will be skipped.

There is usually no need to modify id values, and it can cause some significant problems for users and applications, if they are (reasonably) expecting id values to be immutable. In terms of the database, be aware of foreign keys (either enforced by InnoDB, or implied and not enforced as with MyISAM), or triggers that may fire, etc.

To set new values for the ID column, so that there are no "skipped" values (assuming no foreign key constraints will be violated)

in this example, starting with id values following id 2001, such that next higher id value (2003 in your example) will be set to 2002, the next higher id value after that, will be set to 2003, and so on...

UPDATE mytable t 
  JOIN ( SELECT s.id
              , @new_id := @new_id + 1 AS new_id
           FROM ( SELECT @new_id := 2001 ) i
           JOIN ( SELECT r.id
                   FROM mytable r
                  WHERE r.id > 2001
                  ORDER BY r.id
                ) s
          ORDER BY s.id
       ) u
    ON t.id = u.id
   SET t.id = u.new_id

SQL Fiddle Here

Again, all the warnings that "skipped" values are not a problem, and resetting id values can cause big problems (as mentioned above) apply.

To reset the AUTO_INCREMENT value for the table, it's a simple ALTER TABLE statement. If you attempt to set it lower than the maximum id value, MySQL uses the maximum id value. So, you can just

ALTER TABLE mytable AUTO_INCREMENT = 1;

This statement does not change existing rows, it just sets the auto increment value to the lowest value, such that the next auto increment retrieved will be one higher than maximum id value currently in the table.

To answer you first question, it's harder to get a list of "skipped" id values, since there isn't a row source for them.

If we just want to check if any id values are "skipped", we can make use of the query in the inline view aliased as u (from the UPDATE statement above). We change that UPDATE into a SELECT, and just filter out rows where the id value matches the (generate) new_id value:

SELECT u.*
  FROM ( SELECT s.id
              , @new_id := @new_id + 1 AS new_id
           FROM ( SELECT @new_id := 2001 ) i
           JOIN ( SELECT r.id
                   FROM mytable r
                  WHERE r.id > 2001
                  ORDER BY r.id
                ) s
          ORDER BY s.id
       ) u
WHERE u.id <> u.new_id
ORDER BY u.id

If that query returns no rows, then there are no "skipped" values.

Upvotes: 3

RandomSeed
RandomSeed

Reputation: 29809

As others have tried and failed to tell you, gaps in an auto-increment column is not a bug, it is normal behaviour (it can happen with something as trivial as a rolled back insertion). If your application chokes on gaps, then the application is bugged.

You must not try to make your ID's sequential, you must focus on having your application handle these gaps correctly.

If you absolutely need these values be sequential, then you should not use an auto-increment column at all (or add a hand-crafted order column, perhaps).

Upvotes: 2

user1477388
user1477388

Reputation: 21440

Based on your comment to my comment, a solution would be to simply reset the auto increment column like so: How to reset AUTO_INCREMENT in MySQL?

ALTER TABLE tablename AUTO_INCREMENT = 1

Upvotes: 2

Related Questions