| Bug #86046 | FROM sub query is cached by mistake | ||
|---|---|---|---|
| Submitted: | 23 Apr 2017 7:18 | Modified: | 7 Jul 2017 9:09 |
| Reporter: | Meiji Kimura | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: Query Cache | Severity: | S2 (Serious) |
| Version: | 5.6, 5.6.36 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | query cache | ||
[23 Apr 2017 8:01]
MySQL Verification Team
I tested these on MySQL 5.5.55 and 5.6.36. MySQL 5.7 or later has a similar bugs (more complicated sub query) See Bug#86047
[24 Apr 2017 6:13]
MySQL Verification Team
Hello Meiji-San, Thank you for the report and test case. Verified as described with 5.6.36 build. Thanks, Umesh
[7 Jul 2017 9:09]
Erlend Dahl
MySQL will no longer invest in the query cache, see: http://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/
[3 Aug 2017 2:53]
MySQL Verification Team
As a result of fix for Bug#86047, This problem was solved in MySQL 5.6.37.
[3 Aug 2017 3:05]
MySQL Verification Team
[Right behavior on MySQL 5.6.37]
(2)
mysql [localhost] {msandbox} (test) > -- [Transaction B]
mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 2
Current database: test
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
+-------------------------+-------+
2 rows in set (0.02 sec)
mysql [localhost] {msandbox} (test) > select * from t1;
Empty set (0.00 sec)
mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_not_cached | 1 |
| Qcache_queries_in_cache | 0 |
+-------------------------+-------+
2 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > select * from (select * from t1) a; -- wrong cached in 5.6 only
Empty set (0.01 sec)
mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 0 |
+-------------------------+-------+
2 rows in set (0.00 sec)
(4)
mysql [localhost] {msandbox} (test) > -- [Transaction B]
mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 0 |
+-------------------------+-------+
2 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > select * from t1;
+------+------+------+
| i1 | i2 | v3 |
+------+------+------+
| 1 | 1 | a |
| 2 | 1 | b |
| 3 | 2 | c |
| 4 | 2 | d |
+------+------+------+
4 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 1 |
+-------------------------+-------+
2 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > select * from (select * from t1) a; -- wrong cached in 5.6 only
+------+------+------+
| i1 | i2 | v3 |
+------+------+------+
| 1 | 1 | a |
| 2 | 1 | b |
| 3 | 2 | c |
| 4 | 2 | d |
+------+------+------+
4 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 2 |
+-------------------------+-------+
2 rows in set (0.00 sec)
Description: In the environment query cache is enable, autocommit is enable, (2) both queries returns 'empty', and (4) both queries should return result sets. But only MySQL 5.6 returns 'empty' based on wrong cache from 'select * from (select * from t1) a; (1) [Transaction A] drop table if exists t1; create table t1(i1 int, i2 int, v3 varchar(20)); start transaction; insert into t1 values(1,1,'a'),(2,1,'b'),(3,2,'c'),(4,2,'d'); (2) [Transaction B] select * from t1; select * from (select * from t1) a; -- wrong cached in 5.6 only (3) [Transaction A] commit; (4) [Transaction B] select * from t1; select * from (select * from t1) a; -- Empty set returns based on wrong cache How to repeat: 1.Procedures Please use mysql sandbox, and add these lines vim my.sandbox.cnf autocommit=1 query_cache_size=16M query_cache_type=1 then restart with ./restart. -- [Transaction A] drop table if exists t1; create table t1(i1 int, i2 int, v3 varchar(20)); start transaction; insert into t1 values(1,1,'a'),(2,1,'b'),(3,2,'c'),(4,2,'d'); -- [Transaction B] show global status like 'qcache%cache%'; select * from t1; show global status like 'qcache%cache%'; select * from (select * from t1) a; -- wrong cached in 5.6 only show global status like 'qcache%cache%'; -- [Transaction A] commit; -- [Transaction B] show global status like 'qcache%cache%'; select * from t1; show global status like 'qcache%cache%'; select * from (select * from t1) a; -- wrong cached in 5.6 only show global status like 'qcache%cache%'; 2.Results [Right behavior on 5.5, 5.7, 8.0] (2) mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_not_cached | 2 | | Qcache_queries_in_cache | 0 | +-------------------------+-------+ 2 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > select * from t1; Empty set (0.00 sec) mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_not_cached | 3 | | Qcache_queries_in_cache | 0 | +-------------------------+-------+ 2 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > select * from (select * from t1) a; -- wrong cached in 5.6 only Empty set (0.01 sec) mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_not_cached | 4 | | Qcache_queries_in_cache | 0 | +-------------------------+-------+ 2 rows in set (0.00 sec) (4) mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_not_cached | 4 | | Qcache_queries_in_cache | 0 | +-------------------------+-------+ 2 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > select * from t1; +------+------+------+ | i1 | i2 | v3 | +------+------+------+ | 1 | 1 | a | | 2 | 1 | b | | 3 | 2 | c | | 4 | 2 | d | +------+------+------+ 4 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_not_cached | 4 | | Qcache_queries_in_cache | 1 | +-------------------------+-------+ 2 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > select * from (select * from t1) a; -- wrong cached in 5.6 only +------+------+------+ | i1 | i2 | v3 | +------+------+------+ | 1 | 1 | a | | 2 | 1 | b | | 3 | 2 | c | | 4 | 2 | d | +------+------+------+ 4 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_not_cached | 4 | | Qcache_queries_in_cache | 2 | +-------------------------+-------+ 2 rows in set (0.00 sec) [Wrong behavior on 5.6] (2) mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_not_cached | 13 | | Qcache_queries_in_cache | 0 | +-------------------------+-------+ 2 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > select * from t1; Empty set (0.00 sec) mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_not_cached | 14 | | Qcache_queries_in_cache | 0 | +-------------------------+-------+ 2 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > select * from (select * from t1) a; -- wrong cached in 5.6 only Empty set (0.00 sec) mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_not_cached | 14 | | Qcache_queries_in_cache | 1 | +-------------------------+-------+ 2 rows in set (0.00 sec) (4) mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_not_cached | 14 | | Qcache_queries_in_cache | 1 | +-------------------------+-------+ 2 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > select * from t1; +------+------+------+ | i1 | i2 | v3 | +------+------+------+ | 1 | 1 | a | | 2 | 1 | b | | 3 | 2 | c | | 4 | 2 | d | +------+------+------+ 4 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_not_cached | 14 | | Qcache_queries_in_cache | 2 | +-------------------------+-------+ 2 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > select * from (select * from t1) a; -- wrong cached in 5.6 only Empty set (0.00 sec) mysql [localhost] {msandbox} (test) > show global status like 'qcache%cache%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Qcache_not_cached | 14 | | Qcache_queries_in_cache | 2 | +-------------------------+-------+ 2 rows in set (0.00 sec) Suggested fix: Don't cache 'select * from (select * from t1) a;' into query cache in this situation.