MySQL Bugs: #52515: mysql sessions are not terminated properly

Bug #52515	mysql sessions are not terminated properly
Submitted:	1 Apr 2010 2:51	Modified:	9 May 2011 19:26
Reporter:	Roel Van de Paar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	5.1.45, 5.1.40sp1	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
Start a client session as follows:

C:\mysql5145\bin>START mysql -uroot -P5145 roelt

In the resulting window, type the following:

FLUSH TABLES WITH READ LOCK;
SELECT SLEEP (5000);

Close the window (X at the top right - i.e. kill). 

Start a second session, telling START to wait until the process finishes:

C:\mysql5145\bin>START /WAIT mysql -uroot -P5145 roelt

In the resulting window, type the following:

SELECT SLEEP (10000);

Close the window (X at the top right - i.e. kill). Notice the original "START /WAIT" terminates.

Start a third session, using CMD /K:

C:\mysql5145\bin>CMD /K mysql -uroot -P5145 roelt

In the resulting client, type the following:

SELECT SLEEP (50000);

Now start a new mysql session and verify:

mysql> SHOW FULL PROCESSLIST;
+----+------+-----------------+-------+---------+------+------------+-----------------------+
| Id | User | Host            | db    | Command | Time | State      | Info                  |
+----+------+-----------------+-------+---------+------+------------+-----------------------+
| 48 | root | localhost:49429 | roelt | Query   |    0 | NULL       | SHOW FULL PROCESSLIST |
| 50 | root | localhost:49432 | roelt | Query   |   67 | User sleep | SELECT SLEEP (5000)   |
| 51 | root | localhost:49433 | roelt | Query   |   52 | User sleep | SELECT SLEEP (10000)  |
| 52 | root | localhost:49434 | roelt | Query   |   41 | User sleep | SELECT SLEEP (15000)  |
+----+------+-----------------+-------+---------+------+------------+-----------------------+
4 rows in set (0.00 sec)

Now exit this mysql session and make sure there are no other (earlier) open mysql sessions on your system anymore.

Now check task manager: 

Neither cmd.exe nor mysql.exe are shown in the process list (!), yet the statements above continue.

Even if you start a session normally (start > run > cmd.exe > mysql.exe > SELECT SLEEP > close cmd session) the problem is the same: the transaction keeps running, even though no cmd.exe nor mysql.exe is present anymore.

FLUSH TABLES WITH READ LOCK is not really related to the main bug: the same will work with just SELECT SLEEP's. However, playing around I was able to produce this:

=====================
mysql> SHOW FULL PROCESSLIST;
+----+------+-----------------+-------+---------+------+------------+-----------------------+
| Id | User | Host            | db    | Command | Time | State      | Info                  |
+----+------+-----------------+-------+---------+------+------------+-----------------------+
| 48 | root | localhost:49429 | roelt | Query   |    0 | NULL       | SHOW FULL PROCESSLIST |
| 50 | root | localhost:49432 | roelt | Query   |   67 | User sleep | SELECT SLEEP (5000)   |
| 51 | root | localhost:49433 | roelt | Query   |   52 | User sleep | SELECT SLEEP (10000)  |
| 52 | root | localhost:49434 | roelt | Query   |   41 | User sleep | SELECT SLEEP (15000)  |
+----+------+-----------------+-------+---------+------+------------+-----------------------+
4 rows in set (0.00 sec)

mysql> kill 50;
Query OK, 0 rows affected (0.00 sec)

mysql> kill 51;
Query OK, 0 rows affected (0.00 sec)

mysql> kill 52;
Query OK, 0 rows affected (0.00 sec)

mysql> create table bsdalasd (id int);
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
mysql> SHOW FULL PROCESSLIST;
+----+------+-----------------+-------+---------+------+-------+-----------------------+
| Id | User | Host            | db    | Command | Time | State | Info                  |
+----+------+-----------------+-------+---------+------+-------+-----------------------+
| 48 | root | localhost:49429 | roelt | Query   |    0 | NULL  | SHOW FULL PROCESSLIST |
+----+------+-----------------+-------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

