How to overwrite csv file of MySQL result set?

Question

My SQL query is given below,

select company.Company_name as COMPANY_NAME, 
IFNULL(followup.contact_date, ' ') AS FOLLOW_UP_DATE,
IFNULL((select Full_name from user where user_id = followup.user_id), ' ') AS Employee, 
IFNULL(fix_appoinment_order.appointment_dt, ' ') AS APPOINTMENT_DATE, 
IFNULL((select Full_name from user where user_id = fix_appoinment_order.user_tme), ' ') as TELEMARKETING_EXECUTIVE,
IFNULL((select Full_name from user where user_id = fix_appoinment_order.user_me), ' ') as MARKETING_EXECUTIVE
from company_order
inner join company on company.Company_id = company_order.Company_id
left join company_history on company_order.Compordr_id = company_history.compordr_id
left join followup on followup.followup_id = company_history.followup_id
left join fix_appoinment_order on fix_appoinment_order.fix_appointment_order_id = company_history.fix_appointment_order_id
where
followup.contact_date = date_format(now() + INTERVAL 1 DAY, "%Y-%m-%d") OR 
fix_appoinment_order.appointment_dt = date_format(now() + INTERVAL 1 DAY, "%Y-%m-%d")
INTO OUTFILE '/var/www/backup/AppandFllups.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

I know that, the query working fine, I have to set the cron job to get the result set as csv file and send email.

At first time when I execute the query, there is no issue, but next time I got the error as follows,

Error Code: 1086. File '/var/www/backup/AppandFllups.csv' already exists

I understood, the file is already created while I run at first time, but I am unable solve this issue. I used another cron job to fix this issue but it I am not satisfied, is there anyway to do achieve this?

Answer 1

According to the MySQL Documentation on SELECT ... INTO

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. The character_set_filesystem system variable controls the interpretation of the file name.

I suppose the easiest way would be the following:

STEP #1

Change this line in the query

INTO OUTFILE '/var/www/backup/AppandFllups.csv'

to this

INTO OUTFILE '/var/www/backup/AppandFllups.tmp'

STEP #2

Add these lines to the cronjob just after the dump

cd /var/www/backup
mv AppandFllups.tmp AppandFllups.csv

The main idea is to have /var/www/backup/AppandFllups.tmp be nonexistent just before the dump. The output file gets renamed to something you already use elsewhere. So, there is no need to change other processes or scripts.

Give it a Try !!!