| Bug #41348 | INSERT INTO tbl SELECT * FROM temp_tbl overwrites locking type of temp table | ||
|---|---|---|---|
| Submitted: | 10 Dec 2008 2:38 | Modified: | 29 Jan 2009 5:00 |
| Reporter: | Jonathon Coombes | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Locking | Severity: | S2 (Serious) |
| Version: | 5.1.30 | OS: | Any |
| Assigned to: | Davi Arnaut | CPU Architecture: | Any |
| Tags: | functions, locking, prepared statements, regression | ||
[10 Dec 2008 7:37]
Valeriy Kravchuk
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?
[10 Dec 2008 12:20]
Valeriy Kravchuk
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.
[15 Dec 2008 19:55]
Davi Arnaut
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.
[16 Dec 2008 13:05]
Davi Arnaut
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();
[16 Dec 2008 16:34]
Bugs System
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.
[17 Dec 2008 11:29]
Davi Arnaut
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.
[17 Dec 2008 11:38]
Bugs System
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.
[7 Jan 2009 12:12]
Bugs System
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.
[8 Jan 2009 18:30]
Davi Arnaut
Queued to 5.1-bugteam
[14 Jan 2009 9:33]
Bugs System
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
[15 Jan 2009 6:32]
Bugs System
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)
[19 Jan 2009 11:26]
Bugs System
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)
[19 Jan 2009 13:04]
Bugs System
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)
[19 Jan 2009 16:10]
Bugs System
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)
[20 Jan 2009 18:56]
Bugs System
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)
[29 Jan 2009 5:00]
Paul DuBois
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.
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;