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)
• Status: Verified
• Category: MySQL Server: XA transactions
• Severity: S3 (Non-critical)
• Version: 5.5.35, 5.5.36
• OS: Linux
• CPU Architecture: Any
• Tags: query cache, XA COMMIT

[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