Bug #69396 Can't set query_cache_type to 0 when it is already 0
Submitted: 4 Jun 2013 12:08 Modified: 23 Nov 2014 15:31
Reporter: Rolf Neuberger Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Query Cache Severity:S3 (Non-critical)
Version:5.6.11, 5.5.31 OS:Any
Assigned to:
Tags: 1651, Dynamic System Variable, query cache

[4 Jun 2013 12:08] Rolf Neuberger
Description:
MySQL cannot activate the query cache online if the server has been started with it configured to be off globally. This is documented behaviour.

However, not only attempts to activate the query cache at runtime fail, but any statement that SETs query_cache_type, even statements that set it to zero.

In this case at least, MySQL does not distinguish between SETting the variable to a new value and SETting it to the same value it already holds. The check for protecting a zero-configured query_cache_type variable from online alterations also does not check if the new value is the harmless zero or enum OFF.

This behaviour is the same with MySQL 5.5.31. It only became obvious to us after upgrading a server to MySQL 5.6.11, due to the new default value for query_cache_type.

How to repeat:
Set query_cache_type=0 in config file and restart server.

mysql> SET GLOBAL query_cache_type=0;
ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it
mysql> SET GLOBAL query_cache_type=OFF;
ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

mysql> SET SESSION query_cache_type=0;
ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it
mysql> SET SESSION query_cache_type=OFF;
ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

Suggested fix:
For query_cache_type specifically:
Detect if new value is zero and bypass check for error 1651 if so.

Suggested generally for dynamic variables:
Detect if new value is same as current effective value (in same [GLOBAL vs SESSION] scope). Ignore the SET statement entirely if so.
[4 Jun 2013 13:25] Umesh Shastry
Hello Rolf,

Thank you for the report.
Verified as described.

Thanks,
Umesh
[16 Jul 2013 17:32] Paul Dubois
Noted in 5.7.2 changelog.

If query_cache_type was disabled at startup to prevent the query
cache from being enabled at runtime, disabling query_cache_type at
runtime generated a warning even though it was already disabled.
[23 Nov 2014 7:10] Jonathan DeLanders
why in ever gods name would anyone ever to choose to disable enabling query cache at runtime?   seriously.   WTF.
[23 Nov 2014 7:28] Jonathan DeLanders
use postgres.
[23 Nov 2014 15:31] Rolf Neuberger
Jonathan,
you disable query cache if you aniticpate your session will generate a lot of cachable queries that generally not going to be reissued verbatim any time soon (or ever), "polluting" the cache with low-value entries, while throwing out more valuable ones.

There are also still significant overheads involved in MySQL caching that can slow down sessions issuing a large number of queries involving very small result sets.

The reason for the report is that the original behaviour added extra non-obvious boilerplate requirements to client processes that want to disable the query cache. If I wanted to use the same script to perform operations across several MySQL server installations, for which I determined (through benchmarking or otherwise) that I'm better off without query cache, I had to check the runtime configuration after establishing the connection just to determine if cache steering (SET query_cache_type or SELECT SQL_NO_CACHE) was safe to use.