Bug #20045 | Qcache invalidate crashes when INSERT ... SELECT query uses a VIEW | ||
---|---|---|---|
Submitted: | 24 May 2006 13:31 | Modified: | 27 Nov 2006 18:16 |
Reporter: | Matthew Lord | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: General | Severity: | S1 (Critical) |
Version: | 5.0.21, 5.0.22-bk/5.1BK | OS: | Linux (Linux 2.4, 2.6 - Windows 2003) |
Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[24 May 2006 13:31]
Matthew Lord
[24 May 2006 16:35]
MySQL Verification Team
Server 5.1BK also is affected. mysql> mysql> call `p1`(); Query OK, 0 rows affected (0.02 sec) mysql> set global query_cache_size=1024*1024; Query OK, 0 rows affected (0.00 sec) mysql> call `p1`(); ERROR 2013 (HY000): Lost connection to MySQL server during query ---------------------------------------------------------------------- miguel@hegel:~/dbs> cd 5.1 miguel@hegel:~/dbs/5.1> bin/mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.1.11-beta-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> DROP DATABASE IF EXISTS `test`; Query OK, 4 rows affected (0.08 sec) mysql> CREATE DATABASE `test`; Query OK, 1 row affected (0.01 sec) mysql> USE `test`; Database changed mysql> mysql> DROP PROCEDURE IF EXISTS `p1`; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> DELIMITER // mysql> CREATE PROCEDURE `p1`() -> BEGIN -> start transaction; -> SET @sqlstr="INSERT INTO t1(`c1`) SELECT `c1` FROM v1"; -> PREPARE stmt FROM @sqlstr; -> EXECUTE stmt; -> DEALLOCATE PREPARE stmt; -> END// Query OK, 0 rows affected (0.02 sec) mysql> DELIMITER ; mysql> mysql> DROP TABLE IF EXISTS `t1`; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TABLE `t1`(`c1` int) ENGINE=MyISAM DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.01 sec) mysql> mysql> DROP TABLE IF EXISTS `t2`; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TABLE `t2`(`c1` INT) ENGINE=MyISAM DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.00 sec) mysql> mysql> DROP TABLE IF EXISTS `t3`; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TABLE `t3`(`c1` INT) ENGINE=MyISAM DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.01 sec) mysql> mysql> DROP TABLE IF EXISTS `v1`; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> DROP VIEW IF EXISTS `v1`; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE VIEW `v1` AS select `t3`.`c1` AS `c1` FROM `t3`,`t2` WHERE `t3`.`c1` = -> `t2`.`c1`; Query OK, 0 rows affected (0.01 sec) mysql> mysql> call `p1`(); Query OK, 0 rows affected (0.02 sec) mysql> set global query_cache_size=1024*1024; Query OK, 0 rows affected (0.01 sec) mysql> call `p1`(); ERROR 2013 (HY000): Lost connection to MySQL server during query mysql>
[25 May 2006 2:37]
MySQL Verification Team
Doesn't need the SP to crash. Here's a simpler testcase: ##### DROP DATABASE IF EXISTS `test`; CREATE DATABASE `test`; USE `test`; DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1`(`c1` int) ENGINE=MyISAM DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS `t2`; CREATE TABLE `t2`(`c1` INT) ENGINE=MyISAM DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS `t3`; CREATE TABLE `t3`(`c1` INT) ENGINE=MyISAM DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS `v1`; DROP VIEW IF EXISTS `v1`; CREATE VIEW `v1` AS select `t3`.`c1` AS `c1` FROM `t3`,`t2` WHERE `t3`.`c1` = `t2`.`c1`; start transaction; SET @sqlstr="INSERT INTO t1(`c1`) SELECT `c1` FROM v1"; PREPARE stmt FROM @sqlstr; EXECUTE stmt; DEALLOCATE PREPARE stmt; #####
[18 Jul 2006 19:00]
Konstantin Osipov
This is not related to stored procedures or prepared statements and is a regression caused by the fix for Bug#14767. Below is a short test case that demonstrates the problem: set global query_cache_size=8000000; drop table if exists t1, t2, t3; drop view if exists v1; create table t1(c1 int); create table t2(c1 int); create table t3(c1 int); create view v1 as select t3.c1 as c1 from t3,t2 where t3.c1 = t2.c1; start transaction; insert into t1(c1) select c1 from v1;
[18 Jul 2006 19:43]
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/9298
[1 Sep 2006 8:29]
Konstantin Osipov
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/11204 ChangeSet@1.2257, 2006-09-01 04:26:30+04:00, kostja@bodhi.local +3 -0 A fix and a test case for Bug#20045 "cache invalidate crashes when INSERT .. SELECT the query uses a VIEW". When INSERT ... SELECT used a view in the SELECT list that was not inlined, and there was an active transaction, the server could crash in Query_cache::invalidate. The fix is to distinguish views from base tables in query cache and invalidate them by name. The following alternative solutions were considered: 1) Skip views when invalidating tables. This would introduce a bug as queries like select * from v1 wouldn't get invalidated after after an insert via a view. 2) Exclude views from query cache altogether. Bad, as we'd like queries that use views (like select * from v1) to be cacheable. 3) Invalidate only the first table in insert .. select. The fact that insert ... select invalidates all used tables is a bug and was reported separately (Bug#21959). Besides, fixing only insert .. select won't fix the problem as there're also multi-delete and multi-update. 4) Update query cache code to properly handle views. (Implemented in this patch).
[9 Nov 2006 17:09]
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/15099 ChangeSet@1.2304, 2006-11-09 18:05:40+03:00, evgen@moonbone.local +5 -0 Bug#20045: Server crash on INSERT ... SELECT with a view The regression is caused by the fix for bug 14767. When INSERT ... SELECT used a view in the SELECT list that was not inlined, and there was an active transaction, the server could crash in Query_cache::invalidate. On INSERT ... SELECT only the table being inserted into is invalidated. Thus views that can't be inlined are skipped from invalidation.
[14 Nov 2006 16:48]
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/15305 ChangeSet@1.2304, 2006-11-14 19:50:44+03:00, evgen@moonbone.local +3 -0 Bug#20045: Server crash on INSERT ... SELECT ... FROM non-mergeable view The regression is caused by the fix for bug 14767. When INSERT ... SELECT used a view in the SELECT list that was not inlined, and there was an active transaction, the server could crash in Query_cache::invalidate. On INSERT ... SELECT only the table being inserted into is invalidated. Thus views that can't be inlined are skipped from invalidation. The bug manifests itself in two ways so there is 2 test cases. One checks that the only the table being inserted into is invalidated. And the second one checks that there is no crash on INSERT ... SELECT.
[27 Nov 2006 17:13]
Georgi Kodinov
Pushed in 5.0.32/5.1.14-beta
[27 Nov 2006 18:16]
Paul DuBois
Noted in 5.0.32, 5.1.14 changelogs. Invalidating the query cache caused a server crash for INSERT INTO ... SELECT statements that selected from a view.