List:Commits« Previous MessageNext Message »
From:Mattias Jonsson Date:June 30 2010 7:28am
Subject:bzr commit into mysql-5.1-bugteam branch (mattias.jonsson:3453) Bug#53775
View as plain text  
#At file:///Users/mattiasj/mysql-bzr/b53775-51-bt/ based on revid:martin.hansson@stripped

 3453 Mattias Jonsson	2010-06-30
      Bug#53775: Query on partitioned table returns cached result
                 from previous transaction
      
      Partitioning uses the default implementation of the query cache
      logic, which is not correct for some engines (like InnoDB).
      
      Disabled query cache for partitioned tables, since it is not
      possible for a generic solution.
     @ mysql-test/include/query_cache.inc
        Bug#53775: Query on partitioned table returns cached result
                   from previous transaction
        
        Fixing it to allow usage with partitioning and without
        *-master.opt file
     @ mysql-test/r/cache_innodb.result
        Bug#53775: Query on partitioned table returns cached result
                   from previous transaction
        
        minor change due to changed .inc file
     @ mysql-test/r/partition_cache_innodb.result
        Bug#53775: Query on partitioned table returns cached result
                   from previous transaction
        
        added test for partitioned tables
     @ mysql-test/t/cache_innodb-master.opt
        Bug#53775: Query on partitioned table returns cached result
                   from previous transaction
        
        not needed any more.
     @ mysql-test/t/partition_cache_innodb.test
        Bug#53775: Query on partitioned table returns cached result
                   from previous transaction
        
        new test file
     @ sql/ha_partition.h
        Bug#53775: Query on partitioned table returns cached result
                   from previous transaction
        
        Disable query cache for partitioned tables.

    removed:
      mysql-test/t/cache_innodb-master.opt
    added:
      mysql-test/r/partition_cache_innodb.result
      mysql-test/t/partition_cache_innodb.test
    modified:
      mysql-test/include/query_cache.inc
      mysql-test/r/cache_innodb.result
      sql/ha_partition.h
=== modified file 'mysql-test/include/query_cache.inc'
--- a/mysql-test/include/query_cache.inc	2009-05-15 10:15:56 +0000
+++ b/mysql-test/include/query_cache.inc	2010-06-30 07:28:31 +0000
@@ -19,47 +19,61 @@ eval SET SESSION STORAGE_ENGINE = $engin
 drop table if exists t1,t2,t3;
 --enable_warnings
 
+let $save_query_cache_size=`select @@global.query_cache_size`;
+set GLOBAL query_cache_size=1355776;
+
 #
 # Without auto_commit.
 #
 flush status;
 set autocommit=0;
-create table t1 (a int not null);
+eval create table t1 (a int not null) $partition_clause;
 insert into t1 values (1),(2),(3);
+--sorted_result
 select * from t1;
 show status like "Qcache_queries_in_cache";
 drop table t1;
 commit;
 set autocommit=1;
 begin;
-create table t1 (a int not null);
+eval create table t1 (a int not null) $partition_clause;
 insert into t1 values (1),(2),(3);
+--sorted_result
 select * from t1;
 show status like "Qcache_queries_in_cache";
 drop table t1;
 commit;
-create table t1 (a int not null);
-create table t2 (a int not null);
-create table t3 (a int not null);
+eval create table t1 (a int not null) $partition_clause;
+eval create table t2 (a int not null) $partition_clause;
+eval create table t3 (a int not null) $partition_clause;
 insert into t1 values (1),(2);
 insert into t2 values (1),(2);
 insert into t3 values (1),(2);
+--sorted_result
 select * from t1;
+--sorted_result
 select * from t2;
+--sorted_result
 select * from t3;
 show status like "Qcache_queries_in_cache";
 show status like "Qcache_hits";
 begin;
+--sorted_result
 select * from t1;
+--sorted_result
 select * from t2;
