MySQL Cannot find OUTFILE in /tmp due to systemd PrivateTmp setting

Written by James McDonald

October 5, 2012

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

1 Comment

  1. Joe

    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

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

The reCAPTCHA verification period has expired. Please reload the page.

You May Also Like…

Squarespace Image Export

To gain continued access to your Squarespace website images after cancelling your subscription you have several...

MySQL 8.x GRANT ALL STATEMENT

-- CREATE CREATE USER 'tgnrestoreuser'@'localhost' IDENTIFIED BY 'AppleSauceLoveBird2024'; GRANT ALL PRIVILEGES ON...

Exetel Opt-Out of CGNAT

If your port forwards and inbound and/or outbound site-to-site VPN's have failed when switching to Exetel due to their...