mysql> create table test (id int);
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
=====================

How to repeat:
As per the above.

Suggested fix:
Fix mysql.exe session closure.

(And close the CMD/K session as well before checking process list)

Same on Linux (Centos). Verifying as D2.

Possibly the bug is "partly" in the SELECT SLEEP code ("partly" since the CLI should still be able to handle the closure correctly)?

I doubt that this is a bug in the client or related to SLEEP() at all. The most likely culprit is the server not noticing dropped connections on Windows. You say you've repeated the problem on Linux -- is this with a client on Linux connecting to a Windows server?

Needs feedback on how bug was reproduced on non-Windows systems.

Andrew was originally testing on Linux and he was able to reproduce the same issue once, but cannot repeat it again. For the moment, look at this Windows-only maybe, keeping in mind the same.

Btw, that "one occurrence" was pure-Linux, no Windows involved

Confirmed with a linux box accessed through ssh : 
mysql> show full processlist;
--------------
show full processlist
--------------

+----+------+-----------+------+---------+------+------------+-----------------------+
| Id | User | Host      | db   | Command | Time | State      | Info                  |
+----+------+-----------+------+---------+------+------------+-----------------------+
|  1 | root | localhost | test | Query   |   78 | User sleep | select sleep(5000)    |
|  2 | root | localhost | test | Query   |   38 | User sleep | SELECT SLEEP (10000)  |
|  4 | root | localhost | test | Query   |    0 | NULL       | show full processlist |
+----+------+-----------+------+---------+------+------------+-----------------------+

Note that if I Ctrl-C the client's console it will correctly send a KILL command to the server :
mysql> SELECT SLEEP (10000);
--------------
SELECT SLEEP (10000)
--------------

^CCtrl-C -- sending "KILL QUERY 4" to server ...
Ctrl-C -- query aborted.
+---------------+
| SLEEP (10000) |
+---------------+
+---------------+
1 row in set (9.23 sec)

mysql>

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

3492 Georgi.Kodinov@oracle.com	2010-07-19
      Bug #52515: mysql sessions are not terminated properly
      
      The mysql command line client was not reacting at all on 
      closing its controlling terminal.
      Now it intercepts the SIGHUP on unix-alikes (and the 
      corresponding windows way of notification) and issues
      a KILL command before closing similarly to how Ctrl-C 
      is handled.
      Added a command line option to turn this new behaviour 
      off if needed.

Joro, you can have it more simple in case of windows. Instead of using signal(),
you can always use SetConsoleCtrlHandler() and handle analoges of SIGINT and SIGHUP by checking dwCtrlType in your HandlerRoutine() callback ( http://msdn.microsoft.com/en-us/library/ms683242(VS.85).aspx ), e.g in with a switch/case logic.

SIGINT corresponds to CTRL_C_EVENT, SIGHUP to CTRL_CLOSE_EVENT. You might want to expand the cases for CTRL_BREAK_EVENT, CTRL_LOGOFF_EVENT and CTRL_SHUTDOWN_EVENT.

The handling of all those events might be similar,and be something like handle_sigint in the current implementation. I actually don't see a compelling reason to ignore command window close (CTRL_CLOSE_EVENT), you think it can help somehow?

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

3435 Georgi Kodinov	2010-12-16
      Bug #52515: mysql sessions are not terminated properly
      
      The mysql command line client was not reacting at all on 
      closing its controlling terminal.
      Added windows specific code to handle the console events.
      Added a handler for the SIGHUP to gracefully shutdown
      the running statement.

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

3435 Georgi Kodinov	2011-01-05
      Bug #52515: mysql sessions are not terminated properly
      
      The mysql command line client was not reacting at all on 
      closing its controlling terminal.
      Added windows specific code to handle the console events.
      Added a handler for the SIGHUP to gracefully shutdown
      the running statement.
      Made sure the windows specific code handles correctly Ctrl-C.

Noted in 5.6.3 changelog.

The mysql client sometimes did not properly close sessions terminated
by the user with Control+C. 

CHANGESET - http://lists.mysql.com/commits/136800