MySQL Bugs: #12751: Instance Manager: client hangs after start instance; kill mysqlmanager; show ...

Bug #12751	Instance Manager: client hangs after start instance; kill mysqlmanager; show ...
Submitted:	23 Aug 2005 10:34	Modified:	4 Oct 2006 19:32
Reporter:	Petr Chardin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Instance Manager	Severity:	S3 (Non-critical)
Version:	5.0.16-BK, 5.0.11	OS:	Linux (Linux)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
client hangs after the following event sequence:

define non-guarded instance mysqld;
start IM;
issu "start instance mysqld" in the client;
kill mysqlmanger;
start IM again;
issue "show instance" in the same client;  (In fact, any command)

During this time IM works, and accepts new connections. Just this client does not seem to reconnect.

How to repeat:
do steps described above

Suggested fix:
Probably this a protocol issue. Described situation happens only with "start instance".
This is the only IM command which gives comments to the commands. E.g.
mysql> start instance mysqld;
Query OK, 0 rows affected (0.00 sec)
Instance started
So, the client might experience difficulties with reconnect because IM somehow messes with protocol.
It might have sense to check the same with MySQL server. E.g. issue some  command wich has a comment, then kill server, restart it, then attempt reconnect.

Thank you for a bug report. Verified on 5.0.16-BK on Fedora Core 1 with a sequence of action similat to the described:

Session 1:

[openxs@Fedora 5.0]$ cat my.cnf
[mysqld]
nonguarded
[openxs@Fedora 5.0]$ libexec/mysqlmanager --defaults-file=/home/openxs/dbs/5.0/my.cnf &
[1] 2732
[openxs@Fedora 5.0]$ 051030 15:11:12    loaded user vk
051030 15:11:12 accepting connections on ip socket
051030 15:11:12 accepting connections on unix socket /tmp/mysqlmanager.sock

Session 2:

[openxs@Fedora 5.0]$ bin/mysql -uvk -p --socket=/tmp/mysqlmanager.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 0.2-alpha

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

mysql> show instances;
+---------------+---------+
| instance_name | status  |
+---------------+---------+
| mysqld        | offline |
+---------------+---------+
1 row in set (0,01 sec)

At the same time in Session 1:

051030 15:11:30 accepted mysql connection 1
051030 15:11:30 connection 1 is checked successfully
051030 15:11:48 connection 1: packet_length=15, command=3
051030 15:11:48 query for connection 1 : ----
show instances
-------------------------
051030 15:11:48 query for connection 1 successefully parsed
051030 15:11:48 query for connection 1 executed ok

Session 2:

mysql> stop instance mysqld;
Query OK, 0 rows affected (0,00 sec)

At the same time in Session 1:

051030 15:12:04 connection 1: packet_length=21, command=3
051030 15:12:04 query for connection 1 : ----
stop instance mysqld
-------------------------
051030 15:12:04 query for connection 1 successefully parsed
051030 15:12:04 query for connection 1 executed ok

Session 2:

mysql> start instance mysqld;
Query OK, 0 rows affected (0,01 sec)
Instance started

At the same time in Session 1:

051030 15:12:22 connection 1: packet_length=22, command=3
051030 15:12:22 query for connection 1 : ----
start instance mysqld
-------------------------
051030 15:12:22 query for connection 1 successefully parsed
051030 15:12:22 starting instance mysqld
051030 15:12:22 query for connection 1 executed ok
051030 15:12:23  InnoDB: Started; log sequence number 0 26158707
051030 15:12:23 [Note] /home/openxs/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.16'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

[openxs@Fedora 5.0]$ ps -ef | grep mysqlm
openxs    2732  2451  0 15:11 pts/1    00:00:00 libexec/mysqlmanager --defaults-file=/home/openxs/dbs/5.0/my.cnf
openxs    2738  1384  0 15:11 pts/0    00:00:00 bin/mysql -uvk -p --socket=/tmp/mysqlmanager.sock
openxs    2752  2451  0 15:12 pts/1    00:00:00 grep mysqlm
[openxs@Fedora 5.0]$ kill -9 2732
[openxs@Fedora 5.0]$ libexec/mysqlmanager --defaults-file=/home/openxs/dbs/5.0/my.cnf &
[2] 2753
[1]   Killed                  libexec/mysqlmanager --defaults-file=/home/openxs/
dbs/5.0/my.cnf
[openxs@Fedora 5.0]$ 051030 15:13:03    loaded user vk
051030 15:13:03 accepting connections on ip socket
051030 15:13:03 accepting connections on unix socket /tmp/mysqlmanager.sock

Session 2:

mysql> show instances;
-- hang forever...

In session 1, meanwhile:

[openxs@Fedora 5.0]$ bin/mysql -uvk -p --socket=/tmp/mysqlmanager.sock
Enter password:
051030 15:14:08 accepted mysql connection 1
051030 15:14:08 connection 1 is checked successfully
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 0.2-alpha

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

mysql> show instances;
051030 15:14:14 connection 1: packet_length=15, command=3
051030 15:14:14 query for connection 1 : ----
show instances
-------------------------
051030 15:14:14 query for connection 1 successefully parsed
+---------------+---------+
| instance_name | status  |
+---------------+---------+
| mysqld        | offline |
+---------------+---------+
1 row in set (0,01 sec)

mysql> 051030 15:14:14  query for connection 1 executed ok
exit
051030 15:14:17 connection 1: packet_length=1, command=1
051030 15:14:17 query for connection 1 received quit command
Bye
[openxs@Fedora 5.0]$ ps -ef | grep mysql
openxs    2738  1384  0 15:11 pts/0    00:00:00 bin/mysql -uvk -p --socket=/tmp/mysqlmanager.sock
openxs    2741     1  0 15:12 pts/1    00:00:00 /home/openxs/dbs/5.0/libexec/mysqld --no-defaults --pid-file=mysqld-Fedora.pid
openxs    2753  2451  0 15:13 pts/1    00:00:00 libexec/mysqlmanager --defaults-file=/home/openxs/dbs/5.0/my.cnf
openxs    2781  2451  0 15:14 pts/1    00:00:00 grep mysql

That is what happens.

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

ChangeSet@1.2257, 2006-09-01 18:20:33+04:00, anozdrin@alik. +3 -0
  Fix for BUG#12751: Instance Manager: client hangs after
  start instance; kill mysqlmanager; show ...
  
  The problem was that Instance Manager didn't close client
  sockets (sockets for client connections) on execing mysqld
  instance. So, mysqld-instance inherits these descriptors.
  
  The fix is to set close-on-exec flag for each client socket.

Fixed in 5.0.27 and 5.1.12

Noted in 5.0.27, 5.1.12 changelogs.

Instance Manager didn't close the client socket file when starting a
new mysqld instance. mysqld inherited the socket, causing clients
connected to Instance Manager to hang.

The 5.0.x fix is in 5.0.30.