Solved

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

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now