Bug #31590 incorrect out of memory error and sort aborted messages in logs
Submitted: 14 Oct 2007 20:49 Modified: 30 Mar 2008 20:29
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.1.24BK, 5.0.56BK OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: max_sort_length, sort aborted, sort_buffer_size

[14 Oct 2007 20:49] Shane Bester 
Description:
In the logs I noticed these messages:

071014 22:42:58 [ERROR] e:\builds\mysql-5.0.50\sql\debug\mysqld-debug.exe: Out of memory; restart server and try again (needed 32804 bytes)
071014 22:42:58 [ERROR] e:\builds\mysql-5.0.50\sql\debug\mysqld-debug.exe: Sort aborted

Problem is, the server isn't out of memory, and isn't close to it either.
Tracked it down to various combinations of sort_buffer_size and max_sort_length causing this error to be output.

mysql> drop table if exists `t`;
Query OK, 0 rows affected (0.00 sec)

mysql> create table `t`(`a` int,`b` tinytext)engine=myisam;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into `t`(`a`,`b`) values (1,2),(3,2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from `t` order by `b`;
+------+------+
| a    | b    |
+------+------+
|    1 | 2    |
|    3 | 2    |
+------+------+
2 rows in set (0.00 sec)

mysql> set session sort_buffer_size=30000;
Query OK, 0 rows affected (0.00 sec)

mysql> set session max_sort_length = 2180;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from `t` order by `b`;
ERROR 1037 (HY001): Out of memory; restart server and try again (needed 32804 bytes)
mysql>

How to repeat:
drop table if exists `t`;
create table `t`(`a` int,`b` tinytext)engine=myisam;
insert into `t`(`a`,`b`) values (1,2),(3,2);
select * from `t` order by `b`;
set session sort_buffer_size=30000;
set session max_sort_length = 2180;
select * from `t` order by `b`;

Suggested fix:
improve the filesort function?
[2 Dec 2007 20:38] MySQL Verification Team 
sinisa requested app to try many combinations of sort_buffer_size and max_sort_length

Attachment: bug31590.php (application/octet-stream, text), 1.45 KiB.
[29 Jan 2008 12:02] MySQL Verification Team 
Affects 5.1.24BK, 5.0.56BK also
[30 Jan 2008 20:04] Omer Barnir 
Workaround: Setting different values for buffer sizes can provide a solution for the above
[31 Jan 2008 7:56] MySQL Verification Team 
the workaround proposed will not work in all cases. I can create a workload to trigger the bug for any setting specified.
[8 Feb 2008 10:37] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41920

ChangeSet@1.2596, 2008-02-08 13:35:00+03:00, evgen@sunlight.local +3 -0
  Bug#31590: Wrong error message on sort buffer being too small.
  
  The out of memory error was thrown when the sort buffer size were too small.
  This led to a user confusion.
  
  Now filesort throws the error message about sort buffer being too small.
[14 Feb 2008 13:33] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42279

ChangeSet@1.2666, 2008-02-14 16:33:11+03:00, kaa@kaamos.(none) +1 -0
  Post-merge fix after merging the patch for bug#31590 to 5.1:
  suppress the "Out of sort memory" error in the error log.
[13 Mar 2008 19:27] Bugs System 
Pushed into 6.0.5-alpha
[13 Mar 2008 19:34] Bugs System 
Pushed into 5.1.24-rc
[13 Mar 2008 19:42] Bugs System 
Pushed into 5.0.60
[30 Mar 2008 20:29] Jon Stephens 
Documented bugfix in the 5.0.60, 5.1.23-ndb-6.3.11, 5.1.24, and 6.0.5 changelogs as follows:

        The server returned the error -Out of memory; restart server and try 
        again- when the actual problem was that the sort buffer was too small. 
        Now an appropriate error message is returned in such cases.
[24 Apr 2008 11:21] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45944

ChangeSet@1.2591, 2008-04-24 13:26:28+02:00, msvensson@pilot.mysql.com +1 -0
  Add warning supressions for bug#31590 and bug#26402 that gort lost when
  moving the supressions from perl to SQL
[30 Jan 2009 13:27] Bugs System 
Pushed into 6.0.10-alpha (revid:luis.soares@sun.com-20090129165607-wiskabxm948yx463) (version source revid:luis.soares@sun.com-20090129163120-e2ntks4wgpqde6zt) (merge vers: 6.0.10-alpha) (pib:6)
[30 Jan 2009 15:07] Bugs System 
Pushed into 5.1.32 (revid:luis.soares@sun.com-20090129165946-d6jnnfqfokuzr09y) (version source revid:sp1r-msvensson@pilot.mysql.com-20080424112628-62493) (merge vers: 5.1.25-rc) (pib:6)
[17 Feb 2009 14:53] Bugs System 
Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)
[17 Feb 2009 16:41] Bugs System 
Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)
[17 Feb 2009 18:17] Bugs System 
Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j) (version source revid:tomas.ulin@sun.com-20090201210519-vehobc4sy3g9s38e) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)