Bash MySQL query

Question

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

I see what you're getting at, maybe i need to set a different variable on line 4. — user2355518, May 16 '17 at 16:59
As a note, `bash` is really the worst possible way to get data out of MySQL. If you can use Python, Ruby, Perl, Node.js, *anything* with a native MySQL driver you'll be far better off. Writing a simple wrapper script and mapping your result to an exit status code so `bash` can interpret it is pretty easy. — tadman, May 16 '17 at 17:00
I understand, I need this to exit 0 for a quick custom nagios check. I know there are lots of different tools for that, but I feel this should also be easy. I just can't get bash to do exactly what I want. — user2355518, May 16 '17 at 17:04
Like I said, `bash` is really the worst possible tool for this since it doesn't understand MySQL at all. You *can* do this with a lot of `sed`, `awk` and other random duct tape, but you really don't want to. This is like four lines in any common scripting language where you can install a MySQL driver. — tadman, May 16 '17 at 17:06

score 5 · Accepted Answer · edited May 23 '17 at 12:26

5

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.

Bash MySQL query

1 Answers1