MySQL Bugs: #11946: truncate does not clear the auto

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

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

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>

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."

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

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.

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

Should be fixed so that in 5.0 the counter is always reset to zero, regardless whether we use the fast truncate or not.

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

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

Pushed to 5.0.13.

Noted in 5.0.13 changelog, and in the
TRUNCATE TABLE section.