MySQL Bugs: #21414: SP: Procedure undroppable, to some extent

Bug #21414	SP: Procedure undroppable, to some extent
Submitted:	2 Aug 2006 14:00	Modified:	5 Oct 2006 15:16
Reporter:	Andrey Hristov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0, 5.1	OS:	Any (All)
Assigned to:	Tomash Brechko	CPU Architecture:	Any

Description:
Executing FLUSH TABLES WITH READ LOCK from SP is possible. To unlock the tables UNLOCK TABLES is needed. However, even after UNLOCK TABLES the procedure which was used for FLUSH TABLES cannot be dropped. The server reports that it does not exist but a query against mysql.proc shows quite the opposite. The procedure can be dropped only after another CREATE PROCEDURE statement is executed.

How to repeat:
mysql> select version();
+------------------------------------+
| version()                          |
+------------------------------------+
| 5.1.12-beta-valgrind-max-debug-log |
+------------------------------------+
1 row in set (0.00 sec)

mysql> select name from mysql.proc where db=database();
Empty set (0.03 sec)

mysql> create procedure proc_1() flush tables with read lock;
Query OK, 0 rows affected (0.03 sec)

mysql> call proc_1();
Query OK, 0 rows affected (0.07 sec)

mysql> drop procedure proc_1;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
mysql> select name from mysql.proc where db=database();
+--------+
| name   |
+--------+
| proc_1 |
+--------+
1 row in set (0.05 sec)

mysql> call proc_1();
Query OK, 0 rows affected (0.03 sec)

mysql> drop procedure proc_1;
ERROR 1305 (42000): PROCEDURE dbxy.proc_1 does not exist
mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> drop procedure proc_1;
ERROR 1305 (42000): PROCEDURE dbxy.proc_1 does not exist
mysql> select name from mysql.proc where db=database();
+--------+
| name   |
+--------+
| proc_1 |
+--------+
1 row in set (0.06 sec)

mysql> drop procedure proc_1;
ERROR 1305 (42000): PROCEDURE dbxy.proc_1 does not exist
mysql> select name from mysql.proc where db=database();
+--------+
| name   |
+--------+
| proc_1 |
+--------+
1 row in set (0.03 sec)

mysql> create procedure proc_2() select 2;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> select name from mysql.proc where db=database();
+--------+
| name   |
+--------+
| proc_1 |
+--------+
1 row in set (0.04 sec)

mysql> drop procedure proc_1;
ERROR 1305 (42000): PROCEDURE dbxy.proc_1 does not exist
mysql> select name from mysql.proc where db=database();
+--------+
| name   |
+--------+
| proc_1 |
+--------+
1 row in set (0.03 sec)

mysql> create procedure proc_2() select 2;
Query OK, 0 rows affected (0.04 sec)

mysql> drop procedure proc_1;
Query OK, 0 rows affected (0.16 sec)

mysql> drop procedure proc_2;
Query OK, 0 rows affected (0.13 sec)

--------------------------------------------------------
select version();
select name from mysql.proc where db=database();
create procedure proc_1() flush tables with read lock;
call proc_1();
--error 1223
drop procedure proc_1;
select name from mysql.proc where db=database();
call proc_1();
drop procedure proc_1;
select name from mysql.proc where db=database();
--error 1305
drop procedure proc_1;
select name from mysql.proc where db=database();
--error 1223
create procedure proc_2() select 2;
unlock tables;
select name from mysql.proc where db=database();
--error 1305
drop procedure proc_1;
select name from mysql.proc where db=database();
create procedure proc_2() select 2;
drop procedure proc_1;
drop procedure proc_2;

Thank you for the bug report.

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create dbt
miguel@hegel:~/dbs/5.0> bin/mysql -uroot dbt
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.25-debug

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

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.25-debug | 
+--------------+
1 row in set (0.00 sec)

mysql> select name from mysql.proc where db=database();
Empty set (0.00 sec)

mysql> create procedure proc_1() flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> call proc_1();
Query OK, 0 rows affected (0.00 sec)

mysql> drop procedure proc_1;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
mysql> select name from mysql.proc where db=database();
+--------+
| name   |
+--------+
| proc_1 | 
+--------+
1 row in set (0.00 sec)

