MySQL – Resolving ODBC Item Query Causing Zabbix Server to Stop

I'am new to Zabbix. Today I've tried to add a SQL item (Database monitoring) to a host without success.

Without this SQL item, the Zabbix server runs fine. I check this with service zabbix-server status.

After I enable the SQL item, hit Check now for it, and run service zabbix-server status again, I've got an error:

 zabbix-server.service - Zabbix Server
   Loaded: loaded (/lib/systemd/system/zabbix-server.service; disabled; vendor preset: enabled)
   Active: active (running) since Fri 2018-11-09 15:41:10 CET; 1s ago
  Process: 330 ExecStop=/bin/kill -SIGTERM $MAINPID (code=exited, status=1/FAILURE)
  Process: 335 ExecStart=/usr/sbin/zabbix_server -c $CONFFILE (code=exited, status=0/SUCCESS)
  ...

You can see that process 330 reports status=1/FAILURE.

After I hit Check now the Zabbix frontend pops up with Request sent successfully. But looking for this item in Monitoring > Latest data there is no value.

The item is configured as:

Type: Database monitor
Key: db.odbc.select[num-of-events,backend]
User name: (empty)
Password: (empty)
SQL query: select 1;
Type of information: Numeric (unsigned)

The ODBC data source backend seems to be working fine:

$ isql backend
> +---------------------------------------+
> | Connected!                            |
> |                                       |
> | sql-statement                         |
> | help [tablename]                      |
> | quit                                  |
> |                                       |
> +---------------------------------------+
>  SQL> select 1;
> +---------------------+
> | 1                   |
> +---------------------+
> | 1                   |
> +---------------------+
> SQLRowCount returns 1
> 1 rows fetched

Here is my ODBC config:

/etc/odbcinst.ini

[mysql]
Description = ODBC for MySQL
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8S.so
FileUsage = 1

/etc/odbc.ini

[ODBC Data Sources]
backend  = MySQL unicode backend DSN

[backend]
Driver = mysql
Decription = Connector/ODBC Driver to Backend DB
Server = localhost
Port = 3306
User = ???
Password = ???
Database = backend
OPTION = 3
SOCKET = /var/run/mysqld/mysqld.sock

Zabbix is running on Debian Stretch.
Zabbix server version is 4.0.1.
MySQL version is 10.1.26-MariaDB-0+deb9u1

Any idea why the server crashes?

Edit: There is no difference when using the ANSI driver libmyodbc8a.so instead of the Unicode driver libmyodbc8w.so.

Edit 2: Tested also with ANSI and Unicode version mysql-connector-odbc-5.3.11-linux-debian9-x86-64bit without success.

