[MDEV-16087] Inconsistent SELECT results when query cache is enabled Created: 2018-05-03  Updated: 2020-08-25  Resolved: 2018-06-18

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.2.15, 10.3.6, 10.2, 10.3
Fix Version/s: 10.2.16, 10.3.8

Type: Bug Priority: Critical
Reporter: Benedikt Waldvogel Assignee: Thirunarayanan Balathandayuthapani
Resolution: Fixed Votes: 2
Labels: querycache, regression, regression-10.2
Environment:

Ubuntu 16.04

Attachments: File MDEV-16087.mysqltest     File MDEV-16087.record    
Issue Links:
Relates
relates to MDEV-16251 Query caching should not be enabled b... Closed

 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      |
    		 
    +------------------------------+----------+



 Comments   
Comment by Valerii Kravchuk [ 2018-05-16 ]

Easy to reproduce with 10.3.6, for example:

openxs@ao756:~/dbs/maria10.3$ bin/mysql --host=127.0.0.1 --port=3308 -uroot -e"select @@query_cache_type, version()"
 
+--------------------+----------------+
 
| @@query_cache_type | version()      |
 
+--------------------+----------------+
 
| OFF                | 10.3.6-MariaDB |
 
+--------------------+----------------+
 
openxs@ao756:~/dbs/maria10.3$ bin/mysqltest --host=127.0.0.1 --port=3308 -uroot test < /tmp/MDEV-16087.mysqltest
 
connect  conn1, 127.0.0.1, root, , test, 3308;
 
connect  conn2, 127.0.0.1, root, , test, 3308;
 
connect  conn3, 127.0.0.1, root, , test, 3308;
 
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;
 
id
 
connection conn1;
 
insert into table3 () values ();
 
connection conn2;
 
insert into table1 () values ();
 
select count(*) from table3;
 
count(*)
 
0
 
connection conn3;
 
set autocommit=0;
 
select count(*) from table3;
 
count(*)
 
1
 
select count(*) from table3;
 
count(*)
 
1
 
select sql_no_cache count(*) from table3;
 
count(*)
 
1
 
rollback;
 
connection conn2;
 
rollback;
 
connection conn1;
 
drop table table1;
 
drop table table2;
 
drop table table3;
 
ok
 
openxs@ao756:~/dbs/maria10.3$ bin/mysql --host=127.0.0.1 --port=3308 -uroot -e"set global query_cache_type=on;"
 
openxs@ao756:~/dbs/maria10.3$ bin/mysql --host=127.0.0.1 --port=3308 -uroot -e"select @@query_cache_type, version()"
 
+--------------------+----------------+
 
| @@query_cache_type | version()      |
 
+--------------------+----------------+
 
| ON                 | 10.3.6-MariaDB |
 
+--------------------+----------------+
 
openxs@ao756:~/dbs/maria10.3$ bin/mysqltest --host=127.0.0.1 --port=3308 -uroot test < /tmp/MDEV-16087.mysqltest
 
connect  conn1, 127.0.0.1, root, , test, 3308;
 
connect  conn2, 127.0.0.1, root, , test, 3308;
 
connect  conn3, 127.0.0.1, root, , test, 3308;
 
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;
 
id
 
connection conn1;
 
insert into table3 () values ();
 
connection conn2;
 
insert into table1 () values ();
 
select count(*) from table3;
 
count(*)
 
0
 
connection conn3;
 
set autocommit=0;
 
select count(*) from table3;
 
count(*)
 
1
 
select count(*) from table3;
 
count(*)
 
0
 
select sql_no_cache count(*) from table3;
 
count(*)
 
1
 
rollback;
 
connection conn2;
 
rollback;
 
connection conn1;
 
drop table table1;
 
drop table table2;
 
drop table table3;
 
ok
 
openxs@ao756:~/dbs/maria10.3$

I can not reproduce with 10.2.12, fore example. so looks like a recent enough regression.

Comment by Oleksandr Byelkin [ 2018-05-16 ]

We will do our best to make it part of the next 10.2 release.

Comment by Valerii Kravchuk [ 2018-05-16 ]

Current 10.2.15 from GitHub source is also affected:

openxs@ao756:~/dbs/maria10.2$ bin/mysql --host=127.0.0.1 --port=3308 -uroot -e"select @@query_cache_type, version()"
 
+--------------------+-----------------+
 
| @@query_cache_type | version()       |
 
+--------------------+-----------------+
 
| OFF                | 10.2.15-MariaDB |
 
+--------------------+-----------------+
 
openxs@ao756:~/dbs/maria10.2$ bin/mysql --host=127.0.0.1 --port=3308 -uroot -e"set global query_cache_type=on;"
 
openxs@ao756:~/dbs/maria10.2$ bin/mysql --host=127.0.0.1 --port=3308 -uroot -e"select @@query_cache_type, version()"
 
+--------------------+-----------------+
 
| @@query_cache_type | version()       |
 
