October 22, 2014

[ERROR] mysqld: Sort aborted: Server shutdown in progress

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.

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.

In the error log I found…

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.

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.

About Muhammad Irfan

Muhammad Irfan is vastly experienced in LAMP Stack. Prior to joining Percona Support, he worked in the role of MySQL DBA & LAMP Administrator, maintained high traffic websites, and worked as a Consultant. His professional interests focus on MySQL scalability and on performance optimization.

Comments

  1. I think I saw this error when the diskspace/partition allocated for TEMPDIR was full.

  2. I saw this error recently while doing count(*) on a large table with almost no free RAM available.

  3. Neeraj Khandelwal says:

    I saw this error while killing long running queries.

Speak Your Mind

*