CODEWITHSUNDEEP
mysqlsqlinnodb
Justin Mathieu
Justin Mathieu

Reputation: 471

Can't change MySQL AUTO_INCREMENT

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?

Upvotes: 4

Views: 10008

Answers (2)

Justin Mathieu
Justin Mathieu

Reputation: 471

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!

Upvotes: 13

Александр Королёв
Александр Королёв

Reputation: 438

The thing is, that into Your table has for one of the records a value for the PRIMARY field, equal to the maximum number supported for this field type. This may be result of incorrect insertion data into the table.

First, just in case, You need to see how many records have your table. For it run a simple query:

SELECT COUNT(*) FROM `your_table`;

The result should not be equal to the maximum autoincrement value for Your field. If this is not the case, then Your table is simply full, and most likely increasing the field size, for example, from int to bigint, will help You:

ALTER TABLE `your_table` CHANGE `id` `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID';

Else, to reset AUTO_INCREMENT for a table, You will need to delete the record with the maximum autoincrement value, which, in fact, has blocked Your table for data insertions. For it, run:

DELETE FROM `your_table` WHERE `id` > 1 order by `id` DESC LIMIT 1;

Then just set AUTO_INCREMENT for Your table by running the query:

ALTER TABLE `your_table` AUTO_INCREMENT = 1;

-> MySQL itself will determine what maximum numeric value is set for the PRIMARY field in the table and set autoincrement equal to the value of this field + one.

You may also want to reindex your table, to do this run the following query:

SET @newid=<this max id from Your table>;
UPDATE `your_table` SET `id`=(@newid:=@newid+1) ORDER BY `id`;

I think, that my answer will be useful to someone.

Queries have been tested on MySQL v5.7.

Upvotes: 0

Related Questions