MySQL Bugs: #40368: mysqld_safe not honouring underscore same as dash on server options

Bug #40368	mysqld_safe not honouring underscore same as dash on server options
Submitted:	28 Oct 2008 9:08	Modified:	21 Sep 2010 0:14
Reporter:	Arjen Lentz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1, 5.0, 5,1, 6.0 bzr	OS:	Any (any)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any
Tags:	Contribution, my_print_defaults, mysqld_safe, open_files_limit, Options

Description:
mysqld accepts both dashes and underscores in server options, however mysqld_safe does not.
Since it's unclear from the user's perspective (and they shouldn't NEED to know!) which options get processed only by mysqld_safe, this can mean that they use an option in the wrong way but don't notice it.
Example... open_files_limit
When written like that, it mysqld_safe will not recognise it and thus not call ulimit -n ...
The option WILL be feed to mysqld so it shows the "correct" number in SHOW GLOBAL VARIABLES. Except, since ulimit wasn't called, it's not actually in effect!

How to repeat:
See above.

Suggested fix:
Make mysqld_safe either search/replace substitute on options, or check for dashes as well as underscores in options it needs to process.

Patch provided by from Erik Ljungstrom (Ibiblio)
https://bugs.launchpad.net/ourdelta/+bug/290190
OurDelta (http://launchpad.net/ourdelta)

--- mysql-5.0.67.orig/scripts/mysqld_safe.sh
+++ mysql-5.0.67/scripts/mysqld_safe.sh
@@ -59,6 +59,14 @@
   fi

   for arg do
+
+    # Replaces all underscores with dashes (excluding the two initial ones
+    # and any instances after the first = character) since mysqld accepts
+    # variables in this format.
+    substr_a=$(echo $arg | sed -e 's/\(...\)\(.*\)=\(.*\)/\2/');
+    substr_b=$(echo $substr_a | sed s/_/-/g);
+    arg=`echo $arg | sed s/$substr_a/$substr_b/g`
+
     case "$arg" in
       --skip-kill-mysqld*)
         KILL_MYSQLD=0;

Updated patch that also makes Solaris happy.

NOTE! This fix in scripts/mysqld_safe works, but we probably want to do this in extra/my_print_defaults.c which is used by other tools also and thus may benefit from the fix.
We're looking into that now.

--- mysql-5.0.67.orig/scripts/mysqld_safe.sh    2008-08-04 13:20:02.000000000 +0100
+++ mysql-5.0.67/scripts/mysqld_safe.sh 2008-10-28 22:11:06.000000000 +0000
@@ -59,6 +59,14 @@
   fi

   for arg do
+
+    # Replaces all underscores with dashes (excluding the two initial ones
+    # and any instances after the first = character) since mysqld accepts
+    # variables in this format.
+    substr_a=`echo $arg | sed -e 's/\(...\)\(.*\)=\(.*\)/\2/'`
+    substr_b=`echo $substr_a | sed s/_/-/g`
+    arg=`echo $arg | sed s/$substr_a/$substr_b/g`
+
     case "$arg" in
       --skip-kill-mysqld*)
         KILL_MYSQLD=0;

Thank you for the report.

Please indicate version of MySQL and which variables except open_files_limit you have problem with: open_files_limit can be not set because system privilege reason and I can not repeat the problem with other variables:

$ ./bin/mysqld_safe --no-defaults --skip-grant-tables --binlog_cache_size=12288 &
[1] 16732

$./bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.74-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

mysql> \q
Bye

$./bin/mysqladmin shutdown -uroot
skipped 9 bytes from file: socket (3)
STOPPING server from pid file /users/ssmirnova/build/mysql-5.0/data/shella.mysql.com.pid
081101 21:14:31  mysqld ended

[1]+  Done                    ./bin/mysqld_safe --no-defaults --skip-grant-tables --binlog_cache_size=12288

$ ./bin/mysqld_safe --no-defaults --skip-grant-tables&
[1] 16814

$./bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.74-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

mysql> \q
Bye

