Bug #32136 mysqld_multi --defaults-file not respected while using --mysqld=mysqld_safe

Description:
Description:
mysqld_multi --help gives you the following output
"--defaults-file=...  Read only this configuration file, do not read the
                   standard system-wide and user-specific files
--defaults-extra-file=...  Read this configuration file in addition to the
                   standard system-wide and user-specific files"

However, using --mysqld=mysqld_safe negates the difference between --defaults-file and --defaults-extra-file, they both work the exact same way.

How to repeat:
Start MySQL server with mysqld_multi with not default configuration file, ie.
"/usr/bin/mysqld_multi --defaults-file=/etc/my2.cnf --log=/tmp/mysqld_multi.log start"

Example,
/etc/my2.cnf:
replicate-do-table	= test.test
replicate-wild-ignore-table	= %.%
mysqld = mysqld_safe

/etc/my.cnf:
replicate-do-table	= mysql.users
replicate-do-table	= mysql.db
replicate-wild-ignore-table	= %.%

Information from SHOW SLAVE STATUS from the MySQL server started with
"/usr/bin/mysqld_multi --defaults-file=/etc/my2.cnf --log=/tmp/mysqld_multi.log start":
Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: mysql.users,mysql.users,test.test,mysql.db,mysql.db
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: %.%,%.%,%.%

Suggested fix:
Hand off --defaults-file to mysqld_multi (keeping the documented behavior), or document this behavior

patch mysqld_multi:
@@ -294,6 +294,7 @@
for ($i = 0; defined($groups[$i]); $i++)
{
@options = defaults_for_group($groups[$i]);
+ unshift(@options,"--no-defaults");

$mysqld_found= 1; # The default
$mysqld_found= 0 if (!length($mysqld));

Bug verifiers:
Please reverify -- has this been fixed by the
patch for bug#20748? Thanks.

Verified fixed in 5.0.54-bk.

I have retested this on 5.0.70-bzr, it is happening again.

./bin/mysqld_multi --defaults-file=/tmp/alternate-my.cnf start

Using mysqld=mysqld_safe, still reads data from [mysqld] section in /etc/my.cnf

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/59956

2723 Chad MILLER	2008-11-26
      Bug#32136: mysqld_multi --defaults-file not respected while using \
                    --mysqld=mysqld_safe
      
      The server run didn't know the correct section to read in a
      configuration file, and would read from "[mysqld]" even though
      mysqld_multi had already read the defaults and made them into explicit
      parameters. 
      
      Worse, the "defaults-file" parameter says that it means "Read only
      this configuration file, do not read the standard system-wide and
      user-specific files", which should apply not only to mysql-multi, but
      to the server also.
      
      So, now if "defaults-file" is given, put "no-defaults" before all the
      explicit parameters we read from the defaults-file and feed to the
      mysqld or mysqld_safe.

Patch clarification:

I'm constructing explicit arguments from configuration files by using
"extra/my_print_defaults", which does read from the user's ~/.my.cnf
when appropriate.  Since we read all the configuration files already, we
shouldn't try it again.  *All* configuration reading is now moved to the
wrapper program.

So the stages are,
 1  read parameters to mysql_multi.
 2  if the first parameter affects reading the config file, consume and
capture it.
 3  scan the rest of the parameters for usable values for this stage.
 4  use my_print_defaults, optionally with captured parameter from step
2.  Catch parameters emitted.
 5  call the next program with "--no-defaults" first, and then all the
explicit parameters caught in step 4.

That should ensure that all parameters in mysqld are set by reading each
configuration file exactly once.

Queued to 5.0-, 5.1-, and 6.0-bugteam.

Pushed into 5.0.78 (revid:joro@sun.com-20090206133929-ybpbkkox9wkptopb) (version source revid:chad@mysql.com-20090204185733-ocgg0b3leq8lzysh) (merge vers: 5.0.78) (pib:6)

Pushed into 5.1.32 (revid:davi.arnaut@sun.com-20090209214102-gj3sb3ujpnvpiy4c) (version source revid:davi.arnaut@sun.com-20090209214102-gj3sb3ujpnvpiy4c) (merge vers: 5.1.32) (pib:6)

Pushed into 6.0.10-alpha (revid:matthias.leich@sun.com-20090212211028-y72faag15q3z3szy) (version source revid:chad@mysql.com-20090204193029-4rwj274vu3doszrq) (merge vers: 6.0.10-alpha) (pib:6)

Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef) (version source revid:tomas.ulin@sun.com-20090216083408-rmvyaxjt6mk8sg1y) (merge vers: 5.1.32-ndb-6.3.23) (pib:6)

Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau) (version source revid:tomas.ulin@sun.com-20090216083646-m8st11oj1hhfuuh5) (merge vers: 5.1.32-ndb-6.4.3) (pib:6)

Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j) (version source revid:tomas.ulin@sun.com-20090211111208-wf0acl7c1vl5653e) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)