mysql> call proc_1();
Query OK, 0 rows affected (0.00 sec)

mysql> drop procedure proc_1;
ERROR 1305 (42000): PROCEDURE dbt.proc_1 does not exist
mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> drop procedure proc_1;
ERROR 1305 (42000): PROCEDURE dbt.proc_1 does not exist
mysql> select name from mysql.proc where db=database();
+--------+
| name   |
+--------+
| proc_1 | 
+--------+
1 row in set (0.00 sec)

mysql> drop procedure proc_1;
ERROR 1305 (42000): PROCEDURE dbt.proc_1 does not exist
mysql> select name from mysql.proc where db=database();
+--------+
| name   |
+--------+
| proc_1 | 
+--------+
1 row in set (0.01 sec)

mysql> create procedure proc_2() select 2;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> select name from mysql.proc where db=database();
+--------+
| name   |
+--------+
| proc_1 | 
+--------+
1 row in set (0.00 sec)

mysql> drop procedure proc_1;
ERROR 1305 (42000): PROCEDURE dbt.proc_1 does not exist
mysql> select name from mysql.proc where db=database();
+--------+
| name   |
+--------+
| proc_1 | 
+--------+
1 row in set (0.00 sec)

mysql> create procedure proc_2() select 2;
Query OK, 0 rows affected (0.01 sec)

mysql> drop procedure proc_1;
Query OK, 0 rows affected (0.01 sec)

mysql> drop procedure proc_2;
Query OK, 0 rows affected (0.01 sec)

mysql>

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/commits/11465

ChangeSet@1.2248, 2006-09-06 16:19:25+04:00, kroki@moonlight.intranet +3 -0
  BUG#21414: SP: Procedure undroppable, to some extent
  
  The problem was that if after FLUSH TABLES WITH READ LOCK the user
  issued DROP/ALTER PROCEDURE/FUNCTION the operation would fail (as
  expected), but after UNLOCK TABLE any attempt execute the same operation
  would lead to the error 1305 "PROCEDURE/FUNCTION does not exist",
  and an attempt to execute any stored function will also fail.
  
  The solution is not to reset mysql_proc_table_exists if we are in a
  GLOBAL READ LOCK mode (i.e. if THD::global_read_lock is set).

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/commits/11758

ChangeSet@1.2248, 2006-09-12 14:56:25+04:00, kroki@moonlight.intranet +5 -0
  BUG#21414: SP: Procedure undroppable, to some extent
  
  The problem was that if after FLUSH TABLES WITH READ LOCK the user
  issued DROP/ALTER PROCEDURE/FUNCTION the operation would fail (as
  expected), but after UNLOCK TABLE any attempt to execute the same
  operation would lead to the error 1305 "PROCEDURE/FUNCTION does not
  exist", and an attempt to execute any stored function will also fail.
  
  This happened because under FLUSH TABLES WITH READ LOCK we couldn't open
  and lock mysql.proc table for update, and this fact was erroneously
  remembered by setting mysql_proc_table_exists to false, so subsequent
  statements believed that mysql.proc doesn't exist, and thus that there
  are no functions and procedures in the database.
  
  As a solution, we remove mysql_proc_table_exists flag completely.  The
  reason is that this optimization didn't work most of the time anyway.
  Even if open of mysql.proc failed for some reason when we were trying to
  call a function or a procedure, we were setting mysql_proc_table_exists
  back to true to force table reopen for the sake of producing the same
  error message (the open can fail for number of reasons).  The solution
  could have been to remember the reason why open failed, but that's a lot
  of code for optimization of a rare case.  Hence we simply remove this
  optimization.

Approved over email. Dmitri is OK too.

Queued to 5.0-runtime and 5.1-runtime.

pushed to 5.1.12

Fixed in 5.0.27 and 5.1.12

Noted in 5.0.27, 5.1.12 changelogs.

After FLUSH TABLES WITH READ LOCK followed by UNLOCK TABLES, attempts
to drop or alter a stored routine failed with an error that the
routine did not exist, and attempts to execute the routine failed
with a lock conflict error.

The 5.0.x fix is in 5.0.30.