Checking two conditions using mySQL in bash script

Question

I am doing some content for the database when working with mySql. I set up a script to constantly check for deadlocks using Percona. I try to make my script only send letters to our administrator when new events appear. I set the "emailSent" switch column to distinguish between new and old dead ends. I want to check if the deadlock table is empty, and if it has not been emailed to our administrator. My "else" script works fine, but I am having problems 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 really want to print “Currently there are no new locks” when all rows have lock_mode of “X” and emailSent = '1' (or the table is empty). If they have emailSent = '0' and lock_mode 'X', I want my else to execute.

Any help would be greatly appreciated.

Answer 1

You can 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 work 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