I have a strange problem.
On MySQL 5.6 I have query cache enabled, with the below settings.
The idea of caching is that whenever a write operation happens, the record is invalidated (removed) and created upon next read.
However, I do get the stale data a lot of the times, and it gets corrected when I change some characters in the query.
I know turning off query cache is certainly an option that works, but am I doing something wrong here in terms of settings related to query cache?
I read about write-through caching, but couldn't find any default implementation of it in MySQL nor does it mention anywhere about the invalidations clearly.
Another important point is - I do have a lot of connection objects live at any point of time (a few tens), is it possible that my issue is because of some caching at connection level (if possible) instead of global cache?
EDIT - NOT a duplicate question to
What is the use of "query_cache_wlock_invalidate" in MySql Query Cache?
because here the issue is that the cached value is being returned even after a long time after the write happened, as expectation is that the write resulted in cache invalidation anyways as the lock would have also been given up by then.
EDIT -
query_cache_wlock_invalidate
actually worked. The issue was with incorrect logging of write timestamp which resulted in invalid conclusion.
Setting query_cache_wlock_invalidate
to ON
works.