CODEWITHSUNDEEP
mysqlauto-increment
Nick
Nick

Reputation: 4462

Can't set auto-increment value of tables to lower value

I used to be able to set an auto-increment value to lower than the existing one after deleting rows in a table, but now I am unable to do so (I can only set it to higher than the current value). I'm wondering if this is because of upgrading MySQL (5.6.11)/PHPMyAdmin (3.5.8), or whether I should still be able to do this?

Upvotes: 6

Views: 8249

Answers (3)

user3145885
user3145885

Reputation: 111

You can reset the hidden value by restarting the MySQL server. InnoDB recalculates the auto-increment value after restart.

Update your rows, and restart the mysql deamon. This prevents you from rebuilding your table what can be a hastle with big tables.

Upvotes: 11

Steve Horvath
Steve Horvath

Reputation: 530

Thanks for the correct answer, mogul.

The work-around for InnoDB:

  • dump out the table,
  • change the autoincrement value in the dump file, and
  • re-load it

Just make sure you drop the table before reloading; and you will probably need to disable key checks via SET foreign_key_checks = 0;

Upvotes: 3

mogul
mogul

Reputation: 4553

From the manual:

You cannot reset the counter to a value less than or equal to any that have 
already been used. For MyISAM, if the value is less than or equal to the 
maximum value currently in the AUTO_INCREMENT column, the value is reset 
to the current maximum plus one. For InnoDB, if the value is less than the 
current maximum value in the column, no error occurs and the current sequence 
value is not changed.

So in short, it depends in the storage engine used.

Upvotes: 9

Related Questions