Nagios Support Forum

Posted: **Wed Mar 22, 2017 5:33 am**

Colleagues,

I ran into strange problem - I use check_mysql_query to retrieve the result and check it against threshold. Everything works perfectly, until I had to add filter in SQL query and use specific field value, which is in local language (Japanese). In this case check_mysql_query always returns 0:

My test from command line:

Code: Select all

./check_mysql_query -q "SELECT count(order_id) number_of_orders FROM oc_order WHERE payment_method = '銀行振込' AND order_status_id = 2 AND date_added > DATE_SUB(NOW(), interval 10 minute)" -H XXX.XXX.XXX.XXX -u data_check -p YYYYYYY -d ocdb

Result:

Code: Select all

QUERY OK: 'SELECT count(order_id) number_of_orders FROM oc_order WHERE payment_method = '銀行振込' AND order_status_id = 2 AND date_added > DATE_SUB(NOW(), interval 10 minute)' returned 0.000000 | result=0.000000;;;

If I remove payment_method = '銀行振込' condition - it works perfectly (returns non-null value). If I run the same query from mysql console - it works perfectly (returns non-null value)

Any ideas how to resolve it?

Thanks in advance and a great day to everyone.

Sergii

Posted: **Wed Mar 22, 2017 1:17 pm**

If I have a database created like so using utf8_bin as the Collation

Code: Select all

CREATE DATABASE `testdata` /*!40100 COLLATE 'utf8_bin' */;

And a table created like so:

Code: Select all

CREATE TABLE `oc_order` (
	`order_id` INT NULL,
	`payment_method` VARCHAR(50) NULL
)
COLLATE='utf8_bin'
ENGINE=InnoDB
;
SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='testdata';

And a record inserted into that table:

Code: Select all

INSERT INTO oc_order (payment_method) VALUES ('銀行振込');

I get this from check_mysql_query:

Code: Select all

[root@xi-stable ~]# /usr/local/nagios/libexec/check_mysql_query -V
check_mysql_query v2.0.3 (nagios-plugins 2.0.3)
[root@xi-stable ~]# /usr/local/nagios/libexec/check_mysql_query -q "SELECT * FROM oc_order WHERE payment_method = '銀行振込'" -H 192.168.67.1 -u remote -p welcome -d testdata           
QUERY WARNING: No rows returned

...

[root@core4 libexec]# ./check_mysql_query -V
check_mysql_query v2.2.0.git (nagios-plugins 2.2.0)
[root@core4 libexec]# /usr/local/nagios/libexec/check_mysql_query -q "SELECT * FROM oc_order WHERE payment_method = '銀行振込'" -H 192.168.67.1 -u remote -p welcome -d testdata
QUERY WARNING: No rows returned

Which is unfortunately consistent with the behavior you are seeing. However, when testing from the CLI using the mysql command, everything works out just fine:

Code: Select all

[root@xi-stable ~]# echo "SELECT * FROM oc_order WHERE payment_method = '銀行振込'" | mysql -uroot -pnagiosxi testdata
order_id        payment_method
1       銀行振込

So yes, I would think this problem is specific to check_mysql_query. I've raised this issue on github:
https://github.com/nagios-plugins/nagio ... issues/256

Nagios Support Forum

Is check_mysql_query UTF8 compliant?

Is check_mysql_query UTF8 compliant?

Re: Is check_mysql_query UTF8 compliant?