MySQL InnoDB – How to Set Auto Increment Value Without Downtime

auto-incrementinnodbMySQL

I have an InnoDB table whose primary key column is defined as follows:

id INT NOT NULL AUTO_INCREMENT

This column's auto_increment value has reached INT_MAX, so all inserts are failing with a duplicate key error. However, this table has unused ID values from 0 to 1.8 million, so I would like to reset the auto_increment value to 0 to buy time for a long-term fix to be implemented.

I cannot find any way to accomplish this without incurring significant downtime. My application servers' core functionality is broken if they can't read from this table (but not being able to write, like it is now, is okay).

I've considered the following two solutions, with no success:

Setting the auto_increment value with ALTER TABLE. This isn't possible because it rebuilds the table, which takes too long.

Creating a new table with identical schema and using RENAME TABLE to swap in the new table with the column starting at 0, then back-filling it with data from the old table. This also isn't possible, because the process of backfilling with clobber the auto_increment value (and I believe it would block inserts from the application while backfilling).

Is there any other approach I can take here, or am I completely out of luck?

Best Answer

Bite the bullet and take the downtime.

Setting the auto-increment value for the table is useless since it will be reset to the MAX(id)+1 anyway.

ALTER TABLE -- I don't think INPLACE will work in this case, so it will be a full table copy. (Especially if the id is the PRIMARY KEY.)

pt-online-schema-change -- full copy. Its benefit is that the table continues to be usable. But your table is not usable for another reason, namely the inability to add another id.

What to alter? If you have INT, you have a 2 billion limit; changing to INT UNSIGNED would give you a 4 billion limit. The table size won't change (aside from the shuffling of BTrees). BIGINT is 8 bytes and gives you an unreachable limit. Caution: All tables referencing this one need to change the datatype at the same time. This involves more ALTER TABLEs.

Reuse 0..1.8M? You can explicitly set ids as you do INSERTs, but that probably requires code changes (and risk).

This may be the fastest fix: Move the top 1.8M rows down by doing something like

UPDATE tbl
    SET id = id - (some constant: about 2B -1.8M)
    WHERE id > (the same constant);

and restart the server so that it can discover the new MAX(id). Caution: This technique will not work for version 8.0. CAUTION: If other tables use this id (as a FK or for JOINing, you must also do a similar UPDATE on their table to adjust the foo_id in it.

Caution: Don't set any id to 0 or less than 0. (I think that UPDATE is correct in that respect.) You will have room for only 1.8M rows before you are in trouble again.

Gaps? Do you have lots of gaps in ids? They are caused by INSERT IGNORE, REPLACE, and several other types of INSERT, plus (of course) DELETE. Many of the 'insert' methods that 'burn' ids can be recoded to eliminate the burning and usually not be any slower.

Going forward... It is not always 'right' to have an AUTO_INCREMENT PRIMARY KEY on every table. I estimate that 2/3 of tables are better off with a 'natural PK' -- that is some column (or combination of columns) that is UNIQUE and not to bulky.

Related Question