I have a table in MySQL with ~2,000,000 rows.

When I try to select all records, it exhausts the system's memory and I get an error about memory exhaustion back.

When I try to use the MySQL LIMIT and select 1,000 records, the error in the title is returned:

General error: 126 Incorrect key file for table '/tmp/#sql_27e6_0.MYI'; try to repair it'

What's the best course of action to export all of the data in this table? Why is it returning an error when only trying to pick 1,000 records?

My query:

select * from notes order by id asc limit 1000

  • 1
    How much free disk space do you have in /tmp ? – Philᵀᴹ Mar 24 '15 at 20:26
  • I monitored it while running the query and it's all eaten up. Why would it be eaten up trying to select 1,000 and what are my alternatives? – Noah Matisoff Mar 24 '15 at 20:28
  • 1
    You're doing an ORDER BY. It has to sort the entire table of 2 million records before giving you the first 1000. That's why – Philᵀᴹ Mar 24 '15 at 20:32
  • I need it, is there any way for me to keep it or not? – Noah Matisoff Mar 24 '15 at 20:35

You can remove any /tmp/#sql* files -- they are tmp tables left over after a crash. Or they are part of something (ALTER, CREATE, etc) that is going on, but stuck waiting for disk space.

It is often a mistake to have a separate filesystem for tmp -- you run into problems like this: Plenty of space to finish a task, but not in /tmp. Consider changing mysql's tmpdir setting.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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