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 18:43	Modified:	26 Apr 2005 18:29
Reporter:	Jan Kneschke
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0.3	OS:	Linux (Linux/x86)
Assigned to:	Dmitri Lenev	Target Version:

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.