More on variables ambiguities

April 30, 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.

tags:
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

5 Comments to "More on variables ambiguities"

  1. Giuseppe Maxia wrote:

    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.

  2. Sergei Golubchik wrote:

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

  3. shlomi wrote:

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

  4. shlomi wrote:

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

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

    [...] 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 [...]

Leave Your Comment

 
Powered by Wordpress and MySQL. Theme by openark.org