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.