Description:
Given a table with an auto_increment field, once the auto_increment value has been incremented upwards, even after running DELETE FROM [table] the auto_increment value cannot be reset downwards using the standard syntax:
ALTER TABLE [table] AUTO_INCREMENT=N
In this scenario, this above ALTER command has no effect; the auto_increment value remains what it was prior to the ALTER (and prior to the DELETE). The only way to actually reset the auto_increment value is to TRUNCATE the table (which has caused performance problems for us in the past).
Using the ALTER statement to set the increment *above* its current value seems to work fine.
How to repeat:
- Create a table with an auto_increment field
- Insert a few records
- Check the auto_increment value (either from the table or from the information_schema...it's the same)
- Run DELETE FROM [table]
- Run ALTER TABLE [table] AUTO_INCREMENT=1
- Check the auto_increment value again; it will be the same as it was prior to DELETE
- Insert another record or two
- Check the auto_increment value again; it will have continued to increment from where it was prior to DELETE/ALTER
Suggested fix:
Historically and according to MySQL documentation, this kind of ALTER statement should reset the auto_increment value to the value of N regardless of whether the table rows were deleted or the table was truncated. Assuming the table is now empty, the ALTER command should be able to set auto_increment value to whatever is desired, not just (last max value)+1.
