MySQL Bugs: #10497: Stored Procedure Table Locking problem

Bug #10497	Stored Procedure Table Locking problem
Submitted:	10 May 2005 5:41	Modified:	8 Aug 2005 15:05
Reporter:	John Nieman	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.4 beta	OS:	Windows (Win XP Home v2002 sp2)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Create table and insert into same table in Stored Procedure fails with ERROR 1100 (HY000): Table 'test2' was not locked with LOCK TABLES

How to repeat:
Content of sp_createBackupTable.txt:

Use test;
Drop procedure if exists createBackupTables;
delimiter //
Create procedure createBackupTables()
  LANGUAGE SQL
BEGIN
  Drop table if exists test2;
  Create table test2 like test;
  Insert into test2 select * from test;
END
//
delimiter ; 

Store Procedure created using command line mysql:

C:\Program Files\MySQL\MySQL Server 5.0\data\test>mysql -u root -p < "sp_createBackupTables.txt"
Enter password: *********

In MySQL Command Line Client:

use test;
call createBackupTables();
ERROR 1100 (HY000): Table 'test2' was not locked with LOCK TABLES

Suggested fix:
Remove requirement for lock or perform implicit LOCK TABLES prior to offending statement (though this is not a requirement outside a stored procedure).

Verified with 5.0bk on Linux.

Additional note: if table test2 does not exist when calling the procedure the error message is 

ERROR 1146 (42S02): Table 'test.test2' doesn't exist

instead

If the given stored procedure is separated into two procedures (one containing the drop and create commands, the second containing the insert command), the two procedures perform as expected from the command line client and Windows command line (provided test2 exists).  As Harmut Holzgraefe pointed out, if test2 does not exist the drop and create commands will fail with a different error (ERROR 1146 (42S02)).  An attempt to call the two stored procedures from a third stored procedure will result in the original error.

Thanks to John Nieman for the suggested fix but MySQL 5.0.7 does not accept LOCK/UNLOCK TABLES in an sproc. 

Bracketing a call to an sproc with LOCK/UNLOCK TABLES does not affect the error.

There appears to be no workaround for this bug, which is a showstopper for three projects we have underway.

Hi, John!

Problems that you have encountered are also described in bug #8766 and bug #8072.
Both these bugs were fixed in 5.0.11. I am marking this bug-report as duplicate.

This has been a showstopper for a project I'm working on as well.  I have to call a stored proc from an app I'm developing in C#, which returns the "Table 'table' was not locked with LOCK TABLES" error message each time I call the SP.   Any hope for resolution of this problem soon?

hi,
i could created the SP but when calling i got the error :
"ERROR 1146 (42S02): Table 'test.test2' doesn't exist"
i changed the SP so that it just creates a table(no drop and no update), but it gave me the same error,
why should a ddl occur in an SP at all?

Chagh

Hi Chagh,

Processing time can sometimes be reduced by creating a work table containing a target static subset of a very large table and using the small work table in subsequent processes.

Sincerely,
John Nieman