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?