Bug #58931 ALTER TABLE does not reset AUTO_INCREMENT
Submitted: 14 Dec 2010 22:19 Modified: 14 Dec 2010 23:13
Reporter: Ryan Hansen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:mysql-5.1-telco-7.1 OS:Linux (CentOS 5)
Assigned to: Jon Stephens CPU Architecture:Any
Tags: 7.1.9a
Triage: Triaged: D4 (Minor) / R6 (Needs Assessment) / E6 (Needs Assessment)

[14 Dec 2010 22:19] Ryan Hansen
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.
[14 Dec 2010 23:13] Sveta Smirnova
Thank you for the report.

Verified as described.