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.