CODEWITHSUNDEEP
mysqlbashscripting
user2355518
user2355518

Reputation: 53

Bash MySQL query

I'm trying to run a query against MySQL 5.6.35 in bash, and have it exit 0 if the value is greater than or equal to 14. I can get it to show the results I would expect, but not exit 0.

Script:

#!/bin/bash

query="SELECT count(*) FROM weekly WHERE date > DATE_SUB(NOW(), INTERVAL 1 WEEK)"
mysql -u root -sN weekly_db -e "$query";

if test $query -ge 14 ; then
  echo "OK"
  exit 0
else
  echo "CRITICAL"
  exit 2
fi

Here is the bash script executed:

~# ./check.sh
39
./check.sh: line 6: test: too many arguments
CRITICAL

UPDATE WITH ANSWER:

Here is how I went about this thanks for codeforester's help

#!/bin/bash

query="SELECT count(*) FROM weekly WHERE date > DATE_SUB(NOW(), INTERVAL 1 WEEK)"
OUTPUT=$(mysql -u root sN weekly_db -e "$query";)

if ((OUTPUT >= 14)) ; then
  echo "OK"
  exit 0
else
  echo "CRITICAL"
  exit 2
fi

Output:

~# ./check.sh
OK

Upvotes: 5

Views: 969

Answers (1)

codeforester
codeforester

Reputation: 42999

You are getting the too many arguments error because unquoted $query evaluates to many words that confuses test. I am sure your intention is not to test the query, but the query results. So, you need to use command substitution to capture the output of MySQL:

query_output=$(mysql -u root -sN weekly_db -e "$query")

and then test it with (( ... )) which is more appropriate for numeric logic:

if ((query_output >= 14)); then
  echo "OK"
  exit 0
else
  echo "CRITICAL"
  exit 2
fi

In case MySQL fails (connectivity or query issues), the query output would be empty and your logic will still work - the if condition will evaluate to false and else part would get executed. I guess that's what you want.

See also:

Upvotes: 5

Related Questions