Proxysql: update cache_ttl do not purge cache

[kfrapin]

Hi folks,
I'm currently trying to invalidate the query cache of proxysql using the cache_ttl but it does not seem to work properly.

Proxysql version : ProxySQL version 1.3.5-0-g10bf524, codename Truls

Here is the scenario :

1. Insert some rows into the mysql_query_rules table in order to cache some results, with a cache_ttl set to 7200000
2. Load the rules through the LOAD MYSQL QUERY RULES TO RUNTIME command
3. Query proxy sql, in order to have at least one result cached by proxy sql
4. Update all the cache_ttl values to 1000 (1 second) in order to invalidate the cache ( UPDATE mysql_query_rules SET cache_ttl = 1000)
5. Reload the rules through the LOAD MYSQL QUERY RULES TO RUNTIME command
6. Query proxy sql with the same query as in point 3.

The result returned by proxy sql is still cached.
If I delete all the rules, and reload them, the result returned by proxy sql is correct and not cached.

I do not understand what am I doing wrong...

I have waited some minutes in order to see if it was the purging thread that was slow to purge, but no more success.

[renecannao]

Hi.
This behavior is currently expected.
The expire time is associated with the cache entry, so when you execute a query in point 3 , it creates a cache entry that will expire after 7200000ms (the purge thread will take care of this).
When you change cache_ttl to 1000ms, the Query processor knows that the query can be cached, and checks in the query cache if it is there. According to the entry itself, it is not expired, therefore it is returned to the client.

Note that the query cache doesn't know when the entry was created, it only knows when it will expire .

Therefore this issue seems a reasonable feature request:

• [ ] add creation time in the cached entry
• [ ] trigger an immediate removal of the entry if cache_ttl is now changed.

[renecannao]

This is an enhancement, and it will be implemented in 1.4.1

[kfrapin]

Thanks for the new feature 👍

An idea about the release date of the 1.4.1 version?

Just another little question: do you consider possible to implement an sql function to clear all the cache entries in one call?

[kfrapin]

Any news on this issue?

[krishmanoh]

Hi there, is this feature available now?

[piglercs]

Hi,

I would like to ask if this issue is resolved somehow or if a workaround exists for purging cache. I defined a separate set of rules for caches and would like to delete only part of the cache based on rules.

I have tried to delete the caching rule. After I deleted it, queries ran without using cache but once I re-inserted the rule, results were retrieved from cache again for the first run too.

According to kfrapin's workaround, he deleted all the rules not just the ones which did the caching and it worked but I can't use this solution in my use-case.