MySQL Bugs: #69396: Can't set query_cache_type to 0 when it is already 0

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:		CPU Architecture:	Any
Tags:	1651, Dynamic System Variable, query cache

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.

Hello Rolf,

Thank you for the report.
Verified as described.

Thanks,
Umesh

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.

why in ever gods name would anyone ever to choose to disable enabling query cache at runtime?   seriously.   WTF.

use postgres.

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.