[MDEV-16087] Inconsistent SELECT results when query cache is enabled - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.2.15, 10.3.6, 10.2(EOL), 10.3(EOL)
Fix Version/s: 10.2.16, 10.3.8
Component/s: Storage Engine - InnoDB
Labels:
Environment:
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 @@

 select count(*) from table3;

 count(*)

-1

+0

 select sql_no_cache count(*) from table3;

 count(*)

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

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

MDEV-16087.mysqltest
0.9 kB
2018-05-15 14:51
MDEV-16087.record
0.6 kB
2018-05-15 14:51

Issue Links

relates to

MDEV-16251 Query caching should not be enabled by default in apt packages

Closed

links to

MariaDB docker image issue: "Query caching should not be enabled by default"

Activity

Ascending order - Click to sort in descending order

Valerii Kravchuk added a comment - 2018-05-16 08:03 - edited

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(*)

connection conn3;

set autocommit=0;

select count(*) from table3;

count(*)

select count(*) from table3;

count(*)

select sql_no_cache count(*) from table3;

count(*)

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(*)

connection conn3;

set autocommit=0;

select count(*) from table3;

count(*)

select count(*) from table3;

count(*)

select sql_no_cache count(*) from table3;

count(*)

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.~~

Valerii Kravchuk added a comment - 2018-05-16 08:03 - edited 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.

Oleksandr Byelkin added a comment - 2018-05-16 08:41

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

Oleksandr Byelkin added a comment - 2018-05-16 08:41 We will do our best to make it part of the next 10.2 release.

Valerii Kravchuk added a comment - 2018-05-16 09:13

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(*)

connection conn3;

set autocommit=0;

select count(*) from table3;

count(*)

select count(*) from table3;

count(*)

select sql_no_cache count(*) from table3;

count(*)

rollback;

connection conn2;

rollback;

connection conn1;

drop table table1;

drop table table2;

drop table table3;

ok

openxs@ao756:~/dbs/maria10.2$

Valerii Kravchuk added a comment - 2018-05-16 09:13 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$

Valerii Kravchuk added a comment - 2018-05-16 13:33

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}

Valerii Kravchuk added a comment - 2018-05-16 13:33 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}

Benedikt Waldvogel added a comment - 2018-05-16 13:51

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.

Benedikt Waldvogel added a comment - 2018-05-16 13:51 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.

Valerii Kravchuk added a comment - 2018-05-16 13:59

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.

Valerii Kravchuk added a comment - 2018-05-16 13:59 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.

Benedikt Waldvogel added a comment - 2018-05-16 14:11

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?

Benedikt Waldvogel added a comment - 2018-05-16 14:11 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?

Benedikt Waldvogel added a comment - 2018-05-19 09:35 - edited

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:

Ubuntu 14.04, MariaDB 10.1.33, query_cache_size = 16M → query_cache_type=ON
Ubuntu 14.04, MariaDB 10.2.15, query_cache_size = 16M → query_cache_type=ON
Ubuntu 14.04, MariaDB 10.3.6, query_cache_size = 16M → query_cache_type=ON
Ubuntu 16.04, MariaDB 10.1.33, query_cache_size = 16M → query_cache_type=ON
Ubuntu 16.04, MariaDB 10.2.15, query_cache_size = 16M → query_cache_type=ON
Ubuntu 16.04, MariaDB 10.3.6, query_cache_size = 16M → query_cache_type=ON

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?

Benedikt Waldvogel added a comment - 2018-05-19 09:35 - edited 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: Ubuntu 14.04, MariaDB 10.1.33, query_cache_size = 16M → query_cache_type=ON Ubuntu 14.04, MariaDB 10.2.15, query_cache_size = 16M → query_cache_type=ON Ubuntu 14.04, MariaDB 10.3.6, query_cache_size = 16M → query_cache_type=ON Ubuntu 16.04, MariaDB 10.1.33, query_cache_size = 16M → query_cache_type=ON Ubuntu 16.04, MariaDB 10.2.15, query_cache_size = 16M → query_cache_type=ON Ubuntu 16.04, MariaDB 10.3.6, query_cache_size = 16M → query_cache_type=ON 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?

Valerii Kravchuk added a comment - 2018-05-19 16:06

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.

Valerii Kravchuk added a comment - 2018-05-19 16:06 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.

Benedikt Waldvogel added a comment - 2018-05-19 16: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.

Benedikt Waldvogel added a comment - 2018-05-19 16: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.

Benedikt Waldvogel added a comment - 2018-05-19 16:36

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

Benedikt Waldvogel added a comment - 2018-05-19 16:36 I’ve filed a bug report for the official MariaDB docker image to disable query caching by default.

Benedikt Waldvogel added a comment - 2018-05-22 17:08

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

Benedikt Waldvogel added a comment - 2018-05-22 17:08 I’ve also filed MDEV-16251 to change the query caching default behaviour in apt packages.

Oleksandr Byelkin added a comment - 2018-06-13 10:16

The behavior is innodb dependent

Oleksandr Byelkin added a comment - 2018-06-13 10:16 The behavior is innodb dependent

Oleksandr Byelkin added a comment - 2018-06-13 10:16

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

Oleksandr Byelkin added a comment - 2018-06-13 10:16 -- 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;

Oleksandr Byelkin added a comment - 2018-06-13 12:11

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.

Oleksandr Byelkin added a comment - 2018-06-13 12:11 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.

Marko Mäkelä added a comment - 2018-06-13 12:37

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.

Marko Mäkelä added a comment - 2018-06-13 12:37 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.

Oleksandr Byelkin added a comment - 2018-06-13 12:40

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

Oleksandr Byelkin added a comment - 2018-06-13 12:40 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 */

Marko Mäkelä added a comment - 2018-06-18 08:48

OK to push after addressing my latest review comments.

Marko Mäkelä added a comment - 2018-06-18 08:48 OK to push after addressing my latest review comments .

People

Assignee:: Thirunarayanan Balathandayuthapani

Reporter:: Benedikt Waldvogel

Votes:: 2 Vote for this issue

Watchers:: 8 Start watching this issue

Dates

Created:: 2018-05-03 18:53

Updated:: 2024-07-07 23:54

Resolved:: 2018-06-18 08:58

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Steps to reproduce

1. Start a MariaDB 10.2+ database with default settings.

2. Run the attached mysqltest case MDEV-16087.mysqltest

3. Disable query caching and re-run the mysqltest case:

Environment

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration