Nagios Support Forum

Hi,
I'm trying to run a query "SELECT (TIMEDIFF(Now(), created_at)) as diff FROM db.table WHERE status != '1' ORDER BY created_at desc"

Im, using this command
$USER1$/check_mysql_query -q "$ARG1$" -w "$ARG2$" -c "$ARG3$" -d "$ARG4$" -H "$ARG5$" -u "$ARG6$" -p "$ARG7$"

And I get this error:
COMMAND: /usr/local/nagios/libexec/check_mysql_query -q "SELECT TIME_TO_SEC\(TIMEDIFF\(Now\(\), created_at\)\) FROM db.table WHERE status = '1' ORDER BY created_at DESC LIMIT 1" -w "50" -c "100" -d "DD" -H "abc.xyz" -u "nagios" -p "xxxxx"
OUTPUT: QUERY CRITICAL: Error with query - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\(TIMEDIFF\(Now\(\), created_at\)\) FROM db.table WHERE statu' at line 1

When I run it locally, I get a normal output:

[XXX@XXX ~]$ /usr/lib64/nagios/plugins/check_mysql_query -q "SELECT TIME_TO_SEC(TIMEDIFF(Now(), created_at)) FROM db.table WHERE status != '1' ORDER BY created_at DESC LIMIT 1" -w '50' -c '100' -d db' -H 'abc.xyz' -u 'nagios' -p 'xxxxx'
QUERY CRITICAL: 'SELECT TIME_TO_SEC(TIMEDIFF(Now(), created_at)) FROM db.table WHERE status != '1' ORDER BY created_at DESC LIMIT 1' returned 413.000000

The Test Check Command button is known to have some incorrect output at times. The best way to test is to save the check and let it run naturally as opposed to running it like that in the CCM. What output does that provide?

tmcdonald wrote:The Test Check Command button is known to have some incorrect output at times. The best way to test is to save the check and let it run naturally as opposed to running it like that in the CCM. What output does that provide?

You are right, I enabled the check and I get a different output now:
QUERY WARNING: No rows returned

I hope I will find a solution now.

OK, now I fixed it and it works, two more questions:

1. I get a really ugly output:
QUERY OK: 'SELECT TIME_TO_SEC(TIMEDIFF(Now(), created_at)) FROM db.table WHERE status <> '1' ORDER BY created_at DESC LIMIT 1' returned 23.000000

Why can't I simply get the 23 without all the query?

2. It does not create a graph - I assume because of the output?

By the end it started to create graph, but as another solution I used check_mysql_healt plugin which did the trick as well.
Thanks, you can close it.

Thanks for that update and for the tip of using the check_mysql_health plugin instead - I'll go ahead and close this one out now.