MySQL Bugs: #53775: Query on partitioned table returns cached result from previous transaction

Bug #53775	Query on partitioned table returns cached result from previous transaction
Submitted:	19 May 2010 4:54	Modified:	11 Jul 2012 17:08
Reporter:	Brad Sumersford	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.1.x, 5.6.99	OS:	Any (MAC OS X, Linux)
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
When staring a transaction and updating a row and selecting that row, the same result will be returned after a rollback and the identical select query is issued in a new transaction.  Affects only a partitioned table with query cache enabled.

This bug is similar to http://bugs.mysql.com/bug.php?id=4213 but particular to partitioned tables

Tested on 5.1.37 & 5.1.46 compiled on OS X Leopard with the following flags

./configure CC='gcc -arch x86_64' CXX='g++ -arch x86_64' CFLAG="-O3 -fno-omit-frame-pointer" CXXFLAGS="-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" --prefix=/usr/local/mysql --with-plugins=max

How to repeat:
=== Create a partitioned table (using range, but any will suffice) ===

  CREATE TABLE `widgets` (
    `id` int(11) NOT NULL ,
    `created_at` datetime NOT NULL,
    `cool` tinyint default 0
  ) ENGINE=InnoDB;

  ALTER TABLE widgets PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION month_2010_4 VALUES LESS THAN (734258),
    PARTITION month_2010_5 VALUES LESS THAN (734289),
    PARTITION month_max VALUES LESS THAN MAXVALUE
  );

=== Insert a single row ===

INSERT INTO widgets VALUES (1, now(), 0);

=== Steps to repeat ===

BEGIN;
UPDATE `widgets` SET `cool` = 1 WHERE `id` = 1;
SELECT cool FROM `widgets` WHERE (`widgets`.id = 1) LIMIT 1; # => 1
ROLLBACK;
SELECT cool FROM `widgets` WHERE (`widgets`.id = 1) LIMIT 1; # => 0
BEGIN;
SELECT cool FROM `widgets` WHERE (`widgets`.id = 1) LIMIT 1; # => 1
ROLLBACK;
SELECT cool FROM `widgets` WHERE (`widgets`.id = 1) LIMIT 1; # => 0

I can not repeat it with current 5.1.48 from bzr:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.48-debug-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  CREATE TABLE `widgets` (
    ->     `id` int(11) NOT NULL ,
    ->     `created_at` datetime NOT NULL,
    ->     `cool` tinyint default 0
    ->   ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.13 sec)

mysql>   ALTER TABLE widgets PARTITION BY RANGE (TO_DAYS(created_at)) (
    ->     PARTITION month_2010_4 VALUES LESS THAN (734258),
    ->     PARTITION month_2010_5 VALUES LESS THAN (734289),
    ->     PARTITION month_max VALUES LESS THAN MAXVALUE
    ->   );
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO widgets VALUES (1, now(), 0);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE `widgets` SET `cool` = 1 WHERE `id` = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT cool FROM `widgets` WHERE (`widgets`.id = 1) LIMIT 1; # => 1
+------+
| cool |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT cool FROM `widgets` WHERE (`widgets`.id = 1) LIMIT 1;
+------+
| cool |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT cool FROM `widgets` WHERE (`widgets`.id = 1) LIMIT 1; # => 1
+------+
| cool |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT cool FROM `widgets` WHERE (`widgets`.id = 1) LIMIT 1;
+------+
| cool |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

What value do you have set for query_cache_size?  I am running with query_cache_size = 64M.  

I am going to re-try on a standard linux distro

I have reproduced this on a Debian linux machine with the latest snapshot I could download.

$ uname -a
Linux Varrus 2.6.24-1-amd64 #1 SMP Sat May 10 09:28:10 UTC 2008 x86_64 GNU/Linux

mysql> show variables like '%version%';
+-------------------------+-----------------------------+
| Variable_name           | Value                       |
+-------------------------+-----------------------------+
| protocol_version        | 10                          |
| version                 | 5.1.48-snapshot20100506-log |
| version_comment         | Source distribution         |
| version_compile_machine | x86_64                      |
| version_compile_os      | unknown-linux-gnu           |
+-------------------------+-----------------------------+
5 rows in set (0.00 sec)

