MySQL Bugs: #60319: mysqld_safe does not recognize my.cnf in base directory

Bug #60319	mysqld_safe does not recognize my.cnf in base directory
Submitted:	3 Mar 2011 18:35	Modified:	4 Mar 2011 22:41
Reporter:	Chris Jerdonek	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.5.9	OS:	MacOS
Assigned to:		CPU Architecture:	Any
Tags:	base directory, basedir, my.cnf, mysqld_safe, option file

Description:
The mysqld_safe script will not use a my.cnf file if it is located in mysql's base directory.

The following page says that on Unix, Linux and Mac OS X, MySQL programs should read startup options from $MYSQL_HOME/my.cnf, and that "if MYSQL_HOME is not set and there is no my.cnf file in DATADIR, mysqld_safe [should set] MYSQL_HOME to BASEDIR."

How to repeat:
Create a my.cnf file, say--

"""
[mysqld]

sql-mode="NO_AUTO_CREATE_USER"
"""

Store the my.cnf file in mysql's base directory (e.g. /usr/local/mysql), and run mysqld_safe.

Open a mysql prompt, and execute--

> SHOW VARIABLES LIKE 'sql%';

The result will show no value for sql_mode.  It should show NO_AUTO_CREATE_USER.

Suggested fix:
Here is a patch to mysqld_safe:

@@ -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

I forgot to include the link to the page describing the expected option file behavior.  Here is the link:

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

I see no problem with current 5.1.x that I've built myself on Mac OS X:

macbook-pro:5.1 openxs$ pwd
/Users/openxs/dbs/5.1
macbook-pro:5.1 openxs$ cat my.cnf
[mysqld]
sql-mode="STRICT_ALL_TABLES"

macbook-pro:5.1 openxs$ bin/mysqld_safe &
[1] 10576
macbook-pro:5.1 openxs$ 110304 17:33:33 mysqld_safe Logging to '/Users/openxs/dbs/5.1/var/macbook-pro.err'.
chown: /Users/openxs/dbs/5.1/var/macbook-pro.err: Operation not permitted
110304 17:33:33 mysqld_safe Starting mysqld daemon with databases from /Users/openxs/dbs/5.1/var

macbook-pro:5.1 openxs$ 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
Server version: 5.1.57-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'base%';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| basedir       | /Users/openxs/dbs/5.1/ |
+---------------+------------------------+
1 row in set (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| sql_mode      | STRICT_ALL_TABLES |
+---------------+-------------------+
1 row in set (0.00 sec)

What is the result of:

show variables like 'base%';

in your case?

Sorry, I should only have put 5.5.9 in the version field and not 5.1.  I have changed the bug report.

Below are my results.

> pwd
/usr/local/mysql
> cat ./my.cnf
[mysqld]

sql-mode="NO_AUTO_CREATE_USER"

> mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 217
Server version: 5.5.9 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'base%';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| basedir       | /usr/local/mysql |
+---------------+------------------+
1 row in set (0.00 sec)

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

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of bug #59280