Warning about mysqld's behaviour

Warning about mysqld's behaviour

[Charlie Brady]

Version of mysql is 3.23.58. I haven't looked at other versions, so don't 
know whether this is new or old brain-damage.

I have a straightforward run file:

#!/bin/sh
exec /usr/libexec/mysqld \
  --defaults-file=/etc/my.cnf \
  --basedir=/usr \
  --datadir=/var/lib/mysql \
  --user=mysql \
  --pid-file=/var/run/mysqld/mysqld.pid

This starts a set of four mysqld processes/threads:

...
   492 ?        S      0:00  \_ supervise mysqld
31796 ?        S      0:00  |   \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --
31797 ?        S      0:00  |       \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysq
31798 ?        S      0:00  |           \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/
31800 ?        S      0:00  |           \_ /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/
493   ?        S      0:00  \_ supervise log
...

But notice that the pid file doesn't reference the parent process:

sh-2.05a# svstat .
.: up (pid 31796) 123 seconds, normally down
sh-2.05a#

sh-2.05a# cat /var/run/mysqld/mysqld.pid ; echo
31798
sh-2.05a#

But, it's worse than that, Jim! The parent process - the one that 
supervise is watching and "controlling" ignores INT, TERM and QUIT 
signals.

sh-2.05a# kill -TERM 31796
sh-2.05a# svstat .
.: up (pid 31796) 309 seconds, normally down
sh-2.05a# kill -QUIT 31796
sh-2.05a# svstat .
.: up (pid 31796) 321 seconds, normally down
sh-2.05a# kill -INT 31796
sh-2.05a# svstat .
.: up (pid 31796) 327 seconds, normally down
sh-2.05a#

So we lose control of the daemon:

sh-2.05a# svc -t .
sh-2.05a# svstat .
.: up (pid 31796) 417 seconds, normally down
sh-2.05a#

Fortunately runit provides an easy solution to this problem (although we 
are left with no option but to trust a pid file, with all the problems 
that entails). We just create control/t and control/d files (at least), 
containing:

#! /bin/sh
exec kill -TERM $(cat /var/run/mysqld/mysqld.pid)

Without this feature from runit, we need to do something like this to run 
mysqld under supervise:

trap 'read pid </var/run/mysqld/mysqld.pid && kill "$pid"' TERM QUIT || exit "$?"
/usr/libexec/mysqld .... --pid-file=/var/run/mysqld/mysqld.pid &
wait

[Gerrit, if you can verify this, you'll need to update your run script 
list.]

---
Charlie

Re: Warning about mysqld's behaviour

[Gerrit Pape]

On Sun, Apr 10, 2005 at 11:16:06PM -0400, Charlie Brady wrote:
> Version of mysql is 3.23.58. I haven't looked at other versions, so don't 
> know whether this is new or old brain-damage.
> 
> I have a straightforward run file:
> 
> #!/bin/sh
> exec /usr/libexec/mysqld \
>  --defaults-file=/etc/my.cnf \
>  --basedir=/usr \
>  --datadir=/var/lib/mysql \
>  --user=mysql \
>  --pid-file=/var/run/mysqld/mysqld.pid
> 
> This starts a set of four mysqld processes/threads:

> But notice that the pid file doesn't reference the parent process:
> 
> sh-2.05a# svstat .
> .: up (pid 31796) 123 seconds, normally down
> sh-2.05a# cat /var/run/mysqld/mysqld.pid ; echo
> 31798
> sh-2.05a#

> But, it's worse than that, Jim! The parent process - the one that 
> supervise is watching and "controlling" ignores INT, TERM and QUIT 
> signals.

> So we lose control of the daemon:

> Fortunately runit provides an easy solution to this problem (although we 
> are left with no option but to trust a pid file, with all the problems 
> that entails). We just create control/t and control/d files (at least), 
> containing:
> 
> #! /bin/sh
> exec kill -TERM $(cat /var/run/mysqld/mysqld.pid)