+--sorted_result
 select * from t3;
 show status like "Qcache_queries_in_cache";
 show status like "Qcache_hits";
 insert into t1 values (3);
 insert into t2 values (3);
 insert into t1 values (4);
+--sorted_result
 select * from t1;
+--sorted_result
 select * from t2;
+--sorted_result
 select * from t3;
 show status like "Qcache_queries_in_cache";
 show status like "Qcache_hits";
@@ -67,9 +81,9 @@ commit;
 show status like "Qcache_queries_in_cache";
 drop table t3,t2,t1;
 
-CREATE TABLE t1 (id int(11) NOT NULL auto_increment, PRIMARY KEY  (id));
+eval CREATE TABLE t1 (a int(11) NOT NULL auto_increment, PRIMARY KEY (a)) $partition_clause;
 select count(*) from t1;
-insert into t1 (id) values (0);
+insert into t1 (a) values (0);
 select count(*) from t1;
 drop table t1;
 
@@ -78,11 +92,9 @@ if ($test_foreign_keys)
 #
 # one statement roll back inside transation
 #
-let $save_query_cache_size=`select @@global.query_cache_size`;
-set GLOBAL query_cache_size=1355776;
-CREATE TABLE t1 ( id int(10) NOT NULL auto_increment, a varchar(25) default NULL, PRIMARY KEY  (id), UNIQUE KEY a (a));
-CREATE TABLE t2 ( id int(10) NOT NULL auto_increment, b varchar(25) default NULL, PRIMARY KEY  (id), UNIQUE KEY b (b));
-CREATE TABLE t3 ( id int(10) NOT NULL auto_increment, t1_id int(10) NOT NULL default '0', t2_id int(10) NOT NULL default '0', state int(11) default NULL, PRIMARY KEY  (id), UNIQUE KEY t1_id (t1_id,t2_id), KEY t2_id (t2_id,t1_id), CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`), CONSTRAINT `t3_ibfk_2` FOREIGN KEY (`t2_id`) REFERENCES `t2` (`id`));
+eval CREATE TABLE t1 ( id int(10) NOT NULL auto_increment, a varchar(25) default NULL, PRIMARY KEY  (id), UNIQUE KEY a (a)) $partition_clause;
+eval CREATE TABLE t2 ( id int(10) NOT NULL auto_increment, b varchar(25) default NULL, PRIMARY KEY  (id), UNIQUE KEY b (b)) $partition_clause;
+eval CREATE TABLE t3 ( id int(10) NOT NULL auto_increment, t1_id int(10) NOT NULL default '0', t2_id int(10) NOT NULL default '0', state int(11) default NULL, PRIMARY KEY  (id), UNIQUE KEY t1_id (t1_id,t2_id), KEY t2_id (t2_id,t1_id), CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`), CONSTRAINT `t3_ibfk_2` FOREIGN KEY (`t2_id`) REFERENCES `t2` (`id`)) $partition_clause;
 INSERT INTO t1 VALUES (1,'me');
 INSERT INTO t2 VALUES (1,'you');
 INSERT INTO t3 VALUES (2,1,1,2);
@@ -95,9 +107,6 @@ insert into t3 VALUES ( NULL, 1, 1, 2 );
 commit;
 select t1.* from t1, t2, t3 where t3.state & 1 = 0 and t3.t1_id = t1.id and t3.t2_id = t2.id and t1.id = 1 order by t1.a asc;
 drop table t3,t2,t1;
---disable_query_log
-eval set GLOBAL query_cache_size=$save_query_cache_size;
---enable_query_log
 }
 
 #
@@ -118,12 +127,12 @@ SET GLOBAL query_cache_size = 200000;
 flush status;
 SET @@autocommit=1;
 eval SET SESSION STORAGE_ENGINE = $engine_type;
-CREATE TABLE t2 (s1 int, s2 varchar(1000), key(s1));
+eval CREATE TABLE t2 (a int, s2 varchar(1000), key(a)) $partition_clause;
 INSERT INTO t2 VALUES (1,repeat('a',10)),(2,repeat('a',10)),(3,repeat('a',10)),(4,repeat('a',10));
 COMMIT;
 START TRANSACTION;
 SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
