Description:
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_query_cache_typ... says the query cache type setting is dynamic, yet if you read down it says:
"If the server is started with query_cache_type set to 0, it does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution."
which means it's dynamic if you set it "on" but not if you set it "off".
Other variables have similar weird behaviour:
- slave_parallel_threads can be changed but the "effect" of the change only takes place when you restart the SQL thread.
I think there are a few others.
Additionally you can't query the database server to find out the state/type of these variables.
All of this is confusing if you don't pay _very_ careful attention to the documentation.
How to repeat:
Read the docs and be very careful to read everything as the "Dynamic Variable" Yes/No field is not really completely correct or well specified.
In the specific case I have been caught out by I want to enable the query cache and see I can't without bouncing the server and that is rather invasive.
root@myserver [(none)]> show global variables like '%query_cache%';
+------------------------------+----------------+
| Variable_name | Value |
+------------------------------+----------------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------------+
6 rows in set (0.00 sec)
root@myserver [(none)]> set global query_cache_type = 1;
ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it
root@myserver [(none)]> select @@version;
+------------+
| @@version |
+------------+
| 5.6.17-log |
+------------+
1 row in set (0.00 sec)
Suggested fix:
The section where the "Dynamic variable" field is shown should probably be changed to indicate a "type of variable" and that list of different types should be described somewhere else. This would provide a better indication of configuration settings, and how they may or may not be changed.
Given the server has to implement this, it would be nice if you could actually provide the information in the output of I_S.GLOBAL_VARIABLES as an extra column so the information can be queried from inside the server and scripts won't have to be hard-coded to figure out if they can or can not change specific settings.
