zabbix使用tidb做为后端数据库时，添加主机报错

🪐 TiDB 技术问题
1

我们编码编译安装的zabbix4.0.5版本，使用tidbv4.0.0版本做为数据库存储数据

zabbix在安装过程中一切顺利，但是在添加主机时报错：如下：

[Details ](javascript:void(0))Cannot add host

* Undefined offset: 10264 [hosts.php:667 → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CHost->create() → CHost->massAdd() → CHostGeneral->massAdd() → CHostGeneral->link() → CItemPrototype->syncTemplates() → CItemGeneral->inherit() → CItemGeneral->_inherit() → CItemGeneral->prepareInheritedItems() in include/classes/api/services/CItemGeneral.php:1058]
* Undefined offset: 10264 [hosts.php:667 → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CHost->create() → CHost->massAdd() → CHostGeneral->massAdd() → CHostGeneral->link() → CItemPrototype->syncTemplates() → CItemGeneral->inherit() → CItemGeneral->_inherit() → CItemGeneral->prepareInheritedItems() in include/classes/api/services/CItemGeneral.php:1058]
* Undefined offset: 10264 [hosts.php:667 → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CHost->create() → CHost->massAdd() → CHostGeneral->massAdd() → CHostGeneral->link() → CItemPrototype->syncTemplates() → CItemGeneral->inherit() → CItemGeneral->_inherit() → CItemGeneral->prepareInheritedItems() in include/classes/api/services/CItemGeneral.php:1058]
* Undefined offset: 10264 [hosts.php:667 → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CHost->create() → CHost->massAdd() → CHostGeneral->massAdd() → CHostGeneral->link() → CItemPrototype->syncTemplates() → CItemGeneral->inherit() → CItemGeneral->_inherit() → CItemGeneral->prepareInheritedItems() in include/classes/api/services/CItemGeneral.php:1058]
* Undefined offset: 10264 [hosts.php:667 → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CHost->create() → CHost->massAdd() → CHostGeneral->massAdd() → CHostGeneral->link() → CItemPrototype->syncTemplates() → CItemGeneral->inherit() → CItemGeneral->_inherit() → CItemGeneral->prepareInheritedItems() in include/classes/api/services/CItemGeneral.php:1058]
* Undefined offset: 10264 [hosts.php:667 → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CHost->create() → CHost->massAdd() → CHostGeneral->massAdd() → CHostGeneral->link() → CItemPrototype->syncTemplates() → CItemGeneral->inherit() → CItemGeneral->_inherit() → CItemGeneral->prepareInheritedItems() in include/classes/api/services/CItemGeneral.php:1058]
* Undefined offset: 10264 [hosts.php:667 → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CHost->create() → CHost->massAdd() → CHostGeneral->massAdd() → CHostGeneral->link() → CItemPrototype->syncTemplates() → CItemGeneral->inherit() → CItemGeneral->_inherit() → CItemGeneral->prepareInheritedItems() in include/classes/api/services/CItemGeneral.php:1058]
* Field "parent_itemid" cannot be set to NULL.
2

我好像知道原因了，error这个是关键字或是保留字

image
image1360×454 20.4 KB

3

你好，

此问题应该是 zabbix 方面的错误，但是在 3.4.15 已经解决了，看下下面的链接是否能帮到你，也希望把排查结果反馈到帖子中

zabbix 会有类似的问题，但是应该和上面的报错不是很相符，先查下 zabbix 方便有木有解决办法，看是不是 tidb 与 mysql 方面有不兼容的地方

4

10559:20200623:165540.512 [Z3005] query failed: [1064] You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 356 near “error,snmp_error,ipmi_error,jmx_error,tls_connect,tls_accept,proxy_address,auto_compress,maintenanceid from hosts where status in (0,1,5,6) and flags<>2” [select hostid,proxy_hostid,host,ipmi_authtype,ipmi_privilege,ipmi_username,ipmi_password,maintenance_status,maintenance_type,maintenance_from,errors_from,available,disable_until,snmp_errors_from,snmp_available,snmp_disable_until,ipmi_errors_from,ipmi_available,ipmi_disable_until,jmx_errors_from,jmx_available,jmx_disable_until,status,name,lastaccess,error,snmp_error,ipmi_error,jmx_error,tls_connect,tls_accept,proxy_address,auto_compress,maintenanceid from hosts where status in (0,1,5,6) and flags<>2]
是不是因为这个sql执行过程中报错，而Field “parent_itemid” 的值取自这个sql的结果，所以添加不上主机？

5

这个sql里包含了 error，error是关键字，试着加上`` 反引号，看看能否成功

6

亲，你是如何解决的，我也遇到这个问题了

7

error 字段问题只能修改 zabbix 源码和数据库结构了，目前还在优化中，优先级不高，另外 zabbix 外部数据库的结构可能需要外键，而且这个貌似依赖挺重的，可以关注下