-UPDATE t2 SET s2 = 'w' WHERE s1 = 3;
+UPDATE t2 SET s2 = 'w' WHERE a = 3;
 SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
 show status like "Qcache_queries_in_cache";
 
@@ -162,7 +171,7 @@ connection default;
 SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
 START TRANSACTION;
 SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
-DELETE from t2 WHERE s1=3;
+DELETE from t2 WHERE a=3;
 SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
 COMMIT;
 
@@ -176,7 +185,9 @@ show status like "Qcache_queries_in_cach
 show status like "Qcache_hits";
 
 # Final cleanup
+--disable_query_log
 eval set GLOBAL query_cache_size=$save_query_cache_size;
+--enable_query_log
 disconnect connection1;
 --source include/wait_until_disconnected.inc
 connection default;

=== modified file 'mysql-test/r/cache_innodb.result'
--- a/mysql-test/r/cache_innodb.result	2009-01-31 17:55:06 +0000
+++ b/mysql-test/r/cache_innodb.result	2010-06-30 07:28:31 +0000
@@ -1,8 +1,9 @@
 SET SESSION STORAGE_ENGINE = InnoDB;
 drop table if exists t1,t2,t3;
+set GLOBAL query_cache_size=1355776;
 flush status;
 set autocommit=0;
-create table t1 (a int not null);
+create table t1 (a int not null) ;
 insert into t1 values (1),(2),(3);
 select * from t1;
 a
@@ -16,7 +17,7 @@ drop table t1;
 commit;
 set autocommit=1;
 begin;
-create table t1 (a int not null);
+create table t1 (a int not null) ;
 insert into t1 values (1),(2),(3);
 select * from t1;
 a
@@ -28,9 +29,9 @@ Variable_name	Value
 Qcache_queries_in_cache	1
 drop table t1;
 commit;
-create table t1 (a int not null);
-create table t2 (a int not null);
-create table t3 (a int not null);
+create table t1 (a int not null) ;
+create table t2 (a int not null) ;
+create table t3 (a int not null) ;
 insert into t1 values (1),(2);
 insert into t2 values (1),(2);
 insert into t3 values (1),(2);
@@ -100,19 +101,18 @@ show status like "Qcache_queries_in_cach
 Variable_name	Value
 Qcache_queries_in_cache	2
 drop table t3,t2,t1;
-CREATE TABLE t1 (id int(11) NOT NULL auto_increment, PRIMARY KEY  (id));
+CREATE TABLE t1 (a int(11) NOT NULL auto_increment, PRIMARY KEY (a)) ;
 select count(*) from t1;
 count(*)
 0
-insert into t1 (id) values (0);
+insert into t1 (a) values (0);
 select count(*) from t1;
 count(*)
 1
 drop table t1;
-set GLOBAL query_cache_size=1355776;
-CREATE TABLE t1 ( id int(10) NOT NULL auto_increment, a varchar(25) default NULL, PRIMARY KEY  (id), UNIQUE KEY a (a));
-CREATE TABLE t2 ( id int(10) NOT NULL auto_increment, b varchar(25) default NULL, PRIMARY KEY  (id), UNIQUE KEY b (b));
-CREATE TABLE t3 ( id int(10) NOT NULL auto_increment, t1_id int(10) NOT NULL default '0', t2_id int(10) NOT NULL default '0', state int(11) default NULL, PRIMARY KEY  (id), UNIQUE KEY t1_id (t1_id,t2_id), KEY t2_id (t2_id,t1_id), CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`), CONSTRAINT `t3_ibfk_2` FOREIGN KEY (`t2_id`) REFERENCES `t2` (`id`));
+CREATE TABLE t1 ( id int(10) NOT NULL auto_increment, a varchar(25) default NULL, PRIMARY KEY  (id), UNIQUE KEY a (a)) ;
+CREATE TABLE t2 ( id int(10) NOT NULL auto_increment, b varchar(25) default NULL, PRIMARY KEY  (id), UNIQUE KEY b (b)) ;
+CREATE TABLE t3 ( id int(10) NOT NULL auto_increment, t1_id int(10) NOT NULL default '0', t2_id int(10) NOT NULL default '0', state int(11) default NULL, PRIMARY KEY  (id), UNIQUE KEY t1_id (t1_id,t2_id), KEY t2_id (t2_id,t1_id), CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`), CONSTRAINT `t3_ibfk_2` FOREIGN KEY (`t2_id`) REFERENCES `t2` (`id`)) ;
 INSERT INTO t1 VALUES (1,'me');
 INSERT INTO t2 VALUES (1,'you');
 INSERT INTO t3 VALUES (2,1,1,2);
