MySQL Bugs: #68972: Can't find temporary table

Bug #68972	Can't find temporary table
Submitted:	16 Apr 2013 14:14	Modified:	27 Jul 2016 17:58
Reporter:	Cyril Scetbon	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S1 (Critical)
Version:	5.6.10	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	temporary routine regression

Description:
Hi,

There is a regression bug between MySQL 5.5 and MySQL 5.6, when calling more than once a procedure that :
1)
calls another procedure which creates a temporary table 
2) tries to create a second temporary table from a subquery with the first temporary table previously created in 1)

I will provide you sample routines which raise the error "can't find temporary table" while we can access it just after the failing call

How to repeat:
use files provided to create routines and try more than once to call cyr2 procedure :

mysql> CALL cyr2('');
Query OK, 0 rows affected (0.02 sec)

mysql> CALL cyr2('');
ERROR 1146 (42S02): Table 'test.tmpSplitValues' doesn't exist

Thank you for the bug report.

miguel@tikal:~/dbs/5.5/data> cd ..
miguel@tikal:~/dbs/5.5> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.32-debug 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> source /home/miguel/tmp/proc.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> CALL cyr2('');
Query OK, 0 rows affected (0.07 sec)

mysql> CALL cyr2('');
Query OK, 0 rows affected (0.07 sec)

miguel@tikal:~/dbs/5.6> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.12-debug 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> source /home/miguel/tmp/proc.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected, 1 warning (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> CALL cyr2('');
Query OK, 0 rows affected (0.23 sec)

mysql> CALL cyr2('');
ERROR 1146 (42S02): Table 'test.tmpSplitValues' doesn't exist
mysql>

replacing "create temporary table as select" by "create temporary table + insert into select" is a temporary workaround for us

Attachment: patch3.diff (application/octet-stream, text), 762 bytes.

Hi,

still present in MySQL 5.6.19, in Windows 8.1 64 bit.

Another example of strange behavior:

call test(); -- OK
call test(); -- table "ttest" doesn't exists

STORED PROCEDURE TEXT:

CREATE PROCEDURE `test`()
BEGIN
DROP TEMPORARY TABLE IF EXISTS ttest;
CREATE TEMPORARY TABLE ttest AS
    SELECT 1 AS col_a; 

DROP TEMPORARY TABLE IF EXISTS testb;
CREATE TEMPORARY TABLE testb AS
  SELECT test2() AS col_b
  FROM (SELECT 1 FROM ttest) z;

END$$

CREATE FUNCTION `test2`() RETURNS DOUBLE
BEGIN
RETURN 0; 
END$$

Is there any solution to this bug?
I'm facing the same problem on 5.6 MySql Instance.

The problem was apparently introduced by this commit:

commit c2efb0c2962f91f5d2aaaf7553c90e66b892b8c0
Author: Dmitry Lenev <Dmitry.Lenev@oracle.com>
Date:   Fri Apr 1 22:08:48 2011 +0400

    One more follow-up for the patch for Bug#11746602
    27480: Extend CREATE TEMPORARY TABLES privilege to
    allow temp table operations).
    
    Changed code not to produce unwarranted ER_CANT_REOPEN errors
    in cases when prepared statement which used some table twice,
    directly and indirectly, was re-executed in presence of
    shadowing temporary table. Now, as expected, such statements
    are re-prepared after validation in these cases.
    
    Adjusted code checking privileges for tables from UNION
    clauses in CREATE TABLE and ALTER TABLE to make these
    statements re-execution safe.

Still present in 5.6.30.... Is there any hope to get this fixed after 3 years since the first bug report?

This regression is preventing me from migrating from 5.5 to 5.6.  I don't think the workaround mentioned before is an appropriate solution. Is there any hope to have that fixed one day?

http://bugs.mysql.com/bug.php?id=82247 marked as duplicate of this one.

Posted by developer:
 
Even simpler test case:

CREATE FUNCTION f1() RETURNS INT RETURN 1;
CREATE TEMPORARY TABLE tmp1(a INT);
PREPARE stmt1 FROM "CREATE TEMPORARY TABLE tmp2 AS SELECT b FROM (SELECT f1() AS b FROM tmp1) AS t";
# Fails (but should not!)
EXECUTE stmt1;
DROP TEMPORARY TABLES tmp1, tmp2;
DEALLOCATE PREPARE stmt1;
DROP FUNCTION f1;

Posted by developer:
 
Noted in 5.6.33, 5.7.15 changelogs.

A spurious ER_NO_SUCH_TABLE error could occur when attempting to
execute a prepared CREATE TABLE ... SELECT statement that used a
temporary table in the FROM clause and called a stored function. The
same error could occur for a nonprepared version of the statement if
used in a stored procedure when the procedure was re-executed.

The same error *could occur* for a nonprepared version of the statement if
used in a stored procedure when the procedure was re-executed.

From what I can test, I would say *will occur*. This issue is happening very consistently for me on 5.5.x

I guess you should see the same mention in 5.5.52 release notes?

[sorry please ignore last part of my comment. Please read 5.6.x / 5.7.x ]

Do you think this bug has a good chance to be fixed in the coming MySQL 5.6.33 ?

This bug is now fixed on 5.6.33 and 5.7.15

I never thought it would take more than 3 years to get it fixed ...