MySQL Sensor Query debugging

I am trying to check the numeric value of:

show status like 'Threads_connected';

that is returned from a MySQL database in a MySQL sensor. Can you provide some examples of syntax that works in the "SQL-Expression" form? As soo as i enable the sensor, it goes into error state and doesnt provide feedback as to what is wrong.

My query if done on a command line:

mysql> show status like 'Threads_connected';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 372   |
+-------------------+-------+
1 row in set (0.00 sec)

Thanks,

-chuck

mysql prtg prtg9

Created on Feb 3, 2012 10:33:32 PM by chucksalsa (0) ●1

Last change on Feb 6, 2012 2:17:05 PM by Torsten Lindner [Paessler Support]

Best Answer

Accepted Answer

Votes:

0

Your Vote:

Please make sure your query only reports one value which then can be processed with 'Process numerical result'. For example:

mysql> select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME= 'Threads_connected';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 1              |
+----------------+
1 row in set (0.00 sec)

mysql> select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME= 'table_locks_immediate';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 285            |
+----------------+
1 row in set (0.00 sec)

Created on May 10, 2013 3:38:08 PM by Dieter Loskarn [Paessler Support]

6 Replies

Votes:

0

Your Vote:

Hello,

the 'problem' here is that the sensor in PRTG 'expects' the numerical result in the first row & first column of the result and so can't parse the result like this.

best regards.

Created on Feb 6, 2012 2:18:26 PM by Torsten Lindner [Paessler Support]

Votes:

0

Your Vote:

No a helpful response. I will ask again: Can you provide some examples of syntax that works in the "SQL-Expression" form?

Created on Feb 6, 2012 2:46:23 PM by chucksalsa (0) ●1

Votes:

0

Your Vote:

You will probably need a PHP script or similar to do the SQL connection, and then parse out the info and do a test. I am using one for my mySQL replication. Make sure when you get your field you want to return, to include ":OK" as well, so it should look like "372:OK"

Created on Feb 7, 2012 1:32:23 PM by Rick Elder (0) ●1

Votes:

0

Your Vote:

So no one at Paessler can give an example of a working MySQL syntax for the SQL-Expression form?

Created on Feb 28, 2012 6:46:21 PM by chucksalsa (0) ●1

Votes:

0

Your Vote:

Hi,
every mySQL query can be put there. An example would be

 SELECT AVG(UnitPrice) FROM Products

Please do not put a semicolon on the end of the query.
Best regards

Created on Feb 29, 2012 12:16:58 PM by Konstantin Wolff [Paessler Support]

Accepted Answer

Votes:

0

Your Vote:

Please make sure your query only reports one value which then can be processed with 'Process numerical result'. For example:

mysql> select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME= 'Threads_connected';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 1              |
+----------------+
1 row in set (0.00 sec)

mysql> select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME= 'table_locks_immediate';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 285            |
+----------------+
1 row in set (0.00 sec)

Created on May 10, 2013 3:38:08 PM by Dieter Loskarn [Paessler Support]