MySQL Bugs: #3317: auto_increment not cleared when 'delete from' used

Bug #3317	auto_increment not cleared when 'delete from' used
Submitted:	28 Mar 2004 2:51	Modified:	9 May 2004 3:24
Reporter:	Joseph Goins	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	MYSQL Ver 12.22 Distrib 4.0.18, for Win	OS:	Windows (Windows 2000)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
mysql> insert into invoices values (null,0.99), (null,0.50), (7,1.50), (null,3.5
0);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select invoice_num, amount from invoices;
+-------------+--------+
| invoice_num | amount |
+-------------+--------+
|           1 |   0.99 |
|           2 |   0.50 |
|           7 |   1.50 |
|           8 |   3.50 |
+-------------+--------+
4 rows in set (0.00 sec)

mysql> delete from invoices;    ""THIS IS SUPPOSED TO CLEAR THE COUNTER TOO""
Query OK, 4 rows affected (0.00 sec)

mysql> select invoice_num, amount from invoices;
Empty set (0.00 sec)

mysql> insert into invoices values (null,0.99);
Query OK, 1 row affected (0.00 sec)

mysql> select invoice_num, amount from nvoices;                                 
+-------------+--------+
| invoice_num | amount |
+-------------+--------+
|           9 |   0.99 |                      "" BUT IT DIDN'T APPEAR TO ""
+-------------+--------+
1 row in set (0.00 sec)

How to repeat:
See Description

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:

mysql> delete from invoices;    ""THIS IS SUPPOSED TO CLEAR THE COUNTER TOO""

No! This is NOT supposed to.

Since 3.23.0 this is how Auto_increment works in order to avoid reusing of generated values.

This is precisely documented in manual.

I'll quote the SAMS MySQL V4.0 manual I am reading:

MySQL keeps track of the maximum value used in an AUTO_INCREMENT column (and therefore it knows the value to use next) until the table is emptied using a statement of the following form:

DELETE FROM table_name

This clears the initial sequence number and causes subsequent INSERT statemments to restart the auto column at 1. 

However if you empty the table using a more complex query, such as this:

DELETE FROM table_name WHERE 1

the start-of-sequence information will not be cleared, and subsequent INSERT actions will use values where the previous data left of.  

As my trace showed the simpler DELETE FROM INVOICES did not clear the auto column.

However after further research, I must apologize for reporting this specific problem as a bug.  Between my steps, I did no update so the server had no choice but to assign the next auto_increment num 'it' remembered.

Again, I apologize.

DELETE FROM tbl; no longer resets an auto_increment counter.
Only TRUNCATE does.

But I'm not sure the manual lsays it in clear, all I was able to find is:

  `TRUNCATE TABLE' differs from `DELETE FROM ...' in the following ways:
...
     * The table handler might not remember the last used
     `AUTO_INCREMENT' value but may start counting from the beginning.
     This is true for `MyISAM', `ISAM', and `BDB' tables.
...

which kind of implies that after DELETE FROM table handler (or storage engine) remembers the last used AUTO_INCREMENT' value.

Paul, did I overlook something ?

Hi!

Note that TRUNCATE does not reset the auto-inc counter of an InnoDB table, because TRUNCATE is still mapped to DELETE FROM ... inside InnoDB.

This will change in some future version, when we implement a real TRUNCATE of an InnoDB table.

Regards,

Heikki

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

I've attempted to clarify the DELETE/TRUNCATE behavior at:

http://dev.mysql.com/doc/mysql/en/DELETE.html
http://dev.mysql.com/doc/mysql/en/TRUNCATE.html