Bug #59280 $MYSQL_HOME is not used for the client configuration
Submitted: 4 Jan 2011 14:39 Modified: 6 Apr 2011 1:51
Reporter: Holger Thiel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.5.8 OS:Linux (Debian Etch)
Assigned to: Sergey Vojtovich CPU Architecture:Any
Tags: my.cnf
Triage: Triaged: D3 (Medium)

[4 Jan 2011 14:39] Holger Thiel
Description:
In the my.cnf is the section [mysql] for the client.

In the MySQL 5.5-environment $MYSQL_HOME is not used and the options in $MYSQL_HOME/my.cnf are not read by the client.

The my.cnf seems correct as "mysql --defaults-file=$MYSQL_HOME/my.cnf --help" shows the correct configuration.

How to repeat:
Set $MYSQL_HOME to the path where the my.cnf resides.

bash> $MYSQL_HOME=/DBDATA/instance1

bash> echo $MYSQL_HOME
/DATA/instance1

bash> mysql --help

Look at the output:
"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  
The following groups are read: mysql client"

This would be correct output:
"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 /DATA/instance1/my.cnf ~/.my.cnf  
The following groups are read: mysql client"
[13 Jan 2011 17:29] [ name withheld ]
This affects usage of the embedded server rather severely as well, as setting MYSQL_HOME is a common way to configure libmysqld.

The problem appears to be that DEFAULT_HOME_ENV was simply forgotten about in the conversion to cmake build scripts. The old makefiles defined it to MYSQL_HOME, which is used in mysys/default.c.

For the moment I'm using this workaround:

diff -Naur mysql-5.5.8.orig/mysys/CMakeLists.txt mysql-5.5.8/mysys/CMakeLists.txt
--- mysql-5.5.8.orig/mysys/CMakeLists.txt	2010-12-03 12:58:26.000000000 -0500
+++ mysql-5.5.8/mysys/CMakeLists.txt	2011-01-13 11:58:42.583471734 -0500
@@ -69,6 +69,8 @@
  SET(MYSYS_SOURCES ${MYSYS_SOURCES} my_port.c)
 ENDIF()
 
+ADD_DEFINITIONS( -DDEFAULT_HOME_ENV=MYSQL_HOME)
+
 ADD_CONVENIENCE_LIBRARY(mysys ${MYSYS_SOURCES})
 TARGET_LINK_LIBRARIES(mysys dbug strings ${ZLIB_LIBRARY} 
  ${LIBNSL} ${LIBM} ${LIBRT})

It might be better to just forget about this bit of "configurability" and hardwire "MYSQL_HOME" in default.c, though.  A look in the 5.1 sources doesn't show any evidence that any other value is really of interest.
[13 Jan 2011 18:04] Davi Arnaut
It seems $MYSQL_HOME is a mysqld_safe thing:

"MYSQL_HOME is an environment variable containing the path to the directory in which the server-specific my.cnf file resides. If MYSQL_HOME is not set and you start the server using the mysqld_safe program, mysqld_safe attempts to set MYSQL_HOME as follows:"
[13 Jan 2011 18:19] Davi Arnaut
I see, in 5.1, DEFAULT_HOME_ENV is converted to MYSQL_HOME through C preprocessor stringification (ugh). Yeah, better get rid of DEFAULT_HOME_ENV and just use MYSQL_HOME directly. Also, the documentation around MYSQL_HOME should be expanded to take into account that its value is added to the list of default directories.
[4 Mar 2011 22:42] Sveta Smirnova
Bug #60319 was marked as duplicate of this one.
[6 Mar 2011 8:00] Chris Jerdonek
This issue does not affect mysqld_safe's reading of my.cnf if my.cnf lies in the data directory because mysqld_safe includes the following logic for when my.cnf lies in the data directory:

    if test -z "$defaults" -a -r "$DATADIR/my.cnf"
    then
      defaults="--defaults-extra-file=$DATADIR/my.cnf"
    fi

For consistency, it seems like mysqld_safe should apply this logic equally if my.cnf lies in the home directory MYSQL_HOME.

This would at least address the issue of mysqld_safe not recognizing my.cnf when my.cnf is in the base directory as I reported in Bug #60319.

This would address the issue of mysqld_safe not matching the MySQL 5.5. option file documentation here:

"On Unix, Linux and Mac OS X, MySQL programs read startup options from the following files, in the specified order (top items are used first).
...
$MYSQL_HOME/my.cnf | Server-specific options
..."

(from http://dev.mysql.com/doc/refman/5.5/en/option-files.html )

Below is a patch for the equal application of this logic:

------
@@ -400,10 +400,6 @@
 if test -d $MY_BASEDIR_VERSION/data/mysql
 then
   DATADIR=$MY_BASEDIR_VERSION/data
-  if test -z "$defaults" -a -r "$DATADIR/my.cnf"
-  then
-    defaults="--defaults-extra-file=$DATADIR/my.cnf"
-  fi
 # Next try where the source installs put it
 elif test -d $MY_BASEDIR_VERSION/var/mysql
 then
@@ -458,6 +454,10 @@
 fi
 export MYSQL_HOME
 
+if test -z "$defaults" -a -r "$MYSQL_HOME/my.cnf"
+then
+  defaults="--defaults-extra-file=$MYSQL_HOME/my.cnf"
+fi
 
 # Get first arguments from the my.cnf file, groups [mysqld] and [mysqld_safe]
 # and then merge with the command line arguments
------

If you reject this patch on the grounds that it's not the "right" solution, it seems like for consistency reasons you should at least remove the special case DATADIR logic.  Otherwise, why does mysqld_safe give special treatment to my.cnf lying in the data directory over my.cnf lying in the base directory?
[6 Apr 2011 1:51] Paul Dubois
Noted in 5.5.11, 5.6.2 changelogs.

The MYSQL_HOME environment variable was being ignored.

CHANGESET - http://lists.mysql.com/commits/132481