MySQL Bugs: #68240: some my.cnf entries are ignored

Bug #68240	some my.cnf entries are ignored
Submitted:	1 Feb 2013 8:41	Modified:	2 Feb 2013 2:47
Reporter:	Martijn Otto	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	5.6.9	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Some options set in my.cnf get ignored by mysqld. This happens at least for the following options:

- sql-mode
- query-cache-type

The sql-mode variable I have tried both as sql-mode (as stated in the manual) and sql_mode (as some other people specify it), but neither got picked up by the mysqld process.

The only way to set these options now is by changing the init script.

How to repeat:
Create a my.cnf and set any of these options to anything non-standard and start the mysqld process.

The following query will confirm the value as set by mysqld:

SELECT @@query-cache-type;

Suggested fix:
Make mysqld pick up the values specified in the my.cnf file correctly.

Forgot to mention the version

Can you upload your my.cnf so I can try it?   A minimal test worked okay,
as long as the mysqld is actually reading the file:

------
[mysqld]
query-cache-type=1
sql-mode=strict_all_tables
------

mysql> select @@global.query_cache_type,@@global.sql_mode;
+---------------------------+-------------------+
| @@global.query_cache_type | @@global.sql_mode |
+---------------------------+-------------------+
| ON                        | STRICT_ALL_TABLES |
+---------------------------+-------------------+
1 row in set (0.00 sec)

my.cnf with passwords removed

Attachment: my.cnf (application/octet-stream, text), 7.25 KiB.

innodb.cnf in /etc/mysql/conf.d

Attachment: innodb-64gb.cnf (application/octet-stream, text), 95 bytes.

Besides the uploaded configuration files, we also have an id.cnf in /etc/mysql/conf.d where the server id is specified (for replication purposes).

I'm not seeing any problems here.  I expect to see disabled query cache because there is no line that says "query_cache_type=ON" :) sql_mode looks okay.

That is very strange, when we start the server without adding the sql mode to the command-line, the sql mode will be set to STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION.

We want to disable the STRICT_TRANS_TABLES, as this apparently makes it impossible to omit a value on a column without a default value (which is only a warning without it), even on MyISAM (which is not a transactional table at all).

The setting sql-mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION is from the new default config file in 5.6.8.

My guess is that the new config file is read after yours and that your settings are overwritten.

Please try mysqld --help --verbose and look for the line where the order in which the config files are read is described (at the top of the output).

See James Day's posting https://blogs.oracle.com/supportingmysql/ for more about the new settings.

You are right, running the command you gave yields the following result:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /opt/mysql/server-5.6/etc/my.cnf ~/.my.cnf

Which, to me, seems plain wrong. In my opinion the built-in configuration file should always be read first, so that other files can overrule any of the options. What I think is strange is that a local .my.cnf *would* always win, but not the more commonly used /etc/mysql/my.cnf

Changes to the default settings are also discussed in the manual:

http://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html notes the change in default for query_cache_type.

Also see http://dev.mysql.com/doc/refman/5.6/en/server-default-configuration-file.html,
which comments on the change to sql_mode. You can comment out that setting if you like.

Regarding this:

"
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /opt/mysql/server-5.6/etc/my.cnf ~/.my.cnf

Which, to me, seems plain wrong. In my opinion the built-in configuration file should always be read first, so that other files can overrule any of the options. What I think is strange is that a local .my.cnf *would* always win, but not the more commonly used /etc/mysql/my.cnf
"

Which one do you mean by "built-in configuration file"?

Option file search order is described here:

http://dev.mysql.com/doc/refman/5.6/en/option-files.html

Hope that helps.

if you want to test a specific options file, start mysqld --defaults-file=/path/to/my.cnf

if you want to test no options file and only command line, start mysqld --no-defaults --[other options here]