MySQL - SELECT * INTO OUTFILE LOCAL ?

mysql sql mariadb into-outfile
ThinkCode picture ThinkCode · May 19, 2010 · Viewed 107.7k times · Source

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. But, interestingly LOAD DATA INFILE .... can be changed to LOAD DATA LOCAL INFILE .... and bam, it works.

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!

Is MariaDB capable of handling this problem?

Answer

jerrygarciuh picture jerrygarciuh · Jun 4, 2010

From the manual: 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."

http://dev.mysql.com/doc/refman/5.0/en/select.html

An example:

mysql -h my.db.com -u usrname--password=pass db_name -e 'SELECT foo FROM bar' > /tmp/myfile.txt

Related questions

ERROR 1452: Cannot add or update a child row: a foreign key constraint fails

I have created tables in MySQL Workbench as shown below : ORDRE table: CREATE TABLE Ordre ( OrdreID INT NOT NULL, OrdreDato DATE DEFAULT NULL, KundeID INT DEFAULT NULL, CONSTRAINT Ordre_pk PRIMARY KEY (OrdreID), CONSTRAINT Ordre_fk FOREIGN KEY (KundeID) REFERENCES …

Read More
Cast int to varchar

I have below query and need to cast id to varchar Schema create table t9 (id int, name varchar (55)); insert into t9( id, name)values(2, 'bob'); What I tried select CAST(id as VARCHAR(50)) as col1 from t9; select CONVERT(…

Read More
ERROR 1054 (42S22): Unknown column '‍‍' in 'field list'

I get this annoying error when I try to insert data from db1 to db2 in MaridaDB 10 using mysql CLI. This is while all the columns exist. INSERT INTO db2.thread (threadid, title, postuserid, dateline, views) SELECT `nid`, `title`, `uid`, ‍‍`…

Read More