[MDEV-29076] Dynamically update time_zone caused Query_cache stored repetitive select queries. Created: 2022-07-10  Updated: 2023-11-28

Status: Stalled
Project: MariaDB Server
Component/s: Query Cache, Time zones
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Minor
Reporter: Star Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

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.

 Comments   
Comment by Daniel Black [ 2022-07-11 ]

There is a thd->time_zone_used flag. If this was stored during Query_cache::store_query as a Query_cache_query_flags, or keep flags.time_zone as empty if not used. Then find where Query_cache::invalidate* is called for when changing the timezone only selected queries could be removed.

Others like lc_time_names and default_week_format aren't tracked in thd currently.

Parachute did you want to attempt the time_zone implementation of this?

Comment by Star [ 2022-07-11 ]

Thanks! I would attempt to implement this patch.

Comment by Star [ 2022-07-11 ]

Hi Daniel Black, I have made a Pull Request for this bug at https://github.com/MariaDB/server/pull/2185

>Then find where Query_cache::invalidate* is called for when changing the timezone only selected queries could be removed.

I think we don't need to remove some queries, but we should identify redundant queries before store_query completes.

Comment by Daniel Black [ 2022-07-12 ]

> I think we don't need to remove some queries

Agree. Good decision.

Generated at Thu Feb 08 10:05:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.