Skip to content

# More on variables ambiguities

MySQL Configuration April 30, 2009May 13, 2009

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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
5
Leave a Reply

5 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
3 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Giuseppe Maxia

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.

Guest
Sergei Golubchik

–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.

Author
shlomi

@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.

Author
shlomi

@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.

INFORMATION_SCHEMA.GLOBAL_STATUS: watch out | code.openark.org

[…] There are a few ON/OFF values (I’ve expressed my opinion and concerns on these here and here; why not just use 0/1?). SSL_CIPHER seems like the only variable which can get long enough to […]

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.