> [Gerrit, if you can verify this, you'll need to update your run script 
> list.]

I personally don't use mysql, I won't verify it.  I think instead of
updating the run script on the web page, I'll remove mysql completely
from the list.  This service daemon causes so many troubles for a
supervisor, that's not worth it imho, it even seems to change behavior
from version to version.

Thanks, Gerrit.

Re: Warning about mysqld's behaviour

[Alex Efros]

Hi!

On Thu, Apr 14, 2005 at 08:53:06AM +0000, Gerrit Pape wrote:
> > Version of mysql is 3.23.58. I haven't looked at other versions, so don't 
> > know whether this is new or old brain-damage.
> > 
> > I have a straightforward run file:
> > 
> > #!/bin/sh
> > exec /usr/libexec/mysqld \
> >  --defaults-file=/etc/my.cnf \
> >  --basedir=/usr \
> >  --datadir=/var/lib/mysql \
> >  --user=mysql \
> >  --pid-file=/var/run/mysqld/mysqld.pid

I use this ./run with MySQL 4.0.x. It allow me control mysql using runsvctrl:
---cut---
#!/bin/bash
exec </dev/null >/dev/null 2>/dev/null
SHUTPASS="HERE_IS_ME_PASSWORD"
trap "mysqladmin -u shutdown -p$SHUTPASS shutdown" 0
trap 'exit 2' 1 2 3 15
mysqld_safe --default-character-set=koi8_ru & wait
---cut---
(I've special user "shutdown" with only permission to shutdown server
because I dislike use "root" user and put it password in this script.)

