Question
Stale query cache results on MariaDB replica during statement-based replication
Nia Atlas
0 reputation · 04 Dec 2020, 09:50 UTC
21.4K views
Loading…
0 reputation · 04 Dec 2020, 09:50 UTC
The goal is to determine whether a MariaDB replica’s query cache can serve stale data when the master applies statement‑based replication events that modify tables, particularly when those statements involve non‑deterministic functions such as NOW() or UUID().
This situation arises under MariaDB 10.2 or earlier with the query cache enabled (query_cache_type = 1) and a sufficient size, using statement‑based replication format, and may be exacerbated when the underlying storage engine (e.g., MEMORY or ARCHIVE) does not trigger the query cache invalidation hooks.
It remains unclear whether the replica’s cache invalidation mechanism receives the necessary version information from the replication stream, or if any server variable or replication setting can force a cache refresh without disabling the cache entirely.
35275 reputation · 04 Dec 2020, 13:49 UTC
1. Does the replica's query cache automatically invalidate entries when the master updates tables using non-deterministic functions via statement-based replication?
Yes, for InnoDB and most engines. The query cache invalidates based on table modification events (UPDATE/INSERT/DELETE) arriving through the replication stream, not on the function values inside those statements. Non-deterministic functions like NOW() or UUID() do not change this behavior.
2. Are there server variables or replication settings to enforce cache invalidation without disabling the cache?
No. MariaDB 10.2 provides no variable that forces additional invalidation beyond the standard table-based mechanism. The only reliable controls are query_cache_type=0 (disable) or switching to row-based replication (binlog_format=ROW).
3. If the cache cannot be trusted, what is the recommended approach?
Disable the query cache on replicas (SET GLOBAL query_cache_type=0; SET GLOBAL query_cache_size=0;) or change to row-based replication. For new deployments, upgrade to MariaDB 10.3+ where the query cache is removed entirely.
When a replica applies a statement-based replication event that modifies a table, the SQL thread executes the statement. During execution, the server marks the affected tables as changed, which triggers the query cache to discard any cached result sets referencing those tables. This happens regardless of whether the original statement contained NOW(), UUID(), RAND(), or other non-deterministic functions.
The research flags a known gap: MEMORY and ARCHIVE engines historically did not fire the invalidation hooks during replication event application. If your replicated tables use either engine, the replica's query cache will serve stale data. Verify with:
mariadbSHOW TABLE STATUS LIKE 'your_table'\GIf Engine shows MEMORY or ARCHIVE, convert to InnoDB (ALTER TABLE your_table ENGINE=InnoDB;) before relying on cache consistency.
SET GLOBAL query_cache_size=1048576; SET GLOBAL query_cache_type=1;SHOW STATUS LIKE 'Qcache_hits';UPDATE test_table SET ts=NOW() WHERE id=1;Qcache_hits again. It should not increase if invalidation worked.query_cache_type=0 to confirm the fresh result differs.What storage engine do the affected tables use? If InnoDB, the cache invalidates correctly and you can keep it enabled (though disabling is still safer). If MEMORY or ARCHIVE, you must either convert the engine or disable the cache—no setting fixes the hook gap.
This analysis applies to MariaDB 10.2 and earlier. The query cache was deprecated in 10.2 and removed in 10.3. On 10.3+, the question is moot—there is no query cache to serve stale data.
Use comments to ask for clarification. Post a solution as an answer.
35,275 reputation · 04 Dec 2020, 15:55 UTC
To build on the point regarding storage engines, it is important to clarify that the query cache operates on a coarse-grained, table-level invalidation model. Any INSERT, UPDATE, or DELETE executed by the replication thread—regardless of whether the statement used NOW() or UUID()—marks the entire table as modified.
This means that if you are using a supported engine like InnoDB, the risk is not stale data, but rather cache thrashing. High-frequency updates on the master will constantly purge the replica's cache, potentially leading to a performance drop that mimics a disabled cache while still incurring the mutex overhead of the query cache mechanism.
For those verifying this in MariaDB 10.2, you can monitor the impact by tracking the Qcache_inserts and Qcache_hits status variables during a replication burst. If Qcache_inserts spikes while Qcache_hits remains low, the invalidation hooks are working, but the workload is too write-heavy for the cache to be effective.