Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I'm currently using MySQLWorkbench 5.2.40 on my PC (8GB rRM, Corei7). I have a table with almost 1.5 million records, which I need to visually look over. Surprisingly, the total size does not exceed 200 MB. The problem, when I try to select all the records to view them (which is necessary for me), I get an error: Error Code 2008: MySQL Client ran out of memory. I could not solve this issue.

Questions

  • Are there any recommendation/options other than MySQLWorkbench to view my tables in a way that guarantees there will be no memory issues ?
  • Will there be any harm to install two DB systems in the same machine ?
share|improve this question
Are your tables well normalized? – Sable Foste Aug 10 '12 at 16:07
1  
Do you actually need to look at 1.5 million records at once? You could use LIMIT clause to just look at portions of the data. You could use a commercial tool like Navicat that handles table paging for you automatically. – Mike Brant Aug 10 '12 at 16:07
Yes. I need to look at them. This is important for me. – Jury A Aug 10 '12 at 16:08
@Sable Foste: I need to look at this. Not a DB specialist. But needed to deal with DB for a program. – Jury A Aug 10 '12 at 16:13
Normalization is very important. There are all sorts of great references, but I can personally recommend one from my "beginner" PHP/MySQL book: amazon.com/gp/product/032152599X/ref=wms_ohs_product – Sable Foste Aug 10 '12 at 16:20
show 3 more comments

migrated from stackoverflow.com Aug 13 '12 at 13:50

3 Answers

up vote 0 down vote

There are two ways that you can fix this. One of them is more of a workaround then a permanent fix:

If you issue a query using the mysql client program and receive an error like the following one, it means that mysql does not have enough memory to store the entire query result:

mysql: Out of memory at line 42, 'malloc.c' mysql: needed 8136 byte (8k), memory in use: 12481367 bytes (12189k) ERROR 2008: MySQL client ran out of memory To remedy the problem, first check whether your query is correct. Is it reasonable that it should return so many rows? If not, correct the query and try again. Otherwise, you can invoke mysql with the --quick option. This causes it to use the mysql_use_result() C API function to retrieve the result set, which places less of a load on the client (but more on the server).

And the second one is editing your my.cnf file and paying close attention to the query memory proprieties and tweaking them such that they match your memory requirements. Here you can find all of the proprieties which you can edit in the my.cnf file.

share|improve this answer
I am aware of this & have tried to extend some of the memory related variables in the my.cnf file. Nothing works. The question is what are the exact variables I need to change & the suitable amount of memory I should put in them in my case. I mentioned the device specs & the DB size roughly. Plz help. – Jury A Aug 10 '12 at 19:14
up vote 1 down vote

If you really need to look at all the records, you should take the strain off of MySQLWorkbench. It already has enough on its plate micromanaging MySQL.

SUGGESTION #1 : Use MySQLWorkbench from Another Workstation

Install MySQLWorkbench on another server that is not running MySQL. Remotely connect to MySQL from the other Workstation. By divorcing MySQLWorkbench from MySQL, MySQLWorkbench can focus on what does best, micromanage the data and display at the user's whim. MySQL can focus on doing key caching, query caching, InnoDB, DB Connections, read/writing threading, and all other memory-consuming tasks. This will end the tug-of-war for RAM by both products. Even the OS will reap more of the spoils of war for RAM.

SUGGESTION #2 : Create a Text File and View it in some Editor

Install mysql on Another Workstation. Do not run the server on it, but use the mysql client program. To get data from the table mydb.mytable, from the second server, run this:

C:\> set DESTFILE=C:\subfolderofyourchoice\Display.txt
C:\> mysql -hIPOfMySQLServer -uroot -p --table -Ae"SELECT * FROM mydb.mytable" > %DESTFILE%

Once the file is created , use a text editor to display the file. I suggest the following:

  • Notepad
  • vi (yes I said, vi for Windows, I use it today)
  • Microsoft Word

Give it a Try !!!

share|improve this answer
Word is not a text editor :) – ypercube Sep 12 '12 at 15:35
@ypercube I know, I was just thinking of something that can tolerate a very long file without keeling over in RAM. In rare instances, Microsoft Word was useful to me, but I will not go down that road again. – RolandoMySQLDBA Sep 12 '12 at 15:53
I'm sure you or another educated user could use it a simple text editor. The default behaviours and settings though would be more than a burden for a novice. Notepad++ is so much better for simple text/code editing, – ypercube Sep 12 '12 at 15:55
@ypercube Agreed, simple is also better !!! – RolandoMySQLDBA Sep 12 '12 at 15:58
up vote 0 down vote

One of the issue that cause this is to try to get data like you did in same time you are running application that are using the same database.

  1. Close Applications you have launched that are using this database.
  2. Reload what you wanted to perform with workbench
  3. Your server must be stopped in order to avoid this error. (Eg.tomcat7)
share|improve this answer
@user1577291: I did closed all the applications. No hope. I reloaded and stopped the server. Same thing. BTW, I'm using win 7. My client is the server. I figured out that workbench have only 86 bit version for windows. Which means the maximum RAM can the application have is 3 GB. If my query requires more, this will not be possible due to the workbench limitations. The question is: How can I know how much RAM my query requires ? – Jury A Aug 13 '12 at 19:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.