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

[6 Jul 2015 2:36] Jason Gerfen
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//
[6 Jul 2015 2:48] Jason Gerfen
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 ;
[6 Jul 2015 3:00] Jason Gerfen
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)
[21 Jun 2018 17:30] Miguel Solorzano
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.
[22 Jul 2018 1:00] Bugs System
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".