MySQL Bugs: #9566: explicit LOCK TABLE and store procedures result in illegal state

Bug #9566	explicit LOCK TABLE and store procedures result in illegal state
Submitted:	1 Apr 2005 16:43	Modified:	26 Apr 2005 16:29
Reporter:	Jan Kneschke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.3	OS:	Linux (Linux/x86)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
Using explicit LOCKing and SPs results in a illegals state for the SP handling.

MySQL thinks that not a single procedure exist anymore and you can't DROP them anymore, but you can't create a new one with the same name either as the SP still exists.

SHOW PROCEDURE STATUS still lists the SP.

How to repeat:
DELIMITER $$
DROP TABLE IF EXISTS __test3$$
CREATE TABLE __test3 (
  a INT
)$$
DROP PROCEDURE IF EXISTS test3$$
CREATE PROCEDURE test3()
BEGIN
  SELECT * FROM __test3;
END$$
LOCK TABLE __test3 WRITE$$
CALL test3()$$
## ERROR 1100 (HY000): Table 'proc' was not locked with LOCK TABLES
UNLOCK TABLES$$
DROP PROCEDURE test3$$
## ERROR 1305 (42000): PROCEDURE pasta.test3 does not exist
DELIMITER ;

Suggested fix:
fix the lock handling

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/23728

Fixed in 5.0.5

Patch does not help... for me

Same error

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/23740

Hi, Stas!

It is strange that this patch does not help you... I am pretty sure it should solve the problem and it solves it for me:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.4-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DELIMITER $$
mysql> DROP TABLE IF EXISTS __test3$$
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE __test3 (
    ->   a INT
    -> )$$
Query OK, 0 rows affected (0.65 sec)

mysql> DROP PROCEDURE IF EXISTS test3$$
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> CREATE PROCEDURE test3()
    -> BEGIN
    ->   SELECT * FROM __test3;
    -> END$$
Query OK, 0 rows affected (0.04 sec)

mysql> LOCK TABLE __test3 WRITE$$
Query OK, 0 rows affected (0.00 sec)

mysql> CALL test3()$$
ERROR 1100 (HY000): Table 'proc' was not locked with LOCK TABLES
# This error is expected currently you should lock mysql.proc explicitly

mysql> UNLOCK TABLES$$
Query OK, 0 rows affected (0.00 sec)

mysql> DROP PROCEDURE test3$$
Query OK, 0 rows affected (0.13 sec)
# As you see no error !!!

Could you please check everything one more time and provide your test case if it is different ?

Thank you!

Ok, you are right... my fault - no (second) error

How about: ERROR 1100 (HY000): Table 'proc' was not locked with LOCK TABLES ?
Where & when should I LOCK mysql.proc?

And once more quiestion - what abount bugs 9563 & 9565? I cant work with stored procedures on 5.0.3 & 5.0.4 while this bugs are open!

Thanks a lot
Stas

Hi, Stas!

Currently you have to lock mysql.proc table for READ with other tables in cases 
when you are going to use SP's (for example by calling them) under LOCK TABLES.

So you should have done something like:

LOCK TABLE __test3 WRITE, mysql.proc READ$$

We plan to remove this restriction in future...

Hope this helps!

Noted in 5.0.5 changelog.

I am trying to use lock tables and stored procedures. First I go the error:
Table 'proc' was not locked with LOCK TABLES

In order to fix it, I have used lock tables mysql.proc read, but this time I got the following error:
Table 'proc' was locked with a READ lock and can't be updated.

could you please guide me?