SELECT ... INTO OUTFILE报错ERROR 1 (HY000) at line 1: Can't create/write to file...

MySQL SELECT statement allowed us to pipe the query result in to a file via SELECT ... INTO OUTFILE. It work just fine for me most of the time, until recently I hit this error:

ERROR 1 (HY000) at line 1: Can't create/write to file '\home\myuser\my_output_file.txt' (Errcode: 2) mv: cannot stat `/home/myuser/my_output_file.txt': No such file or directory

The first thing come to my mind is the problem of file permission, so I grant 777 permission to my output directory. But the problem still exist.

Then I check the Mysql username I use, and found that it was granted with FILE permission correctly.

Then I start to google on this error code, and most of the search result point to similars causes.

So I read again the documentation of MySQL, and I found this:

The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax.

And the keyword is "server host". I made wrong assumption that it will write file to the server that execute the SELECT ... INTO OUTFILE.

And the workaround is simple, just change my SELECT statement by remove the OUTFILE portion. And pipe the query to file from the MySQL command line

mysql -u myuser -ppassword -h remotehost mydb < myquery.sql > my_output_file.txt

Reading documentation is bored but important :P