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