Bug #11946 truncate does not clear the auto_increment in innodb tables
Submitted: 14 Jul 2005 16:32 Modified: 2 Sep 2005 17:27
Reporter: Adam Tylmad Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:4.0.22 Standard/4.1.XX OS:Windows (w2k/Linux)
Assigned to: Osku Salerma CPU Architecture:Any

[14 Jul 2005 16:32] Adam Tylmad
Description:
When running truncate on a innodb table an auto_increment pk should reset it's internal counter so that any added row after the truncate should have ID=1

How to repeat:
create table innodbtable (id int(4) not null auto_increment, primary key (id)) type=InnoDB;
insert into innodbtable () values ();
insert into innodbtable () values ();
select * from innodbtable;

ID
1
2

truncate innodbtable;
insert into innodbtable () values ();
select * from innodbtable;

ID
3

Suggested fix:
reset the auto_increment counter when running truncate
[14 Jul 2005 17:29] MySQL Verification Team
Here there is at least a documentation conflict:

At: http://dev.mysql.com/doc/mysql/en/innodb-auto-increment-column.html

15.7.3. How an AUTO_INCREMENT Column Works in InnoDB

If you specify an AUTO_INCREMENT column for a table, the InnoDB table handle in the data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. The auto-increment counter is stored only in main memory, not on disk.

InnoDB uses the following algorithm to initialize the auto-increment counter for a table T that contains an AUTO_INCREMENT column named ai_col: After a server startup, when a user first does an insert to a table T, InnoDB executes the equivalent of this statement:

SELECT MAX(ai_col) FROM T FOR UPDATE;

Then you can see at the bottom that for 4.0.XX and 4.1.XX the
counters is 3 for the next record how is showed by the show
table status from db_name. 

However at:

http://dev.mysql.com/doc/mysql/en/truncate.html
13.2.9. TRUNCATE Syntax
<cut>
The table handler does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning. This is true even for MyISAM and InnoDB, which normally does not reuse sequence values.

The above is true currently for 5.0.XX.

c:\mysql\bin>mysqladmin -uroot create db2

c:\mysql\bin>mysql -uroot db2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.24-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table innodbtable (id int(4) not null auto_increment, primary key (id))
    -> type=InnoDB;
Query OK, 0 rows affected (0.13 sec)

mysql> insert into innodbtable () values ();
Query OK, 1 row affected (0.03 sec)

mysql> insert into innodbtable () values ();
Query OK, 1 row affected (0.01 sec)

mysql> select * from innodbtable;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.01 sec)

mysql> truncate innodbtable;
Query OK, 2 rows affected (0.03 sec)

mysql> show table status from db2\G
*************************** 1. row ***************************
           Name: innodbtable
           Type: InnoDB
     Row_format: Fixed
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: NULL
   Index_length: 0
      Data_free: 0
 Auto_increment: 3
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
 Create_options:
        Comment: InnoDB free: 4096 kB
1 row in set (0.00 sec)

mysql> insert into innodbtable () values ();
Query OK, 1 row affected (0.05 sec)

mysql> select * from innodbtable;
+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

mysql>
http://dev.mysql.com/doc/mysql/en/truncate.html

c:\mysql\bin>mysql -uroot db2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.10-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table innodbtable (id int(4) not null auto_increment, primary key (id))
    -> type=InnoDB;
Query OK, 0 rows affected, 1 warning (0.83 sec)

mysql> insert into innodbtable () values ();
Query OK, 1 row affected (0.08 sec)

mysql> insert into innodbtable () values ();
Query OK, 1 row affected (0.05 sec)

mysql> select * from innodbtable;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.08 sec)

mysql> truncate innodbtable;
Query OK, 2 rows affected (0.09 sec)

mysql> insert into innodbtable () values ();
Query OK, 1 row affected (0.03 sec)

mysql> select * from innodbtable;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql>
[15 Jul 2005 8:40] Marko Mäkelä
TRUNCATE is mapped to DELETE in MySQL/InnoDB 5.0.2 and earlier. This behavior is by design. We should perhaps address this better in the documentation.

Miguel, I don't see a controversy. At http://dev.mysql.com/doc/mysql/en/truncate.html, the paragraph you quoted only applies to MySQL 5.0.3 and later when there are no foreign key constraints on the table. See an earlier paragraph in that section: "For InnoDB before version 5.0.3, TRUNCATE TABLE is mapped to DELETE, so there is no difference. Starting with MySQL/InnoDB-5.0.3, fast TRUNCATE TABLE is available. The operation is still mapped to DELETE if there are foreign key constraints that reference the table."
[15 Jul 2005 11:41] Heikki Tuuri
Marko,

this should be documented more clearly.

Also, how does one know in 5.0.xx if TRUNCATE does reset the counter or not? If it is mapped to a DELETE, then it does NOT reset the counter?

Regards,

Heikki
[18 Jul 2005 8:55] Marko Mäkelä
Right, also 5.0 won't reset the auto-increment counter when it has to do DELETE instead of plain TRUNCATE. I'm not sure how to best address this.
[18 Jul 2005 16:13] Heikki Tuuri
Marko,

can your code reset the counter using the InnoDB functions? Also when it notices that it cannot use the fast truncate mechanism?

Regards,

Heikki
[5 Aug 2005 12:23] Heikki Tuuri
Should be fixed so that in 5.0 the counter is always reset to zero, regardless whether we use the fast truncate or not.
[17 Aug 2005 8:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28369
[30 Aug 2005 9:39] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29021
[30 Aug 2005 9:42] Osku Salerma
Pushed to 5.0.13.
[2 Sep 2005 17:27] Paul DuBois
Noted in 5.0.13 changelog, and in the
TRUNCATE TABLE section.