MySQL – Fix Huge Gap in Auto Increment Primary Key

auto-incrementinnodbMySQL

I Have normal InnoDB table with autoincremental primary key. There Is about 100 thousands records. Accidentally was saved record with ID 10 million. Other record continue in this row. Is any possible And safe solution how:

A) change some IDS (row started from 10 millions) to max ID from less row? Off course Is necessary to change foreign IDS too.
B) start to fill gap between 100 thousand to 10 million?

I would want it because of better readability.

Best Answer

  1. Locate all tables with IDs in them (FOREIGN KEYs, etc). Figure out the exact numbers for below (I took your 10M and 100K as exact.)
  2. Take your application "down".
  3. Disable FOREIGN KEYS
  4. Run UPDATE tbl SET x_id = x_id - 9900000 WHERE x_id >= 10000000; for each such table.
  5. Enable FOREIGN KEYS
  6. Do ALTER TABLE ... AUTO_INCREMENT = ... (or otherwise make sure it gets lowered)
  7. Bring your app back up.

It would be wise to experiment before launching into the entire task. If you have a test platform do it all there. If not...

Limit the damage (for testing): WHERE x_id >= 10000000 AND x_id < 10000010 LIMIT 11 -- then check that the number of rows changed was no more than 10. And verify that all actions on those users are still OK. You still must go through all 6 steps, but if something breaks, you will have less damage to clean up. And, in doing this small test, you will (hopefully) figure out all the UPDATEs that are needed.

To be more daring,... Don't disable/enable the FKs. Instead, figure out what order to do the UPDATEs in so that the FKs won't complain. Verfiy that the CASCADEs will do what you need. This also gives you more confidence that the values will be correctly fixed; that is, no dangling messed up FKs.

(A caveat on AUTO_INCREMENT - The handling of such has changed with 8.0; my steps may be changed a bit.)

As a Postlog, ask yourself whether FKs have been more a benefit or more a pain.

Related Question