// shall I write some keywords here to boost search engine ranking?

Monday, June 15, 2009

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


Vishal said...

Your solution seems to be diff and usefull but I am trying query like below

mysql -u username -pPassword -e "select * from sentbox_3 where publishtimestamp >= '2010-01-05 00:00:00' and publishtimestamp < '2010-08-20 23:59:59' FIELDS TERMINATED BY ',' ENCLOSED BY '/"' LINES TERMINATED BY '\n'" mydatabse > /var/www/backup/somefile.csv

Is this valid way or not?
Will this resulting csv file will be importable into other databse?
and also this command is not working why?

Thiam Teck (1983 - ?) said...

Hi Vishal, may I know what error you get by executing this?

Heba said...

Thanksss! I was assuming the same as well and your solution solved my problem :)

Heba said...

Thanks a lot. I also had the same wrong assumption and your solution just solved it for me :)