Sort aborted Error in MySQL Error Log

mysql> show VARIABLES like'log_err%';
+---------------+-----------------------------------------------+
| Variable_name | Value                                         |
+---------------+-----------------------------------------------+
| log_error     | /home/dbmysql/mysql/data/CNMYSQLDB.err |
+---------------+-----------------------------------------------+
1 row in set (0.19 sec)

 

检查mysql的日志时发现日志中有下述错误：

120115 10:46:09 [ERROR] /home/dbmysql/mysql/bin/mysqld: Sort aborted

 

比较常见的有tmpdir空间不足，sort_buffer_size ，read_rnd_buffer_size、read_buffer_size 设置不合理，
含有order by,groupy by 等需要排序sql性能比较低下需要优化，等其它原因。

以下是官方文档中对Sort aborted的解释：
Cause
There are many possible causes of Sort aborted errors, see details below.

Solution
From MySQL 5.5.11 the error messages have been made more specific.
The 5.5.11 errors have 5.5.11 at the start of the line in this description.
In addition in 5.5.11 and later if you turn on --log_warnings = 2
the error messages will give details of the host the client connection is coming from,
the user account and the query to assist in diagnosing the cause.

Insufficient disk space in tmpdir prevented tmpfile from being created
Make more space available.

Insufficient memory for sort_buffer_size to be allocated
Add more memory or adjust settings to use less memory.

5.5.11 error: [ERROR] bin-dbg/sql/mysqld: Out of sort memory; consider increasing server sort buffer size

Somebody ran KILL in the middle of a filesortNormal, no action required.

5.5.11 error, verbose version: [ERROR] Sort aborted. By KILL command: YES SQL: update t1 set j = 6 order by i desc, j asc

The server was shut down while some queries were sorting
Normal, no action required.

A transaction got rolled back or aborted due to lock wait timeout or deadlock
Normal, no action required. The lock wait timeout or deadlock might be something you want to investigate if that is not expected, though it is normal for these to happen sometimes in transactional databases.

Unexpected errors, such as source table or even temporary table was corrupt
Look for possible causes.

Processing of a subquery failed which was also sorting
Possibly look for a cause for the subquery to fail or consider it normal, it could be either.

Wrong number of arguments for a function
Fix the function call.

5.5.11 error: [Warning] Sort aborted : Incorrect number of arguments for FUNCTION test.f1; expected 0, got 1