@@ -138,14 +138,14 @@ SET GLOBAL query_cache_size = 200000;
 flush status;
 SET @@autocommit=1;
 SET SESSION STORAGE_ENGINE = InnoDB;
-CREATE TABLE t2 (s1 int, s2 varchar(1000), key(s1));
+CREATE TABLE t2 (a int, s2 varchar(1000), key(a)) ;
 INSERT INTO t2 VALUES (1,repeat('a',10)),(2,repeat('a',10)),(3,repeat('a',10)),(4,repeat('a',10));
 COMMIT;
 START TRANSACTION;
 SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
 count(*)
 0
-UPDATE t2 SET s2 = 'w' WHERE s1 = 3;
+UPDATE t2 SET s2 = 'w' WHERE a = 3;
 SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
 count(*)
 1
@@ -202,7 +202,7 @@ START TRANSACTION;
 SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
 count(*)
 2
-DELETE from t2 WHERE s1=3;
+DELETE from t2 WHERE a=3;
 SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
 count(*)
 1
@@ -218,5 +218,4 @@ Qcache_queries_in_cache	1
 show status like "Qcache_hits";
 Variable_name	Value
 Qcache_hits	1
-set GLOBAL query_cache_size=1048576;
 drop table t2;

=== added file 'mysql-test/r/partition_cache_innodb.result'
--- a/mysql-test/r/partition_cache_innodb.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/partition_cache_innodb.result	2010-06-30 07:28:31 +0000
@@ -0,0 +1,203 @@
+SET SESSION STORAGE_ENGINE = InnoDB;
+drop table if exists t1,t2,t3;
+set GLOBAL query_cache_size=1355776;
+flush status;
+set autocommit=0;
+create table t1 (a int not null) PARTITION BY HASH (a) PARTITIONS 3;
+insert into t1 values (1),(2),(3);
+select * from t1;
+a
+1
+2
+3
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+drop table t1;
+commit;
+set autocommit=1;
+begin;
+create table t1 (a int not null) PARTITION BY HASH (a) PARTITIONS 3;
+insert into t1 values (1),(2),(3);
+select * from t1;
+a
+1
+2
+3
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+drop table t1;
+commit;
+create table t1 (a int not null) PARTITION BY HASH (a) PARTITIONS 3;
+create table t2 (a int not null) PARTITION BY HASH (a) PARTITIONS 3;
+create table t3 (a int not null) PARTITION BY HASH (a) PARTITIONS 3;
+insert into t1 values (1),(2);
+insert into t2 values (1),(2);
+insert into t3 values (1),(2);
+select * from t1;
+a
+1
+2
+select * from t2;
+a
+1
+2
+select * from t3;
+a
+1
+2
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+begin;
+select * from t1;
+a
+1
+2
+select * from t2;
+a
+1
+2
+select * from t3;
+a
+1
+2
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+insert into t1 values (3);
+insert into t2 values (3);
+insert into t1 values (4);
+select * from t1;
+a
+1
+2
+3
+4
+select * from t2;
+a
+1
+2
+3
+select * from t3;
+a
+1
+2
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+commit;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+drop table t3,t2,t1;
+CREATE TABLE t1 (a int(11) NOT NULL auto_increment, PRIMARY KEY (a)) PARTITION BY HASH (a) PARTITIONS 3;
+select count(*) from t1;
+count(*)
+0
+insert into t1 (a) values (0);
+select count(*) from t1;
+count(*)
+1
+drop table t1;
+SET SESSION STORAGE_ENGINE = InnoDB;
+SET @@autocommit=1;
+connection default
+SHOW VARIABLES LIKE 'have_query_cache';
+Variable_name	Value
+have_query_cache	YES
+SET GLOBAL query_cache_size = 200000;
+flush status;
+SET @@autocommit=1;
+SET SESSION STORAGE_ENGINE = InnoDB;
+CREATE TABLE t2 (a int, s2 varchar(1000), key(a)) PARTITION BY HASH (a) PARTITIONS 3;
+INSERT INTO t2 VALUES (1,repeat('a',10)),(2,repeat('a',10)),(3,repeat('a',10)),(4,repeat('a',10));
+COMMIT;
+START TRANSACTION;
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+0
+UPDATE t2 SET s2 = 'w' WHERE a = 3;
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+1
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+connection connection1
+START TRANSACTION;
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+0
+INSERT INTO t2 VALUES (5,'w');
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+1
+COMMIT;
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+1
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+connection default
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+1
+COMMIT;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+2
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+connection connection1
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+2
+START TRANSACTION;
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+2
+INSERT INTO t2 VALUES (6,'w');
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+3
+connection default
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+2
+START TRANSACTION;
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+2
+DELETE from t2 WHERE a=3;
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+1
+COMMIT;
+connection connection1
+COMMIT;
+SELECT sql_cache count(*) FROM t2 WHERE s2 = 'w';
+count(*)
+2
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+drop table t2;