Using support-files/my-medium.cnf as my.cnf BUT ADDING AND ONLY ADDING query_cache_size = 64M to the configuration.

Without the query_cache_size declaration the bug is not observed.

Test and Result files for query_cache_partition bug

Attachment: query_cache_partition_bug.tar.gz (application/x-gzip, text), 1.42 KiB.

After running a debug version of mysql with --debug=d,qcache,query:o,/tmp/mysqld.trace I noticed that when the table is not partitioned the cache insertion is refused (due to the previous update of the table)

Query_cache::ask_handler_allowance: qcache: Handler does not allow caching for test.widgets

However when the table IS partitioned, the cache insertion is made for a in_trans: 1 cache hit.  This cache hit is later returned when the second transaction is started and the in_trans: 1 matches again.

This is due to the absence of a defined register_query_cache_table method on ha_partition, instead the parent handler method is called which always returns true.

I have submitted a simple query_cache_partition.test and query_cache_partition.result

Thank you for the report.

Verified as described.

Matthias, this doesn't sound like specific to the query cache implementation.
Could you please take a look?

Brad, your analysis is correct, and the best fix I can come up with is what you propose, disable query cache for partitioned tables.

If we implement ha_partition::register_query_cache_table, then we would have a big problem on the callback function, we would probably have to have a separate ha_partition callback function for each storage engine. And it would need to call the correct callback function for the real storage engine, for each partition, which could be a lot of overhead.

Also the current implementation in 5.1 uses a ulonglong engine_data, which is specific to each engine (not used in MyISAM or InnoDB) partitioning should not allow to cache a query if any partition returns engine_data != 0.

So I will propose a small patch which never allows caching a query which includes a partitioned table.

Changing category to Server: Partitioning.

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/112520

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.

Disabling the query cache for partitioned tables
is a bit too harsh of a solution.

bug#58062 was marked as duplicate.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Fixed in 5.1+. Documented in the 5.1.63, 5.5.23, and 5.6.5 changelogs as follows:

        After updating a row of a partitioned table and selecting that
        row within the same transaction with the query cache enabled,
        then performing a rollback, the same result was returned by an
        identical SELECT issued in a new transaction.

Closed.

Bug #64923 was marked as duplicate of this one.

So, the "fix" for this bug is to make the query cache entirely inoperative for any partitioned table? Surely that includes some mention on some, most, or perhaps *all* of the following pages!

http://dev.mysql.com/doc/refman/5.5/en/query-cache.html

http://dev.mysql.com/doc/refman/5.5/en/partitioning-limitations.html

http://dev.mysql.com/doc/refman/5.5/en/query-cache-operation.html

http://dev.mysql.com/doc/refman/5.5/en/news-5-5-23.html

The changelog entry for 5.5.23 doesn't even mention that the effect of the change is a pretty large change in behavior from previous releases.

Kolbe

I see Mikael's comment that "Disabling the query cache for partitioned tables is a bit too harsh of a solution.", but I don't see any text stating whether that was, in the end, the solution chosen. It appears that the query cache *is* disabled for any partitioned tables as of MySQL 5.5.23. Is this the intended behavior, or should this be filed as a new bug?

drop table if exists t1;
create table t1 (id int) partition by range (id) (partition p0 values less than maxvalue);
insert into t1 values (1);
set global query_cache_type=1;
set global query_cache_size=10*1024*1024;
flush status;
reset query cache;
show status like 'Qcache%';
select * from t1;
show status like 'Qcache%';
select * from t1;
show status like 'Qcache%';

In MySQL 5.5.22:

mysql 5.5.22 (root) [test]> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5.22 (root) [test]> create table t1 (id int) partition by range (id) (partition p0 values less than maxvalue);
Query OK, 0 rows affected (0.06 sec)

