MySQL Bugs: #37644: Truncate does not reset auto-increment value (InnoDB)

Bug #37644	Truncate does not reset auto-increment value (InnoDB)
Submitted:	25 Jun 2008 20:23	Modified:	25 Jun 2008 22:11
Reporter:	JF Prieur	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Linux (Fedora 8)
Assigned to:		CPU Architecture:	Any

Description:
I saw similar bugs for other, older versions, so my apologies if this is redundant.

5.0.45 is latest packaged Fedora 8 version. Using InnoDB, when I truncate a table to empty it of its data, the auto-increment counter for that table does NOT reset to 1 after truncation, it remains to the last value used+1.

How to repeat:
Create an InnoDB table with auto-increment primary key, enter data, truncate.

Thank you for the bug report. I did a simple test case on FC 8 with server built from source and on Windows with release 5.0.51b and I can't repeat (if you have a repeatable test case please provide it). Thanks in advance.

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.51b-community-nt MySQL Community Edition (GPL)

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

mysql> drop database if exists wd;
Query OK, 1 row affected (0.06 sec)

mysql> create database wd;
Query OK, 1 row affected (0.02 sec)

mysql> use wd;
Database changed
mysql> create table tw (id int auto_increment not null primary key) engine innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> show create table tw\G
*************************** 1. row ***************************
       Table: tw
Create Table: CREATE TABLE `tw` (
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql> insert into tw values (null),(null);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

mysql> truncate tw;
Query OK, 2 rows affected (0.05 sec)

mysql> show table status from wd\G
*************************** 1. row ***************************
           Name: tw
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: 2008-06-25 18:16:28
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment: InnoDB free: 5120 kB
1 row in set (0.00 sec)

mysql> select * from tw;
Empty set (0.00 sec)

mysql> insert into tw values (null),(null);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

mysql>

Thanks for the quick reply. I believe the problem may be the following: (I have attached the dump for my database along with some data so you can see)

When I truncate each table individually, you are correct the auto-increment value goes back to 1. I have relationships between these tables and am using foreign keys. When I truncate the first table (pdb_info) which deletes all the other records in the other tables (since I have set DELETE cascade), the auto_increment is set to 1 for the pdb_info table but not for the other, linked tables, whose value for auto_increment stays at the last row ID +1 value.

I do not know if this behaviour is by design or not.

I hope this info helps a bit more,

Thank you for the feedback. Indeed that behavior is by design and documented, please see:

http://dev.mysql.com/doc/refman/5.0/en/truncate.html
http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html

"Under some conditions, TRUNCATE tbl_name for an InnoDB table is mapped to DELETE FROM tbl_name and doesn't reset the AUTO_INCREMENT counter. See Section 12.2.9, “TRUNCATE Syntax”.