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. — sjsam
– sjsam, Commented Dec 2, 2015 at 3:35

RolandoMySQLDBA · Accepted Answer · 2015-12-02 03:57:15Z

3

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

answered Dec 2, 2015 at 3:57

RolandoMySQLDBA

44.4k16 gold badges96 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Checking two conditions using mySQL in a bash script

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related