Bug #8850 Truncate table in a stored procedure locks the tables
Submitted: 28 Feb 2005 20:21 Modified: 8 Mar 2005 23:00
Reporter: Shane Bester
Status: Closed
Category:Server Severity:S3 (Non-critical)
Version:5.0.3 Alpha OS:Microsoft Windows (Windows XP)
Assigned to: Sinisa Milivojevic Target Version:

[28 Feb 2005 20:21] Shane Bester 
Description:
Truncate table in a stored procedure locks the tables.

How to repeat:
CREATE PROCEDURE `dbpos`.`uputest`()
begin
truncate table slsruntimemap;

insert into slsruntimemap
(
lmapid
)
values
(
1
);
end

I get the following error message;

ERROR 1192 (HY000): Can't execute the given command because you have active locked tables
or an active transaction;

A workaround is to change the procedure to the following (however this will cause issues
with any auto-incrementing columns in the tables as they will not be reset)

CREATE PROCEDURE `dbpos`.`uputest`()
begin
delete from slsruntimemap;

insert into slsruntimemap
(
lmapid
)
values
(
1
);
end

Suggested fix:
Find out why TRUNCATE TABLE is keeping the tables locked.
[1 Mar 2005 10:04] Marko Mäkelä 
Could this bug be caused because of Bug #8151?
[5 Mar 2005 20:32] 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/internals/22695
[8 Mar 2005 21:33] 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/internals/22794
[8 Mar 2005 23:00] Sinisa Milivojevic 
Pushed with Changeset 1.818