I am trying to dump a large table (40GB, 600,000 rows) using a SELECT ... INTO OUTFILE query. I want to use that because I want my results sorted in a particular way (so mysqldump is not good enough).

The problem is, I am getting an error code 28. I am pretty sure it is because it is running out of disk space on the default /tmp location. I have enough space on the disk elsewhere, if I could just specify at runtime what location to use for tmp.

I found out I can change the tmpdir by modifying my.cnf, but I do not have sufficient server privileges to modify it or restart mysqld.

What is the solution here?

EDIT: a few answers have suggested using mysql -e. I may have to go this route and post-process my file. The reason I wanted to use SELECT ... INTO OUTFILE is because I want to

fields   terminated by '\t'
         escaped by '\\'
lines    terminated by '\r\n' ;
  • 1
    "I have enough space on the disk elsewhere" do you mean you have enough space on another disk? – Jack Douglas Aug 28 '12 at 15:00
  • Or a different partition, I'm assuming. – Derek Downey Aug 28 '12 at 15:05
  • yes, on a different partition – pocketfullofcheese Aug 28 '12 at 15:10

Assuming you have the FILE privilege, you should be able to specify a location other than the /tmp location. For example:

SELECT .. INTO OUTFILE '/home/foo/myfile.csv'

The other option, if you have access to the command line, is to use the -e switch:

> mysql -e "SELECT * FROM foo ORDER BY bar" > '/path/to/file'
  • I am specifying another location, but mysql seems to do the sorting or otherwise using a tmp file before writing it to where I specify – pocketfullofcheese Aug 28 '12 at 15:12
  • the second option might get around that. I will give it a try! – pocketfullofcheese Aug 28 '12 at 15:12
  • wait, the second option does not allow me to format the output. I wanted to use terminated by, escaped by, and lines terminated by – pocketfullofcheese Aug 28 '12 at 15:22

The problem is that the servers pocket is full of cheese, but there's no way of specifying an alternative pocket to put the cheese in due to the cheese creator (the MySQL server process) not having permission to place cheese (query output) in any other pocket (directory location) due to privs.

If your host can't give you an alternative mount point to dump the file to (with appropriate privs for the MySQL server to write to), a workaround is to connect to the server from a MySQL prompt (making sure you're on the same subnet or the same server, as you don't want to transfer 40Gb of data over the internet) and use mysql -e:

mysql -h your.host.name.com -u youruser --password=yourpass -e 'select stuff from mytable order by stuff' > /wherever/you/want/mylocaloutput.txt

That'll create a local file for you in the location of your choosing.

  • 3
    that's deep. mind = blown. – swasheck Aug 28 '12 at 15:02
  • haha. upvoted for creativity. However, my problem is that SELECT ... INTO OUTFILE writes to /tmp regardless of where you specify the output. mysql -e does not let me format the output the way INTO OUTFILE does (specify delimiter, escape char, line ending, etc) – pocketfullofcheese Aug 28 '12 at 15:25
  • Yeah, it does. You can use string concatenation (CONCAT and CONCAT_WS()) in your SELECT clause to achieve the same thing. Not sure how to escape selected column values though – Philᵀᴹ Aug 28 '12 at 15:34
  • Please make sure there are no rats around (bad queries) during the process. Call the exterminator (DBA) beforehand. +1 !!! – RolandoMySQLDBA Aug 28 '12 at 20:39

Actually, you can use mysqldump to export the data using the same semantics as SELECT ... INTO OUTFILE

# mysqldump --help | grep terminated
                      Fields in the output file are terminated by the given
                      Fields in the output file are enclosed by the given
                      Fields in the output file are optionally enclosed by the
                      given character.
                      Fields in the output file are escaped by the given
                      Lines in the output file are terminated by the given

For example, to dump a table mydb.mytable to a CSV file:

mysqldump --fields-terminated-by="\\t" --fields-enclosed-by="'" --lines-terminated-by="\\r\\n" mydb mytable > mydb_mytable.csv
  • I don't think the formatting is what the OP's concern is (he wants it sorted a certain way). – Derek Downey Aug 28 '12 at 14:58
  • @DTest I was just mentioning mysqldump having the same capacity to do SELECT INTO OUTFILE. That was all. – RolandoMySQLDBA Aug 28 '12 at 15:09
  • I'm +1'ing this, because it might end up being the only way to eventually get the file sorted (by doing the sort after the dump, instead of having mysql handle it) – Derek Downey Aug 28 '12 at 15:15
  • Can you explain how the query below is related to the mysqldump part? I see only a SELECT ... INTO OUTFILE statement below and the --tab part of the mysqldump command above; where is the connection? – pocketfullofcheese Aug 28 '12 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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