Continuing Variables ambiguities in names and values, there are two more issues I wish to present.
First, I’ve shown that variable values may be ambiguous. Thus, 1 and ON are interchangeable. But also 1 and YES.
The important thing to note is that it’s not always like that. You can’t just swap 1 for ON or YES as you will: it depends on the variable. Thus, a specific variable (e.g. query_cache_type) may accept ON for 1 – but will not accept YES. Another may accept YES for 1 – bot not ON.
Confused? Here’s the second issue. This one is really a bug, as I see it. Take a look at the following:
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+
The ON value is actually illegal: if you try to set this value in your my.cnf file – you’ll get a non-file-per-table behavior. The real value you should be using in my.cnf is 1 (or no value at all).
And so in this latter example, 1 equals ON, but only one of them is valid in the my.cnf file, and yet only the other one is being reported.
The behavior is documented here. But, if, like myself, you find it undesired, please comment on bug #44509.
Just to make your life more difficult, some variables can also accept “disabled”
http://dev.mysql.com/doc/refman/5.1/en/events-configuration.html#events-event-scheduler-option
and some options are disabled by “–skip-option-name” instead.
Yes. We need more consistency.
–skip –enable –disable can be used with any boolean option.
For example as –enable-innodb-file-per-table
Also, boolean options accept TRUE/FALSE.
What option accepts YES/NO ? I couldn’t find any.
ON/OFF (and DISABLED), as far as I could see, is only accepted by event-scheduler, I’m fixing that.
@Sergei,
A YES variable (I never mentioned a NO):
mysql> show global variables like ‘updat%’;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| updatable_views_with_limit | YES |
+—————————-+——-+
1 row in set (0.01 sec)
I didn’t know about the –enable, –disable: very interesting!
The –skip is familiar from mysqldump.
@Giuseppe:
I don’t mind that much that some variables can have options like “OFF/ON/DISABLED”
I would just expect it to be consistent for that particular variable throughout parameters, variables, SHOW and mysqld.