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 (OCA) 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
Triage: Triaged: D2 (Serious) / R3 (Medium) / E2 (Low)

[1 Apr 2010 2:51] Roel Van de Paar
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.
[1 Apr 2010 2:55] Roel Van de Paar
(And close the CMD/K session as well before checking process list)
[1 Apr 2010 3:06] Roel Van de Paar
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)?
[5 Apr 2010 23:30] Jim Winstead
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?
[19 May 2010 18:57] Jim Winstead
Needs feedback on how bug was reproduced on non-Windows systems.
[28 May 2010 4:51] Roel Van de Paar
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.
[28 May 2010 4:52] Roel Van de Paar
Btw, that "one occurrence" was pure-Linux, no Windows involved
[13 Jul 2010 12:08] Georgi Kodinov
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>
[19 Jul 2010 15:12] Bugs System
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.
[19 Jul 2010 16:39] Vladislav Vaintroub
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?
[16 Dec 2010 12:54] Bugs System
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.
[5 Jan 2011 11:24] Bugs System
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.
[9 May 2011 19:26] Paul Dubois
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