Can't change MySQL AUTO_INCREMENT

Question

I am working on updating a table from using a software generated ID to a MySQL auto_increment ID.

The ID field is int(10) and database type is InnoDB

After updating the IDs to be sequential, starting at 1, the new highest ID is 122. Previously, the highest ID was 62029832

Now I am trying to update the auto_increment value so that the next insert is 123.

The current auto_increment value is 62029833.

So far I have tried:

ALTER TABLE tableName AUTO_INCREMENT = 123; --- No luck. Doesn't error, just doesn't stick.

INSERT INTO tableName (ID) VALUES (123); DELETE FROM tableName WHERE ID = 123; --- Still no luck

I would like to avoid truncating the table if there is another method.

From what I've read, InnoDB should allow the change to 123 since the highest value is currently 122, but it's acting as though there is a higher value.

Just to test, I've also tried changing the auto_increment to 1000, 2000, 122, etc. Nothing sticks.

Any ideas?

Answer 1

After working on it some more, I found a dumb, non-intuitive solution.

First, remove AUTO_INCREMENT from your ID column. I had foreign key checks on so I had to run:

SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `warehouse`.`addresses`
    CHANGE COLUMN `aID` `aID` INT(10) UNSIGNED NOT NULL;
SET FOREIGN_KEY_CHECKS = 1;

Next, update the AUTO_INCREMENT value:

ALTER TABLE 'warehouse'.'addresses' AUTO_INCREMENT = 123;

Finally, re-add AUTO_INCREMENT:

SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `warehouse`.`addresses`
    CHANGE COLUMN `aID` `aID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
SET FOREIGN_KEY_CHECKS = 1;

Hope this helps some poor soul!