MySQL Bugs: #58062: Results incorrect after rollback on Partitioned table

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

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!

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 >

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.

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.

Duplicate of bug#53775.