Bug #44509 Inconsistent use of ON/OFF variable values in my.cnf vs SHOW GLOBAL VARIABLES

• Submitted: 28 Apr 2009 9:15
• Modified: 29 Apr 2009 5:55
• Reporter: Shlomi Noach (OCA)
• Status: Verified
• Category: MySQL Server: Options
• Severity: S3 (Non-critical)
• Version: >= 5.0
• OS: Any
• CPU Architecture: Any
• Tags: configuration, innodb_file_per_table

[28 Apr 2009 9:15] Shlomi Noach

Description:
The following is from an instance A running with innodb_file_per_table:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    | 
+-----------------------+-------+
1 row in set (0.00 sec)

However, putting the following:
innodb_file_per_table = ON
in my.cnf for instance B - does not turn the option on. It is an invalid value.
The real value that must be specified is 1, as in:
innodb_file_per_table = 1

To recap: the valid value "1" is reported in SHOW GLOBAL VARIABLES as "ON", which is itself an invalid value.

How to repeat:
Set the following in my.cnf:

[mysqld]
innodb_file_per_table=ON

Restart MySQL, log in,

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   | 
+-----------------------+-------+
1 row in set (0.00 sec)

Suggested fix:
Have SHOW GLOBAL VARIABLES report "1" for innodb_file_per_table, since this is the valid value.

[28 Apr 2009 10:48] Valeriy Kravchuk

Actually, this is even documented to some extent. Please, read the manual, http://dev.mysql.com/doc/refman/5.1/en/dynamic-system-variables.html:

"Variables that have a type of “boolean” can be set to 0, 1, ON or OFF. (If you set them on the command line or in an option file, use the numeric values.)"

So, current behaviour is intended, it seems. Do you want it to be changed or to be more clearly documented in case of innodb_file_per_table?

[28 Apr 2009 20:13] Sergei Golubchik

I agree that it's inconsistent, the value shown in SHOW VARIABLES and mysqld --help should a) be the same, b) work both on the command line and from SQL.

[29 Apr 2009 4:32] Shlomi Noach

I would like the current behavior to change in either one of two ways:

1. have SHOW GLOBAL STATUS report "1"

2. Have the configuration file accept "ON"

Otherwise this is really pointless and confusing: the cnf file states one value, and SHOW GLOBAL VARIABLES reports another, neither agree with each other.

Regards

[28 Oct 2011 3:16] Valeriy Kravchuk

Bug #62988 was marked as a duplicate of this one.

[28 Oct 2011 3:18] Valeriy Kravchuk

Bug #62986 was marked as a duplicate of this one.

[28 Oct 2011 3:21] Valeriy Kravchuk

Changed synopsis to explain real problem better (based on recent duplicates).

[28 Oct 2011 23:14] Dimitriy A

Note that some options work with ON or OFF configuration.
Example options are innodb_stats_on_metadata and innodb_rollback_on_timeout.