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

[16 Apr 2013 14:14] Cyril Scetbon
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
[16 Apr 2013 15:14] MySQL Verification Team
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>
[17 Apr 2013 13:31] Cyril Scetbon
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.

[2 Jul 2014 12:45] Luca Scomparin
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$$
[16 Jul 2015 17:22] Markus Fantone
Is there any solution to this bug?
I'm facing the same problem on 5.6 MySql Instance.
[5 Nov 2015 15:43] Elena Stepanova
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.
[24 May 2016 5:05] Romain Riche
Still present in 5.6.30.... Is there any hope to get this fixed after 3 years since the first bug report?
[15 Jul 2016 14:19] Romain Riche
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?
[16 Jul 2016 19:59] MySQL Verification Team
http://bugs.mysql.com/bug.php?id=82247 marked as duplicate of this one.
[23 Jul 2016 19:50] Dmitry Lenev
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;
[27 Jul 2016 17:58] Paul DuBois
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.
[28 Jul 2016 2:18] Romain Riche
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?
[28 Jul 2016 2:21] Romain Riche
[sorry please ignore last part of my comment. Please read 5.6.x / 5.7.x ]
[4 Aug 2016 7:33] Romain Riche
Do you think this bug has a good chance to be fixed in the coming MySQL 5.6.33 ?
[18 Sep 2016 1:45] Romain Riche
This bug is now fixed on 5.6.33 and 5.7.15
[18 Sep 2016 3:01] Cyril SCETBON
I never thought it would take more than 3 years to get it fixed ...