zabbix-server.log tells the error

 10002:20181109:170635.268 Got signal [signal:11(SIGSEGV),reason:1,refaddr:0x60]. Crashing ...
 10002:20181109:170635.269 ====== Fatal information: ======
 ...
 10002:20181109:170635.269 === Backtrace: ===
 10002:20181109:170635.270 27: /usr/sbin/zabbix_server: poller #2 [got 1 values in 0.004743 sec, getting values](zbx_backtrace+0x35) [0x55a008633b24]
 10002:20181109:170635.270 26: /usr/sbin/zabbix_server: poller #2 [got 1 values in 0.004743 sec, getting values](zbx_log_fatal_info+0x119) [0x55a008633d38]
 10002:20181109:170635.270 25: /usr/sbin/zabbix_server: poller #2 [got 1 values in 0.004743 sec, getting values](+0xf0060) [0x55a008634060]
 10002:20181109:170635.270 24: /lib/x86_64-linux-gnu/libpthread.so.0(+0x110c0) [0x7f1e2039a0c0]
 10002:20181109:170635.270 23: /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so(+0x79e4d) [0x7f1e12b3ee4d]
 10002:20181109:170635.270 22: /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so(_Z22add_compiled_collationP12CHARSET_INFO+0x25) [0x7f1e12b3f995]
 10002:20181109:170635.270 21: /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so(_Z22init_compiled_charsetsi+0x15) [0x7f1e12b8eda5]
 10002:20181109:170635.270 20: /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so(+0x7918f) [0x7f1e12b3e18f]
 10002:20181109:170635.270 19: /lib/x86_64-linux-gnu/libpthread.so.0(+0xe739) [0x7f1e20397739]
 10002:20181109:170635.270 18: /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so(_ZSt9call_onceIRFvvEJEEvRSt9once_flagOT_DpOT0_+0x5a) [0x7f1e12b3dcea]
 10002:20181109:170635.270 17: /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so(_Z22my_charset_get_by_nameP17MY_CHARSET_LOADERPKcji+0x2a) [0x7f1e12b3e67a]
 10002:20181109:170635.270 16: /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so(_Z21get_charset_by_csnamePKcji+0x3b) [0x7f1e12b3e70b]
 10002:20181109:170635.270 15: /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so(myodbc_init+0x156) [0x7f1e12b24951]
 10002:20181109:170635.270 14: /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so(my_SQLAllocEnv+0x1c) [0x7f1e12b2825b]
 10002:20181109:170635.270 13: /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so(SQLAllocHandle+0x60) [0x7f1e12b29326]
 10002:20181109:170635.271 12: /usr/lib/x86_64-linux-gnu/libodbc.so.2(+0xdbb7) [0x7f1e1f43dbb7]
 10002:20181109:170635.271 11: /usr/lib/x86_64-linux-gnu/libodbc.so.2(SQLConnect+0x217) [0x7f1e1f43fdc7]
 10002:20181109:170635.271 10: /usr/sbin/zabbix_server: poller #2 [got 1 values in 0.004743 sec, getting values](zbx_odbc_connect+0x1ac) [0x55a0085c697b]
 10002:20181109:170635.271 9: /usr/sbin/zabbix_server: poller #2 [got 1 values in 0.004743 sec, getting values](get_value_db+0x22a) [0x55a00859d132]
 10002:20181109:170635.271 8: /usr/sbin/zabbix_server: poller #2 [got 1 values in 0.004743 sec, getting values](+0x4968d) [0x55a00858d68d]
 10002:20181109:170635.271 7: /usr/sbin/zabbix_server: poller #2 [got 1 values in 0.004743 sec, getting values](+0x4b950) [0x55a00858f950]
 10002:20181109:170635.271 6: /usr/sbin/zabbix_server: poller #2 [got 1 values in 0.004743 sec, getting values](poller_thread+0x192) [0x55a0085909d4]
 10002:20181109:170635.271 5: /usr/sbin/zabbix_server: poller #2 [got 1 values in 0.004743 sec, getting values](zbx_thread_start+0x32) [0x55a0086411de]
 10002:20181109:170635.271 4: /usr/sbin/zabbix_server: poller #2 [got 1 values in 0.004743 sec, getting values](MAIN_ZABBIX_ENTRY+0x9ac) [0x55a00857bcee]
 10002:20181109:170635.271 3: /usr/sbin/zabbix_server: poller #2 [got 1 values in 0.004743 sec, getting values](daemon_start+0x315) [0x55a008633346]
 10002:20181109:170635.271 2: /usr/sbin/zabbix_server: poller #2 [got 1 values in 0.004743 sec, getting values](main+0x2f6) [0x55a00857b340]
 10002:20181109:170635.271 1: /lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xf1) [0x7f1e1d1de2e1]
 10002:20181109:170635.271 0: /usr/sbin/zabbix_server: poller #2 [got 1 values in 0.004743 sec, getting values](_start+0x2a) [0x55a00857a51a]
 10002:20181109:170635.271 === Memory map: ===
 ...

Edit 3: Tested with mysql-connector-odbc-5.1.13-linux-debian6.0-x86-64bit.tar.gz and no success.
This failure seems related to https://support.zabbix.com/browse/ZBX-7665 and https://bugs.mysql.com/bug.php?id=73709

Best Answer

I've found a solution by using MariaDB ODBC connector instead of the MySQL connector.

Download https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.0.6/ I use debian-x86_64.tar.gz
Unpack it tar -xvzf mariadb-connector-odbc-3.0.6-ga-debian-x86_64.tar.gz
Copy cp lib/libmaodbc.so /usr/lib/

Add the following lines to /etc/odbcinst.ini

[mariadb]
Description = ODBC for MariaDB
Driver = /usr/lib/libmaodbc.so

Edit you DNS in /etc/odbc.ini
```
Driver = mariadb
Socket = /var/run/mysqld/mysqld.sock
```
The last line is also important, otherwise the connector looks for /tmp/mysqld.sock.

This works on Debian Jessie (8) but not with Debian Stretch (9). If you run ldd libmaodbc.so you see that the MariaDB ODBC connector depends on libssl.so.1.0.0 but Stretch have only libssl.so.1.0.2 and libssl.so.1.1 installed.

Best Answer

Related Solutions

Mysql – How Do I Configure OpenLDAP to use MySQL (Ubuntu 9.04)

Mysql – thesql with innodb_file_per_table – cannot see any .ibd files

Related Topic