MySQL Bugs: #73581: Error: Can't reopen table when using trigger and procedure with the same name

Bug #73581	Error: Can't reopen table when using trigger and procedure with the same name
Submitted:	14 Aug 2014 11:05	Modified:	14 Aug 2014 12:04
Reporter:	Sergey Antonyuk	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.66-0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	can't reopen table, PROCEDURE, temporary table, trigger

Description:
When I use the trigger and procedure with the same name, I get an error "can't reopen table" while calling the procedure. The procedure performs DELETE from the table using JOIN with a temporary table. It's enough to rename the trigger and the problem disappears.

How to repeat:
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `id` INT
) Engine = InnoDB;

INSERT INTO t1 VALUES(1);

DELIMITER $$
DROP TRIGGER IF EXISTS `delete_t1`$$
CREATE TRIGGER `delete_t1` AFTER DELETE ON `t1`
    FOR EACH ROW SET @a=1;$$

DROP PROCEDURE IF EXISTS `T` $$
CREATE PROCEDURE `T`()
    SQL SECURITY DEFINER
BEGIN
    DROP TEMPORARY TABLE IF EXISTS `t2`;
    CREATE TEMPORARY TABLE `t2` (
      `id` INT
    ) Engine = Memory;
    INSERT INTO `t2` VALUES(1);

     CALL delete_t1();

    DROP TEMPORARY TABLE `t2`;
END $$

DROP PROCEDURE IF EXISTS `delete_t1` $$
CREATE PROCEDURE `delete_t1`()   # Name of the procedure is equal to the trigger name
    SQL SECURITY DEFINER
BEGIN
    DELETE FROM t1 USING t1 INNER JOIN (SELECT id FROM t2) AS tmp ON t1.id = tmp.id;
END $$

DELIMITER ;

CALL T();

# Actual result: error: can't reopen table t2. Nothing is deleted from t1;

# Expected resullt: no errors. t1 is clean.

Repeatable on 5.1 but not with 5.6. I will check 5.5:

C:\dbs>net start mysqld51
The MySQLD51 service is starting.
The MySQLD51 service was started successfully.

C:\dbs>51

C:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --debug-info --prompt="mysql 5.1 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.74-Win X64 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 > USE test
Database changed
mysql 5.1 > DROP TABLE IF EXISTS `t1`;
Query OK, 0 rows affected (0.06 sec)

mysql 5.1 > CREATE TABLE `t1` (
    ->   `id` INT
    -> ) Engine = InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql 5.1 >
mysql 5.1 > INSERT INTO t1 VALUES(1);
Query OK, 1 row affected (0.03 sec)

mysql 5.1 > DELIMITER $$
mysql 5.1 > DROP TRIGGER IF EXISTS `delete_t1`$$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.1 > CREATE TRIGGER `delete_t1` AFTER DELETE ON `t1`
    ->     FOR EACH ROW SET @a=1;$$
Query OK, 0 rows affected (0.09 sec)

mysql 5.1 >
mysql 5.1 > DROP PROCEDURE IF EXISTS `T` $$
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql 5.1 > CREATE PROCEDURE `T`()
    ->     SQL SECURITY DEFINER
    -> BEGIN
    ->     DROP TEMPORARY TABLE IF EXISTS `t2`;
    ->     CREATE TEMPORARY TABLE `t2` (
    ->       `id` INT
    ->     ) Engine = Memory;
    ->     INSERT INTO `t2` VALUES(1);
    ->
    ->      CALL delete_t1();
    ->
    ->     DROP TEMPORARY TABLE `t2`;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql 5.1 >
mysql 5.1 > DROP PROCEDURE IF EXISTS `delete_t1` $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.1 > CREATE PROCEDURE `delete_t1`()   # Name of the procedure is equal to the trigger name
    ->     SQL SECURITY DEFINER
    -> BEGIN
    ->     DELETE FROM t1 USING t1 INNER JOIN (SELECT id FROM t2) AS tmp ON t1.id = tmp.id;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql 5.1 >
mysql 5.1 > DELIMITER ;
mysql 5.1 > CALL T();
ERROR 1137 (HY000): Can't reopen table: 't2'
mysql 5.1 > exit
Bye

C:\dbs>net start mysqld56
The MySQLD56 service is starting...
The MySQLD56 service was started successfully.

