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

[15 Nov 2005 12:34] Arnold Greving
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 |
+----+
[15 Nov 2005 12:53] Miguel Solorzano
Thank you for the bug report.
[18 Jan 2006 9:34] Giuseppe Maxia
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
[8 Mar 2006 8:57] Magnus BlÄudd
Duplicate of bug#11932? Or at least caused by same problem.
[30 Mar 2006 7:15] 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/commits/4312
[6 Apr 2006 10:19] 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/commits/4549
[6 Apr 2006 18:40] Sergey Vojtovich
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.
[8 Apr 2006 13:24] Jon Stephens
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.