Bug #26599 Setting of query_cache_type not consistent in config/client
Submitted: 23 Feb 2007 16:37 Modified: 7 Jul 2017 9:13
Reporter: MC Brown Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Query Cache Severity:S3 (Non-critical)
Version:5.1.15, 5.0.27,4.1 OS:Linux (Linux)
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[23 Feb 2007 16:37] MC Brown
Description:
The methods for setting the value of query_cache_type do not match when used within the configuration file and online. 

I was checking the comments on http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html

These say that you can set the value of query_cache_type using either a numeric or text value. 

If I set the value to DEMAND in my.cnf:

query_cache_type = DEMAND

I get the following error: 

Unknown suffix 'D' used for variable 'query_cache_type' (value 'DEMAND')
070223 16:01:41 [ERROR] /usr/local/libexec/mysqld: Error while setting value 'DEMAND' to 'query_cache_type'

However, I can set it within the client using the strings: 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.27

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set query_cache_type=DEMAND;
Query OK, 0 rows affected (0.09 sec)

mysql> show variables like 'query_cache_type';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| query_cache_type | DEMAND | 
+------------------+--------+
1 row in set (0.19 sec)

mysql> set query_Cache_type=off;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   | 
+------------------+-------+
1 row in set (0.00 sec)

mysql> Bye

The above is from 5.0.27, but the same process is still faulty under 5.1.15. 

The operation is inconsistent with other enumerated types (for example, delay_key_write, which accepts ON|OFF|ALL) which accept string or enumerated value as valid in both options or client. 

How to repeat:
Try adding: 

query_cache_type = DEMAND

to my.cnf and starting mysqld

Suggested fix:
The setting of the value should support the string format and numeric format to bring the option into consistency with other enumerated types.

Currently the definition for query_cache_type is to only accept a numeric value when parsing options (from sql/mysqld.cc): 

  {"query_cache_type", OPT_QUERY_CACHE_TYPE,
   "0 = OFF = Don't cache or retrieve results. 1 = ON = Cache all results except SELECT SQL_NO_CACHE ... queries. 2 = DEMAND = Cache on\
ly SELECT SQL_CACHE ... queries.",
   (gptr*) &global_system_variables.query_cache_type,
   (gptr*) &max_system_variables.query_cache_type,
   0, GET_ULONG, REQUIRED_ARG, 1, 0, 2, 0, 1, 0},

That should be GET_STR and a suitable parsing to identify the options.
[23 Feb 2007 23:45] Miguel Solorzano
Thank you for the bug report. Verified as described.
[7 Jul 2017 9:13] Erlend Dahl
MySQL will no longer invest in the query cache, see:

http://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/