[ZBX-22493] SQL errors after re-assigning the hosts to the proxy Created: 2023 Mar 11  Updated: 2024 Apr 10  Resolved: 2023 Nov 19

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Proxy (P)
Affects Version/s: 6.4.0
Fix Version/s: 5.0.40rc1, 6.0.24rc1, 6.4.9rc1, 7.0.0alpha8, 7.0 (plan)

Type: Problem report Priority: Trivial
Reporter: Volodymyr Mosiichuk Assignee: Andris Zeila
Resolution: Fixed Votes: 3
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

sqlite3

Attachments: Text File zabbix_proxy.log    
Issue Links:
Duplicate
Team: Team A
Sprint: Sprint 106 (Nov 2023)
Story Points: 1

 Description   

Steps to reproduce:

  1. Add the host with some inventory data (like hostname or coordinates).
  2. Assign it to the already configured proxy and wait for when the config will sync.
  3. Assign back the monitored host to the Zabbix server and wait when the configuration will sync.
  4. Assign the host again to the same Zabbix proxy.

Result:
See log file on the Zabbix proxy

  4774:20230311:154019.561 received configuration data from server at "10.60.0.110", datalen 7941
  4774:20230311:154019.602 [Z3005] query failed: [0] UNIQUE constraint failed: host_inventory.hostid [insert into host_inventory (hostid,type,type_full,name,alias,os,os_full,os_short,serialno_a,serialno_b,tag,asset_tag,macaddress_a,macaddress_b,hardware,hardware_full,software,software_full,software_app_a,software_app_b,software_app_c,software_app_d,software_app_e,contact,location,location_lat,location_lon,notes,chassis,model,hw_arch,vendor,contract_number,installer_name,deployment_status,url_a,url_b,url_c,host_networks,host_netmask,host_router,oob_ip,oob_netmask,oob_router,date_hw_purchase,date_hw_install,date_hw_expiry,date_hw_decomm,site_address_a,site_address_b,site_address_c,site_city,site_state,site_country,site_zip,site_rack,site_notes,poc_1_name,poc_1_email,poc_1_phone_a,poc_1_phone_b,poc_1_cell,poc_1_screen,poc_1_notes,poc_2_name,poc_2_email,poc_2_phone_a,poc_2_phone_b,poc_2_cell,poc_2_screen,poc_2_notes) values (10569,'','','rtr-core-01-hal1','','6.48.6','','','<SOME_VALUE>','','','','','','','','','','','','','','','','','<SOME_VALUE>','26.2852965661635','','','<SOME_VALUE>','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','');
insert into host_inventory (hostid,type,type_full,name,alias,os,os_full,os_short,serialno_a,serialno_b,tag,asset_tag,macaddress_a,macaddress_b,hardware,hardware_full,software,software_full,software_app_a,software_app_b,software_app_c,software_app_d,software_app_e,contact,location,location_lat,location_lon,notes,chassis,model,hw_arch,vendor,contract_number,installer_name,deployment_status,url_a,url_b,url_c,host_networks,host_netmask,host_router,oob_ip,oob_netmask,oob_router,date_hw_purchase,date_hw_install,date_hw_expiry,date_hw_decomm,site_address_a,site_address_b,site_address_c,site_city,site_state,site_country,site_zip,site_rack,site_notes,poc_1_name,poc_1_email,poc_1_phone_a,poc_1_phone_b,poc_1_cell,poc_1_screen,poc_1_notes,poc_2_name,poc_2_email,poc_2_phone_a,poc_2_phone_b,poc_2_cell,poc_2_screen,poc_2_notes) values (10571,'','','rtr-core-01-aws1','','6.48.5','','','','','','','','','','','','','','','','','','','','','','','','RouterOS CHR','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','');
]
  4774:20230311:154019.604 cannot process received configuration data from server at "10.60.0.110": cannot insert rows in table "host_inventory"
  4774:20230311:154029.712 received configuration data from server at "10.60.0.110", datalen 23449

Expected:
It shouldn't be any errors

The problem appears with the hosts that have an inventory filled in

 Comments   
Comment by Volodymyr Mosiichuk [ 2023 Mar 11 ]

This is the case when we have a couple of Zabbix proxies in one data center. During the proxy maintenance, we shift all monitored hosts to the slave proxy, and after a while re-assign them to the master proxy.

Seems like the proxy DB still contains inventory data, even though the monitored host is not assigned to the proxy anymore

Comment by Aigars Kadikis [ 2023 Mar 13 ]

Does the slave proxy is having different proxy name? Run on both proxies:

grep Hostname= /etc/zabbix/zabbix_proxy.conf
Comment by Volodymyr Mosiichuk [ 2023 Mar 13 ]

Sorry, perhaps I misled you with an additional configuration. They have different naming - zbx-proxy-01-wro1 and zbx-proxy-02-wro1. Both were added to the server as active proxies.

Actually, it can be reproduced with one proxy as well. I just did it with one proxy.

