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 directoryThe 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.txtReading documentation is bored but important :P
4 comments:
Hi,
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?
:)
Hi Vishal, may I know what error you get by executing this?
Thanksss! I was assuming the same as well and your solution solved my problem :)
Thanks a lot. I also had the same wrong assumption and your solution just solved it for me :)
Post a Comment