MySQL 查詢中的 ODBC 項導致 Zabbix 伺服器停止

我是 Zabbix 的新手。今天我嘗試向主機添加一個 SQL 項（數據庫監控），但沒有成功。

沒有這個 SQL 項，Zabbix 伺服器執行良好。我用service zabbix-server status.

啟用 SQL 項後，點擊立即檢查，然後service zabbix-server status再次執行，我遇到了錯誤：

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)
 ...

您可以看到程序 330 報告status=1/FAILURE。

在我點擊Check now後，Zabbix 前端會彈出Request sent successfully。但是在監控 > 最新數據中尋找這個項目沒有任何價值。

該項目配置為：

• 類型：數據庫監視器
• 鍵：db.odbc.select$$ num-of-events,backend $$
• 使用者名：（空）
• 密碼：（空）
• SQL查詢：select 1;
• 資訊類型：數字（無符號）

ODBC 數據源後端似乎工作正常：

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

這是我的 ODBC 配置：

/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 在 Debian Stretch 上執行。Zabbix 伺服器版本為 4.0.1。MySQL 版本為 10.1.26-MariaDB-0+deb9u1

知道伺服器崩潰的原因嗎？

編輯：使用 ANSI 驅動程序 libmyodbc8a.so 而不是 Unicode 驅動程序 libmyodbc8w.so 時沒有區別。

編輯 2：還使用 ANSI 和 Unicode 版本 mysql-connector-odbc-5.3.11-linux-debian9-x86-64bit 進行了測試，但沒有成功。

zabbix-server.log告訴錯誤

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: ===
...

編輯 3：用 mysql-connector-odbc-5.1.13-linux-debian6.0-x86-64bit.tar.gz 測試，但沒有成功。此故障似乎與https://support.zabbix.com/browse/ZBX-7665和https://bugs.mysql.com/bug.php?id=73709有關

我通過使用 MariaDB ODBC 連接器而不是 MySQL 連接器找到了解決方案。

1. 下載https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.0.6/我用的是debian-x86_64.tar.gz
2. 打開包裝tar -xvzf mariadb-connector-odbc-3.0.6-ga-debian-x86_64.tar.gz
3. 複製cp lib/libmaodbc.so /usr/lib/
4. 將以下行添加到**/etc/odbcinst.ini**

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

5. 在/etc/odbc.ini中編輯您的 DNS

Driver = mariadb
Socket = /var/run/mysqld/mysqld.sock

最後一行也很重要，否則連接器會查找 /tmp/mysqld.sock。

這適用於 Debian Jessie (8)，但不適用於 Debian Stretch (9)。如果您執行ldd libmaodbc.so，您會看到 MariaDB ODBC 連接器依賴於 libssl.so.1.0.0，但 Stretch 僅安裝了 libssl.so.1.0.2 和 libssl.so.1.1。

引用自：https://serverfault.com/questions/939330