Solved

"Error Code : 1037 - Out of memory; restart server and try again" when using order by clause

Posted on 2006-11-19
1
869 Views
Last Modified: 2007-12-19
I've just re-installed mysql and now certain queries cause my app to fail.

Specifically, the query "select distinct * from mytable order by name" causes the following error:

Error Code : 1037 Out of memory; restart server and try again (needed 65528 bytes)

If I remove the "order by" clause then it runs just fine so I'm guessing that the memory issue is a red herring? If not, then how do you determine what an acceptable memory value should be?
0
Comment
Question by:groovyjon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 35

Accepted Solution

by:
Raynard7 earned 125 total points
ID: 17975925
Hi,

each thread / connection has a memory limit assigned to it - this is the maximum amount of room for all temp tables and memory tables at any point in time.

If you have this too small then if you need to do a sort - it creates a temporary table and then sorts by it - then there is not enough room to create it in memory.

The size of memory depends on what system you are running, what queries that you are using and the number of simultaneous users.

If you have say 512 MB free normally and you only have 20 users or so then 6MB would do your job, if you had less and many many more users then this would start paging memory on your machine.

I would probably say that 1 MB should be sufficient for most normal purposes.

Additionally it is important to note the memory is not allocated until it is being used by the user - so even if you have three connections at 1MB memory they probably will not take 3MB but probably 1 until the others take more when runnign queries.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question