Update: Apparently systemd controlled services perform some sort of /tmp redirection. See below
Weird problem
Can't find mysql OUTFILE when writing it to /tmp
I was using this mysql client command:
SELECT A, B INTO OUTFILE '/tmp/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM maps_table;
To export some table data to CSV. However everytime I went to look at /tmp/result.txt I couldn't find the file in /tmp. Rerunning the SELECT INTO OUTFILE Statement told me file exists.
Sending OUTFILE to another directory works fine
So as a work-a-round I did:
# make a temporary mkdir /test # allow it to be world writeable chmod 777 /test -Rv
And rerun the mysql client command to the new location:
SELECT A, B INTO OUTFILE '/test/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM maps_table;
And this time it worked.
I have currently no idea why the mysql client said it had successfully written to /tmp and then I couldn't find the file.
Fedora 17 with latest patches as at 2012-10-04
Systemd has a special secure tmp setting
cat /usr/lib/systemd/system/mysqld.service | grep -i tmp # Place temp files in a secure directory, not /tmp PrivateTmp=true
Location of '/tmp' for mysql on a systemd controlled service.
If I go into /tmp and do a ls -al I get a listing of systemd directories
drwx------ 4 root root 4096 Oct 4 15:43 systemd-namespace-VtrG43 drwx------ 4 root root 4096 Sep 30 03:10 systemd-namespace-y5upmq drwx------ 4 root root 4096 Sep 28 17:52 systemd-namespace-yG3VVa drwx------ 4 root root 4096 Sep 28 17:53 systemd-namespace-ytszIa drwx------ 4 root mysql 4096 Oct 4 15:43 systemd-namespace-ZCRckq drwx------ 4 root colord 4096 Oct 2 18:55 systemd-namespace-ziE3az
Notice the directory with the group ownership of mysql. In my case it is "systemd-namespace-ZCRckq"
If you change to that directory:
cd /tmp/systemd-namespace-ZCRckq/private
ls -al total 24 drwxrwxrwt 2 root mysql 4096 Oct 5 07:47 . drwx------ 4 root mysql 4096 Oct 4 15:43 .. -rw-rw-rw- 1 mysql mysql 471 Oct 4 21:50 result.txt
You find your missing "/tmp/result.txt"
Systemd indeed has a secure tmp option as shown here:
cat /usr/lib/systemd/system/mysqld.service | grep -i tmp # Place temp files in a secure directory, not /tmp PrivateTmp=true
THANK YOU, this saved me.. been searching for my files everywhere coudlnt figure out wth happened to them when doing this INTO OUTFILE call to /tmp. super annoying