CODEWITHSUNDEEP
mysqlcsvcentosmariadbsystemd
Koala Yeung
Koala Yeung

Reputation: 7853

MySQL select into outfile "file already exists" and "file does not exist" duality (?) case

I ran this command successfully on my CentOS machine's MariaDB:

MariaDB> select * from foobar into outfile '/tmp/schrodinger_cat.csv'
fields terminated by ',' enclosed by '"' lines terminated by '\n';

Query OK, 900 rows affected (0.06 sec)

Cat is alive? No.

You'd expect to find '/tmp/schrodinger_cat.csv'. Me too. I ls it:

# ls /tmp/schrodinger_cat.csv

ls: cannot access /tmp/schrodinger_cat.csv: No such file or directory

Cat is dead? No.

What? So I went back to the MariaDB command line client and run the same SQL again:

MariaDB> select * from foobar into outfile '/tmp/schrodinger_cat.csv'
fields terminated by ',' enclosed by '"' lines terminated by '\n';

ERROR 1086 (HY000): File '/tmp/schrodinger_cat.csv' already exists

Cat is alive exclusively for MariaDB? No.

I thought maybe the MariaDB is accessing another file system? So I tried this:

MariaDB> \! ls /tmp/schrodinger_cat.csv;

ls: cannot access /tmp/schrodinger_cat.csv: No such file or directory

So, no.

Any idea what happened? How do I get the file '/tmp/schrodinger_cat.csv'?

Upvotes: 3

Views: 3924

Answers (1)

Koala Yeung
Koala Yeung

Reputation: 7853

Turns out it is exclusive for MariaDB.

# ls /tmp/systemd-private-*-mariadb.service-*/tmp

/tmp/systemd-private-xxxxxxxxxxxxxxxxxxxxx-mariadb.service-xxx/tmp:
schrodinger_cat.csv

The reason is that the CentOS systemd service file set PrivateTmp to true:

[Unit]
Description=MariaDB database server
After=syslog.target
After=network.target

[Service]
Type=simple
User=mysql
Group=mysql

ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n
# Note: we set --basedir to prevent probes that might trigger SELinux alarms,
# per bug #547485
ExecStart=/usr/bin/mysqld_safe --basedir=/usr
ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID

# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300

# Place temp files in a secure directory, not /tmp
PrivateTmp=true

[Install]
WantedBy=multi-user.target

You may find more information from this RedHat blog post. If PrivateTmp=true the process will have a, well, private /tmp folder.

Turn out the cat is alive and well in a private castle :-)

Upvotes: 3

Related Questions