Noted in 5.0.78, 5.1.32, 6.0.10 changelog.

For mysqld_multi, using the --mysqld=mysqld_safe option caused the
--defaults-file and --defaults-extra-file options to behave the same
way.

This is not mysqld_multi problem and fix is wrong:

ssmirnova@host ~/build/mysql-5.0
$/users/ssmirnova/build/mysql-5.0/bin/mysqld_safe --defaults-file=/users/ssmirnova/build/mysql-5.0/support-files/my-small.cnf --basedir=/users/ssmirnova/build/mysql-5.0 --datadir=/users/ssmirnova/build/mysql-5.0/data --ledir=/users/ssmirnova/build/mysql-5.0/libexec --log-error --mysqld-path=/users/ssmirnova/build/mysql-5.0/libexec/mysqld --port=33050 --socket=/tmp/mysql_ssmirnova50.sock --basedir=/users/ssmirnova/build/mysql-5.0 --datadir=/users/ssmirnova/build/mysql-5.0/data --character_set_server=utf8 --log-error --server-id=50 --wait_timeout=30 --myisam-recover=force --myisam_recover=BACKUP\,FORCE --ssl --ssl-ca=/users/ssmirnova/src/mysql-5.0/mysql-test/std_data/cacert.pem --ssl_cert=/users/ssmirnova/src/mysql-5.0/mysql-test/std_data/server-cert.pem --ssl-key=/users/ssmirnova/src/mysql-5.0/mysql-test/std_data/server-key.pem --tmpdir=/users/ssmirnova/build/mysql-5.0/tmp &
[1] 24473

ssmirnova@host ~/build/mysql-5.0
$nohup: ignoring input and redirecting stderr to stdout
nohup: ignoring input and redirecting stderr to stdout
Starting mysqld daemon with databases from /users/ssmirnova/build/mysql-5.0/data
nohup: ignoring input and redirecting stderr to stdout
STOPPING server from pid file /users/ssmirnova/build/mysql-5.0/data/host.mysql.com.pid
090326 10:09:43  mysqld ended

[1]+  Done                    /users/ssmirnova/build/mysql-5.0/bin/mysqld_safe --defaults-file=/users/ssmirnova/build/mysql-5.0/support-files/my-small.cnf --basedir=/users/ssmirnova/build/mysql-5.0 --datadir=/users/ssmirnova/build/mysql-5.0/data --ledir=/users/ssmirnova/build/mysql-5.0/libexec --log-error --mysqld-path=/users/ssmirnova/build/mysql-5.0/libexec/mysqld --port=33050 --socket=/tmp/mysql_ssmirnova50.sock --basedir=/users/ssmirnova/build/mysql-5.0 --datadir=/users/ssmirnova/build/mysql-5.0/data --character_set_server=utf8 --log-error --server-id=50 --wait_timeout=30 --myisam-recover=force --myisam_recover=BACKUP\,FORCE --ssl --ssl-ca=/users/ssmirnova/src/mysql-5.0/mysql-test/std_data/cacert.pem --ssl_cert=/users/ssmirnova/src/mysql-5.0/mysql-test/std_data/server-cert.pem --ssl-key=/users/ssmirnova/src/mysql-5.0/mysql-test/std_data/server-key.pem --tmpdir=/users/ssmirnova/build/mysql-5.0/tmp

ssmirnova@host ~/build/mysql-5.0
$cat data/host.mysql.com.err 090326 10:04:27  mysqld started090326 10:04:27 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072090326 10:04:27 [ERROR] /users/ssmirnova/build/mysql-5.0/libexec/mysqld: unknown variable 'mysqld-path=/users/ssmirnova/build/mysql-5.0/libexec/mysqld'090326 10:04:27  mysqld ended

090326 10:09:43  mysqld started
090326 10:09:43 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072
090326 10:09:43 [ERROR] /users/ssmirnova/build/mysql-5.0/libexec/mysqld: unknown variable 'mysqld-path=/users/ssmirnova/build/mysql-5.0/libexec/mysqld'
090326 10:09:43  mysqld ended

