Hey all,
Just wanted to get some input on a script I am using to import files into a MySQL database.
The process is pretty simple: my main server exports these files and FTPs them. I have a script that FTPs them to the machine running that runs this script. The FTP script runs without issue every time, bith from the cron or manually.
The problem occurs when the cron runs the script below. Please be kind , these are my first scripts.
The loop runs fine the first time , but when it loops back again it dies at the point where mysqlimport is called again. The first file , in this case the website_tabProperty.txt works fine. It dies at website_tabBuilding.txt.
My cron is set to run this script as root.
I have been running this script manually "./" for a long time and works every time.
I figure its a problem with the loop and the cron but I cant come up with anything.
Thanks for your help.
Bill
Code:
#!/bin/bash
echo "Starting new batch import `date`"
echo "Starting new batch import `date`" >> /root/import.log
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#Imporatnt: The last file and table in these arrays MUST be the data that is targeted for
#the append operation.
#define the files names for import and existance verification. These are renamed to the names below
file_name[1]=/root/website_tabProperty.txt
file_name[2]=/root/website_tabBuilding.txt
file_name[3]=/root/website_tabApartment.txt
file_name[4]=/root/website_tabMeters.txt
file_name[5]=/root/website_tabReadings.txt
#define the file names for importation. The file names must match the table names
target_file_name[1]=/root/tabProperty.txt
target_file_name[2]=/root/tabBuilding.txt
target_file_name[3]=/root/tabApartment.txt
target_file_name[4]=/root/tabMeters.txt
target_file_name[5]=/root/tabReadings.txt
#define the target tables to flush
target_table[1]=tabProperty
target_table[2]=tabBuilding
target_table[3]=tabApartment
target_table[4]=tabMeters
target_table[5]=tabReadings
#this number nust be one higher than the max index in the arrays.
MAX_LOOP=6
#Set this value to the index we want to append - will force it to skip the flush
APPEND_INDEX=5
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#set the file name suffix for the renaming routine
suffix=`date +%m_%d_%Y`
COUNTER=1
while [ $COUNTER -lt $MAX_LOOP ]; do
echo "---------------------------------------"
echo "---------------------------------------" >> /root/import.log
echo "Processing index - "$COUNTER
echo "Processing index - "$COUNTER >> /root/import.log
DIR=${file_name[COUNTER]}
if [ -f $DIR ];then
echo "Original "${file_name[COUNTER]}" exists"
echo "Original "${file_name[COUNTER]}" exists" >> /root/import.log
echo "Renaming "${file_name[COUNTER]}
echo "Renaming "${file_name[COUNTER]} >> /root/import.log
cp ${file_name[COUNTER]} ${target_file_name[COUNTER]}
echo "Removing "${file_name[COUNTER]}
echo "Removing "${file_name[COUNTER]} >> /root/import.log
rm ${file_name[COUNTER]}
#Only flush the table if it is not the last one in the index
if [ $COUNTER != $APPEND_INDEX ]; then
echo "Flushing "${target_table[COUNTER]}
echo "Flushing "${target_table[COUNTER]} >> /root/import.log
mysql -u root -p8895asdf7665553 -e "use wellspring; delete from "${target_table[COUNTER]}";"
sleep 2
else
echo "Not Flushing "${target_table[COUNTER]}
echo "Not Flushing "${target_table[COUNTER]} >> /root/import.log
fi
echo "Importing "${target_file_name[COUNTER]}
echo "Importing "${target_file_name[COUNTER]} >> /root/import.log
mysqlimport -u root -p8895asdf7665553 --local wellspring ${target_file_name[COUNTER]}
sleep 2
echo "Renaming "${target_file_name[COUNTER]}
echo "Renaming "${target_file_name[COUNTER]} >> /root/import.log
cp ${target_file_name[COUNTER]} ${target_file_name[COUNTER]}.$suffix
sleep 1
echo "Removing "${target_file_name[COUNTER]}
echo "Removing "${target_file_name[COUNTER]} >> /root/import.log
rm ${target_file_name[COUNTER]}
sleep 1
else
echo "Original "${file_name[COUNTER]}" does not exist"
echo "Original "${file_name[COUNTER]}" does not exist" >> /root/import.log
fi
let COUNTER=COUNTER+1
done
echo "---------------------------------------"
echo "---------------------------------------" >> /root/import.log
echo "Finished batch import `date`"
echo "Finished batch import `date`" >> /root/import.log
echo "*********************************************************************************"
echo "*********************************************************************************" >> /root/import.log
exit