Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
[ERROR] mysqld: Sort aborted: Server shutdown in progress
+2 Vote Up -0 Vote Down

Recently, one of our support customers faced this: “[ERROR] mysqld: Sort aborted: Server shutdown in progress.” At first it would appear this occurred because of a mysql restart (i.e. the MySQL server restarted and the query got killed during the stopping of mysql). However, while debugging this problem I found no evidence of a MySQL server restart – which proves that what’s “apparent” is not always the case, so this error message was a bit misleading. Check this bug report for further details http://bugs.mysql.com/bug.php?id=18256 (it was reported back in 2006).

I found that there are two possible reasons for this error: Either the MySQL server restarts during execution of the query, or the query got killed forcefully during execution which utilizes the “Using filesort” algorithm.

So, let’s try to reproduce the scenario for this particular error. I opened two mysql sessions, mysql1 and mysql2.

mysql1> EXPLAIN SELECT * FROM dummy WHERE user_id <> 245424 GROUP BY title ORDER BY group_id;
+----+-------------+-------+-------+---------------+---------+---------+------+--------+----------------------------------------------+
| user_id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows   | Extra                                        |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | dummy | range | PRIMARY       | PRIMARY | 4       | NULL | 580096 | Using where; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+----------------------------------------------+

Then, I executed a query in session1 (i.e. mysql1) and while the query is running I killed it by logging into other session, mysql2.

mysql2> show full processlist\G
*************************** 1. row ***************************
Id: 2
User: root
Host: localhost
db: test
Command: Query
Time: 1
State: Sorting result
Info: SELECT * FROM dummy WHERE id <> 245424 GROUP BY title ORDER BY group_id
Rows_sent: 0
Rows_examined: 0
mysql2> kill 2;
Query OK, 0 rows affected (0.00 sec)
mysql2> show full processlist\G
*************************** 1. row ***************************
User_id: 2
User: root
Host: localhost
db: test
Command: Killed
Time: 8
State: Creating sort index
Info: SELECT * FROM dummy WHERE user_id <> 245424 GROUP BY title ORDER BY group_id
Rows_sent: 0
Rows_examined: 0
mysql1> SELECT * FROM dummy WHERE user_id <> 245424 GROUP BY title ORDER BY group_id;
ERROR 2013 (HY000): Lost connection to MySQL server during query

In the error log I found…

2013-12-16 00:05:42 3746 [ERROR] /usr/local/mysql/bin/mysqld: Sort aborted: Server shutdown in progress.

This looks confusing because of the error message, “Server shutdown in progress”. Then I restarted the MySQL server during execution of this query and the same error logged in error log which seems correct as mysql is shutdown during query execution.

2013-12-16 00:05:46 3746 [ERROR] /usr/local/mysql/bin/mysqld: Sort aborted: Server shutdown in progress

So, basically when the MySQL server is shutting down it kills all connected threads so “server shutdown in progress” error message is correct in this context. That means this error is not only recorded when the MySQL server restarts during query execution (which uses ORDER BY on non-index column’s) but also when that particular query is explicitly killed during it’s execution.

Conclusion:
The MySQL error “Server shutdown in progress” is confusing. It pops up when you kill a query explicitly and it then appears like the MySQL server is restarted – which is not the case in that scenario. This bug is quite old and was finally fixed in MySQL 5-5-35 and 5-6-15.

The post [ERROR] mysqld: Sort aborted: Server shutdown in progress appeared first on MySQL Performance Blog.

Votes:

You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.