ssmirnova@host ~/build/mysql-5.0
$grep -i "mysqld-path" /users/ssmirnova/build/mysql-5.0/support-files/my-small.cnf 

ssmirnova@host ~/build/mysql-5.0
$grep -i "mysqld-path" ~/.my.cnf
mysqld-path     = /users/ssmirnova/build/mysql-4.1/libexec/mysqld
mysqld-path     = /users/ssmirnova/build/mysql-4.1/libexec/mysqld
mysqld-path     = /users/ssmirnova/build/mysql-4.1/libexec/mysqld
mysqld-path     = /users/ssmirnova/build/mysql-5.0/libexec/mysqld
#mysqld-path=/users/ssmirnova/mysql-debug-5.0.22-linux-i686/bin/mysqld
mysqld-path     = /users/ssmirnova/build/mysql-5.0/libexec/mysqld
#mysqld-path    = /users/ssmirnova/mysql-5.0.27b/libexec/mysqld
mysqld-path     = /users/ssmirnova/build/mysql-5.0/libexec/mysqld
#mysqld-path    = /users/ssmirnova/mysql-5.0.27b/libexec/mysqld
mysqld-path     = /users/ssmirnova/build/mysql-5.1/libexec/mysqld
#mysqld-path     = /users/ssmirnova/mysql-5.1.16-beta-linux-i686-glibc23/bin/mysqld
mysqld-path     = /users/ssmirnova/build/mysql-5.1/libexec/mysqld
mysqld-path     = /users/ssmirnova/build/mysql-5.1/libexec/mysqld
mysqld-path     = /users/ssmirnova/build/mysql-maria/libexec/mysqld
mysqld-path     = /users/ssmirnova/build/mysql-5.2/libexec/mysqld
mysqld-path     = /users/ssmirnova/build/mysql-5.2/libexec/mysqld
mysqld-path     = /users/ssmirnova/build/mysql-5.2/libexec/mysqld
mysqld-path     = /users/ssmirnova/build/mysql-5.2/libexec/mysqld
mysqld-path     = /users/ssmirnova/build/mysql-6.0/libexec/mysqld
mysqld-path     = /users/ssmirnova/build/mysql-6.0/libexec/mysqld
mysqld-path     = /users/ssmirnova/build/mysql-6.0/libexec/mysqld

http://dev.mysql.com/doc/refman/5.1/en/mysqld-safe.html#option_mysqld_safe_defaults-file shows defaults-file should work for mysqld_safe in the same way as for other programs.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/71216

2725 Chad MILLER	2009-04-02
      Bug#43876: mysqld_multi introduces --no-defaults which screws up my system
      Bug#32136: mysqld_multi --defaults-file not respected while using \
      	--mysqld=mysqld_safe
      
      Revert change that adds "--no-defaults" to mysqld_multi.
      
      This closes Bug#43508 and re-opens Bug#32136.

Pushed into 5.0.82 (revid:davi.arnaut@sun.com-20090505184158-dvmedh8n472y8np5) (version source revid:davi.arnaut@sun.com-20090505184158-dvmedh8n472y8np5) (merge vers: 5.0.82) (pib:6)

Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)

Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:patrick.crews@sun.com-20090402223909-o6cpilqfrc0wj5e6) (merge vers: 6.0.11-alpha) (pib:6)

Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)

Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)

Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)

I suspect that there's a problem in how mysqld_multi handles --defaults-extra-file : 
I'm getting the report twice when using a --defaults-extra-file option : 
$  bin/mysqld_multi --defaults-extra-file=my.cnf --mysqld=mysqld_safe report 
Reporting MySQL servers
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
MySQL server from group: mysqld4 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
MySQL server from group: mysqld4 is not running

whereas with --defaults-file I'm getting it only once : 
$  bin/mysqld_multi --defaults-file=my.cnf --mysqld=mysqld_safe report 
Reporting MySQL servers
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
MySQL server from group: mysqld4 is not running

the --defaults-file and --defaults-extra-file mysqld_multi options apply only to the mysqld_multi command. They don't get passed through to the underlying servers. For them the default rules apply (as if --defaults-file/--defaults-extra-file was not specified. mysqld_multi just reads the relevant [mysqldNNN] section and expands the parameters as command line parameters to mysqld.
The mysqld_multi documentation says nowhere that they're passed through to the mysqld program, so one should not expect them to pass through.
The ability to specify --defaults-extra-file to the underlying mysqld on mysqld_multi's command line is a nice feature that can be requested in a separate feature request if really needed.