MySQL Bugs: #77623: Table disappears within cursor executing a prepared statement

Bug #77623	Table disappears within cursor executing a prepared statement
Submitted:	6 Jul 2015 2:36	Modified:	21 Jul 2018 17:30
Reporter:	Jason Gerfen	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	5.6.25	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	concat(), cursor, innodb, loop, prepared statement, table disappears

Description:
Problem: When using a stored procedure to perform the functionality in the example below an error 'ERROR 1146 (42S02)' occurs.

1. A stored procedure is defined
2. A cursor loop is opened
3. A prepared statement is created with concat() (and tested without)
4. The statement is executed & access to the database tables creates the error mentioned (ERROR 1146 (42S02)).

I was originally attempting to use 5.7.7-fc but rolled back to 5.6.25 in hopes it was a bug with an unstable release.

How to repeat:
DROP PROCEDURE IF EXISTS test//
CREATE PROCEDURE test()
BEGIN

 DECLARE c INT DEFAULT 0;

 DECLARE tb CHAR(16) DEFAULT NULL;
 DECLARE fld CHAR(32) DEFAULT NULL;

 DECLARE ops CURSOR FOR SELECT `tbl`,`field` FROM `test`;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET c = 1;

 OPEN ops;
  LOOP1: loop
   FETCH ops INTO tb, fld;

   SET @check = CONCAT('SELECT COUNT(*) INTO @chk FROM `',tb,'` WHERE `keyID` = "',UID,'"');
    PREPARE stmt FROM @check;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    IF c THEN
     CLOSE ops;
     LEAVE LOOP1;
    END IF;

  END LOOP LOOP1;
 END IF;

END//

My apologies. The previous test case is inaccurate.

DELIMITER //
DROP PROCEDURE IF EXISTS Test_SP//
CREATE PROCEDURE Test_SP()
BEGIN

 DECLARE c INT DEFAULT 0;

 DECLARE tb CHAR(16) DEFAULT NULL;
 DECLARE fld CHAR(32) DEFAULT NULL;

 DECLARE ops CURSOR FOR SELECT `tbl`,`field` FROM `sqlSec_map`;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET c = 1;

 OPEN ops;
  LOOP1: loop
   FETCH ops INTO tb, fld;

   SET @sql = CONCAT('DESCRIBE `',tb,'`');
   PREPARE stmt FROM @sql;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;

   IF c THEN
    CLOSE ops;
    LEAVE LOOP1;
   END IF;

 END LOOP LOOP1;
END//

DELIMITER ;

It only seems to occur within a cursor loop. The above test case if printed with 'SELECT @sql' prior to the PREPARE & EXECUTE statements yields:

+---------------------------+
| @sql                      |
+---------------------------+
| DESCRIBE `'certificates'` |
+---------------------------+

Note the single quote?

If run outside of a cursor loop example:

mysql> set @tb = "certificates";
mysql> select concat('describe `',@tb,'`');
+------------------------------+
| concat('describe `',@tb,'`') |
+------------------------------+
| describe `certificates`      |
+------------------------------+
1 row in set (0.00 sec)

Thank you for the bug report. Is this test case complete, doesn't need a create table. Please print here the whole test case, the expected and real result?. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".