Sorry for the confusion. We can dump the file to the server where the database is hosted but this problem revolves around 2 different servers - 1. Client, 2. Database.
So we want to dump the output to the client and MySQL has no such implementation. I explored the -e option and specifying the server name but it outputs only .txt file and there is no way of specifying CSV or pipe-delimited file. Thank you... On Wed, May 19, 2010 at 12:53 PM, Brian Evans <grkni...@scent-team.com>wrote: > On 5/19/2010 12:37 PM, Cool Guy wrote: > > Does the connecting user have the FILE privilege? > > Yes, the connecting user has full file privileges. > > > Check with "SELECT File_priv from mysql.user where user='x'" (replace x > with the real connecting user name). > This assumes the user is not the default root account. > > > > Apples and Oranges. LOAD DATA INFILE loads a file on the server's data > directory by the server, LOAD DATA LOCAL INFILE is read by the client and > sent to the server. > > LOAD DATA INFILE also requires the FILE privilege. > > LOAD DATA is loading the file from the client while select into outfile > is dumping the file to the client. Not so sure why the latter shouldn't > work? > > > "SELECT INTO OUTFILE" saves a file to the server, not the client. > > Quoting http://dev.mysql.com/doc/refman/5.1/en/select.html > "The SELECT ... INTO OUTFILE statement is intended primarily to let you > very quickly dump a table to a text file on the server machine. If you want > to create the resulting file on some client host other than the server host, > you cannot use SELECT ... INTO OUTFILE. In that case, you should instead use > a command such as mysql -e "SELECT ..." > file_name to generate the file on > the client host. " > > > > Thanks.. > > On Wed, May 19, 2010 at 12:27 PM, Brian Evans <grkni...@scent-team.com>wrote: > >> On 5/19/2010 12:09 PM, Cool Guy wrote: >> >> 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 .... >> >> Now, we moved the database to a different server and SELECT * INTO OUTFILE >> .... no longer works, understandable - security reasons I believe. >> >> >> Does the connecting user have the FILE privilege? >> >> >> But, interestingly LOAD DATA INFILE .... can be changed to LOAD DATA >> LOCAL INFILE .... and bam, it works. >> >> >> Apples and Oranges. LOAD DATA INFILE loads a file on the server's data >> directory by the server, LOAD DATA LOCAL INFILE is read by the client and >> sent to the server. >> >> LOAD DATA INFILE also requires the FILE privilege. >> >> Brian >> >> >> I am not complaining nor am I expressing disgust towards MySQL. The >> alternative to that added 2 lines of extra code and a system call form a >> .sql script. All I wanted to know is why LOAD DATA LOCAL INFILE works and >> why is there no such thing as SELECT INTO OUTFILE LOCAL? >> >> I did my homework, couldn't find a direct answer to my questions above. I >> couldn't find a feature request @ MySQL either. If someone can clear that >> up, that had be awesome! >> >> >> I am planning on suggesting MariaDB to our company cos' it is not under >> Oracle's admin. Does MariaDB already have this "SELECT INTO OUTFILE LOCAL >> .." feature implemented or is it in the wishlist/to-do list? >> >> >> Thank you, >> >> >> Kiran >> >> >> >> _______________________________________________ >> Mailing list: >> https://launchpad.net/~maria-discuss<https://launchpad.net/%7Emaria-discuss> >> Post to : maria-discuss@lists.launchpad.net >> Unsubscribe : >> https://launchpad.net/~maria-discuss<https://launchpad.net/%7Emaria-discuss> >> More help : https://help.launchpad.net/ListHelp >> >> > > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : maria-discuss@lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp > >
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
