Can't get Database Monitor (MySQL) to work - Cannot connect to ODBC DSN:[SQL_ERROR]:

[tholozan_prime]

Hello friends,

I'm trying to get Zabbix to monitor the results of a SQL query via the Database Monitor item type / db.odbc.select. My item keeps showing "Not Supported", and even with Debug level set to 5, the only error data i have to work with is:

 15489:20180419:221638.703 Item [<hostname_redacted>:db.odbc.select['rowcomp1',<dsn_redacted>]] error: Cannot connect to ODBC DSN:[SQL_ERROR]:

We're using Zabbix 3.0, and i followed the instructions in the documentation in terms of ODBC configuration. I am using Ubuntu 16.04, which had the unixodbc and unixodbc-dev packages in the repository, but not the MySQL connector driver. For that, i got the .tar.gz from the MySQL website.

I am able to run my query successfully, as the zabbix user, with the isql utility, so i feel like my /etc/odbc.ini and /etc/odbcinst.ini files are in good shape. The ini files can be read by the zabbix user, as can the library. The DSN in the item configuration matches the DSN in my odbc.ini, and the driver in odbc.ini matches the setting in odbcinst.ini. The sql starts with "select", and returns 1 value, in 1 row and 1 column. I've tried it with and without a final semi-colon.

I've restarted the server to no effect, deleted / re-added the item with no effect.

Has anyone seen this before? I feel somewhat bewildered by the fact that there is no further text in the error - the documentation indicates that i'll get further information on the nature of the error after that [SQL_ERROR]: bit, but that's all i get in my log.

[tholozan_prime]

Jackpot! Figured it out. Summarily: The most recent version of libmyodbc is 8, as provided by the MySQL website. Zabbix needs 5.0 ... or maybe it needs to match the version of the MySQL server? Not sure. Replacing the libmyodbc8w.so driver configuration with libmyodbc5a.so solved my issue - the monitor is now getting valid data.

Running strace on the poller processes, i was able to see that Zabbix was making a connection to the DB, and asking for / receiving data. I had a hunch this was related to the tar.gz i downloaded; thinking about it, I initially wondered if this was an ANSI / Unicode problem. And, it may actually still be. At any rate, the docs for Zabbix 3.0 show an example odbcinst.ini, and they show the 5a (version 5, ANSI) library in use. Putting that equivalent driver in my stack solved my issue.

Cheers!