Checking two conditions using mySQL in a bash script

Question

I am doing some upkeep for a database at work using mySql. I have set up a script to constantly check for deadlocks using Percona. What I am trying to do is have my script only send emails to our administrator when new occurrences appear. I set up a switch column "emailSent" to differentiate new versus old deadlocks. What I want to check is whether or not the deadlock table is empty, and if it's not has the email been sent to our admin. My "else" script is working fine, but I am having issues with the initial "if":

if [ "a$(mysql --skip-column-names -h -u -p -e  "SELECT * from deadlocks WHERE lock_mode= 'X' AND emailSent = '0';")" = "NULL" ]

then 

echo There are currently no new deadlocks active.

I essentially want to print "There are currently no new deadlocks active" when all rows have a lock_mode of 'X', and emailSent = '1' (or the table is empty). If they have emailSent = '0' and lock_mode of 'X' I want my else to execute.

Any help would be greatly appreciated.

You might wish to use `[[ "stuff1" == "stuff2" ]]`. If the results are null, you might wish to compare with "" right? Please see [this](http://stackoverflow.com/questions/12262696/using-unset-vs-setting-a-variable-to-empty). — sjsam, Dec 02 '15 at 03:35

score 3 · Accepted Answer · answered Dec 02 '15 at 03:57

You could do the following

MYSQL_HOST=10.20.30.40
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT COUNT(1) from deadlocks WHERE lock_mode='X' AND emailSent='0'"
DEADLOCKS_FOUND=`mysql ${MYSQL_CONN} -ANe "${SQL}"`
if [ ${DEADLOCKS_FOUND} -gt 0 ]
then
    echo "Deadlocks Detected"
fi

or if you are working locally

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT COUNT(1) from deadlocks WHERE lock_mode='X' AND emailSent='0'"
DEADLOCKS_FOUND=`mysql ${MYSQL_CONN} -ANe "${SQL}"`
if [ ${DEADLOCKS_FOUND} -gt 0 ]
then
    echo "Deadlocks Detected"
fi

Checking two conditions using mySQL in a bash script

1 Answers1