Bug #41348 INSERT INTO tbl SELECT * FROM temp_tbl overwrites locking type of temp table

Description:
Using certain prepared statements that have a function call have a READ lock that is causing problems. The prepared statement tested was INSERT INTO .. SELECT .. and the function can be as simple as a returning a string constant. The error shows as:

mysql>   EXECUTE stmt_sp_insert_b;
ERROR 1099 (HY000): Table '__b' was locked with a READ lock and can't be updated

Note that this has changed between 5.1.28 and 5.1.30.The statements work without the function, but otherwise you get the lock error.

How to repeat:
DROP TABLE IF EXISTS trac;

CREATE TABLE trac (
  NODEID VARCHAR(20),
  PARENTID VARCHAR(20),
  try VARCHAR(20)
) ENGINE=INNODB;

INSERT INTO trac VALUES ('a','top',NULL),('b','a',NULL),('c','b',NULL);

DELIMITER $$
drop FUNCTION if exists try;
CREATE FUNCTION try() RETURNS VARCHAR(250)
 BEGIN
     return 'hhhhhhh' ;
 END;$$

  
DROP TEMPORARY TABLE IF EXISTS TMPRESULTGRP;
DROP TEMPORARY TABLE IF EXISTS __b;

CREATE TEMPORARY TABLE __b (lvl INT, nodeid VARCHAR(20), parentid VARCHAR(20),try varchar(20)) ENGINE=INNODB;
CREATE TEMPORARY TABLE TMPRESULTGRP LIKE __b;

-- Problem statement with function call
SET @stmt := CONCAT('insert into __b select @lvl, tnt.nodeid, tnt.parentid, try()',' FROM trac tnt ');
-- Working statement without function call
-- SET @stmt := CONCAT('insert into __b select @lvl, tnt.nodeid, tnt.parentid, NULL',' FROM trac tnt ');
PREPARE stmt_sp_insert_b FROM @stmt;

SET @stmt := CONCAT('INSERT INTO TMPRESULTGRP', ' SELECT * FROM __b');
PREPARE stmt_sp_insert_res_tbl FROM @stmt;

EXECUTE stmt_sp_insert_b;

DEALLOCATE PREPARE stmt_sp_insert_b;
DROP TEMPORARY TABLE __b;

