Bug #58062 Results incorrect after rollback on Partitioned table
Submitted: 8 Nov 2010 20:30 Modified: 11 Nov 2010 12:47
Reporter: James Estes Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.41-3, 5.1.54, 5.1, 5.6.99 bzr OS:Linux (ubuntu12.1)
Assigned to: CPU Architecture:Any
Tags: cache, count, partitioning
Triage: Needs Triage: D2 (Serious)

[8 Nov 2010 20:30] James Estes
Description:
We are getting incorrect results from queries against a partitioned table after rollbacks.  

This may have something to do with a statement cache not properly invalidating after a rollback.  You can switch out the "test" queries used in the "How to repeat" script with any select (e.g.  select * from person_p) and get the same incorrect behavior.  If you change the second query by just adding a space (ie "select    count(*) from person_p") the second query will return the correct result.  This is what makes me suspect some sort of statement cache not being invalidated. 

Note that the FIRST query after rollback gets the correct result.  Only subsequent queries have the incorrect result.

This same behavior is not occurring against a non-partitioned version of the same table (to verify, just take the partition by declaration out of the test and re-run).

How to repeat:
From a mysql session, run the following:

drop table if exists person_p;
create table person_p(
 name varchar(128), 
 dept int, 
 primary key(dept,name)
) ENGINE=InnoDB
PARTITION BY RANGE (`dept`) 
(
 PARTITION d0 VALUES LESS THAN (1),
 PARTITION d1 VALUES LESS THAN (2), 
 PARTITION d2 VALUES LESS THAN (3),
 PARTITION d_other VALUES LESS THAN MAXVALUE);

set autocommit=0;
start transaction;

insert into person_p(name,dept) values('a',1);
insert into person_p(name,dept) values('b',1);
insert into person_p(name,dept) values('c',1);

select count(*) from person_p;
-- expecting 3
rollback;

select count(*) from person_p;
-- expecting 0, and seeing 0

select count(*) from person_p;
-- expecting 0, and seeing 3!
[9 Nov 2010 2:16] Miguel Solorzano
Could you please upgrade to the latest released version and comment the results?
Thanks in advance.

C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3306 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.53-Win X64 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 5.1 >use test
Database changed
mysql 5.1 >drop table if exists person_p;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.1 >create table person_p(
    ->  name varchar(128),
    ->  dept int,
    ->  primary key(dept,name)
    -> ) ENGINE=InnoDB
    -> PARTITION BY RANGE (`dept`)
    -> (
    ->  PARTITION d0 VALUES LESS THAN (1),
    ->  PARTITION d1 VALUES LESS THAN (2),
    ->  PARTITION d2 VALUES LESS THAN (3),
    ->  PARTITION d_other VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.23 sec)

mysql 5.1 >
mysql 5.1 >set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1 >start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1 >
mysql 5.1 >insert into person_p(name,dept) values('a',1);
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >insert into person_p(name,dept) values('b',1);
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >insert into person_p(name,dept) values('c',1);
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >
mysql 5.1 >select count(*) from person_p;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql 5.1 >-- expecting 3
mysql 5.1 >rollback;
Query OK, 0 rows affected (0.06 sec)

mysql 5.1 >
mysql 5.1 >select count(*) from person_p;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql 5.1 >-- expecting 0, and seeing 0
mysql 5.1 >
mysql 5.1 >select count(*) from person_p;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql 5.1 >-- expecting 0, and seeing 3!
mysql 5.1 >
[10 Nov 2010 16:25] James Estes
5.1.41 is the latest available binary for my distro.  I started to build from source but was getting issues, so I wanted to update you with what I do know.  I managed to update to a percona build:  Server version: 5.1.51-rel11.5 (Percona Server (GPL), 11.5 , Revision 132), and the issue is still occurring in that build.

I also modified my my.cnf file to turn off query cache:
(the commented out portion was what I'd had before)
#query_cache_limit	= 1M
#query_cache_size        = 16M
query_cache_limit = 0
query_cache_size = 0

WITH query cache, the results have the incorrect results.  
WITHOUT query cache, the results are correct. 

So it seems the bug is only related to partitioned tables, WITH query cache, after a rollback.
[10 Nov 2010 20:07] Sveta Smirnova
Thank you for the feedback.

Verified as described. Test case same as provided, but at start I added:

set global query_cache_size=1024*1024*512;

Btw our binaries for any supported platform including generic Linux which works on any Linux available at http://dev.mysql.com/downloads.
[11 Nov 2010 12:47] Mattias Jonsson
Duplicate of bug#53775.