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

[1 Apr 2005 16:43] Jan Kneschke
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
[6 Apr 2005 21:39] 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/23728
[7 Apr 2005 7:36] Dmitry Lenev
Fixed in 5.0.5
[7 Apr 2005 7:38] Stas Nichiporovich
Patch does not help... for me

Same error
[7 Apr 2005 7:59] 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/23740
[7 Apr 2005 8:10] Dmitry Lenev
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!
[7 Apr 2005 8:30] Stas Nichiporovich
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
[7 Apr 2005 13:02] Dmitry Lenev
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!
[26 Apr 2005 16:29] Paul Dubois
Noted in 5.0.5 changelog.
[6 Sep 2011 13:31] Ali Fakhraee
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?