+--------------------+-----------------+
 
| ON                 | 10.2.15-MariaDB |
 
+--------------------+-----------------+
 
openxs@ao756:~/dbs/maria10.2$ bin/mysqltest --host=127.0.0.1 --port=3308 -uroot test < /tmp/MDEV-16087.mysqltest
 
connect  conn1, 127.0.0.1, root, , test, 3308;
 
connect  conn2, 127.0.0.1, root, , test, 3308;
 
connect  conn3, 127.0.0.1, root, , test, 3308;
 
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;
 
id
 
connection conn1;
 
insert into table3 () values ();
 
connection conn2;
 
insert into table1 () values ();
 
select count(*) from table3;
 
count(*)
 
0
 
connection conn3;
 
set autocommit=0;
 
select count(*) from table3;
 
count(*)
 
1
 
select count(*) from table3;
 
count(*)
 
0
 
select sql_no_cache count(*) from table3;
 
count(*)
 
1
 
rollback;
 
connection conn2;
 
rollback;
 
connection conn1;
 
drop table table1;
 
drop table table2;
 
drop table table3;
 
ok
 
openxs@ao756:~/dbs/maria10.2$

Comment by Valerii Kravchuk [ 2018-05-16 ]

MariaDB 10.1.34 (current 10.1 from GitHub) is not affected:

 

openxs@ao756:~/dbs/maria10.1$ bin/mysql --host=127.0.0.1 --port=3308 -uroot -e"select @@query_cache_type, version()"
-----------------------------------+

@@query_cache_type version()

-----------------------------------+

ON 10.1.34-MariaDB

-----------------------------------+
openxs@ao756:~/dbs/maria10.1$ bin/mysqltest --host=127.0.0.1 --port=3308 -uroot test < /tmp/MDEV-16087.mysqltest
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;
set autocommit=0;
select * from table2;
id
insert into table3 () values ();
insert into table1 () values ();
select count from table3;
count
0
set autocommit=0;
select count from table3;
count
1
select count from table3;
count
1
select sql_no_cache count from table3;
count
1
rollback;
rollback;
drop table table1;
drop table table2;
drop table table3;
ok
openxs@ao756:~/dbs/maria10.1${noformat}

Comment by Benedikt Waldvogel [ 2018-05-16 ]

I can not reproduce with 10.2.12, fore example

Are you sure? I could reproduce it with 10.2.5 and 10.2.11.

Comment by Valerii Kravchuk [ 2018-05-16 ]

It could have been a mistake or misinterpretation as well, caused by the default setting of query_cache_type (OFF) in 10.2.

Anyway, recent 10.2 and 10.3 are affected, this is something to fix.

Comment by Benedikt Waldvogel [ 2018-05-16 ]

Anyway, recent 10.2 and 10.3 are affected, this is something to fix.

Sure, I agree.

Anyway, I’m confused by the default behavior. I actually observe on 10.2.11 and 10.2.14 for example, that query caching is enabled by default. Is that specific to my environment?

Comment by Benedikt Waldvogel [ 2018-05-19 ]

I actually observe […] that query caching is enabled by default

I investigated further and noticed that Ubuntu enables query caching by default in current versions:

This means that a very large user base is affected by this bug.
I’m wondering if an enabled query cache is a good default and shouldn’t rather be turned off?
Is it a non-ideal default configuration that should be filed for the Ubuntu package or shouldn’t even the behaviour of query_cache_type change?

Comment by Valerii Kravchuk [ 2018-05-19 ]

What about the value of query_cache_type? If it's OFF, having query_cache_size > 0 may add some overhead, but query caching does not really happen and there is no impact on results.

Comment by Benedikt Waldvogel [ 2018-05-19 ]

I’ve updated my answer to make it more clear. query_cache_type=ON by default on Ubuntu 14.04 and Ubuntu 16.04. BTW: It’s also the case for the official MariaDB Docker images.

Comment by Benedikt Waldvogel [ 2018-05-19 ]

I’ve filed a bug report for the official MariaDB docker image to disable query caching by default.

Comment by Benedikt Waldvogel [ 2018-05-22 ]

I’ve also filed MDEV-16251 to change the query caching default behaviour in apt packages.

Comment by Oleksandr Byelkin [ 2018-06-13 ]

The behavior is innodb dependent

Comment by Oleksandr Byelkin [ 2018-06-13 ] 

-- source include/have_innodb.inc
 
-- source include/have_query_cache.inc
 
-- source include/not_embedded.inc
 
 
 
set 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;

Comment by Oleksandr Byelkin [ 2018-06-13 ]

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.

Comment by Marko Mäkelä [ 2018-06-13 ]

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 +1100

WL#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 +1100

Bug #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.

Comment by Oleksandr Byelkin [ 2018-06-13 ]

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 */

Comment by Marko Mäkelä [ 2018-06-18 ]

OK to push after addressing my latest review comments.

Generated at Thu Feb 08 08:26:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.