SQL Error attaching templates to hosts
Hello everybody
First some vitals:
For most hosts, this has worked perfectly, but a random few started erroring during the import with a long SQL error. So I manually added one, and got the same error. A bit of trial and error and I found it only throws the error when assigning the template. Manually addiong the template I got this SQL error:
Here's the pertinent SQL error:
I've no idea how we're getting a duplicate, we've never touched the DB other than to create it or upgrade it (per the docs). How can we fix this? I've got probably about 15-20 that have done this, and they're random within the list I imported. There's no duplicates, and indeed the API detects and errors on that anyway. There's no errors in the server logs, only the apache error log, and that's the same as the one above. Nominally everything is 'fine'.
Thanks in advance!
First some vitals:
- Zabbix server version 7.2.7
- Zabbix frontend version 7.2.7
- Server OS: RHEL 9.6
- Two front-ends in HA failover config (active/passive)
- Database: Percona XtraDB Cluster Ver 8.0 - 2x hosts running on RHEL 9.6 as a cluster
For most hosts, this has worked perfectly, but a random few started erroring during the import with a long SQL error. So I manually added one, and got the same error. A bit of trial and error and I found it only throws the error when assigning the template. Manually addiong the template I got this SQL error:
Code:
Error in query [INSERT INTO items (itemid,hostid,templateid,name,type,key_,value_type,units,history,trends,valuemapid,inventory_link,logtimefmt,description,status,authtype,username,password,params,timeout,delay,trapper_hosts,master_itemid,url,query_fields,request_method,post_type,posts,headers,status_codes,follow_redirects,retrieve_mode,output_format,http_proxy,verify_peer,verify_host,ssl_cert_file,ssl_key_file,ssl_key_password,allow_traps,ipmi_sensor,jmx_endpoint,snmp_oid,publickey,privatekey,flags,uuid,interfaceid) VALUES ('61656','10766','42455','Host name of Zabbix agent running','0','agent.hostname','1','','7d','0',NULL,'0','','','0','0','','','','','1h','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61657','10766','42456','Zabbix agent ping','0','agent.ping','3','','7d','365d','928','0','','The agent always returns \"1\" for this item. May be used in combination with `nodata()` for the availability check.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61658','10766','42484','Version of Zabbix agent running','0','agent.version','1','','7d','0',NULL,'0','','','0','0','','','','','1h','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61659','10766','42483','Cache bytes','0','perf_counter_en[\"\\Memory\\Cache Bytes\"]','3','B','7d','365d',NULL,'0','','Cache Bytes is the sum of the Memory\\\\System Cache Resident Bytes, Memory\\\\System Driver Resident Bytes, Memory\\\\System Code Resident Bytes, and Memory\\\\Pool Paged Resident Bytes counters. This counter displays the last observed value only; it is not an average.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61660','10766','42482','Free system page table entries','0','perf_counter_en[\"\\Memory\\Free System Page Table Entries\"]','3','','7d','365d',NULL,'0','','This indicates the number of page table entries not currently in use by the system. If the number is less than 5,000, there may be a memory leak or you running out of memory.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61661','10766','42481','Memory page faults per second','0','perf_counter_en[\"\\Memory\\Page Faults/sec\"]','0','','7d','365d',NULL,'0','','Page Faults/sec is the average number of pages faulted per second. It is measured in number of pages faulted per second because only one page is faulted in each fault operation, hence this is also equal to the number of page fault operations. This counter includes both hard faults (those that require disk access) and soft faults (where the faulted page is found elsewhere in physical memory.) Most processors can handle large numbers of soft faults without significant consequence. However, hard faults, which require disk access, can cause significant delays.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61662','10766','42480','Memory pages per second','0','perf_counter_en[\"\\Memory\\Pages/sec\"]','0','','7d','365d',NULL,'0','','This measures the rate at which pages are read from or written to disk to resolve hard page faults.\r\nIf the value is greater than 1,000, as a result of excessive paging, there may be a memory leak.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61663','10766','42479','Memory pool non-paged','0','perf_counter_en[\"\\Memory\\Pool Nonpaged Bytes\"]','3','B','7d','365d',NULL,'0','','This measures the size, in bytes, of the non-paged pool. This is an area of system memory for objects that cannot be written to disk but instead must remain in physical memory as long as they are allocated.\r\nThere is a possible memory leak if the value is greater than 175MB (or 100MB with the /3GB switch). Consequently, Event ID 2019 is recorded in the system event log.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61664','10766','42478','Used swap space in %','0','perf_counter_en[\"\\Paging file(_Total)\\% Usage\"]','0','%','7d','365d',NULL,'0','','The used space of swap volume/file in percent.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61665','10766','42477','CPU DPC time','0','perf_counter_en[\"\\Processor Information(_total)\\% DPC Time\"]','0','%','7d','365d',NULL,'0','','Processor DPC time is the time that a single processor spent receiving and servicing deferred procedure calls (DPCs). DPCs are interrupts that run at a lower priority than standard interrupts. `% DPC Time` is a component of `% Privileged Time` because DPCs are executed in privileged mode. If a high `% DPC Time` is sustained, there may be a processor bottleneck or an application or hardware related issue that can significantly diminish overall system performance.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61666','10766','42476','CPU interrupt time','0','perf_counter_en[\"\\Processor Information(_total)\\% Interrupt Time\"]','0','%','7d','365d',NULL,'0','','The processor information `% Interrupt Time` counter indicates how much time the processor spends handling hardware interrupts during sample intervals. It reflects the activity of devices like the system clock, mouse, disk drivers, and network cards. A value above 20% suggests possible hardware issues.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61667','10766','42475','CPU privileged time','0','perf_counter_en[\"\\Processor Information(_total)\\% Privileged Time\"]','0','%','7d','365d',NULL,'0','','The processor information `% Privileged Time` counter shows the percent of time that the processor is spent executing in Kernel (or Privileged) mode. Privileged mode includes services interrupts inside Interrupt Service Routines (ISRs), executing Deferred Procedure Calls (DPCs), Device Driver calls and other kernel-mode functions of the Windows Operating System.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61668','10766','42474','CPU user time','0','perf_counter_en[\"\\Processor Information(_total)\\% User Time\"]','0','%','7d','365d',NULL,'0','','The processor information `% User Time` counter shows the percent of time that the processor(s) is spent executing in User mode.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61669','10766','42473','Context switches per second','0','perf_counter_en[\"\\System\\Context Switches/sec\"]','0','','7d','365d',NULL,'0','','Context Switches/sec is the combined rate at which all processors on the computer are switched from one thread to another.\r\nContext switches occur when a running thread voluntarily relinquishes the processor, is preempted by a higher priority ready thread, or switches between user-mode and privileged (kernel) mode to use an Executive or subsystem service.\r\nIt is the sum of Thread\\\\Context Switches/sec for all threads running on all processors in the computer and is measured in numbers of switches.\r\nThere are context switch counters on the System and Thread objects. This counter displays the difference between the values observed in the last two samples, divided by the duration of the sample interval.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61670','10766','42472','CPU queue length','0','perf_counter_en[\"\\System\\Processor Queue Length\"]','0','','7d','365d',NULL,'0','','The Processor Queue Length shows the number of threads that are observed as delayed in the processor Ready Queue and are waiting to be executed.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61671','10766','42471','Number of threads','0','perf_counter_en[\"\\System\\Threads\"]','3','','7d','365d',NULL,'0','','The number of threads used by all running processes.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61672','10766','42470','Number of processes','0','proc.num[]','3','','7d','365d',NULL,'0','','The number of processes.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61673','10766','42469','CPU utilization','0','system.cpu.util','0','%','7d','365d',NULL,'0','','CPU utilization expressed in %.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61674','10766','42468','System name','0','system.hostname','1','','7d','0',NULL,'3','','The host name of the system.','0','0','','','','','1h','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61675','10766','42467','System local time','0','system.localtime','3','unixtime','7d','365d',NULL,'0','','The local system time of the host.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61676','10766','42466','Operating system architecture','0','system.sw.arch','1','','7d','0',NULL,'0','','The architecture of the operating system.','0','0','','','','','1h','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61677','10766','44634','Operating system','0','system.sw.os','1','','7d','0',NULL,'5','','','0','0','','','','','1h','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61678','10766','42465','Free swap space','15','system.swap.free','3','B','7d','365d',NULL,'0','','The free space of the swap volume/file expressed in bytes.','0','0','','','last(//system.swap.size[,total]) - last(//system.swap.size[,total]) / 100 * last(//perf_counter_en[\"\\Paging file(_Total)\\% Usage\"])','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','',NULL),('61679','10766','42464','Total swap space','0','system.swap.size[,total]','3','B','7d','365d',NULL,'0','','The total space of the swap volume/file expressed in bytes.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61680','10766','42463','System description','0','system.uname','1','','7d','0',NULL,'0','','System description of the host.','0','0','','','','','15m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61681','10766','42462','Uptime','0','system.uptime','3','uptime','7d','0',NULL,'0','','The system uptime expressed in the following format: \"N days, hh:mm:ss\".','0','0','','','','','30s','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61682','10766','44453','Get filesystems','0','vfs.fs.get','4','','0','0',NULL,'0','','The `vfs.fs.get` key acquires raw information set about the filesystems. Later to be extracted by preprocessing in dependent items.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61683','10766','42461','Total memory','0','vm.memory.size[total]','3','B','7d','365d',NULL,'0','','Total memory expressed in bytes.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61684','10766','42460','Used memory','0','vm.memory.size[used]','3','B','7d','365d',NULL,'0','','Used memory in bytes.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61685','10766','42459','Memory utilization','15','vm.memory.util','0','%','7d','365d',NULL,'0','','Memory utilization in %.','0','0','','','last(//vm.memory.size[used]) / last(//vm.memory.size[total]) * 100','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','',NULL),('61686','10766','42457','Number of cores','0','wmi.get[root/cimv2,\"Select NumberOfLogicalProcessors from Win32_ComputerSystem\"]','3','','7d','365d',NULL,'0','','The number of logical processors available on the computer.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61687','10766','42458','Windows: Network interfaces WMI get','0','wmi.getall[root\\cimv2,\"select Name,Description,NetConnectionID,Speed,AdapterTypeId,NetConnectionStatus,GUID from win32_networkadapter where PhysicalAdapter=True and NetConnectionStatus>0\"]','4','','0','0',NULL,'0','','Raw data of `win32_networkadapter.`','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','','159'),('61688','10766','42485','Zabbix agent availability','5','zabbix[host,agent,available]','3','','7d','365d','927','0','','Used for monitoring the availability status of the agent.','0','0','','','','','1m','',NULL,'','','0','0','','','200','1','0','0','','0','0','','','','0','','','','','','0','',NULL)] [Duplicate entry '61658' for key 'items.PRIMARY'] [zabbix.php:17 → require_once() → ZBase->run() → ZBase->processRequest() → CController->run() → CControllerHostUpdate->doAction() → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CHost->update() → CHost->updateForce() → CHostGeneral->updateTemplates() → CHostGeneral::linkTemplatesObjects() → CItem::linkTemplateObjects() → CItem::inherit() → CItem::inheritChunk() → CItem::createForce() → DB::insert() → DB::insertBatch() → DBexecute() → trigger_error() in include/db.inc.php:249]
Code:
[Duplicate entry '61658' for key 'items.PRIMARY']
Thanks in advance!
Comment