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.
