MySQL Bugs: #18256: Kill thread -> error: "Server shutdown in progress"

Bug #18256	Kill thread -> error: "Server shutdown in progress"
Submitted:	15 Mar 2006 16:13	Modified:	10 Oct 2013 16:56
Reporter:	Olaf van der Spek (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S4 (Feature request)
Version:	5.0.21-BK, 5.0.18, 5.1.30	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Hi,

When you kill a thread, the server returns the error "Server shutdown in progress" to that thread. That error is not correct.

How to repeat:
kill 1

Thank you for a problem report. Please, send the uname -a results and try to repeat it on 5.0.19 now available, just to be sure. 

What error message do you expect to get? Do you kill thread from itself or from the other thread? I am asking because I've got another behaviour (simply reconnect upon next statement) when I tried to kill session from the other one in 5.0.19.

Linux xwis.net 2.6.15-1-686-smp #2 SMP Thu Feb 2 18:39:24 UTC 2006 i686 GNU/Linux

I can't test with 5.0.19 as it's not in Debian yet. I'll test as soon as it is.

I killed the thread from another thread. A message like 'connection/query/thread killed' would be nice.

Sorry, but I am not able to repeat the problem you described with 5.0.20-BK. Look:

openxs@suse:~/dbs/5.0> 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 5 to server version: 5.0.20

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

Now, from the other session:

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 3  | root | localhost | test | Query   | 0    |       | show processlist |
| 5  | root | localhost | test | Sleep   | 4    |       |                  |
+----+------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

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

And trying to do something in the original one:

mysql> select version();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: test

+-----------+
| version() |
+-----------+
| 5.0.20    |
+-----------+
1 row in set (0.07 sec)

$ mysql
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 21524 to server version: 5.0.19-Debian_2-log

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

mysql> select gid, pid, cid, cty from xcl_games_players;
ERROR 1053 (08S01): Server shutdown in progress
mysql> 

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

mysql>

So, you have one session, running a "long" select, then, from the other session, you kill that first one, while select is running, and it receives that message "Server shutdown in progress". This message should be changed. Have I got your idea right this time?

Yes. :)

In session 1:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.21

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

mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 1  | root | localhost | test | Sleep   | 73   |       |                  |
| 2  | root | localhost | test | Query   | 0    |       | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

In session 2:

mysql> select * from mysql.user t1, mysql.user t2, mysql.user t3, mysql.user t4, mysql.user t5, mysql.user t6;

In session 1, while the above is running:

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

In session 2 you'll get:

ERROR 1053 (08S01): Server shutdown in progress
mysql> select version();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: test

+-----------+
| version() |
+-----------+
| 5.0.21    |
+-----------+
1 row in set (0.13 sec)

But was server really restarted? No! (checked from Session  and in the error log). So, this error message is slightly misleading. The idea to change it looks like a reasonable feature request for me.

Ah. So that's what's going wrong.  I've been getting bogus "server shutdown in progress" messages while trying to do database loads on a shared MySQL server at HostGator.  HostGator has a thread of their own which does a KILL QUERY or KILL CONNECTION on MySQL transactions they think are taking too long.  So I'm getting this error occasionally. 

An honest error message would be appropriate.

Bug#20856 Aborting a statement should not cause error 1053 (Server shutdown)
was marked a duplicate of this bug.

Same issue.

# uname -a
Linux HOSTNAMESNIPPED 2.6.18-92.el5 #1 SMP Tue Apr 29 13:16:15 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux

# mysql -V
mysql  Ver 14.14 Distrib 5.1.26-rc, for redhat-linux-gnu (x86_64) using readline 5.1

# rpm -q MySQL-server-community
MySQL-server-community-5.1.26-0.rhel5

# cat /etc/issue
Red Hat Enterprise Linux Server release 5.2 (Tikanga)
Kernel \r on an \m

# mysql -e 'select * from tablename;' databasename > /dev/null &
# mysqladmin kill 1161158
ERROR 1053 (08S01) at line 1: Server shutdown in progress

Any updates? It's almost five years since the original report.

http://bugs.mysql.com/bug.php?id=69975 is a duplicate

This still happens in recent 5.5 and 5.6 versions. It's really confusing as the same error is logged when indeed MySQL server is being stopped during long query execution as well as just the executing thread is killed.
This is what I get in error log if I kill a long query being in "Sorting result" state:

Version: '5.6.12-log'  socket: '/tmp/mysql_sandbox15612.sock'  port: 15612  MySQL Community Server (GPL)
2013-08-21 15:30:20 28458 [ERROR] /home/sandbox/tarballs/oracle5.6.12/bin/mysqld: Sort aborted: Server shutdown in progress

Still happening with 5.6.13.
mysql> SHOW VARIABLES LIKE '%version%'; \! uname -a
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.13                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.13-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux-glibc2.5               |
+-------------------------+------------------------------+
7 rows in set (0.01 sec)

Linux a9-db-04 2.6.32-279.19.1.el6.x86_64 #1 SMP Wed Dec 19 07:05:20 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux

Noted in 5.5.35, 5.6.15, 5.7.3 changelogs.

Killing a query that is performing a filesort operation resulted in
an ER_SERVER_SHUTDOWN (Server shutdown in progess) error.

5.5$ bzr log -r 4496
------------------------------------------------------------
revno: 4496
committer: Praveenkumar Hulakund <praveenkumar.hulakund@oracle.com>
branch nick: mysql_5_5
timestamp: Sat 2013-10-05 15:29:02 +0530
message:
  Bug#11745656 - KILL THREAD -> ERROR: "SERVER SHUTDOWN IN PROGRESS"
  
  Description:
  ------------
  There are 2 issues reported in the bug report,
  
  1. One session running a "long" select, then, from the other
  session, you kill that first one, while select is
  running, and it receives that message "Server shutdown in
  progress".
  Reported Date: 02-Apr-2006
  
  => Looks like this isuse is already fixed in 2009 by the patch
     pushed for bug28141. 
  
  2. Killing query which goes to filesort, logs error entries like:
  
  120416  9:17:28 [ERROR] mysqld: Sort aborted: Server shutdown in
                                                progress 
  120416  9:18:48 [ERROR] mysqld: Sort aborted: Server shutdown in
                                                progress 
  120416  9:19:39 [ERROR] mysqld: Sort aborted: Server shutdown in
                                                progress 
  Reported Date: 16-Apr-2012                                              
  
  => This issue is introduced in 5.5+ versions. Fixing this issue
     in this patch.
  
  
  Analysis:
  ---------
  In function "filesort()", on error we are logging error message.
  To the error message, the message related THD::killed_errno is
  also appeneded, if it is set.(THD::kill_errno value is obtained
  by calling member function THD::killed_errno)
  
  In the scenario mentioned in this bug report, when we kill the
  connection, THD::kill_errno is set to the THD::KILL_CONNECTION.
  Enum type THD::KILL_CONNECTION corressponds to value 
  ER_SERVER_SHUTDOWN. Because of this, "Server shutdown in ...." is
  appended to the message logged.
  
  Fix:
  ----
  Modified code of "filesort()" function to append "KILL_QUERY"
  status to error message when thread is killed and server
  shutdown is not in progress.