Sveta, it's not about mysqld at all, it's about the mysqld_safe script and the options it accepts.
Therefore, I thinkany version since MySQL 4.0 is affected (in 4.0 the option parsing was changed and that's probably when the problem was "created").

mysqld_safe handles a number of options, including:
 open-files-limit
 core-file-size
 log-error

mysqld_safe does not recognise any of its options if they use underscores instead of dashes, so when people put like open_files_limit or log_error into their my.cnf, mysqld_safe will ignore it.
You can easily see which options are affected, since mysqld_safe is a script; just less to view and see for yourself.

Your own test with binlog_cache_size is incorrect, since that option is not used by mysqld_safe.

Sveta - in addition, you cannot check the validity of your testing from inside mysqld, since it will display the option correctly: mysqld will parse options with an underscore properly.

It's not that easy to make *visible* what's going on without adding some debugging code into mysqld_safe. Here's a suggestion:
In my.cnf, put a open_files_limit=2345 (with underscores).
Just before mysqld is started, add a line into the mysqld_safe script with
 ulimit -a
to display what has been set. You will find that the open files limit will actually still be its default for the process, rather than 2345.

Note that SHOW VARIABLES from inside mysqld will show 2345 because the option is parsed properly there - but mysqld can't set ulimit, so it's just displaying what it presumes mysqld_safe has adjusted (but hasn't). It's a very sneaky messy situation.

Fix incorporated in OurDelta builds from 5.0.67 patchset d7
Slightly modified from first posted fix, Solaris needed a different approach - now it should work on pretty much any Unix around.

Index: mysql-5.0.67/scripts/mysqld_safe.sh
===================================================================
--- mysql-5.0.67.orig/scripts/mysqld_safe.sh    2008-08-04 13:20:02.000000000 +0100
+++ mysql-5.0.67/scripts/mysqld_safe.sh 2008-10-28 22:11:06.000000000 +0000
@@ -59,6 +59,14 @@
   fi

   for arg do
+
+    # Replaces all underscores with dashes (excluding the two initial ones
+    # and any instances after the first = character) since mysqld accepts
+    # variables in this format.
+    substr_a=`echo $arg | sed -e 's/\(...\)\(.*\)=\(.*\)/\2/'`
+    substr_b=`echo $substr_a | sed s/_/-/g`
+    arg=`echo $arg | sed s/$substr_a/$substr_b/g`
+
     case "$arg" in
       --skip-kill-mysqld*)
         KILL_MYSQLD=0;
Index: mysql-5.0.67/patch_info/mysqld_safe_fixlp290190_fixmysql40368.info
===================================================================
--- /dev/null   2008-10-17 18:51:21.426369000 +0100
+++ mysql-5.0.67/patch_info//mysqld_safe_fixlp290190_fixmysql40368.info       2008-10-28 23:14:16.000000000 +0000
@@ -0,0 +1,6 @@
+File=mysqld_safe_fixlp290190_fixmysql40368.patch
+Name=mysqld_safe_fix
+Version=1.1
+Author=Erik Ljungstrom
+License=GPL
+Comment=Fixes bugs lp:290190 mysql:40368

Thank you for the feedback.

Verified reading source code, although mysqld_safe passes options with underscores to mysqld in my environment.

Sveta,

Note that while options are passed to mysqld, in specific case of open-files-limit this does not help.

The reason mysqld_safe parses this is because it needs to run "ulimit" as root, as mysqld is started as non-root user and cannot raise ulimit.

So this breaks open-files-limit when used with _ underscores.

Trent

review sent in email cc:internals@lists.mysql.com

Erik, the original author of the patch, accepted my modified version, now I need to test it on several Unices.

Cool. Probably the status of this bug needs to be updated from "Need Feedback" to "In Progress" or review or something.

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

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/73391

2756 Guilhem Bichot	2009-05-05
      Fix for BUG#40368 "mysqld_safe not honouring underscore same as dash on server options".
      Such bug led "mysqld_safe --core_file_size=#" to not work because mysqld_safe wouldn't recognize
      that "ulimit -c" is needed; only --core-file-size=# worked. Same for --open_files_limit and other
      options with _ where mysqld_safe needs to do something more than passing to mysqld.
      Original fix by Erik Ljungstrom erik at ibiblio dot org ; slightly modified here.
      Tested on all internally accessible Unix.

queued to 6.0-maria. Should hit at least 6.0 and hopefully 5.4.

Pushed into 6.0.12-alpha (revid:alik@sun.com-20090516083402-0avycdy7w6dnn0tv) (version source revid:serg@mysql.com-20090505185952-rb1hg47owvrs7onb) (merge vers: 6.0.11-alpha) (pib:6)

Noted in 6.0.12 changelog.

mysqld_safe did not treat dashes and underscores as equivalent in
option names.

fix ported to next-mr-bugfixing.
Note that Erik had signed the SCA as he is in
https://sca.dev.java.net/CA_signatories.htm:
"Erik Ljungstrom - MySQL - Erik Ljungstrom"

Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:guilhem@mysql.com-20091118204049-wlrfy9r2arc7xmat) (merge vers: 6.0.14-alpha) (pib:13)

Setting report to NDI pending push to 5.6.x.

Pushed into 5.6.0-beta (revid:alik@sun.com-20091211055628-ltr7fero363uev7r) (version source revid:alik@sun.com-20091211055453-717czhtezc74u8db) (merge vers: 5.6.0-beta) (pib:13)

Noted in 5.6.0 changelog.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091211201717-03qf8ckwiw0np80p) (merge vers: 5.6.0-beta) (pib:16)

Moved 5.6.0 changelog entry to 5.5.3.

See also bug #47095