0

I am struggling to monitor a mysql database (via sql statement) in zabbix.

I tried to monitor the updating of mysql database by following the official guide: https://www.zabbix.com/documentation/current/manual/config/items/itemtypes/odbc_checks#:~:text=ODBC%20monitoring%20corresponds%20to%20the,later%20ported%20to%20other%20platforms.

and this post https://blog.zabbix.com/database-odbc-monitoring-with-zabbix/8076/

But, I have an error "Cannot connect to ODBC DSN: [SQL_ERROR]:[08001][0][[unixODBC][MySQL][ODBC 8.0(w) Driver]Driver does not support server versions under 4.1.1]"

I can successfully come to the step isql test in the official guide, but i do not understand the step:

To enable ODBC support, Zabbix should be compiled with the following flag:
  --with-unixodbc[=ARG]   use odbc driver against unixODBC package

On the web interface, there is always such error "Driver does not support server versions under 4.1.1".

Similar error has been raised here as well:

https://www.zabbix.com/forum/zabbix-troubleshooting-and-problems/391108-zabbix-can-t-connect-to-my-mysql-database-from-new-installed-server

https://www.zabbix.com/forum/em-portugues-y-en-espanol/418997-zabbix-5-2-4-is-unable-to-connect-to-my-mysql-azure-database

No clear solution is provided!

I am waiting for my hero to save me for this zabbix issue!

enter image description here

XYZ
  • 352
  • 5
  • 19
  • Can you use the MySQL Server directly, without ODBC, and confirm the version? E.g. run this SQL query: `SELECT VERSION();` and tell us what it says? – Bill Karwin Jun 23 '21 at 14:58
  • @BillKarwin, yes, I can use mysql server directly. I can login to mysql directly `mysql -u username -puserpassword`, running `select version();` returns 8.0.25. I believed all the software I have installed are the newest since I just downloaded the newest and installed them recently. – XYZ Jun 23 '21 at 15:11

5 Answers5

1

Try to install and use mariadb-connector-odbc instead of mysql-connector-odbc.

Eric Aya
  • 69,473
  • 35
  • 181
  • 253
Allan Zeidler
  • 317
  • 2
  • 7
  • Any more explanation why mariadb-connector-odbc works, while mysql-connector-odbc does not? what is the difference between these two? – XYZ May 19 '22 at 12:52
1

It works for me. Install mariadb-connector-odbc.x86_64 instead of mysql-connector-odbc.

# yum list mariadb-connector-odbc.x86_64

mariadb-connector-odbc.x86_64  3.1.12-1.el8  @AppStream

# more /etc/odbcinst.ini
[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib64/libmaodbc.so
Setup=/usr/lib64/libodbcmyS.so
FileUsage=1

Connect test

# isql zabbix -vv
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit
# more /etc/odbc.ini
[zabbix]
Description    = Zabbix DB
Driver         = MYSQL
Server         = xx.xx.xx.xx
User           = xxxxxx
Password       = xxxxxx
Port           = 3306
Database       = xxxxxx
Flames
  • 11
  • 2
0

I have found a very useful post that can solve my problem: https://blog.zabbix.com/zabbix-agent-user-parameters/7910/

It does not solve the issue in my question "Cannot connect to ODBC DSN: [SQL_ERROR]:[08001][0][[unixODBC][MySQL][ODBC 8.0(w) Driver]Driver does not support server versions under 4.1.1]".

But my goal is to monitor the content in the database, as long as I can run my sql query and display the result on the zabbix frontend/web interface, that is enough for me.

The solution is to create shell script and with this shell script, run sql query and print out the sql query result.

  1. Create a shell script and define sql query and print the result
root@Mars:~# cat /usr/lib/zabbix/externalscripts/mysql_nruser.sh
mysql_nruser=$(echo "SELECT count(*) FROM user" | mysql mysql -uMyUserName -pMyPassWord)
echo $mysql_nruser
  1. Create a user parameter config file under the folder: etc/zabbix/zabbix_agentd.d
root@Mars:/etc/zabbix/zabbix_agentd.d# ls
userparameter_mysql.conf

  1. Within the userparameter_mysql.conf file, add

    UserParameter=mysql.nruser,/usr/lib/zabbix/externalscripts/mysql_nruser.sh

    This statement can either be added in the '/etc/zabbix/zabbix_agentd.conf` file.

  2. Create the items on the web interface/frontend and define the regular expression to filter out the content you are interested (details given in the post).

Hurrah! Now, you can monitor the content within database via sql query on the zabbix web interface/frontend.

XYZ
  • 352
  • 5
  • 19
0

I had the same problem, and the solution was to downgrade the version of ODBC I was using. I installed version 5.3 and it worked.

André Danelon
  • 1
  • 2
0

You could take look at zbxdb This is a tool I wrote a while ago to monitor sql databases using python. It is quite easy to add new versions for monitoring. As long as a python version for your driver is available, it will mostly just work.

If you have issues with this, just drop me a line, in GitHub.