MySQL select into outfile /tmp no output

mysql sql into-outfile
Andy picture Andy · Jun 24, 2009 · Viewed 25.3k times · Source

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"?

Answer

Dipin picture Dipin · Jun 24, 2009

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.

Related questions

MySQL export into outfile : CSV escaping chars

I've a database table of timesheets with some common feilds. id, client_id, project_id, task_id, description, time, date There are more but thats the gist of it. I have an export running on that table to a CSV …

Read More
How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE?

I am trying to dump the contents of a table to a csv file using a MySQL SELECT INTO OUTFILE statement. If I do: SELECT column1, column2 INTO OUTFILE 'outfile.csv' FIELDS TERMINATED BY ',' FROM table_name; outfile.…

Read More
MySQL - SELECT * INTO OUTFILE LOCAL ?

MySQL is awesome! I am currently involved in a major server migration and previously, our small database used to be hosted on the same server as the client. So we used to do this : SELECT * INTO OUTFILE .... LOAD DATA INFILE .... …

Read More