Bug #76764 my_print_defaults does not take into account /usr/my.cnf
Submitted: 21 Apr 2015 3:55 Modified: 21 Apr 2015 21:24
Reporter: Marcus Zambrano Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.6.23, 5.6.24 OS:Linux (RHEL 6)
Assigned to: CPU Architecture:Any

[21 Apr 2015 3:55] Marcus Zambrano
Description:
The my_print_defaults binary does not seem to process options from /usr/my.cnf. 

How to repeat:
///
`/etc/my.cnf` file contents
///

# cat /etc/my.cnf 
[mysqld] 
log-error 
innodb-buffer-pool-size=512M 

///
`/usr/my.cnf` contents
///

# cat /usr/my.cnf 
[mysqld] 
innodb-buffer-pool-size=32M 

///
Output from `my_print_defaults` binary
///

# my_print_defaults mysqld 
--log-error 
--innodb-buffer-pool-size=512M 

///
MySQL starts with Buffer Pool value from `/usr/my.cnf` file which is in conflict with `my_print_defaults` output
///

# service mysqld start &> /dev/null 
2015-04-21 01:36:26 19890 [Note] InnoDB: Using atomics to ref count buffer pool pages 
2015-04-21 01:36:26 19890 [Note] InnoDB: The InnoDB memory heap is disabled 
2015-04-21 01:36:26 19890 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 
2015-04-21 01:36:26 19890 [Note] InnoDB: Memory barrier is not used 
2015-04-21 01:36:26 19890 [Note] InnoDB: Compressed tables use zlib 1.2.3 
2015-04-21 01:36:26 19890 [Note] InnoDB: Using Linux native AIO 
2015-04-21 01:36:26 19890 [Note] InnoDB: Using CPU crc32 instructions 
2015-04-21 01:36:26 19890 [Note] InnoDB: Initializing buffer pool, size = 32.0M 
2015-04-21 01:36:26 19890 [Note] InnoDB: Completed initialization of buffer pool 
2015-04-21 01:36:26 19890 [Note] InnoDB: Highest supported file format is Barracuda. 
2015-04-21 01:36:26 19890 [Note] InnoDB: 128 rollback segment(s) are active. 
2015-04-21 01:36:26 19890 [Note] InnoDB: Waiting for purge to start 
2015-04-21 01:36:26 19890 [Note] InnoDB: 5.6.23 started; log sequence number 1793278

Suggested fix:
Update my_print_defaults to parse options from /usr/my.cnf and all other possible cnf locations.
[21 Apr 2015 7:26] Umesh Shastry
Hello Marcus Zambrano,

Thank you for the report.
Observed similar behavior with 5.6.24.

Looks like default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

Thanks,
Umesh
[21 Apr 2015 7:29] Umesh Shastry
// rpm installation

[root@cluster-repo ~]# rpm -qa|grep -i mysql-community
mysql-community-libs-compat-5.6.24-3.el6.x86_64
mysql-community-release-el6-5.noarch
mysql-community-client-5.6.24-3.el6.x86_64
mysql-community-libs-5.6.24-3.el6.x86_64
mysql-community-devel-5.6.24-3.el6.x86_64
mysql-community-common-5.6.24-3.el6.x86_64
mysql-community-server-5.6.24-3.el6.x86_64

// conf files
[root@cluster-repo ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
log-error
innodb-buffer-pool-size=512M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@cluster-repo ~]# cat /usr/my.cnf
[mysqld]
innodb-buffer-pool-size=1028M
[root@cluster-repo ~]#

// my_print_defaults mysqld

[root@cluster-repo ~]# my_print_defaults mysqld
--log-error
--innodb-buffer-pool-size=512M
--datadir=/var/lib/mysql
--socket=/var/lib/mysql/mysql.sock
--symbolic-links=0
--sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

// mysqld log extract

150423 14:25:47 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2015-04-23 14:25:48 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-04-23 14:25:48 0 [Note] /usr/sbin/mysqld (mysqld 5.6.24) starting as process 10401 ...
2015-04-23 14:25:48 10401 [Note] Plugin 'FEDERATED' is disabled.
2015-04-23 14:25:48 10401 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-04-23 14:25:48 10401 [Note] InnoDB: The InnoDB memory heap is disabled
2015-04-23 14:25:48 10401 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-04-23 14:25:48 10401 [Note] InnoDB: Memory barrier is not used
2015-04-23 14:25:48 10401 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-04-23 14:25:48 10401 [Note] InnoDB: Using Linux native AIO
2015-04-23 14:25:48 10401 [Note] InnoDB: Not using CPU crc32 instructions
2015-04-23 14:25:48 10401 [Note] InnoDB: Initializing buffer pool, size = 1.0G
2015-04-23 14:25:48 10401 [Note] InnoDB: Completed initialization of buffer pool
2015-04-23 14:25:48 10401 [Note] InnoDB: Highest supported file format is Barracuda.
[21 Apr 2015 10:32] Terje Røsten
Hi Marcus,

can you move your config settings to /etc/my.cnf instead?
[21 Apr 2015 21:24] Marcus Zambrano
I have already eliminated the /usr/my.cnf file and merged it into the /etc/my.cnf file. Just noting my_print_defaults omission of /usr/my.cnf and the precedence it has over /etc/my.cnf.
[24 Jun 2015 18:30] Mike Griffin
Per bug https://bugs.mysql.com/bug.php?id=68318    "mysql_install_db creates a spurious my.cnf in an odd location, /usr/my.cnf"

Bug 76764 is likely to be encountered by RHEL users that have used mysql_install_db with the Oracle provided mysql-community-server

Sure we can remove /usr/my.cnf as you suggest but there is no clear sign that this file was created by the Oracle provided mysql-community-server since my_print_defaults does not read the file and mysqld does not tell you that the file is read:

# rm -rf /var/lib/mysql* /usr/my.cnf /usr/my-new.cnf /etc/my* /var/log/mysqld.log*
# mkdir /var/lib/mysql
# mysql_install_db --user=mysql --datadir=/var/lib/mysql/ 2>&1 | tail
The latest information about MySQL is available on the web at

  http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

New default config file was created as /usr/my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings

# wc -l /usr/my.cnf
28 /usr/my.cnf
#
# my_print_defaults mysqld | grep -i strict
# grep -i strict /usr/my.cnf
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# /usr/sbin/mysqld --help --verbose --skip-networking --pid-file=/tmp/y3O97dYoaUssg.pid --skip-log-bin 2> /dev/null | grep -A1 'Default options are read'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

Moving config settings to /etc/my.cnf instead would be a reasonable suggestion if there were a way to easily discover that /usr/my.cnf exists at all and is used by the server, which is not currently the case.