MySQL Bugs: #3047: DELETE FROM table does not reset AUTO-INCREMENT

Bug #3047	DELETE FROM table does not reset AUTO-INCREMENT
Submitted:	2 Mar 2004 16:18	Modified:	30 Mar 2004 8:41
Reporter:	Todd Keup	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	4.1.1a alpha	OS:	Windows (Windows XP Pro)
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
The manual states that if you delete all rows in a table with DELETE FROM table_name (without a WHERE) in AUTOCOMMIT mode, the sequence starts over for all table types except InnoDB.  I find this to be true on my Linux installation running MySQL 3.23.58, but not on the 4.1.1a alpha release running on Windows XP Pro.

How to repeat:
CREATE TABLE t1 (
    t1_id SMALLINT UNSIGNED AUTO_INCREMENT,
    t1_text CHAR(10),
    PRIMARY KEY (t1_id)
);

INSERT INTO t1 (t1_id, t1_text) VALUES(NULL, 'First'), (NULL, 'Second'), (NULL, 'Third');

SELECT * FROM t1;
+-------+---------+
| t1_id | t1_text |
+-------+---------+
|     1 | First   |
|     2 | Second  |
|     3 | Third   |
+-------+---------+

DELETE FROM t1;
Query OK, 3 rows affected (0.00 sec)

INSERT INTO t1 (t1_id, t1_text) VALUES(NULL, 'First'), (NULL, 'Second'), (NULL, 'Third');

SELECT * FROM t1;
+-------+---------+
| t1_id | t1_text |
+-------+---------+
|     4 | First   |
|     5 | Second  |
|     6 | Third   |
+-------+---------+

Suggested fix:
Repair so that MyISAM tables will function as they did in previous releases.

I did notice that the CREATE TABLE pages of the manual states...

"As of MySQL 4.1.1, specifying the NO_AUTO_VALUE_ON_ZERO flag for the --sql-mode server option or the sql_mode server variable allows you to store 0 in AUTO_INCREMENT columns as 0, instead of generating a new sequence value..."

...maybe this has something to do with the bug?

Verified against 4.0.19/Linux and 4.1.2/Linux.  If the behavior will not be reverted, the documentation needs to be updated.

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

Additional info:

The manual was incorrect here (I have now fixed this)

In MySQL 4.0 we change that 'DELETE FROM table_name' returns the number of rows and doesn't reset auto increment, as this is what most user and some programs expect.  If you want to reset the auto-increment value for an MyISAM table, use TRUNCATE TABLE instead.