Solved

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

Posted on 2006-11-19
1
866 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
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

840 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