Bug #71351 select hit query cache after xa commit, no result return
Submitted: 12 Jan 2014 3:03 Modified: 24 Jan 2014 21:50
Reporter: HongXiang Jiang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: XA transactions Severity:S3 (Non-critical)
Version:5.5.35, 5.5.36 OS:Linux
Assigned to: CPU Architecture:Any
Tags: query cache, XA COMMIT
Triage: Needs Triage: D3 (Medium)

[12 Jan 2014 3:03] HongXiang Jiang
Description:
when a xa transaction is prepared, and then the server crashed, restart the MySQL server, do select first, then do xa commit, at last do the select before again, the second hit the query cache, and no result return.

How to repeat:
repeat it like below:

mysql> create table t(id int)engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> xa start '111';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values(1);
Query OK, 1 row affected (0.00 sec)
mysql> xa end '111';
Query OK, 0 rows affected (0.00 sec)
mysql> xa prepare '111';
Query OK, 0 rows affected (0.00 sec)

after doing this, a xa transaction with the xid '111' is prepared.

kill -9 mysqld
restart mysqld

mysql> select * from t;
Empty set (0.00 sec)
mysql> xa commit '111';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t;
Empty set (0.00 sec)

nothing returned, the second select hit the query cache? 

mysql> select * from t where id < 5;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Suggested fix:
the second select need not hit the query cache!!
[24 Jan 2014 21:50] Sveta Smirnova
Thank you for the report.

Verified as described in version 5.5. With version 5.6 is not repeatable.

To repeat, start MTR test suite as ./mtr --start innodb --mysqld=--query_cache_size=1000000 &, then connect and execute queries provided, kill -9, then start-dirty