4

I'm interested in exporting a mysql select statement into a file on a samba share. Everything works fine until I try to update a file. For example, I have mysql exporting into outfile peachtree.csv and when the data changes I want it to over write the old file with the new data, however, I get the error: ERROR 1086 (HY000): File '/srv/samba/share/peachtree.csv' already exists. Is there an option or switch I can put into the statement to force or make it over write the existing file?

SELECT * FROM peachtree,
INTO OUTFILE '/srv/samba/share/peachtree.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Colin Inskeep
  • 261
  • 3
  • 6
  • 16
  • No. It cannot be overwritten. The answer here points at the docs. http://stackoverflow.com/questions/960627/mysql-into-outfile-overide-existing-file. – Erik Nedwidek Jan 18 '13 at 18:43

2 Answers2

5

You cannot. This is a security feature. Otherwise, you might be able to select into such files as /etc/password or /etc/shells.

From the manual:

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. file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed.

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

123
  • 66
  • 1
  • 2
-1

This solution uses dynamic SQL and writes to a Windows filesystem.

Select Now() Into @Now;                                              # get DateTime
Set @Now = Replace(@Now, ':', '-');                                  # format DateTime for the filesystem

# set Destination filename using formatted DateTime
Set @FilNam = Concat("'", 'd:/Folder/qrySelectToFile ', @Now, '.TXT', "'");
#Select @FilNam;                                                     # debug

# build query to execute, one clause at a time, for encoding purposes
Set @Sel     =  'Select * From vewViewName Into OutFile '    ; # Select From Into
Set @FldsTrm = ' Fields Terminated By ","'                         ; # Fields Terminated
Set @FldsEnc = Concat(" Enclosed By '", Char(34 Using utf8), "'")  ; #        Enclosed. 'Using' clause is to avoid a blob of 34 spaces.
Set @LinsTrm = " Lines Terminated By '\\r\\n'"                     ; # Lines  Terminated
Set @Qry = Concat(@Sel, @FilNam, @FldsTrm, @FldsEnc, @LinsTrm, ';'); # all together now

Prepare            Stmt From @Qry;                                   # Prepare
Execute            Stmt          ;                                   # Execute
Deallocate Prepare Stmt          ;                                   # Done

Other answers at MySQL INTO OUTFILE override existing file?

Bilbo
  • 358
  • 1
  • 10