Bug #1261 AutoIncrement not reset when deleting all records
Submitted: 12 Sep 2003 7:56 Modified: 15 Sep 2003 9:09
Reporter: Martin Bartelds Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:3.23.55 OS:FreeBSD (FreeBSD)
Assigned to: CPU Architecture:Any

[12 Sep 2003 7:56] Martin Bartelds 
Description:
I'm not sure if this problem belongs in the category "MyISAM Table Handler".

Java ODBC interface from <java.sql>
When a "DELETE FROM <db_name>" is done, the records are removed but the Auto_Increment value is not reset to 0 or 1.
The query is entered using the java "Statement" class, function executeUpdate();

My application suffers from this problem after upgrading MySql from a previous version, I'm not sure about the original MySQL release.

How to repeat:
Execute the DELETE instruction.
[15 Sep 2003 9:09] Indrek Siitan 
This is a documented change. If you want to reset the auto-increment
counter, you have to use "TRUNCATE TABLE <tablename>" instead of 
DELETE FROM.
[10 Mar 2005 17:06] [ name withheld ] 
But doesn't this require a restart of mysql?