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
– user2355518, Commented May 16, 2017 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
– tadman, Commented May 16, 2017 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
– user2355518, Commented May 16, 2017 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
– tadman, Commented May 16, 2017 at 17:06

Community · Accepted Answer · 2017-05-23 12:26:23Z

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.

2 Comments

user2355518 Over a year ago

First off, thanks! You are correct I just want to test the results of the query. I tried this, but I'm getting a ton of mysql config output when running the command and it ends up critical. :~# ./check.sh ./check.sh: line 6: ((: mysql Ver 14.14 Distrib 5.6 .... CRITICAL

user2355518 Over a year ago

okay, you led me to the correct answer. I'll update the original question. thanks again.

Collectives™ on Stack Overflow

Bash MySQL query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related