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 ]
• Status: Not a Bug
• Category: MySQL Server: MyISAM storage engine
• Severity: S3 (Non-critical)
• Version: 4.0.12
• OS: Windows (Windows 2000 Server)
• 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;