Bug #8850 Truncate table in a stored procedure locks the tables
Submitted: 28 Feb 2005 19:21 Modified: 8 Mar 2005 22:00
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.3 Alpha OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[28 Feb 2005 19: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 9:04] Marko Mäkelä 
Could this bug be caused because of Bug #8151?
[5 Mar 2005 19: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 20: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 22:00] MySQL Verification Team 
Pushed with Changeset 1.818