C:\dbs>56

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.21 Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > USE test
Database changed
mysql 5.6 > DROP TABLE IF EXISTS `t1`;
Query OK, 0 rows affected (0.20 sec)

mysql 5.6 > CREATE TABLE `t1` (
    ->   `id` INT
    -> ) Engine = InnoDB;
Query OK, 0 rows affected (0.36 sec)

mysql 5.6 >
mysql 5.6 > INSERT INTO t1 VALUES(1);
Query OK, 1 row affected (0.03 sec)

mysql 5.6 >
mysql 5.6 > DELIMITER $$
mysql 5.6 > DROP TRIGGER IF EXISTS `delete_t1`$$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.6 > CREATE TRIGGER `delete_t1` AFTER DELETE ON `t1`
    ->     FOR EACH ROW SET @a=1;$$
Query OK, 0 rows affected (0.16 sec)

mysql 5.6 >
mysql 5.6 > DROP PROCEDURE IF EXISTS `T` $$
Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql 5.6 > CREATE PROCEDURE `T`()
    ->     SQL SECURITY DEFINER
    -> BEGIN
    ->     DROP TEMPORARY TABLE IF EXISTS `t2`;
    ->     CREATE TEMPORARY TABLE `t2` (
    ->       `id` INT
    ->     ) Engine = Memory;
    ->     INSERT INTO `t2` VALUES(1);
    ->
    ->      CALL delete_t1();
    ->
    ->     DROP TEMPORARY TABLE `t2`;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql 5.6 >
mysql 5.6 > DROP PROCEDURE IF EXISTS `delete_t1` $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.6 > CREATE PROCEDURE `delete_t1`()   # Name of the procedure is equal to the trigger name
    ->     SQL SECURITY DEFINER
    -> BEGIN
    ->     DELETE FROM t1 USING t1 INNER JOIN (SELECT id FROM t2) AS tmp ON t1.id = tmp.id;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql 5.6 >
mysql 5.6 > DELIMITER ;
mysql 5.6 >
mysql 5.6 > CALL T();
Query OK, 0 rows affected (0.06 sec)

5.5 not affected too:

C:\dbs>55

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.40 Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 > USE test
Database changed
mysql 5.5 > DROP TABLE IF EXISTS `t1`;
Query OK, 0 rows affected (0.08 sec)

mysql 5.5 > CREATE TABLE `t1` (
    ->   `id` INT
    -> ) Engine = InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql 5.5 >
mysql 5.5 > INSERT INTO t1 VALUES(1);
Query OK, 1 row affected (0.05 sec)

mysql 5.5 >
mysql 5.5 > DELIMITER $$
mysql 5.5 > DROP TRIGGER IF EXISTS `delete_t1`$$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.5 > CREATE TRIGGER `delete_t1` AFTER DELETE ON `t1`
    ->     FOR EACH ROW SET @a=1;$$
Query OK, 0 rows affected (0.13 sec)

mysql 5.5 >
mysql 5.5 > DROP PROCEDURE IF EXISTS `T` $$
Query OK, 0 rows affected, 1 warning (0.09 sec)

mysql 5.5 > CREATE PROCEDURE `T`()
    ->     SQL SECURITY DEFINER
    -> BEGIN
    ->     DROP TEMPORARY TABLE IF EXISTS `t2`;
    ->     CREATE TEMPORARY TABLE `t2` (
    ->       `id` INT
    ->     ) Engine = Memory;
    ->     INSERT INTO `t2` VALUES(1);
    ->
    ->      CALL delete_t1();
    ->
    ->     DROP TEMPORARY TABLE `t2`;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql 5.5 >
mysql 5.5 > DROP PROCEDURE IF EXISTS `delete_t1` $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.5 > CREATE PROCEDURE `delete_t1`()   # Name of the procedure is equal to the trigger name
    ->     SQL SECURITY DEFINER
    -> BEGIN
    ->     DELETE FROM t1 USING t1 INNER JOIN (SELECT id FROM t2) AS tmp ON t1.id = tmp.id;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql 5.5 >
mysql 5.5 > DELIMITER ;
mysql 5.5 >
mysql 5.5 > CALL T();
Query OK, 0 rows affected (0.06 sec)

Thank you for the bug report. I suggest you to upgrade according how to mentioned at http://www.mysql.com/support/eol-notice.html. Thanks.

Posted by developer:
 
This issue is fixed in all supported versions of MySQL server, so closing as not feasible to fix.