Details
-
Bug
-
Status: Stalled (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
-
None
-
Linux, e.g. Ubuntu
Description
I set the config "query_cache_size=1355776" and "query_cache_type=1" in the config file in order to use Query_cache. However, for the same "Select *" operation, the Query_cache failed to identify them and stores the redundant queries, after I change the option "time_zone" on the fly. It is not reasonable because the "Selected" table/rows are not time type or date type;
time_zone should not affect the query result.
Similar situations for "default_week_format" and "lc_time_names". Maybe Query_cacher should have a smart identification for redundant queries. It would be more efficient for Query_cache to identify them accurately.
I put the test case and result followed.
Test:
set time_zone = SYSTEM;
|
reset query cache;
|
flush status;
|
|
|
create database if not exists mysqltest;
|
create table mysqltest.t1 (i int not null);
|
insert into mysqltest.t1 (i) values (1);
|
show status like "Qcache_queries_in_cache";
|
|
|
select * from mysqltest.t1 order by i;
|
show status like "Qcache_queries_in_cache";
|
|
|
set time_zone = "+9:00";
|
select * from mysqltest.t1 order by i;
|
show status like "Qcache_queries_in_cache";
|
|
|
select * from mysqltest.t1 order by i;
|
show status like "Qcache_queries_in_cache";
|
Result:
set time_zone = SYSTEM;
|
reset query cache;
|
flush status;
|
create database if not exists mysqltest;
|
create table mysqltest.t1 (i int not null);
|
insert into mysqltest.t1 (i) values (1);
|
show status like "Qcache_queries_in_cache";
|
Variable_name Value
|
Qcache_queries_in_cache 0
|
select * from mysqltest.t1 order by i;
|
i
|
1
|
show status like "Qcache_queries_in_cache";
|
Variable_name Value
|
Qcache_queries_in_cache 1
|
set time_zone = "+9:00";
|
select * from mysqltest.t1 order by i;
|
i
|
1
|
show status like "Qcache_queries_in_cache";
|
Variable_name Value
|
Qcache_queries_in_cache 2
|
select * from mysqltest.t1 order by i;
|
i
|
1
|
show status like "Qcache_queries_in_cache";
|
Variable_name Value
|
Qcache_queries_in_cache 2
|
Suggested fix:
time_zone should not affect the query result, which are not time type of date type.