need suggestion for creating bash script

Question

Getting P1 alerts when the status of a table doesn't get updated by logger, we need to be able to log the data and update the status based on the following conditions. For now we are using this script to run it when alerted and look into scheduling it later

Check If there are any tables with status = 1 that are older than 5mins in the following 2 tables

select * from test1 where status = 1 and CREATED < DATE_SUB(NOW(), INTERVAL 5 MINUTE);

select * test2 where status = 1 and CREATED < DATE_SUB(NOW(), INTERVAL 5 MINUTE);
If we find any records in step# 1 save the output to log from the above query and check if the table specified in TEMP_TABLE_NAME(eg:TEMP_test1) exists, because here temp table automatically gets generated.
If the temp table doesn't exist update the status to 0 to clear out the alert, update could be needed for just one of the table listed above

Update test1 set status = 0 where ID = ;
If the TEMP_TABLE_NAME(eg:TEMP_test1) does exist, we need to reach out to concerned team to investigate
Email the results of the output from step: 1 to xxx@gmail.com for review

I have tried writing below code using google but stucked about stoirng file and how to compare temp table exists or not.

email="pqrs@gmail.com"
pid=$$
logFile="/tmp/alertlog"
emailflag=0

host=XX.XX.XX.XX
user=ABC
passwd=XYZ


alert_on_error () {
    echo $1 >> /tmp/$pid.count
    cat /tmp/$pid.count | mail -s "Error running $0 on `hostname`"  $email
    rm -rf /tmp/$pid.*
    exit 1
}

mysql="mysql --connect-timeout=50 --compress --quick --disable-auto-rehash --skip-column-names -u $user -h $host -p$passwd -A"
    ID1='$mysql -e "select * from test1 where status = 1 and CREATED < DATE_SUB(NOW(), INTERVAL 5 MINUTE);"'
ID2='$mysql -e select * from test2 where status = 1 and CREATED < DATE_SUB(NOW(), INTERVAL 5 MINUTE);"'

Barmar · Answer

You can simply put everything in a .sql file and execute it. E.g. create myscript.sql

select * from TEST1 where status = 1 and CREATED < DATE_SUB(NOW(), INTERVAL 5 MINUTE);
select * from TEST2 where status = 1 and CREATED < DATE_SUB(NOW(), INTERVAL 5 MINUTE);
UPDATE TEST1 SET status = 0 WHERE status = 1 and CREATED < DATE_SUB(NOW(), INTERVAL 5 MINUTE);
UPDATE TEST2 SET status = 0 WHERE status = 1 and CREATED < DATE_SUB(NOW(), INTERVAL 5 MINUTE);

Then execute

mysql databasename < myscript.sql >> logfile.txt

need suggestion for creating bash script

1 Answers