Bug #350 auto_increment field not reset when doing "DELETE FROM table;"
Submitted: 30 Apr 2003 9:26 Modified: 30 Apr 2003 9:41
Reporter: [ name withheld ] Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:4.0.12 OS:Microsoft Windows (Windows 2000 Server)
Assigned to: CPU Architecture:Any

[30 Apr 2003 9:26] [ name withheld ]
Description:
When using "DELETE FROM table;" to clear the entries from a MyISAM table (other table types not tested) with an auto_increment field, the auto_increment counter is not reset to 1.

How to repeat:
Running mysqld-nt version 4.0.12 (client does not matter, tested using PHPMyAdmin), execute the following queries:

DROP TABLE IF EXISTS testtable;
CREATE TABLE testtable (
  field1 int NOT NULL auto_increment,
  field2 varchar(5),
  PRIMARY KEY(field1)
) TYPE=MYISAM;

INSERT INTO testtable (field2) VALUES ('one'),('two');
DELETE FROM testtable;
INSERT INTO testtable (field2) VALUES ('three');
SELECT field1 FROM testtable WHERE field2='three';
-> returns the value 3

This also occurs in MySQL Max 3.23, but not MySQL Standard 3.23 (the version I was using before I upgraded to 4.0) which returns the expected value of 1.

Replace "DELETE FROM testtable;" with "TRUNCATE testtable;" and the last INSERT will behave properly (the SELECT will return the value 1).
[30 Apr 2003 9:41] Alexander Keremidarski
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

This is well docummented behaviour. 

auto_increment values must not be reused as it usualy breaks dependencies in database

In old releases before 3.23 auto_increment values were reused after DELETE which was proved to lead to many troubles and in 3.23 this behaviour was changed to current - much more consistant one.

If one wants to reuse values it is doable with following syntax:
 ALTER TABLE SET AUTO_INCERMENT=N;