I've created a simple host with no templates assigned to it, set the inventory to Manual, and filled in field OS, then have done change "Monitored by proxy" for the host as follows:

None(first state) -> proxy -> None -> Proxy

Please wait a bit to sync server <-> proxy config or sync it manually for each save.

And received the following message in the proxy logs

  4774:20230313:150125.592 received configuration data from server at "10.60.0.110", datalen 1024
  4774:20230313:150125.601 [Z3005] query failed: [0] UNIQUE constraint failed: host_inventory.hostid [insert into host_inventory (hostid,type,type_full,name,alias,os,os_full,os_short,serialno_a,serialno_b,tag,asset_tag,macaddress_a,macaddress_b,hardware,hardware_full,software,software_full,software_app_a,software_app_b,software_app_c,software_app_d,software_app_e,contact,location,location_lat,location_lon,notes,chassis,model,hw_arch,vendor,contract_number,installer_name,deployment_status,url_a,url_b,url_c,host_networks,host_netmask,host_router,oob_ip,oob_netmask,oob_router,date_hw_purchase,date_hw_install,date_hw_expiry,date_hw_decomm,site_address_a,site_address_b,site_address_c,site_city,site_state,site_country,site_zip,site_rack,site_notes,poc_1_name,poc_1_email,poc_1_phone_a,poc_1_phone_b,poc_1_cell,poc_1_screen,poc_1_notes,poc_2_name,poc_2_email,poc_2_phone_a,poc_2_phone_b,poc_2_cell,poc_2_screen,poc_2_notes) values (10581,'','','','','Linux','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','');
]
  4774:20230313:150125.603 cannot process received configuration data from server at "10.60.0.110": cannot insert rows in table "host_inventory"
  4774:20230313:150135.730 received configuration data from server at "10.60.0.110", datalen 26583
  4774:20230313:150145.981 received configuration data from server at "10.60.0.110", datalen 210
Comment by Michal Czerniakiewicz [ 2023 Mar 29 ]

I do confirm the problem exist on 6.4.0 version. Replicated with Zabbix appliance.

  3957:20230329:005311.981 received configuration data from server at "192.168.1.141", datalen 33034
  3957:20230329:005311.983 [Z3005] query failed: [0] UNIQUE constraint failed: host_inventory.hostid [insert into host_inventory (hostid,type,type_full,name,alias,os,os_full,os_short,serialno_a,serialno_b,tag,asset_tag,macaddress_a,macaddress_b,hardware,hardware_full,software,software_full,software_app_a,software_app_b,software_app_c,software_app_d,software_app_e,contact,location,location_lat,location_lon,notes,chassis,model,hw_arch,vendor,contract_number,installer_name,deployment_status,url_a,url_b,url_c,host_networks,host_netmask,host_router,oob_ip,oob_netmask,oob_router,date_hw_purchase,date_hw_install,date_hw_expiry,date_hw_decomm,site_address_a,site_address_b,site_address_c,site_city,site_state,site_country,site_zip,site_rack,site_notes,poc_1_name,poc_1_email,poc_1_phone_a,poc_1_phone_b,poc_1_cell,poc_1_screen,poc_1_notes,poc_2_name,poc_2_email,poc_2_phone_a,poc_2_phone_b,poc_2_cell,poc_2_screen,poc_2_notes) values (10568,'','','appliance','','Linux version 4.18.0-372.32.1.el8_6.x86_64 (mockbuild@b40d1a07489940babe33b48a7599d7ae) (gcc version 8.5.0 20210514 (Red Hat 8.5','','','','','','','','','','','','','','','','','','','','56.95387','24.22067','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','');
]
  3957:20230329:005311.983 cannot process received configuration data from server at "192.168.1.141": cannot insert rows in table "host_inventory"
  3957:20230329:005322.007 received configuration data from server at "192.168.1.141", datalen 35078
  3956:20230329:005522.877 Got signal [signal:15(SIGTERM),sender_pid:4070,sender_uid:0,reason:0]. Exiting ...
  3969:20230329:005523.062 syncing history data in progress... 
  3969:20230329:005523.062 syncing history data done
  3963:20230329:005523.870 [1] thread stopped [preprocessing worker #1]
  3963:20230329:005523.870 [3] thread stopped [preprocessing worker #3]
  3963:20230329:005523.871 [2] thread stopped [preprocessing worker #2]
  3956:20230329:005523.888 Zabbix Proxy stopped. Zabbix 6.4.0 (revision 5b2736b6027).

zabbix_proxy.log

Comment by T.J. Yang [ 2023 Sep 26 ]

This bug exist on 7.0alpha4 version also. I had to delete the host and re-add and specify proxy server at first setting.

Comment by Andris Zeila [ 2023 Nov 17 ]

Released ZBX-22493 in:

  • pre-5.0.40rc1 2f1e62060f7
  • pre-6.0.24rc1 ca30a07380a
  • pre-6.4.9rc1 30aec03b839
  • pre-7.0.0alpha8 e0fdce6c22e
Generated at Wed May 14 07:42:03 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.