Bug #31184 sort aborted
Submitted: 25 Sep 2007 13:25 Modified: 29 Oct 2007 17:43
Reporter: Matt Ryan Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:5.0.48 enterprise OS:Windows (server 2003)
Assigned to: CPU Architecture:Any
Tags: aborted, sort

[25 Sep 2007 13:25] Matt Ryan
Description:
070925  5:31:56 [ERROR] MySQL: Sort aborted
070925  6:18:01 [ERROR] MySQL: Out of memory (Needed 56309936 bytes)

we started seeing these errors after upgrading to 5.0.32, we were hoping 5.0.48 would fix them, it did not

one of the sql's that crashes, we cant get this sql to work and it's needed, these would run before, even with a low myisam sort memory setting, now they fail no matter what we set it to.  There are many many more sql's that are failing, this is just a sample.

the sql ONLY crashes if you run it though the odbc connector, ie use access and pull the data into access

myisam settings from my.ini
tmp_table_size=256M
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=256M
key_buffer_size=256M
read_buffer_size=256M
read_rnd_buffer_size=256M
sort_buffer_size=512M
memlock

mysql> show variables like 'myisam%sort%';
+---------------------------+--------------+
| Variable_name             | Value        |
+---------------------------+--------------+
| myisam_max_sort_file_size | 107374182400 |
| myisam_sort_buffer_size   | 268435456    |
+---------------------------+--------------+

SELECT A.DON, STA, ESD, SUBSTRING(A.DTE_TXN,2,4) DTE_TXN, SUM(B.QTY) QTY, ' ' FILLER FROM RTSS_LAST_STAT A LEFT JOIN RTSS_AC B ON A.DON = B.DON GROUP BY A.DON

mysql> describe rtss_ac;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| dic     | char(3)  | NO   | PRI |         |       |
| qty     | char(5)  | NO   | PRI |         |       |
| don     | char(14) | NO   | PRI |         |       |
| suf     | char(1)  | NO   | PRI |         |       |
| dte_txn | char(5)  | NO   |     |         |       |
+---------+----------+------+-----+---------+-------+
mysql> select count(*) from rtss_ac;
+----------+
| count(*) |
+----------+
| 163783   |
+----------+

mysql> describe rtss_last_stat;
+--------------+----------+------+-----+---------+-------+
| Field        | Type     | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| dic          | char(3)  | NO   |     |         |       |
| fr_ric       | char(3)  | NO   |     |         |       |
| niin         | char(11) | NO   |     |         |       |
| ui           | char(2)  | NO   |     |         |       |
| qty          | char(5)  | NO   |     |         |       |
| don          | char(14) | NO   | PRI |         |       |
| suf          | char(1)  | NO   |     |         |       |
| dte_txn      | char(5)  | NO   |     |         |       |
| ship_to      | char(3)  | NO   |     |         |       |
| sta          | char(2)  | NO   |     |         |       |
| lst_sos      | char(3)  | NO   |     |         |       |
| esd          | char(4)  | NO   |     |         |       |
| stor         | char(3)  | NO   |     |         |       |
| d_t          | char(4)  | NO   |     |         |       |
| ctasc        | char(10) | NO   |     |         |       |
| ARCHIVE      | char(1)  | YES  |     |         |       |
| ARCHIVE_DATE | date     | YES  |     |         |       |
+--------------+----------+------+-----+---------+-------+

mysql> select count(*) from rtss_last_stat;
+----------+
| count(*) |
+----------+
| 3487844  |
+----------+

mysql> explain
    -> SELECT A.DON, STA, ESD, SUBSTRING(A.DTE_TXN,2,4) DTE_TXN, SUM(B.QTY) QTY,
 ' ' FILLER
    -> FROM RTSS_LAST_STAT A LEFT JOIN RTSS_AC B
    -> ON A.DON = B.DON
    -> GROUP BY A.DON
    -> ;
+----+-------------+-------+------+---------------+------+---------+--------------+---------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra                           |
+----+-------------+-------+------+---------------+------+---------+--------------+---------+---------------------------------+
| 1  | SIMPLE      | A     | ALL  |               |      |         |  | 3487844 | Using temporary; Using filesort |
| 1  | SIMPLE      | B     | ref  | don           | don  | 14      | finlog.A.don | 1       |                                 |
+----+-------------+-------+------+---------------+------+---------+--------------+---------+---------------------------------+

How to repeat:

any large join with sort will error

Suggested fix:
ensure myisam sorts are done in temp tables, rather than running out of memory
[25 Sep 2007 16:26] Valeriy Kravchuk
Please, set the following my.ini values:

tmp_table_size=2M
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=2M
key_buffer_size=256M
read_buffer_size=2M
read_rnd_buffer_size=2M
sort_buffer_size=2M
# memlock

(remove memlock option!), and try again. Inform about the results.
[26 Sep 2007 12:05] Matt Ryan
Removed memlock, no change, still kills the sql and drops the connection

we can run the sql into a temp table, and select from the temp table without error
[29 Sep 2007 17:43] Valeriy Kravchuk
Have you set other values I had recommended?
[30 Oct 2007 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".