MySQL INTO OUTFILE override existing file?
PhpMysqlSqlInto OutfilePhp Problem Overview
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.
Php Solutions
Solution 1 - Php
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.
Solution 2 - Php
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
Solution 3 - Php
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.
Solution 4 - Php
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;
Solution 5 - Php
3 steps to do this right. Your backup will be executed every night while you sleep (or not)
STEP 1 : Create a stored procedure for your SQL
CREATE PROCEDURE backupCSV()
SELECT * INTO OUTFILE '/home/sites/example.com/www/files/backup.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM ( ....
STEP 2 : Create a script "/home/backupCSV.sh" to delete old file and call the stored procedure
echo "$(date +"%Y-%m-%d %T") START MAINTENANCE SCRIPT "
rm /home/sites/example.com/www/files/backup.csv
echo "$(date +"%Y-%m-%d %T") SUCCESS >> Old file deleted"
mysql --user=[user] --password=[password] [dataBaseName] --execute="CALL backupCSV();"
echo "$(date +"%Y-%m-%d %T") SUCCESS >> New file created"
echo "$(date +"%Y-%m-%d %T") END MAINTENANCE SCRIPT "
STEP 3 : Update Crontab to execute the script everyday
# m h dom mon dow user command
0 3 * * * root sh -x /home/backupCSV.sh
STEP 4 (optionnal) : thanks me ;)
Solution 6 - Php
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
Solution 7 - Php
old question.. but there is an option OVERWRITE ON that you can add to the statement
SELECT * INTO OUTFILE '/home/sites/example.com/www/files/backup.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' OVERWRITE ON
...