Is check_mysql_query UTF8 compliant?

Support forum for Nagios Core, Nagios Plugins, NCPA, NRPE, NSCA, NDOUtils and more. Engage with the community of users including those using the open source solutions.
Locked
jester
Posts: 1
Joined: Wed Mar 22, 2017 5:25 am

Is check_mysql_query UTF8 compliant?

Post by jester »

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
User avatar
mcapra
Posts: 3739
Joined: Thu May 05, 2016 3:54 pm

Re: Is check_mysql_query UTF8 compliant?

Post by mcapra »

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
Former Nagios employee
https://www.mcapra.com/
Locked