MySQL Bugs: #14945: Truncate table doesn't reset the auto

Bug #14945	Truncate table doesn't reset the auto_increment counter
Submitted:	15 Nov 2005 12:34	Modified:	8 Apr 2006 13:24
Reporter:	Arnold Greving	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.15/5.0.17 BK	OS:	Linux (Debian sid)
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any

Description:
When TRUNCATE TABLE is called within an stored procedure the auto_increment counter is not being reset to 0. The bug occurs both in the NDBCLUSTER and MyISAM storage engines (probably in more engines)

I'm using a cluster. My config:

Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @192.168.60.1  (Version: 5.0.15, Nodegroup: 0, Master)
id=3    @192.168.60.2  (Version: 5.0.15, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.60.62  (Version: 5.0.15)

[mysqld(API)]   10 node(s)
id=4    @192.168.60.11  (Version: 5.0.15)
id=5    @192.168.60.13  (Version: 5.0.15)
id=6    @192.168.60.12  (Version: 5.0.15)
id=7 (not connected, accepting connect from any host)
id=8 (not connected, accepting connect from any host)
id=9 (not connected, accepting connect from any host)
id=10 (not connected, accepting connect from any host)
id=11 (not connected, accepting connect from any host)
id=12 (not connected, accepting connect from any host)
id=13 (not connected, accepting connect from any host)

How to repeat:
create table test (id int primary key auto_increment) ; 

delimiter //
create procedure trunc()
begin
  truncate table fas_bizztravel.test;
end
//
delimiter ;

insert into test set id=null;
insert into test set id=null;
insert into test set id=null;

select * from test;

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

call trunc();

insert into test set id=null;
insert into test set id=null;
insert into test set id=null;

select * from test;
+----+
| id |
+----+
|  4 |
|  5 |
|  6 |
+----+

Thank you for the bug report.

Verified on MySQL 5.0.18. It affects MyISAM tables, but not InnoDB .

How to repeat:

create table test1 (id int not null auto_increment primary key) engine=MyISAM;
create procedure test_truncate() deterministic truncate test1;
insert into test1 values (null);
select * from test1;
+----+
| id |
+----+
|  1 |
+----+

call test_truncate();
insert into test1 values (null);
select * from test1;
+----+
| id |
+----+
|  2 |
+----+

truncate test1;
insert into test1 values (null);
select * from test1;
+----+
| id |
+----+
|  1 |
+----+

Regards
Giuseppe Maxia

Duplicate of bug#11932? Or at least caused by same problem.

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/commits/4312

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/commits/4549

Fixed in 5.0.21, 5.1.9. Please note that this problem still persists for ndb tables and will be tracked by BUG#18864. Actually ordinary TRUNCATE TABLE doesn't reset AUTO_INCREMENT value on ndb table.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.0.21 and 5.1.9 changelogs. Closed.