Stack Exchange Network

Stack Exchange network consists of 183 Q&A communities including Stack Overflow , the largest, most trusted online community for developers to learn, share their knowledge, and build their careers. Visit Stack Exchange

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 only takes a minute to sign up.

Sign up to join this community

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

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' ;
                "I have enough space on the disk elsewhere" do you mean you have enough space on another disk?
– Jack Douglas
                Aug 28, 2012 at 15:00
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, 2012 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, 2012 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. 
                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, 2012 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, 2012 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, 2012 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-terminated-by=name
                      Fields in the output file are terminated by the given
                      string.
  --fields-enclosed-by=name
                      Fields in the output file are enclosed by the given
                      character.
  --fields-optionally-enclosed-by=name
                      Fields in the output file are optionally enclosed by the
                      given character.
  --fields-escaped-by=name
                      Fields in the output file are escaped by the given
                      character.
  --lines-terminated-by=name
                      Lines in the output file are terminated by the given
                      string.
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, 2012 at 14:58
                @DTest I was just mentioning mysqldump having the same capacity to do SELECT INTO OUTFILE. That was all.
– RolandoMySQLDBA
                Aug 28, 2012 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, 2012 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, 2012 at 15:24
        
Thanks for contributing an answer to Database Administrators Stack Exchange!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.