I've switched from sending TERM to mysqld to using "mysqladmin shutdown"
some time ago because on very busy server sending TERM sometimes result
in damaging database. There was number of cases when after reboot with
"init 6" I found database crashed. :(

-- 
			WBR, Alex.

Re: Warning about mysqld's behaviour

[Charlie Brady]

On Thu, 14 Apr 2005, Alex Efros wrote:

> I use this ./run with MySQL 4.0.x. It allow me control mysql using runsvctrl:
> ---cut---
> #!/bin/bash
> exec </dev/null >/dev/null 2>/dev/null
> SHUTPASS="HERE_IS_ME_PASSWORD"
> trap "mysqladmin -u shutdown -p$SHUTPASS shutdown" 0
> trap 'exit 2' 1 2 3 15
> mysqld_safe --default-character-set=koi8_ru & wait

The reason that I use runsvctrl is that I believe that (with runit) I can 
provide a better "mysqld_safe" than the mysql packagers can. Or, to put it 
another way, mysqld_safe (called safe_mysqld in mysql-3) is just an 
attempt to do in a shell script (most of) what runit already does more 
simply and reliably.

One thing that safe_mysqld does which a runit run script won't do is 
attempt to detect hung processes and then send them a -9 signal - but I 
think that's a very suspect thing to do.

Since safe_mysqld depends on pid files and parsing the oputput of the ps 
command, I'm sure that it's full of potential race conditions.

> ---cut---
> (I've special user "shutdown" with only permission to shutdown server
> because I dislike use "root" user and put it password in this script.)
>
> I've switched from sending TERM to mysqld to using "mysqladmin shutdown"
> some time ago because on very busy server sending TERM sometimes result
> in damaging database. There was number of cases when after reboot with
> "init 6" I found database crashed. :(

safe_mysqld starts mysqld with --skip-locking - perhaps that contributed 
to your database corruption.

I prefer to follow Einstein's directive - make it as simple as possible - 
but no simpler.

---
Charlie

Re: Warning about mysqld's behaviour

[Vincent Danen]

[-- Attachment #1: Type: text/plain, Size: 3168 bytes --]


On Apr 14, 2005, at 2:53, Gerrit Pape wrote:

> On Sun, Apr 10, 2005 at 11:16:06PM -0400, Charlie Brady wrote:
>> Version of mysql is 3.23.58. I haven't looked at other versions, so 
>> don't
>> know whether this is new or old brain-damage.
>>
>> I have a straightforward run file:
>>
>> #!/bin/sh
>> exec /usr/libexec/mysqld \
>>  --defaults-file=/etc/my.cnf \
>>  --basedir=/usr \
>>  --datadir=/var/lib/mysql \
>>  --user=mysql \
>>  --pid-file=/var/run/mysqld/mysqld.pid
>>
>> This starts a set of four mysqld processes/threads:
>
>> But notice that the pid file doesn't reference the parent process:
>>
>> sh-2.05a# svstat .
>> .: up (pid 31796) 123 seconds, normally down
>> sh-2.05a# cat /var/run/mysqld/mysqld.pid ; echo
>> 31798
>> sh-2.05a#
>
>> But, it's worse than that, Jim! The parent process - the one that
>> supervise is watching and "controlling" ignores INT, TERM and QUIT
>> signals.
>
>> So we lose control of the daemon:
>
>> Fortunately runit provides an easy solution to this problem (although 
>> we
>> are left with no option but to trust a pid file, with all the problems
>> that entails). We just create control/t and control/d files (at 
>> least),
>> containing:
>>
>> #! /bin/sh
>> exec kill -TERM $(cat /var/run/mysqld/mysqld.pid)
>
>> [Gerrit, if you can verify this, you'll need to update your run script
>> list.]
>
> I personally don't use mysql, I won't verify it.  I think instead of
> updating the run script on the web page, I'll remove mysql completely
> from the list.  This service daemon causes so many troubles for a
> supervisor, that's not worth it imho, it even seems to change behavior
> from version to version.

I don't have a problem here running the latest mysql (4.1.11) using the 
following scripts:

[root@titan mysqld]# cat run
#!/bin/sh
PATH="/sbin:/usr/sbin:/bin:/usr/bin"

# this runs mysqld supervised

umask 077

DATADIR="/var/lib/mysql"
PID_FILE="$DATADIR/`/bin/hostname`.pid"
MYSQLD_OPTS=""
LOG=""

# overwrite the above
if [ -f /etc/sysconfig/mysqld ]; then
   . /etc/sysconfig/mysqld
fi

if [ "$LOG" != "" ]; then
   LOGFILE="--log=$LOG"
fi

/usr/sbin/mysqld --basedir=/ --datadir=$DATADIR --user=mysql 
--pid-file=$PID_FILE $LOGFILE --skip-locking $MYSQLD_OPTS 2>&1
[root@titan mysqld]# cat finish
#!/bin/sh
PATH="/sbin:/usr/sbin:/bin:/usr/bin"

# this uses mysqladmin to shutdown mysqld, but we have to tell it where 
to find .my.cnf

HOME=/root /usr/bin/mysqladmin shutdown

As you can see, we can start and stop it all we like, provided we store 
the root user's password (for the db root user) in root's (system root) 
~/.my.cnf:

[root@titan mysqld]# srv stop mysqld
Stopping mysqld: mysqld mysqld/log done
[root@titan mysqld]# srv status mysqld
/service/mysqld: down 1 seconds
/service/mysqld/log: down 5 seconds
[root@titan mysqld]# srv start mysqld
Starting mysqld: mysqld/log mysqld done
[root@titan mysqld]# srv status mysqld
/service/mysqld: run (pid 3590) 9 seconds
/service/mysqld/log: run (pid 3580) 9 seconds

-- 
Annvix - Secure Linux Server: http://annvix.org/
"lynx -source http://linsec.ca/vdanen.asc | gpg --import"
{FEE30AD4 : 7F6C A60C 06C2 4811 FA1C  A2BC 2EBC 5E32 FEE3 0AD4}

[-- Attachment #2: This is a digitally signed message part --]
[-- Type: application/pgp-signature, Size: 186 bytes --]