Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.2.15, 10.3.6, 10.2(EOL), 10.3(EOL)
-
Ubuntu 16.04
Description
MariaDB 10.2 and newer returns inconsistent results when query caching is enabled (query_cache_type=ON).
Steps to reproduce
1. Start a MariaDB 10.2+ database with default settings.
For example by running a docker container on port 3500:
shell> docker run --name mariadb-10.2.14 -p 3500:3306 -e MYSQL_ROOT_PASSWORD=test -e MYSQL_USER=test -e MYSQL_PASSWORD=test -e MYSQL_DATABASE=test -d mariadb:10.2.14
|
2. Run the attached mysqltest case MDEV-16087.mysqltest
shell> mysqltest -h 127.0.0.1 --port=3500 --user=test --password=test --result-file=MDEV-16087.record test < MDEV-16087.mysqltest
|
It fails with:
--- MDEV-16087.record 2018-05-15 15:42:11.495372790 +0200
|
+++ MDEV-16087.reject 2018-05-15 15:52:24.547984967 +0200
|
@@ -18,7 +18,7 @@
|
1
|
select count(*) from table3;
|
count(*)
|
-1
|
+0
|
select sql_no_cache count(*) from table3;
|
count(*)
|
1
|
|
|
mysqltest: Result content mismatch
|
3. Disable query caching and re-run the mysqltest case:
shell> echo "set global query_cache_type = off;" | mysql -h 127.0.0.1 --port=3500 --user=root --password=test
|
shell> mysqltest -h 127.0.0.1 --port=3500 --user=test --password=test --result-file=MDEV-16087.record test < MDEV-16087.mysqltest
|
Mysqltest succeeds.
Environment
- Ubuntu
- MariaDB 10.2.14
- Query cache settings:
MariaDB [(none)]> show variables like '%query_cache%';+------------------------------+----------+| Variable_name | Value |+------------------------------+----------+| have_query_cache | YES || query_cache_limit | 131072 || query_cache_min_res_unit | 4096 || query_cache_size | 67108864 || query_cache_strip_comments | OFF || query_cache_type | ON || query_cache_wlock_invalidate | OFF |+------------------------------+----------+
Attachments
Issue Links
- relates to
-
-
- Closed
-
- links to
Activity
Problem is on innodb side (QC do not track innodb transactions).
It correct version (10.1) innodb reject storing results of first (conn2) select from table3 (I do not know innodb reasoning behind it). Then first select of conn3 put result in the cache and second takes it.
in 10.2 innodb allow to store results of conn2, so first select of conn3 see other thread filling results in the QC and do nothing with QC, second just take results form QC put by conn2.
There are two related changes in MySQL 5.7, which were merged to MariaDB 10.2.2 by jplindst:
commit b0ebbb963c266b8e06f9efa6a97e5c8e3e7cd59b
Author: Sunny Bains <Sunny.Bains@Oracle.Com>
Date: Tue Oct 16 13:56:54 2012 +1100WL#6047 - Interim fix for query cache invalidation logic.
i_innodb.innodb_bug14658648 was failing because the design requires a
trx id for the logic to work. In WL#6047 RO transactions don't have a
transaction id and that breaks this checks.The interim fix is to use transaction start time. However, this is too
coarse because it is in seconds. Also, because when transactions are
promoted to RW they retain the original start time. Needs to be verified
if this is correct.
This replaced a correctly working transaction ID with a race-condition-prone timestamp. Then, a follow-up fix attempted to address a regression that was found:
commit 5cca468f6284f91bc9ef2db96a6b0cb9eba95400
Author: Sunny Bains <Sunny.Bains@Oracle.Com>
Date: Wed Mar 20 13:37:25 2013 +1100Bug #16497925 - QUERY CACHE BREAKS REPEATABLE READ
Use the read view low limit id transactions because the RO transactions don't
have transaction IDs assigned to them. If a view hasn't been created for a
transaction then it couldn't have done a select. And when a read view is
created for it, its low_limit_id will be the system max trx id.Approved by Jimmy Yang rb#2210
WL#6047: Do not allocate trx id for read-only transactions must have changed the logic so that trx_t::id will be 0 for transactions that have not modified any InnoDB tables.
It looks like both these changes must be reverted and the code be adjusted so that row_search_check_if_query_cache_permitted() in MariaDB 10.2 will be equivalent to the 10.1 version.
Note: Oracle probably does not care about bugs in the query cache. I believe that they removed the query cache altogether in MySQL 8.0.
I can only confirm that if rollback to old version of conditions it will help in this case (of course I do not understand what changes are about and if it crash something else):
diff --git a/storage/innobase/row/row0sel.cc b/storage/innobase/row/row0sel.cc
|
index b5e1bab352d..ceb9d55f054 100644
|
--- a/storage/innobase/row/row0sel.cc
|
+++ b/storage/innobase/row/row0sel.cc
|
@@ -5938,10 +5938,7 @@ row_search_check_if_query_cache_permitted(
|
saw at the time of the read view creation. */
|
|
const bool ret = lock_table_get_n_locks(table) == 0
|
- && ((trx->id != 0 && trx->id >= table->query_cache_inv_id)
|
- || !MVCC::is_view_active(trx->read_view)
|
- || trx->read_view->low_limit_id()
|
- >= table->query_cache_inv_id);
|
+ && trx->id >= table->query_cache_inv_id;
|
if (ret) {
|
/* If the isolation level is high, assign a read view for the
|
transaction if it does not yet have one */
|
-- source include/have_innodb.inc-- source include/have_query_cache.inc-- source include/not_embedded.incset GLOBAL query_cache_type=ON;set LOCAL query_cache_type=ON;connect (conn1,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);connect (conn2,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);connect (conn3,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);connection conn1;create table table1 (id bigint(20) auto_increment, primary key (id)) ENGINE=InnoDB;create table table2 (id bigint(20) auto_increment, primary key (id)) ENGINE=InnoDB;create table table3 (id bigint(20) auto_increment, primary key (id)) ENGINE=InnoDB;connection conn2;set autocommit=0;select * from table2;connection conn1;insert into table3 () values ();connection conn2;insert into table1 () values ();select count(*) from table3;connection conn3;set autocommit=0;select count(*) from table3;select count(*) from table3;select sql_no_cache count(*) from table3;rollback;connection conn2;rollback;connection conn1;drop table table1;drop table table2;drop table table3;set GLOBAL query_cache_type=default;