Skip to main content

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

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

Post a Comment

Popular posts from this blog

Slow Android emulator

I have a 2.67 GHz Celeron processor, 1.21 GB of RAM on a x86 Windows XP Professional machine. My understanding is that the Android emulator should start fairly quickly on such a machine, but for me it does not. I have followed all instructions in setting up the IDE, SDKs, JDKs and such and have had some success in staring the emulator quickly but is very particulary. How can I, if possible, fix this problem?

CCNA 3 Final Exam => latest version

1 . Which security protocol or measure would provide the greatest protection for a wireless LAN? WPA2 cloaking SSIDs shared WEP key MAC address filtering   2 . Refer to the exhibit. All trunk links are operational and all VLANs are allowed on all trunk links. An ARP request is sent by computer 5. Which device or devices will receive this message? only computer 4 computer 3 and RTR-A computer 4 and RTR-A computer 1, computer 2, computer 4, and RTR-A computer 1, computer 2, computer 3, computer 4, and RTR-A all of the computers and the router   3 . Refer to the exhibit. Hosts A and B, connected to hub HB1, attempt to transmit a frame at the same time but a collision occurs. Which hosts will receive the collision jamming signal? only hosts A and B only hosts A, B, and C only hosts A, B, C, and D only hosts A, B, C, and E   4 . Refer to the exhibit. Router RA receives a packet with a source address of 192.168.1.65 and a destination address of 192.168.1.161...