mysql 5.5.22 (root) [test]> insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql 5.5.22 (root) [test]> set global query_cache_type=1;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5.22 (root) [test]> set global query_cache_size=10*1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5.22 (root) [test]> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5.22 (root) [test]> reset query cache;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5.22 (root) [test]> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 10468280 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql 5.5.22 (root) [test]> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql 5.5.22 (root) [test]> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 10466744 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 1        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql 5.5.22 (root) [test]> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql 5.5.22 (root) [test]> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 10466744 |
| Qcache_hits             | 1        |
| Qcache_inserts          | 1        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+
8 rows in set (0.00 sec)

And in MySQL 5.5.23:

mysql 5.5.23 (root) [test]> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5.23 (root) [test]> create table t1 (id int) partition by range (id) (partition p0 values less than maxvalue);
insert into t1 values (1);
set global query_cache_type=1;
set global query_cache_size=10*1024*1024;
flush status;
reset query cache;
show status like 'Qcache%';
select * from t1;
show status like 'Qcache%';
select * from t1;
show status like 'Qcache%';Query OK, 0 rows affected (0.09 sec)

mysql 5.5.23 (root) [test]> insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql 5.5.23 (root) [test]> set global query_cache_type=1;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5.23 (root) [test]> set global query_cache_size=10*1024*1024;
Query OK, 0 rows affected (0.01 sec)

mysql 5.5.23 (root) [test]> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5.23 (root) [test]> reset query cache;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5.23 (root) [test]> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 10468280 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql 5.5.23 (root) [test]> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql 5.5.23 (root) [test]> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 10468280 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

mysql 5.5.23 (root) [test]> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql 5.5.23 (root) [test]> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 10468280 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)

Looks like the complete removal of support for use of query cache with partitioning was indeed the intended resolution for this bug. The documentation certainly needs to be updated to better reflect this change.

        ------------------------------------------------------------
        revno: 2661.803.1
        committer: Mattias Jonsson <mattias.jonsson@oracle.com>
        branch nick: b11761296-5.1_no_qc
        timestamp: Mon 2012-02-20 22:59:11 +0100
        message:
          Bug#11761296: 53775: QUERY ON PARTITIONED TABLE RETURNS CACHED
                                                  RESULT FROM PREVIOUS TRANSACTION
          
          The current Query Cache API is not fully compatible with
          the partitioning engine.
          
          There is no good way to implement support for QC due to:
          1) a static callback for ha_partition would need to have access
          to all partition names and call the underlying callback for each
          [sub]partition with the correct name.
          2) pruning would be impossible, even if one used the ulonglong
          engine_data due to if engine_data is changed, the table is
          invalidated by the QC.
          
          So the only viable solution to avoid incorrect data is to not allow
          caching of queries using partitioned tables.
          
          (There are some extra changes, due to removal of \r as line break)

It will be interesting to see if this change means that the Query Cache will be completely unavailable for MySQL Cluster 7.2.7, which is to be based on MySQL 5.5.25a. The most recent version of MySQL Cluster, 7.6, is based on MySQL 5.5.22, which does not include this new behavior of disabled query cache for partitioned tables.

Bug #65541 is (more or less) a request for documentation for *this* bug, and it has bee sitting as "Verified" for a month without any activity or assignment.

Revised changelog entry in the 5.1.63, 5.5.23, and 5.6.5 changelogs:

    The query cache did not always function correctly with
    partitioned tables in a transactional context. For this reason,
    the query cache is now disabled for any queries using
    partitioned tables, and such queries can no longer be cached.
    For more information, see "Restrictions and Limitations on 
    Partitioning".

Also noted behaviour change in the indicated section as well as that discussing the query cache in the 5.1+ versions of the Manual.

Returned to Closed status.

BUG#65541 is a duplicate of this bug.

Is this every going to be fixed. It seems like a very critical function of the query cache was removed and had not been worked on since many months.
In the bug is not getting fixed, at least allow a system variable which can get around it. Our use case does not care about this bug, we should be allowed to cache queries.