I have a bash script that is running a mysql query and creating a csv file with a time stamp. It then uploads that to a ftp server. Everything works great when I manually run it. But then I have a cron job set to run every monday, wednesday and friday at 5am est. When the cron job runs, it appends to the last file. For example, the file that ran wednesday is called 15-07-08_report.csv. On Friday the cron job runs and instead of creating a new file called 15-07-10_report.csv, it just appends to the file called 15-07-08_report.csv. If I run it manually without the cron job, the bash script will create a new file on the ftp server called 15-07-10_report.csv and leave the file from wednesday. That what I want to happen when the cron job runs.
bash script:
#!/bin/bash
#DB server details
server_ip='mysql_server.com';
db_user='user';
db_pass='password';
db_name='db_name';
#Delete all previous csv files
rm *.csv
#create new file
csv_file="$(date -d now +%y-%m-%d)_report.csv";
touch $csv_file
#Query to be execute in MySql and save data in csv file
mysql -u$db_user -p$db_pass -h$server_ip < /var/www/html/tools/report-script/report.sql $db_name | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' >> $csv_file
#Open FTP and put files there
echo "start ftp"
#Your FTP IP or URL
HOST="ftp.upload.com"
#FTP user name
USER="user_me"
echo "pass"
#FTP user password
PASSWD="my_pass"
echo "change"
#Change to directory where the original csv file is located
cd /var/www/html/tools/report-script
echo "transfer file"
ftp -n $HOST <<END_SCRIPT
quote USER $USER
quote PASS $PASSWD
!#Folder on the FTP server you want to save to. By default it will save to the user's home directory
cd /folder
!# Turn off prompting yes or no to transfer file
prompt
!#copies the cvs file to ftp server
mput *.csv
quit
END_SCRIPT
exit 0
on the server that i am running the script on, i delete any previous cvs files and then create a new one with todays date. I then dump the sql query into the csv file with todays date. I don't really need the touch command in there as the query dumps it into a csv file anyways. i was just trying to see if it made a difference, it doesn't.
The file that it is appending too is on the ftp server.
I guess I don't need the
cd /var/www/html/tools/report-script
I was under the impression i had to tell it where to go but i guess it's already working from that directory.
---------- Post updated at 10:38 AM ---------- Previous update was at 10:16 AM ----------
I don't know what's doing the append. if i run it manually using
sh test_report.sh
it runs fine. the script runs and transfers a file called 15-07-10_report.csv. So when I look at the ftp server I see two files. One called 15-07-08_report.csv and one called 15-07-10_report.csv.
When the cron job runs, I check the ftp server and see that the last file is still there of 15-07-08_report.csv but the date modified is 7/10/2015 5:00:05 AM. It didn't craete a new file with the name 15-07-10_report.cvs.
I've been reading this thread with some interest and curiosity so this is just "thinking out loud" without any real experience to draw on.
However, I would be asking whether the cron ftp job actually closed off the ftp session plus whether the next cron run picks up the old session. Certainly, running the script interactively would definitely create a new ftp connection. I would initially check via a process listing whether, when the cron job has supposedly finished, that the associated processes are gone and the ftp session is no longer there. I currently can't think of any other way the cron job could append to an old file unless it's still connected to it and has it held open.
ah! that's makes so much sense. so when the cron job runs, it doesn't delete anything because the file isn't in the directory that the cron job is running in. and then it pulls the last file that is sitting in /var/www/html/tools/report-script/ because it was never deleted and the new file was never created in that directory. Thank you!
---------- Post updated at 03:37 PM ---------- Previous update was at 02:06 PM ----------
Just to follow up, I did verify that this was this issue. I found the new csv files created in the home folder where the cron job runs...