=== removed file 'mysql-test/t/cache_innodb-master.opt'
--- a/mysql-test/t/cache_innodb-master.opt	2006-08-16 12:58:49 +0000
+++ b/mysql-test/t/cache_innodb-master.opt	1970-01-01 00:00:00 +0000
@@ -1 +0,0 @@
---set-variable=query_cache_size=1M

=== added file 'mysql-test/t/partition_cache_innodb.test'
--- a/mysql-test/t/partition_cache_innodb.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/partition_cache_innodb.test	2010-06-30 07:28:31 +0000
@@ -0,0 +1,10 @@
+--source include/have_query_cache.inc
+--source include/have_innodb.inc
+--source include/have_partition.inc
+
+let $partition_clause= PARTITION BY HASH (a) PARTITIONS 3;
+let $engine_type= InnoDB;
+# Partitioned InnoDB does not supports FOREIGN KEYs
+let $test_foreign_keys= 0;
+
+--source include/query_cache.inc

=== modified file 'sql/ha_partition.h'
--- a/sql/ha_partition.h	2009-11-17 19:02:16 +0000
+++ b/sql/ha_partition.h	2010-06-30 07:28:31 +0000
@@ -519,6 +519,20 @@ public:
   virtual int extra(enum ha_extra_function operation);
   virtual int extra_opt(enum ha_extra_function operation, ulong cachesize);
   virtual int reset(void);
+  /*
+    Do not allow caching of partitioned tables, since we cannot return
+    a callback or engine_data that would work for a generic engine.
+  */
+  virtual my_bool register_query_cache_table(THD *thd, char *table_key,
+                                             uint key_length,
+                                             qc_engine_callback
+                                               *engine_callback,
+                                             ulonglong *engine_data)
+  {
+    *engine_callback= NULL;
+    *engine_data= 0;
+    return FALSE;
+  }
 
 private:
   static const uint NO_CURRENT_PART_ID;


Attachment: [text/bzr-bundle] bzr/mattias.jonsson@oracle.com-20100630072831-hu9iloxqoiikwp4a.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (mattias.jonsson:3453) Bug#53775Mattias Jonsson30 Jun