MySQL Bugs: #10497: Stored Procedure Table Locking problem

Bug #10497	Stored Procedure Table Locking problem
Submitted:	10 May 2005 7:41	Modified:	8 Aug 2005 17:05
Reporter:	John Nieman
Status:	Duplicate
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0.4 beta	OS:	Microsoft Windows (Win XP Home v2002 sp2)
Assigned to:	Dmitri Lenev	Target Version:

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