[MDEV-4471] xa transaction hit the query cache, after xa commit, no result return Created: 2013-05-02  Updated: 2014-01-14  Resolved: 2014-01-14

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.2, 5.5.30, 5.1.67, 5.2.14, 5.3.12
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: boyce (Inactive) Assignee: Elena Stepanova
Resolution: Fixed Votes: 0
Labels: None


 Description   

make sure the query cache is enable
a xa transaction is prepared and the server crashed, after restarted, if do a select between 'xa recover' and 'xa commit', and do the select again after 'xa commit', the second select will hit the query cache and return the wrong value.
how to reappear:

mysql> create table t(id int auto_increment primary key, a int)engine=innodb;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> xa start '111';
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> insert into t(a) 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)
 
 
 
mysql> xa recover;
 
ERROR 2006 (HY000): MySQL server has gone away
 
No connection. Trying to reconnect...
 
Connection id:    2
 
Current database: mydb
 
 
 
+----------+--------------+--------------+------+
 
| formatID | gtrid_length | bqual_length | data |
 
+----------+--------------+--------------+------+
 
|        1 |            3 |            0 | 111  |
 
+----------+--------------+--------------+------+
 
1 row in set (0.00 sec)
 
 
 
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)
 
 
 
mysql> show status like 'Qcache_hits';
 
+---------------+-------+
 
| Variable_name | Value |
 
+---------------+-------+
 
| Qcache_hits   | 1     |
 
+---------------+-------+

the last select hit the query cache and return no rows, but it may return the value (1,1)

 Comments   
Comment by Elena Stepanova [ 2013-05-02 ]

Reproducible on MySQL 5.1 and 5.5, but seems to be fixed in 5.6 and up.

MTR test case
(please note that in MySQL-5.6 it needs to be run with -mysqld=-query_cache_type=1):

--source include/have_innodb.inc
call mtr.add_suppression("Found 1 prepared XA transactions");
--let $qcache_size = `select @@query_cache_size`

create table t(id int auto_increment primary key, a int) engine=innodb;
xa start '111';
insert into t(a) values(1);
xa end '111';
xa prepare '111';
--enable_reconnect

--connect (con1,localhost,root,,)
--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
restart
EOF
--shutdown_server 0

--connection default
--source include/wait_until_connected_again.inc

set global query_cache_size = 1024*1024;

xa recover;
select * from t;
xa commit '111';
select * from t;
show status like 'QCache_hits';
select sql_no_cache * from t;

drop table t;
eval set global query_cache_size = $qcache_size;

  1. End of test case

I do not know which exact commit in MySQL 5.6 fixed it. If the fix will be eventually merged into 10.x, I think it makes sense to wait, and have it fixed only in 10.x, since the situation is rather rare and the failure is not particularly dangerous. If it doesn't look like the fix from MySQL could be applied to MariaDB, it might be fixed in 5.5 as well.

Comment by boyce (Inactive) [ 2013-05-03 ]

I have debugged the source code of MySQL 5.6.10 and MySQL 5.5.20, the changes in the function row0sel.cc::row_search_check_if_query_cache_permitted may cause the difference between MySQL 5.5 and MySQL 5.6
In MySQL 5.6.10, the code is added blew, perhaps maria haven't merge this Feature yet.
/* Disable query cache altogether for all tables if recovered XA
transactions in prepared state exist. This is because we do not
restore the table locks for those transactions and we may wrongly
set ret=TRUE above if "lock_table_get_n_locks(table) == 0". See
"Bug#14658648 XA ROLLBACK (DISTRIBUTED DATABASE) NOT WORKING WITH
QUERY CACHE ENABLED".
Read trx_sys->n_prepared_recovered_trx without mutex protection,
not possible to end up with a torn read since n_prepared_recovered_trx
is word size. */
if (trx_sys->n_prepared_recovered_trx > 0)

{ return(FALSE); }
Comment by Oleksandr Byelkin [ 2013-05-07 ]

as far as it was fixed in 5.6 it will be merged to 10.0.4.

Comment by Sergei Golubchik [ 2014-01-14 ]

Elena, could you please test whether it is fixed now?

Comment by Elena Stepanova [ 2014-01-14 ]

Fixed in 10.0.4

Generated at Thu Feb 08 06:56:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.