MySQL Bugs: #19612: InnoDB files per table my.cnf option is read inconsistently

Bug #19612	InnoDB files per table my.cnf option is read inconsistently
Submitted:	8 May 2006 14:31	Modified:	4 Feb 2011 18:38
Reporter:	Oli Sennhauser	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	any?	OS:	Any (any?)
Assigned to:		CPU Architecture:	Any

Description:
InnoDB files per table has to be set like this:

innodb_file_per_table =  1

But variable shows like this:

show variables like '%file_per%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

This is inconsistent and confuses.

--------------------------------------------------

When InnoDB files per table is set like this:

innodb_file_per_table =  ON

show variables like '%file_per%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+

It is confusing much more until you find (after long searching and not knowing how and why) this:

060508 16:23:53 [Warning] /u00/app/mysql/product/mysql-5.1.9/bin/mysqld: ignoring option '--innodb_file_per_table' due to invalid value 'ON'

This is inconsistent again!

--------------------------------------------------

Wrong parameters prohibit to start MySQL except when they are InnoDB paramters :-(

How to repeat:
See above.

Suggested fix:
1. make on/1 and off/0 equivalents
2. prohibit starting mysql when parameters are specified wrong as it is done with NON InnoDB parameters.

Changing the category to MySQL Server.

I would call it a bug (because of inconsistency) or a documentation request (possible values and their interpretation are not described in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html), not a feature request. Do you agree?

Hi Valeriy
When you call it a bug so much so better. I do agree with you.
But documentation should/could also be adapted?
Thanks Oli

Verified with 5.0.22-BK build. It is either a bug or a documentation request:

openxs@suse:~/dbs/5.0> bin/mysqld_safe --innodb_file_per_table=1 &
[1] 23787
openxs@suse:~/dbs/5.0> Starting mysqld daemon with databases from /home/openxs/dbs/5.0/var

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like '%file_per%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysqladmin -uroot shutdown
STOPPING server from pid file /home/openxs/dbs/5.0/var/suse.pid
060515 22:54:37  mysqld ended

[1]+  Done                    bin/mysqld_safe --innodb_file_per_table=1
openxs@suse:~/dbs/5.0> bin/mysqld_safe --innodb_file_per_table=ON &
[1] 23823
openxs@suse:~/dbs/5.0> Starting mysqld daemon with databases from /home/openxs/dbs/5.0/var

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like '%file_per%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec)

This is documented; see: http://dev.mysql.com/doc/refman/5.0/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.)"

However, I agree that it might be worth highlighting that fact in a more visible way.

As Stefan points out, this behavior is documented already as a general principle of how system variable values can be specified as command options versus at runtime with SET, so I have made no changes to the manual.

I agree, however, that it's confusing to users that you can use 0/1 or OFF/ON at runtime, but onlyl 0/1 for command options, and I tend to consider this a server bug. So please feel free to reopen this as such (changing the category from Documentation to Server).

This bug fixed as part of bug#51631 in 5.5 and will not be back-ported to to 5.1