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