Thursday, April 12, 2012

MySQL INTO OUTFILE overide existing file?


I've written a big sql script that creates a CSV file. I want to call a cronjob every night to create a fresh CSV file and have it available on the website.



Say for example I'm store my file in '/home/sites/example.com/www/files/backup.csv'



and my SQL is




SELECT * INTO OUTFILE '/home/sites/example.com/www/files/backup.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM ( ....



MySQL gives me an error when the file already exists




File '/home/sites/example.com/www/files/backup.csv' already exists




Is there a way to make MySQL overwrite the file?



I could have PHP detect if the file exists and delete it before creating it again but it would be more succinct if I can do it directly in MySQL.


Source: Tips4all

6 comments:

  1. No, there's no way to overwrite it. From the docs:


    file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed.


    It might be a better idea to use a different filename each night, as having multiple backups means you can recover from problems that have existed for more than a day. You could then maintain a symlink that always points at the latest complete csv.

    ReplyDelete
  2. For a job like this I would place it into a bash file, delete the file

    #!/bin/bash
    rm /path/to/backup.csv
    ./backup_sql_query.sh <<-- This contains the script to backup to CSV.


    The better option is to actually add a timestamp though. Disk space isn't expensive in this day and age.

    ReplyDelete
  3. There is no way.
    Only one possible you can procedure with dynamic statement.

    CREATE PROCEDURE export_dynamic(IN file_name char(64))
    BEGIN
    set @myvar = concat('SELECT * INTO OUTFILE ',"'",file_name,"'",' FROM Table1') ;
    PREPARE stmt1 FROM @myvar;
    EXECUTE stmt1;
    Deallocate prepare stmt1;
    END;

    Regards,
    Mani B
    Pace Automation
    Chennai

    ReplyDelete
  4. Kind of a dated question, but hopefully this will help someone.

    Simply escape to a shell from within mysql and execute a rm command to remove the file before you attempt to write it. For example:

    Mysql> \! rm -f /home/sites/example.com/www/files/backup.csv

    Enjoy!

    ReplyDelete
  5. Why not rm -f /home/sites/example.com/www/files/backup.csv in the script ran by cron?

    You can run this from inside mysql. Just escape to the shell with '!'
    For example:

    Mysql> \! rm -f /home/sites/example.com/www/files/backup.csv

    ReplyDelete
  6. these exports where created using 'SELECT * INTO OUTFILE.....'

    mysql> \! ls -l /tmp/rooster*.sql
    -rw-rw-rw- 1 mysql mysql 371960 Mar 21 13:55 /tmp/rooster1.sql
    -rw-rw-rw- 1 mysql mysql 371960 Mar 21 13:55 /tmp/rooster2.sql


    but when i'm trying to delete the file:

    mysql> \! rm -f /tmp/rooster*.sql
    rm: cannot remove `/tmp/rooster1.sql': Operation not permitted
    rm: cannot remove `/tmp/rooster2.sql': Operation not permitted

    ReplyDelete