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
| Field | Original Value | New Value |
|---|---|---|
| Description |
Since MariaDB 10.2 and later I’m observing inconsistent results when query caching is enabled ({{query_cache_type=ON}}).
I can reliably reproduce the behaviour with a unit test. Unfortunately, the unit test is too complex to attach here since it contains several transactions with many statements. In a late phase of the test, a specific SELECT query is executed that returns zero rows while one row would have been expected. When query caching is disabled ({{query_cache_type=OFF}} or {{query_cache_size=0}}), the SELECT query returns one result as expected. I can reliably reproduce the unexpected behaviour with MariaDB 10.2.5, 10.2.11, 10.2.14 and 10.3.6 when query caching is enabled. The behaviour is expected with MariaDB 10.1.32 or when query caching is disabled. I’ve tried to reduce the complex test scenario to a minimal, reproducible test that I could attach to this issue. Unfortunately, I wasn’t successful yet. So any suggestion how to continue narrowing down this issue is highly welcomed. |
Since MariaDB 10.2 and later I’m observing inconsistent results when query caching is enabled ({{query_cache_type=ON}}).
I can reliably reproduce the behaviour with a unit test. Unfortunately, the unit test is too complex to attach here since it contains several transactions with many statements. In a late phase of the test, a specific SELECT query is executed that returns zero rows while one row is expected. When query caching is disabled ({{query_cache_type=OFF}} or {{query_cache_size=0}}), the SELECT query returns one result as expected. I can reliably reproduce the unexpected behaviour with MariaDB 10.2.5, 10.2.11, 10.2.14 and 10.3.6 when query caching is enabled. The behaviour is expected with MariaDB 10.1.32 or when query caching is disabled. I’ve tried to reduce the complex test scenario to a minimal, reproducible test that I could attach to this issue. Unfortunately, I wasn’t successful yet. So any suggestion how to continue narrowing down this issue is highly welcomed. |
| Description |
Since MariaDB 10.2 and later I’m observing inconsistent results when query caching is enabled ({{query_cache_type=ON}}).
I can reliably reproduce the behaviour with a unit test. Unfortunately, the unit test is too complex to attach here since it contains several transactions with many statements. In a late phase of the test, a specific SELECT query is executed that returns zero rows while one row is expected. When query caching is disabled ({{query_cache_type=OFF}} or {{query_cache_size=0}}), the SELECT query returns one result as expected. I can reliably reproduce the unexpected behaviour with MariaDB 10.2.5, 10.2.11, 10.2.14 and 10.3.6 when query caching is enabled. The behaviour is expected with MariaDB 10.1.32 or when query caching is disabled. I’ve tried to reduce the complex test scenario to a minimal, reproducible test that I could attach to this issue. Unfortunately, I wasn’t successful yet. So any suggestion how to continue narrowing down this issue is highly welcomed. |
Since MariaDB 10.2 and later I’m observing inconsistent results when query caching is enabled ({{query_cache_type=ON}}).
I can reliably reproduce the behaviour with a unit test. Unfortunately, the unit test is too complex to attach here since it contains several transactions with many statements. In a late phase of the test, a specific SELECT query is executed that returns zero rows while one row is expected. When query caching is disabled ({{query_cache_type=OFF}} or {{query_cache_size=0}}), the SELECT query returns one result as expected. I can reliably reproduce the unexpected behaviour with MariaDB 10.2.5, 10.2.11, 10.2.14 and 10.3.6 when query caching is enabled. The behaviour is expected with MariaDB 10.1.32 or when query caching is disabled. MariaDB server configuration is untouched except for the query cache configuration. I can even reproduce the behaviour with MariaDB running in a docker container: {{docker run mariadb:10.2.14}} I’ve tried to reduce the complex test scenario to a minimal, reproducible test that I could attach to this issue. Unfortunately, I wasn’t successful yet. So any suggestion how to continue narrowing down this issue is highly welcomed. |
| Description |
Since MariaDB 10.2 and later I’m observing inconsistent results when query caching is enabled ({{query_cache_type=ON}}).
I can reliably reproduce the behaviour with a unit test. Unfortunately, the unit test is too complex to attach here since it contains several transactions with many statements. In a late phase of the test, a specific SELECT query is executed that returns zero rows while one row is expected. When query caching is disabled ({{query_cache_type=OFF}} or {{query_cache_size=0}}), the SELECT query returns one result as expected. I can reliably reproduce the unexpected behaviour with MariaDB 10.2.5, 10.2.11, 10.2.14 and 10.3.6 when query caching is enabled. The behaviour is expected with MariaDB 10.1.32 or when query caching is disabled. MariaDB server configuration is untouched except for the query cache configuration. I can even reproduce the behaviour with MariaDB running in a docker container: {{docker run mariadb:10.2.14}} I’ve tried to reduce the complex test scenario to a minimal, reproducible test that I could attach to this issue. Unfortunately, I wasn’t successful yet. So any suggestion how to continue narrowing down this issue is highly welcomed. |
Since MariaDB 10.2 and later I’m observing inconsistent results when query caching is enabled ({{query_cache_type=ON}}).
The default engine ({{ENGINE=InnoDB}}) is used for all tables. I can reliably reproduce the behaviour with a unit test. Unfortunately, the unit test is too complex to attach here since it contains several transactions with many statements. In a late phase of the test, a specific SELECT query is executed that returns zero rows while one row is expected. When query caching is disabled ({{query_cache_type=OFF}} or {{query_cache_size=0}}), the SELECT query returns one result as expected. I can reliably reproduce the unexpected behaviour with MariaDB 10.2.5, 10.2.11, 10.2.14 and 10.3.6 when query caching is enabled. The behaviour is expected with MariaDB 10.1.32 or when query caching is disabled. MariaDB server configuration is untouched except for the query cache configuration. I can even reproduce the behaviour with MariaDB running in a docker container: {{docker run mariadb:10.2.14}} I’ve tried to reduce the complex test scenario to a minimal, reproducible test that I could attach to this issue. Unfortunately, I wasn’t successful yet. So any suggestion how to continue narrowing down this issue is highly welcomed. |
| Description |
Since MariaDB 10.2 and later I’m observing inconsistent results when query caching is enabled ({{query_cache_type=ON}}).
The default engine ({{ENGINE=InnoDB}}) is used for all tables. I can reliably reproduce the behaviour with a unit test. Unfortunately, the unit test is too complex to attach here since it contains several transactions with many statements. In a late phase of the test, a specific SELECT query is executed that returns zero rows while one row is expected. When query caching is disabled ({{query_cache_type=OFF}} or {{query_cache_size=0}}), the SELECT query returns one result as expected. I can reliably reproduce the unexpected behaviour with MariaDB 10.2.5, 10.2.11, 10.2.14 and 10.3.6 when query caching is enabled. The behaviour is expected with MariaDB 10.1.32 or when query caching is disabled. MariaDB server configuration is untouched except for the query cache configuration. I can even reproduce the behaviour with MariaDB running in a docker container: {{docker run mariadb:10.2.14}} I’ve tried to reduce the complex test scenario to a minimal, reproducible test that I could attach to this issue. Unfortunately, I wasn’t successful yet. So any suggestion how to continue narrowing down this issue is highly welcomed. |
Since MariaDB 10.2 and later return inconsistent results when query caching is enabled ({{query_cache_type=ON}}).
h2. Steps to reproduce h3. 1. Start a MariaDB 10.2+ database with default settings. For example by running a docker container on port 3500: {noformat} 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 {noformat} h3. 2. Run the attached mysqltest case {noformat} mysqltest -v -h 127.0.0.1 --port=3500 --user=test --password=test --result-file= {noformat} It fails with: {noformat} --- +++ @@ -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 {noformat} h3. 3. Disable query caching and re-run the mysqltest case: {noformat} 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= {noformat} Mysqltest succeeds. h2. Environment * Ubuntu * MariaDB 10.2.14 * Query cache settings: {noformat} 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 | +------------------------------+----------+ {noformat} |
| Attachment | MDEV-16087.mysqltest [ 45637 ] |
| Attachment | MDEV-16087.record [ 45638 ] |
| Description |
Since MariaDB 10.2 and later return inconsistent results when query caching is enabled ({{query_cache_type=ON}}).
h2. Steps to reproduce h3. 1. Start a MariaDB 10.2+ database with default settings. For example by running a docker container on port 3500: {noformat} 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 {noformat} h3. 2. Run the attached mysqltest case {noformat} mysqltest -v -h 127.0.0.1 --port=3500 --user=test --password=test --result-file= {noformat} It fails with: {noformat} --- +++ @@ -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 {noformat} h3. 3. Disable query caching and re-run the mysqltest case: {noformat} 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= {noformat} Mysqltest succeeds. h2. Environment * Ubuntu * MariaDB 10.2.14 * Query cache settings: {noformat} 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 | +------------------------------+----------+ {noformat} |
MariaDB 10.2 and newer return inconsistent results when query caching is enabled ({{query_cache_type=ON}}).
h2. Steps to reproduce h3. 1. Start a MariaDB 10.2+ database with default settings. For example by running a docker container on port 3500: {noformat} 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 {noformat} h3. 2. Run the attached mysqltest case {noformat} mysqltest -v -h 127.0.0.1 --port=3500 --user=test --password=test --result-file= {noformat} It fails with: {noformat} --- +++ @@ -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 {noformat} h3. 3. Disable query caching and re-run the mysqltest case: {noformat} 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= {noformat} Mysqltest succeeds. h2. Environment * Ubuntu * MariaDB 10.2.14 * Query cache settings: {noformat} 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 | +------------------------------+----------+ {noformat} |
| Description |
MariaDB 10.2 and newer return inconsistent results when query caching is enabled ({{query_cache_type=ON}}).
h2. Steps to reproduce h3. 1. Start a MariaDB 10.2+ database with default settings. For example by running a docker container on port 3500: {noformat} 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 {noformat} h3. 2. Run the attached mysqltest case {noformat} mysqltest -v -h 127.0.0.1 --port=3500 --user=test --password=test --result-file= {noformat} It fails with: {noformat} --- +++ @@ -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 {noformat} h3. 3. Disable query caching and re-run the mysqltest case: {noformat} 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= {noformat} Mysqltest succeeds. h2. Environment * Ubuntu * MariaDB 10.2.14 * Query cache settings: {noformat} 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 | +------------------------------+----------+ {noformat} |
MariaDB 10.2 and newer return inconsistent results when query caching is enabled ({{query_cache_type=ON}}).
h2. Steps to reproduce h3. 1. Start a MariaDB 10.2+ database with default settings. For example by running a docker container on port 3500: {noformat} 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 {noformat} h3. 2. Run the attached mysqltest case [^ {noformat} mysqltest -v -h 127.0.0.1 --port=3500 --user=test --password=test --result-file= {noformat} It fails with: {noformat} --- +++ @@ -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 {noformat} h3. 3. Disable query caching and re-run the mysqltest case: {noformat} 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= {noformat} Mysqltest succeeds. h2. Environment * Ubuntu * MariaDB 10.2.14 * Query cache settings: {noformat} 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 | +------------------------------+----------+ {noformat} |
| Description |
MariaDB 10.2 and newer return inconsistent results when query caching is enabled ({{query_cache_type=ON}}).
h2. Steps to reproduce h3. 1. Start a MariaDB 10.2+ database with default settings. For example by running a docker container on port 3500: {noformat} 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 {noformat} h3. 2. Run the attached mysqltest case [^ {noformat} mysqltest -v -h 127.0.0.1 --port=3500 --user=test --password=test --result-file= {noformat} It fails with: {noformat} --- +++ @@ -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 {noformat} h3. 3. Disable query caching and re-run the mysqltest case: {noformat} 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= {noformat} Mysqltest succeeds. h2. Environment * Ubuntu * MariaDB 10.2.14 * Query cache settings: {noformat} 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 | +------------------------------+----------+ {noformat} |
MariaDB 10.2 and newer return inconsistent results when query caching is enabled ({{query_cache_type=ON}}).
h2. Steps to reproduce h3. 1. Start a MariaDB 10.2+ database with default settings. For example by running a docker container on port 3500: {noformat} 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 {noformat} h3. 2. Run the attached mysqltest case [^ {noformat} mysqltest -h 127.0.0.1 --port=3500 --user=test --password=test --result-file= {noformat} It fails with: {noformat} --- +++ @@ -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 {noformat} h3. 3. Disable query caching and re-run the mysqltest case: {noformat} 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= {noformat} Mysqltest succeeds. h2. Environment * Ubuntu * MariaDB 10.2.14 * Query cache settings: {noformat} 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 | +------------------------------+----------+ {noformat} |
| Description |
MariaDB 10.2 and newer return inconsistent results when query caching is enabled ({{query_cache_type=ON}}).
h2. Steps to reproduce h3. 1. Start a MariaDB 10.2+ database with default settings. For example by running a docker container on port 3500: {noformat} 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 {noformat} h3. 2. Run the attached mysqltest case [^ {noformat} mysqltest -h 127.0.0.1 --port=3500 --user=test --password=test --result-file= {noformat} It fails with: {noformat} --- +++ @@ -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 {noformat} h3. 3. Disable query caching and re-run the mysqltest case: {noformat} 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= {noformat} Mysqltest succeeds. h2. Environment * Ubuntu * MariaDB 10.2.14 * Query cache settings: {noformat} 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 | +------------------------------+----------+ {noformat} |
MariaDB 10.2 and newer returns inconsistent results when query caching is enabled ({{query_cache_type=ON}}).
h2. Steps to reproduce h3. 1. Start a MariaDB 10.2+ database with default settings. For example by running a docker container on port 3500: {noformat} 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 {noformat} h3. 2. Run the attached mysqltest case [^ {noformat} mysqltest -h 127.0.0.1 --port=3500 --user=test --password=test --result-file= {noformat} It fails with: {noformat} --- +++ @@ -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 {noformat} h3. 3. Disable query caching and re-run the mysqltest case: {noformat} 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= {noformat} Mysqltest succeeds. h2. Environment * Ubuntu * MariaDB 10.2.14 * Query cache settings: {noformat} 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 | +------------------------------+----------+ {noformat} |
| Description |
MariaDB 10.2 and newer returns inconsistent results when query caching is enabled ({{query_cache_type=ON}}).
h2. Steps to reproduce h3. 1. Start a MariaDB 10.2+ database with default settings. For example by running a docker container on port 3500: {noformat} 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 {noformat} h3. 2. Run the attached mysqltest case [^ {noformat} mysqltest -h 127.0.0.1 --port=3500 --user=test --password=test --result-file= {noformat} It fails with: {noformat} --- +++ @@ -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 {noformat} h3. 3. Disable query caching and re-run the mysqltest case: {noformat} 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= {noformat} Mysqltest succeeds. h2. Environment * Ubuntu * MariaDB 10.2.14 * Query cache settings: {noformat} 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 | +------------------------------+----------+ {noformat} |
MariaDB 10.2 and newer returns inconsistent results when query caching is enabled ({{query_cache_type=ON}}).
h2. Steps to reproduce h3. 1. Start a MariaDB 10.2+ database with default settings. For example by running a docker container on port 3500: {noformat} 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 {noformat} h3. 2. Run the attached mysqltest case [^ {noformat} shell> mysqltest -h 127.0.0.1 --port=3500 --user=test --password=test --result-file= {noformat} It fails with: {noformat} --- +++ @@ -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 {noformat} h3. 3. Disable query caching and re-run the mysqltest case: {noformat} 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= {noformat} Mysqltest succeeds. h2. Environment * Ubuntu * MariaDB 10.2.14 * Query cache settings: {noformat} 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 | +------------------------------+----------+ {noformat} |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Attachment | MDEV-16087.mysqltest [ 45639 ] |
| Attachment |
|
| Attachment |
|
| Attachment | MDEV-16087.record [ 45640 ] |
| Description |
MariaDB 10.2 and newer returns inconsistent results when query caching is enabled ({{query_cache_type=ON}}).
h2. Steps to reproduce h3. 1. Start a MariaDB 10.2+ database with default settings. For example by running a docker container on port 3500: {noformat} 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 {noformat} h3. 2. Run the attached mysqltest case [^ {noformat} shell> mysqltest -h 127.0.0.1 --port=3500 --user=test --password=test --result-file= {noformat} It fails with: {noformat} --- +++ @@ -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 {noformat} h3. 3. Disable query caching and re-run the mysqltest case: {noformat} 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= {noformat} Mysqltest succeeds. h2. Environment * Ubuntu * MariaDB 10.2.14 * Query cache settings: {noformat} 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 | +------------------------------+----------+ {noformat} |
MariaDB 10.2 and newer returns inconsistent results when query caching is enabled ({{query_cache_type=ON}}).
h2. Steps to reproduce h3. 1. Start a MariaDB 10.2+ database with default settings. For example by running a docker container on port 3500: {noformat} 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 {noformat} h3. 2. Run the attached mysqltest case [^ {noformat} shell> mysqltest -h 127.0.0.1 --port=3500 --user=test --password=test --result-file= {noformat} It fails with: {noformat} --- +++ @@ -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 {noformat} h3. 3. Disable query caching and re-run the mysqltest case: {noformat} 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= {noformat} Mysqltest succeeds. h2. Environment * Ubuntu * MariaDB 10.2.14 * Query cache settings: {noformat} 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 | +------------------------------+----------+ {noformat} |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Affects Version/s | 10.3.6 [ 23003 ] | |
| Labels | querycache | querycache regression |
| Assignee | Oleksandr Byelkin [ sanja ] |
| Affects Version/s | 10.2.15 [ 23006 ] |
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$
|
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}
| Labels | querycache regression | querycache regression regression-10.2 |
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.
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.
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?
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?
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.
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.
I’ve filed a bug report for the official MariaDB docker image to disable query caching by default.
| Remote Link | This issue links to "MariaDB docker image issue: "Query caching should not be enabled by default" (Web Link)" [ 28501 ] |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] |
| Link |
This issue relates to |
I’ve also filed MDEV-16251 to change the query caching default behaviour in apt packages.
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
-- 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;
|
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.
| Assignee | Oleksandr Byelkin [ sanja ] | Thirunarayanan B [ thiru ] |
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 */
|
| Assignee | Thirunarayanan B [ thiru ] | Marko Mäkelä [ marko ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Marko Mäkelä [ marko ] | Thirunarayanan B [ thiru ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| issue.field.resolutiondate | 2018-06-18 08:58:58.0 | 2018-06-18 08:58:58.313 |
| Component/s | Storage Engine - InnoDB [ 10129 ] | |
| Fix Version/s | 10.2.16 [ 23110 ] | |
| Fix Version/s | 10.3.8 [ 23113 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 86926 ] | MariaDB v4 [ 154303 ] |
| Zendesk Related Tickets | 177512 |
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.mysqltestconnect 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;idconnection conn1;insert into table3 () values ();connection conn2;insert into table1 () values ();select count(*) from table3;count(*)0connection conn3;set autocommit=0;select count(*) from table3;count(*)1select count(*) from table3;count(*)1select sql_no_cache count(*) from table3;count(*)1rollback;connection conn2;rollback;connection conn1;drop table table1;drop table table2;drop table table3;okopenxs@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.mysqltestconnect 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;idconnection conn1;insert into table3 () values ();connection conn2;insert into table1 () values ();select count(*) from table3;count(*)0connection conn3;set autocommit=0;select count(*) from table3;count(*)1select count(*) from table3;count(*)0select sql_no_cache count(*) from table3;count(*)1rollback;connection conn2;rollback;connection conn1;drop table table1;drop table table2;drop table table3;okopenxs@ao756:~/dbs/maria10.3$I can not reproduce with 10.2.12, fore example. so looks like a recent enough regression.