Bug #61291 5.5 - scripts/mysql.server doesn't check for pid file on 'start'
Submitted: 25 May 2011 22:08 Modified: 15 Aug 2012 16:56
Reporter: Justin Rovang Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Installing Severity:S2 (Serious)
Version:5.5 OS:Linux (Ubuntu 10.4, Debian 6.0)
Assigned to: CPU Architecture:Any
Tags: init.d, mysql.server, pid check
Triage: Needs Triage: D3 (Medium)

[25 May 2011 22:08] Justin Rovang
Description:
The script/mysql.server script (Meant for init.d) allows you to start more than one server instance on the same pid/sock file, causing a disruption in service if '/etc/init.d/mysql start' is called twice. 

How to repeat:
===============================================
= GENERAL OVERVIEW                           
===============================================
Compile and install from scratch.
Perform necessary install procedures, including moving scripts/mysql.server to /etc/init.d/mysql and chmod +x it.

/etc/init.d/mysql start
Verify working: mysql -p
/etc/init.d/mysql start (second time)
Verify working: mysql -p   (Socket error)
(`ps` lists two instances with the same settings, pid, sock, port, etc)

===============================================
DETAILED STEPS TO REPLICATION ("REPLAY LOG")
From Ubuntu 10.4 - patched, bare system.
===============================================
sudo su
cd /root

apt-get install build-essential bison g++ libncurses5-dev cmake libaio-dev

wget http://www.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.12.tar.gz/from/http://mirror.services.wisc.edu/mysql/ -O mysql-5.5.tar.gz

tar -zxvf mysql-5.5.tar.gz

groupadd mysql
useradd -r -g mysql mysql

mkdir /opt/mysql

cd mysql-5.5.12

cmake . \
-DCMAKE_INSTALL_PREFIX=/opt/mysql

make
make install

### (vi /etc/environment, add "/opt/mysql/bin" to path) ###
PATH=$PATH:/opt/mysql/bin

mkdir /var/log/mysql
chown mysql:mysql /var/log/mysql

mkdir /var/run/mysqld
chown mysql:mysql /var/run/mysqld

cp support-files/my-large.cnf /etc/my.cnf
### Edit my.cnf to use /var/run/mysqld/mysql.sock ###

cp support-files/mysql.server /etc/init.d/mysql
chmod ug+x /etc/init.d/mysql
update-rc.d mysql defaults

cd /opt/mysql
./scripts/mysql_install_db

chown -R mysql:mysql /opt/mysql/data

### Start the server ###
/etc/init.d/mysql start

mysql
### works fine

ps -A ux |grep mysqld_safe
### one instance

### Start it again, 'by accident' ###
/etc/init.d/mysql start

mysql
### RROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysql.sock' (2)

ps -A ux |grep mysqld_safe
### two instances

============================================
= OTHER OBSERVATIONS
============================================
If you leave the two running for a while (~1-5 minutes, I didn't time it) - the second instance will exit, and the first will remain active, but still not accepting connections. The error log is full of errors due to a second process utilizing the same resources, as obvious at this point.

Suggested fix:
It seems likely that the 'scripts/mysql.server' script isn't checking the target pid file before firing off a new instance.
[25 May 2011 22:26] Justin Rovang
You could add something similar to what I see in my pre-canned debian init.d script to the 'start' case in the script (not sure what exit or logging you'd want)

if [ -f "$mysqld_pid_file_path" ] && ps `cat $mysqld_pid_file_path` > /dev/null 2>&1
then
  echo "MySQL is already running"
  exit 0
fi
[26 May 2011 2:17] Alexey Kishkin
verified as described.

[root@archhome mysql]# ./support-files/mysql.server start
Starting MySQL... SUCCESS! 

[root@archhome mysql]# ps ax |grep mysql
 9422 pts/1    S      0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/archhome.pid
 9499 pts/1    Sl     0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/archhome.err --pid-file=/usr/local/mysql/data/archhome.pid
 9521 pts/1    S+     0:00 grep mysql

[root@archhome mysql]# ./support-files/mysql.server start
Starting MySQL SUCCESS! 
[root@archhome mysql]# ps ax |grep mysql
 9422 pts/1    S      0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/archhome.pid
 9499 pts/1    Sl     0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/archhome.err --pid-file=/usr/local/mysql/data/archhome.pid
 9528 pts/1    S      0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/archhome.pid
 9609 pts/1    Sl     0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/archhome.err --pid-file=/usr/local/mysql/data/archhome.pid
 9622 pts/1    S+     0:00 grep mysql
[26 May 2011 2:19] Alexey Kishkin
[root@archhome mysql]# bin/mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[15 Jul 2011 0:13] Justin Rovang
The same happens for the mysqld_multi script.
[22 Jun 2012 8:45] Valeriy Kravchuk
I can not repeat this with 5.5.26:

[openxs@chief 5.5]$ ./support-files/mysql.server start
Starting MySQL... SUCCESS! 
[openxs@chief 5.5]$ ps aux | grep mysqld
openxs    4673  0.1  0.0 108336  1432 pts/1    S    11:42   0:00 /bin/sh /home/openxs/dbs/5.5/bin/mysqld_safe --datadir=/home/openxs/dbs/5.5/data --pid-file=/home/openxs/dbs/5.5/data/chief.pid
openxs    4776  2.9  2.2 457756 46056 pts/1    Sl   11:42   0:00 /home/openxs/dbs/5.5/bin/mysqld --basedir=/home/openxs/dbs/5.5 --datadir=/home/openxs/dbs/5.5/data --plugin-dir=/home/openxs/dbs/5.5/lib/plugin --log-error=/home/openxs/dbs/5.5/data/chief.err --pid-file=/home/openxs/dbs/5.5/data/chief.pid --socket=/tmp/mysql.sock
openxs    4799  0.0  0.0 103376   824 pts/1    R+   11:42   0:00 grep --color=auto mysqld
[openxs@chief 5.5]$ 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.5.26-debug Source distribution

Copyright (c) 2000, 2011, 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> exit
Bye
[openxs@chief 5.5]$ ./support-files/mysql.server start
Starting MySQL SUCCESS! 
[openxs@chief 5.5]$ ps aux | grep mysqld
openxs    4673  0.0  0.0 108336  1432 pts/1    S    11:42   0:00 /bin/sh /home/openxs/dbs/5.5/bin/mysqld_safe --datadir=/home/openxs/dbs/5.5/data --pid-file=/home/openxs/dbs/5.5/data/chief.pid
openxs    4776  1.1  2.5 523552 50616 pts/1    Sl   11:42   0:00 /home/openxs/dbs/5.5/bin/mysqld --basedir=/home/openxs/dbs/5.5 --datadir=/home/openxs/dbs/5.5/data --plugin-dir=/home/openxs/dbs/5.5/lib/plugin --log-error=/home/openxs/dbs/5.5/data/chief.err --pid-file=/home/openxs/dbs/5.5/data/chief.pid --socket=/tmp/mysql.sock
openxs    4891  0.0  0.0 103376   824 pts/1    R+   11:42   0:00 grep --color=auto mysqld
[openxs@chief 5.5]$ 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 2
Server version: 5.5.26-debug Source distribution

Copyright (c) 2000, 2011, 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.

...

Probably fixed already.
[15 Aug 2012 16:56] Justin Rovang
I can second that this has been fixed in 5.5.26 as well. I would say this should be closed - I would close it but I'm uncertain of the etiquette .