MySQL Bugs: #68807: mysql_install_db ignores /etc/my.cnf in 5.6

Bug #68807	mysql_install_db ignores /etc/my.cnf in 5.6
Submitted:	28 Mar 2013 19:50	Modified:	24 Oct 2014 16:02
Reporter:	Arnaud Adant	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S3 (Non-critical)
Version:		OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	mysql_install_db 5.6 install installation

Description:
mysql_install_db ignores /etc/my/cnf whereas in the 5.5. version is used it.

It can be a problem if the file is created to specify the datadir. Since it will be ignored, mysql_install_db 5.6 will create the data files in basedir/data.

And when mysqld runs it will consider /etc/my.cnf first, hence conflicting datadir.

How to repeat:
1. install 5.6.10 from a tar.gz distribution

2. create a configuration file in /etc/my.cnf :

cat /etc/my.cnf
[mysqld]
datadir=/tmp/data

3. run scripts/mysql_install_db

4. check that /tmp/data is empty

ls -al /tmp/data

whereas

ls -al data is not.

Run the same with MySQL 5.5.30 and check that /tmp/data is not empty in this case.

Note that :

bin/mysqld --print-defaults gives this in both 5.5.30 and 5.6.10

bin/mysqld would have been started with the following arguments:
--datadir=/tmp/data

Suggested fix:
mysql_install_db should search the datadir in the configuration file order 

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

maybe use mysqld --prints-default |grep datadir ?

This should be documented.

We have a similar error after having upgraded mysql 5.5 to 5.6 :

2013-06-07 11:41:08 7f61b06b9700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-06-07 11:41:08 7f61b06b9700 InnoDB: Error: Fetch of persistent statistics requested for table "ourdb"."ourtable" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

This still happens while doing a fresh install using mysql-5.6.14:

2013-12-01 08:05:21 4702 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2013-12-01 08:05:21 4702 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2013-12-01 08:05:21 4702 [ERROR] Error in checking mysql.slave_master_info repository info type of TABLE.
2013-12-01 08:05:21 4702 [ERROR] Error creating master info: Error checking repositories.
2013-12-01 08:05:21 4702 [ERROR] Failed to create or recover replication info repository.
2013-12-01 08:05:21 4702 [Note] Check error log for additional messages. You will not be able to start replication until the issue is resolved and the server restarted.

The .frm files are created for the table, and are type innodb, but what is happening is that the innodb data file & logfile are created in /home/y/var/mysql/data. Because of this, setting master_info_repository to TABLE will result in non-usable mysql replication.

Noted in 5.6.22 changelog.

mysql_install_db ignored option files in the default locations.

$ bzr log -r 6207
------------------------------------------------------------
revno: 6207
committer: Arun Kuruvila <arun.kuruvila@oracle.com>
branch nick: mysql-5.6
timestamp: Fri 2014-10-17 11:54:42 +0530
message:
  Bug #16570238: MYSQL_INSTALL_DB IGNORES /ETC/MY.CNF IN 5.6
  
  Description: The "mysql_install_db" utility ignores the
  default options specified in the default configuration
  files. Consider that the data directory path was specified
  in any one of the default file eg. in "/etc/my.cnf", the
  values for the options are read from the files in the order
  as specified below:
  /etc/my.cnf
  /etc/mysql/my.cnf
  /usr/local/mysql/etc/my.cnf
  ~/.my.cnf.
  Since "mysql_install_db" ignores these files when the
  default-file option is not specified and uses the generated
  "my.cnf" defaults file in the installation directory.
  Hence the data directory resides in the installation
  directory after "mysql_install_db". When the mysqld server
  is started, it will start scanning the defaults files
  specified in the above list and hence would find a
  conflicting data directory option value.
  
  Analysis: The "mysql_install_db" utility creates a default
  option file named "my.cnf" in the base installation
  directory. And this newly created "my.cnf" file is used as
  the default-file when "--default-file" option is not
  explicitly specified by the user. So this will skip reading
  of default options from the configuration files specified
  in the above list.
  
  Fix: Whenever default file is not explicitly specified by
  the user, newly created "my.cnf" is not set as the
  default-file. This will make "mysql_install_db" utility to
  read default options from the default configuration files
  in the order specified above.
  
  NOTE:- mtr test case is not added since it needs root
  permission to create configuration files in default
  locations.