Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: 10.0.2, 5.5.30, 5.1.67, 5.2.14, 5.3.12
-
Fix Version/s: None
-
Component/s: None
-
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)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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)
as far as it was fixed in 5.6 it will be merged to 10.0.4.
Elena, could you please test whether it is fixed now?
Fixed in 10.0.4
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;
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.