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.

  • 2
    Fastest solution: just leave it as it is. Who cares about a 10 million gap. – ypercubeᵀᴹ Nov 23 '18 at 8:25
  • I know, but I it Is not practical. It Is table of users and I need work with IDS outside of database. – tomasr Nov 23 '18 at 8:30
  • And will having 6-digit IDs instead of 8-digit makes such a difference? What will you do when you actually have 1 million rows? Or 10 million rows? – ypercubeᵀᴹ Nov 23 '18 at 9:04
  • It Is 5 Digits (100 houndreds Is not let) versus 8 Digits And yes It Is big difference. I asked on solution not on excuse. – tomasr Nov 23 '18 at 15:45
  • Can you add some details then? The CREATE TABLE definition, at least the relevant part: PK column, its type. Also how many tables have FOREIGN KEY constraints, whether they have ON UPDATE CASCADE or not, and if there are other tables that reference those tables. – ypercubeᵀᴹ Nov 23 '18 at 16:01
  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.

  • Regarding "Don't disable/enable the FKs. Instead, figure out what order to do the UPDATEs in so that the FKs won't complain." - if you defined the FKs with ON UPDATE CASCADE this should be handled for you (assuming you have FKs defined everywhere they should be, of course. – David Spillett Nov 23 '18 at 17:19
  • While this is the right way to answer the question (so I voted it up), I want to stress OP that not only foreign keys can break- many application and other internal logic can depend on ids never changing- the fix could be worse than not doing anything. As an additional advice, remember to ALTER the table to change the innodb auto_increment table value, or the next insert would continue inserting the high values. – jynus Nov 24 '18 at 15:13
  • 1
    Thanks, guys; I folded some of what you said into my Answer. – Rick James Nov 24 '18 at 17:22
  • Hi, It Is propably right answer, but I am affraid to use it. INT has a lot of space, so only one benefit would be in better readability. – tomasr Nov 29 '18 at 7:21
  • @tomasr - INT takes 4 bytes. MEDIUMINT UNSIGNED takes 3 bytes and has a range of 0..16M`. If you are arguing to go with the latter, then do so. – Rick James Nov 29 '18 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.