MySQL select into outfile /tmp no output

Question

I cannot get the following code to generate any output. The MySQL user has "all" grant level, /tmp is writable, the query returns a results set.

mysql> SELECT field FROM test_table WHERE condition='test'
    -> INTO OUTFILE '/tmp/test.csv'
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';
Query OK, 1 row affected (0.00 sec)

mysql>
[1]+  Stopped                 mysql
[root@web1 ~]# cat /tmp/test.csv
cat: /tmp/test.csv: No such file or directory

Should I be seeing different output from MySQL in case of failure?

Can I verify the result further than "1 row affected"?

Menispermaceous

Why would a select statement affect anything? – Nashua

Presumably it refers to the outfile function rather than the select. – Menispermaceous

Answer 1

The files generate by the outfile clause are created on the mysql server host. Please make sure you are looking on the mysql server host as it seems you are on the client host which most likely isn't the mysql server host.

See http://dev.mysql.com/doc/refman/5.0/en/select.html in the section about outfile for documentation regarding this.

Agreeable

Thanks Dipin, you are correct that this is not the DB server. Many thanks – Menispermaceous

Dipin you just saved me beating my head all night. Thanks! – Inexpugnable

Answer 2

I came across this problem in Fedora 17 and it was caused by systemd. I think it's good to share.

mysql> select * into outfile '/tmp/z1' from t1;
Query OK, 673 rows affected (0.01 sec)
mysql> select * into outfile '/tmp/z2' from t1;
Query OK, 673 rows affected (0.01 sec)
mysql> select * into outfile '/tmp/z1' from t1;
ERROR 1086 (HY000): File '/tmp/z1' already exists
mysql> Bye

# cat /tmp/z1
cat: /tmp/z1: No such file or directory
# ls -d systemd-*
/tmp/systemd-private-AQEueG
/tmp/systemd-private-AuCNDY
/tmp/systemd-private-TOMNxZ
/tmp/systemd-private-UacrpE
/tmp/systemd-private-yal7lQ
/tmp/systemd-private-ZlRJeN
# ls /tmp/systemd-private-TOMNxZ
z1  z2

The culprit was in /usr/lib/systemd/system/mysqld.service.

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

Thanks to this blog, I found the clue.

Curmudgeon

Answer 3

Sounds like you might be running into a file permissions problem. Be sure that the user:group that mysqld is running under has adaqute permission to write to /tmp/test.csv

There's a whole variety of server daemon/file permission flavours that would solve the problem. Presumably UNIX-based, you could: chgrp mysqldGROUP /tmp

But that makes it seem so easy- your server is configured in a certain way, so you adapt to that. The mysqld process should really only be able to read/write from a handful of places.

Rustproof