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:
None 
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
Description:
Crash is occuring in Query_cache::invalidate ().  A small test case below.  If query 
cache size is set to 0 then the crash does not occur.

How to repeat:
######
DROP DATABASE IF EXISTS `test`;
CREATE DATABASE `test`;
USE `test`;

DROP PROCEDURE IF EXISTS `p1`;
DELIMITER //
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//
DELIMITER ;

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`;

call `p1`();
########
[24 May 2006 16:35] Miguel Solorzano
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] Shane Bester
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.