Thank you for a problem report. Sorry, but I can not repeat the behaviour described:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.1.30-enterprise-gpl-advanced MySQL Enterprise Server - Advance
d Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS trac;
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE trac (
    ->   NODEID VARCHAR(20),
    ->   PARENTID VARCHAR(20),
    ->   try VARCHAR(20)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO trac VALUES ('a','top',NULL),('b','a',NULL),('c','b',NULL);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> DELIMITER $$
mysql> drop FUNCTION if exists try$$
Query OK, 0 rows affected (0.19 sec)

mysql> CREATE FUNCTION try() RETURNS VARCHAR(250)
    ->  BEGIN
    ->      return 'hhhhhhh' ;
    ->  END;
    -> $$
Query OK, 0 rows affected (0.03 sec)

mysql> DELIMITER ;
mysql> DROP TEMPORARY TABLE IF EXISTS TMPRESULTGRP;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TEMPORARY TABLE IF EXISTS __b;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> CREATE TEMPORARY TABLE __b (
    ->   lvl INT,
    ->   nodeid VARCHAR(20),
    ->   parentid VARCHAR(20),
    ->   try varchar(20)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TEMPORARY TABLE TMPRESULTGRP LIKE __b;
Query OK, 0 rows affected (0.13 sec)

mysql>
mysql> SET @stmt := CONCAT('insert into __b select @lvl, tnt.nodeid, tnt.parenti
d, try()',
    -> ' FROM trac tnt ');
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt_sp_insert_b FROM @stmt;
Query OK, 0 rows affected (0.13 sec)
Statement prepared

mysql> EXECUTE stmt_sp_insert_b;
Query OK, 3 rows affected (0.09 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from __b;
+------+--------+----------+---------+
| lvl  | nodeid | parentid | try     |
+------+--------+----------+---------+
| NULL | a      | top      | hhhhhhh |
| NULL | b      | a        | hhhhhhh |
| NULL | c      | b        | hhhhhhh |
+------+--------+----------+---------+
3 rows in set (0.00 sec)

What I am doing wrong?

Correct test case is:

DROP TABLE IF EXISTS trac;

CREATE TABLE trac (
  NODEID VARCHAR(20),
  PARENTID VARCHAR(20),
  try VARCHAR(20)
) ENGINE=INNODB;

INSERT INTO trac VALUES ('a','top',NULL),('b','a',NULL),('c','b',NULL);

DELIMITER $$
drop FUNCTION if exists try$$
CREATE FUNCTION try() RETURNS VARCHAR(250)
 BEGIN
     return 'hhhhhhh' ;
 END;
$$

DELIMITER ;  

DROP TEMPORARY TABLE IF EXISTS TMPRESULTGRP;
DROP TEMPORARY TABLE IF EXISTS __b;

CREATE TEMPORARY TABLE __b (
  lvl INT, 
  nodeid VARCHAR(20), 
  parentid VARCHAR(20),
  try varchar(20)
) ENGINE=INNODB;
CREATE TEMPORARY TABLE TMPRESULTGRP LIKE __b;

SET @lvl := 1;

SET @stmt := CONCAT('insert into __b select @lvl, tnt.nodeid, tnt.parentid, try()',
' FROM trac tnt ');
PREPARE stmt_sp_insert_b FROM @stmt;

SET @stmt := CONCAT('INSERT INTO TMPRESULTGRP', ' SELECT * FROM __b');
PREPARE stmt_sp_insert_res_tbl FROM @stmt;
EXECUTE stmt_sp_insert_res_tbl;
DEALLOCATE PREPARE stmt_sp_insert_res_tbl;

EXECUTE stmt_sp_insert_b;

In 5.1.30 I've got:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.1.30-enterprise-gpl-advanced MySQL Enterprise Server - Advance
d Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS trac;
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> CREATE TABLE trac (
    ->   NODEID VARCHAR(20),
    ->   PARENTID VARCHAR(20),
    ->   try VARCHAR(20)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> INSERT INTO trac VALUES ('a','top',NULL),('b','a',NULL),('c','b',NULL);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> DELIMITER $$
mysql> drop FUNCTION if exists try$$
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION try() RETURNS VARCHAR(250)
    ->  BEGIN
    ->      return 'hhhhhhh' ;
    ->  END;
    -> $$
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql> DROP TEMPORARY TABLE IF EXISTS TMPRESULTGRP;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TEMPORARY TABLE IF EXISTS __b;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> CREATE TEMPORARY TABLE __b (
    ->   lvl INT,
    ->   nodeid VARCHAR(20),
    ->   parentid VARCHAR(20),
    ->   try varchar(20)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TEMPORARY TABLE TMPRESULTGRP LIKE __b;
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> SET @lvl := 1;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SET @stmt := CONCAT('insert into __b select @lvl, tnt.nodeid, tnt.parenti
d, try()',
    -> ' FROM trac tnt ');
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt_sp_insert_b FROM @stmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql>
mysql> SET @stmt := CONCAT('INSERT INTO TMPRESULTGRP', ' SELECT * FROM __b');
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt_sp_insert_res_tbl FROM @stmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt_sp_insert_res_tbl;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DEALLOCATE PREPARE stmt_sp_insert_res_tbl;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> EXECUTE stmt_sp_insert_b;
ERROR 1099 (HY000): Table '__b' was locked with a READ lock and can't be updated

In 5.0.72 it works as expected. This is a regression bug.

Reduced test case:

DELIMITER |;
CREATE FUNCTION f1() RETURNS VARCHAR(250)
 BEGIN
     return 'hhhhhhh' ;
 END|
DELIMITER ;|

CREATE TEMPORARY TABLE t1 (a VARCHAR(20));
CREATE TEMPORARY TABLE t2 LIKE t1;

PREPARE ps1 FROM "INSERT INTO t2 SELECT * FROM t1";

INSERT INTO t1 SELECT f1();

Initial analysis:

A statement prepare (ps1) can inadvertently overwrite the locking type of a temporary table to a read lock.

DELIMITER |;
CREATE FUNCTION f1() RETURNS VARCHAR(250)
 BEGIN
     return 'hhhhhhh' ;
 END|
DELIMITER ;|

CREATE TEMPORARY TABLE t1 (a VARCHAR(20));
CREATE TABLE t2 LIKE t1;

INSERT INTO t2 SELECT * FROM t1;
INSERT INTO t1 SELECT f1();

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/61787

2739 Davi Arnaut	2008-12-16
      Bug#41348: INSERT INTO tbl SELECT * FROM temp_tbl overwrites
                 locking type of temp table
      
      The problem is that INSERT INTO .. SELECT FROM .. and CREATE
      TABLE .. SELECT FROM a temporary table could inadvertently
      overwrite the locking type of the temporary table. The lock
      type of temporary tables must always be a write lock.
      
      The solution is to ensure that the lock type of temporary
      tables is never overwritten and that its always a write lock.

Another manifestation of this problem can be triggered with a UPDATE statement:

CREATE FUNCTION f1() RETURNS INT
 BEGIN
     return 1 ;
 END|
DELIMITER ;|

CREATE TEMPORARY TABLE t1 (a INT);
CREATE TEMPORARY TABLE t2 (a INT);

UPDATE t1,t2 SET t1.a = t2.a;
INSERT INTO t2 SELECT f1();

The general problem also affects MySQL 5.0.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/61853

2739 Davi Arnaut	2008-12-17
      Bug#41348: INSERT INTO tbl SELECT * FROM temp_tbl overwrites
                  locking type of temp table
      
      The problem is that INSERT INTO .. SELECT FROM .. and CREATE
      TABLE .. SELECT FROM a temporary table could inadvertently
      overwrite the locking type of the temporary table. The lock
      type of temporary tables should be a write lock by default.
      
      The solution is to reset the lock type of temporary tables
      back to its default value after they are used in a statement.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/62588

2728 Davi Arnaut	2009-01-07
      Bug#41348: INSERT INTO tbl SELECT * FROM temp_tbl overwrites
                 locking type of temp table
      
      The problem is that INSERT INTO .. SELECT FROM .. and CREATE
      TABLE .. SELECT FROM a temporary table could inadvertently
      overwrite the locking type of the temporary table. The lock
      type of temporary tables should be a write lock by default.
      
      The solution is to reset the lock type of temporary tables
      back to its default value after they are used in a statement.

Queued to 5.1-bugteam

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/63197

2748 He Zhenxing	2009-01-14 [merge]
      Auto merge

Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:davi.arnaut@sun.com-20090113150631-y84w12o2zmh7j3qd) (merge vers: 5.1.31) (pib:6)

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)

Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:davi.arnaut@sun.com-20090108185343-ccylwxgk2rm6plku) (merge vers: 6.0.10-alpha) (pib:6)

Noted in 5.1.31, 6.0.10 changelogs.

INSERT INTO .. SELECT ... FROM and CREATE TABLE ... SELECT ... FROM a
TEMPORARY table could inadvertently change the locking type of the
temporary table from a write lock to a read lock, causing statement 
failure.