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
Triage: Triaged: D2 (Serious)

[19 May 2010 4:54] Brad Sumersford
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
[19 May 2010 6:45] Valeriy Kravchuk
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)
[19 May 2010 13:19] Brad Sumersford
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
[19 May 2010 17:51] Brad Sumersford
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.
[25 May 2010 18:01] Brad Sumersford
Test and Result files for query_cache_partition bug

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

[25 May 2010 18:04] Brad Sumersford
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
[26 May 2010 7:13] Sveta Smirnova
Thank you for the report.

Verified as described.
[24 Jun 2010 9:33] Konstantin Osipov
Matthias, this doesn't sound like specific to the query cache implementation.
Could you please take a look?
[29 Jun 2010 13:47] Mattias Jonsson
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.
[30 Jun 2010 7:29] 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/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.
[8 Jul 2010 12:59] Mikael Ronström
Disabling the query cache for partitioned tables
is a bit too harsh of a solution.
[11 Nov 2010 12:48] Mattias Jonsson
bug#58062 was marked as duplicate.
[1 Mar 2012 16:16] Jon Stephens
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
[1 Mar 2012 16:17] Jon Stephens
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.
[12 Apr 2012 20:17] Sveta Smirnova
Bug #64923 was marked as duplicate of this one.
[7 Jul 2012 22:42] Kolbe Kegel
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
[7 Jul 2012 22:54] Kolbe Kegel
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)
[7 Jul 2012 23:07] Kolbe Kegel
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)
[8 Jul 2012 4:52] Kolbe Kegel
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.
[8 Jul 2012 4:59] Kolbe Kegel
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.
[11 Jul 2012 17:08] Jon Stephens
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.
[12 Jul 2012 8:14] Jon Stephens
BUG#65541 is a duplicate of this bug.
[4 Nov 2014 21:31] Premal Shah
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.