Problem with importing Templates - Errors in PostgreSQL

[PrisonHunter]

Hi!

I'm having trouble importing any template into my configuration

For example: https://github.com/zabbix/community-...p_nas_snmp.xml
I'm getting the error:

pg_query(): Query failed: BŁĄD: null value in column "query_fields" of relation "items" violates not-null constraint SZCZEG??Y: Niepoprawne ograniczenia wiersza (58196, 20, .1.3.6.1.4.1.24681.1.3.5.0, 10701, CPU temperature, CPU-TemperatureEX, 60, 90d, 365d, 0, 3, , C, , , null, null, , , 0, , , , , 0, null, CPU temperature in centigrade, 0, 7d, 0, , null, , , null, null, 200, 1, 0, , null, 0, 0, 0, , , , 0, 0, 0, 0, af0df8ffbe1d4786be2ce877adfb1215, 0, 2, 0). [zabbix.php:17 → require_once() → ZBase->run() → ZBase->processRequest() → CController->run() → CControllerPopupImport->doAction() → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CConfiguration->import() → CConfigurationImport->import() → CConfigurationImport->processItems() → CConfigurationImport->createItemsWithDependency() → CItem->create() → CItem::createForce() → DB::insert() → DB::insertBatch() → DBexecute() → pg_query() in include/db.inc.php:368][*]Error in query [INSERT INTO items (itemid,name,type,snmp_oid,delay,units,description ,uuid,value_type,history,trends,status,inventory_l ink,timeout,key_,hostid,valuemapid,flags,params) VALUES ('58196','CPU temperature','20','.1.3.6.1.4.1.24681.1.3.5.0','60 ','C','CPU temperature in centigrade','af0df8ffbe1d4786be2ce877adfb1215','3' ,'90d','365d','0','0','','CPU-TemperatureEX','10701',NULL,'0',''),('58197','Devi ce system name','20','.1.3.6.1.4.1.24681.1.3.13.0','3600','' ,'Host name','605bbae4f5cc4add948ec2d0e7ba53c5','4','7d', '0','0','0','','HostNameEX','10701',NULL,'0',''),( '58198','Device model name','20','.1.3.6.1.4.1.24681.1.3.12.0','10800',' ','','9ed2355e9de4454588b3fbb89b6b5b09','4','1d',' 0','0','0','','ModelNameEX','10701',NULL,'0',''),( '58199','Device firmware revision','20','.1.3.6.1.2.1.47.1.1.1.1.9.1','3600 ','','','9434585e656a4d07912221500b554bac','4','90 d','0','0','16','','PhysicalFirmwareRev','10701',N ULL,'0',''),('58200','Device hardware revision','20','.1.3.6.1.2.1.47.1.1.1.1.8.1','1080 0','','','ff7bd7486c284941968c4eb537e17518','4','1 d','0','0','14','','PhysicalHardwareRev','10701',N ULL,'0',''),('58201','Device software revision','20','.1.3.6.1.2.1.47.1.1.1.1.10.1','360 0','','','651f8c01027948a9be6ef63dc7ab021f','4','9 0d','0','0','0','','PhysicalSoftwareRev','10701',N ULL,'0',''),('58202','Device vendor name','20','.1.3.6.1.2.1.47.1.1.1.1.12.1','3600',' ','','347cf9b46a68416e96233dac88b08cf0','4','1d',' 0','0','31','','sysPhysicalMfgName','10701',NULL,' 0',''),('58203','CPU usage','20','.1.3.6.1.4.1.24681.1.3.1.0','60','%', 'System CPU usage','4077fd60261f416c801ac4b6127fa5ee','3','90d ','365d','0','0','','SystemCPU-UsageEX','10701',NULL,'0',''),('58204','System free memory','20','.1.3.6.1.4.1.24681.1.3.3.0','60','b' ,'','30e1525a8699402b844873117187ff7f','3','90d',' 365d','0','0','','SystemFreeMemEX','10701',NULL,'0 ',''),('58205','System free memory (%)','15','','60','%','','0033dee08a1f4424b779450d 36352adc','0','90d','365d','0','0','','SystemPFree MemEX','10701',NULL,'0','last(//SystemFreeMemEX)/(last(//SystemTotalMemEX)/100)'),('58206','System temperature','20','.1.3.6.1.4.1.24681.1.3.6.0','60 ','C','System temperature in centigrade','1489579e989847ed8a16a76552cc1e4d','3' ,'90d','365d','0','0','','SystemTemperatureEX','10 701',NULL,'0',''),('58207','System total memory','20','.1.3.6.1.4.1.24681.1.3.2.0','10800', 'b','','d6612660f23a4b9eb531538cef06ffda','3','1d' ,'365d','0','0','','SystemTotalMemEX','10701',NULL ,'0',''),('58208','System uptime','20','.1.3.6.1.2.1.25.1.1.0','60s','uptime ','','0a62a8a403524d59ac83e3bba03dae88','3','1d',' 365d','0','0','','sysUptime','10701',NULL,'0','')] [BŁĄD: null value in column "query_fields" of relation "items" violates not-null constraint SZCZEG??Y: Niepoprawne ograniczenia wiersza (58196, 20, .1.3.6.1.4.1.24681.1.3.5.0, 10701, CPU temperature, CPU-TemperatureEX, 60, 90d, 365d, 0, 3, , C, , , null, null, , , 0, , , , , 0, null, CPU temperature in centigrade, 0, 7d, 0, , null, , , null, null, 200, 1, 0, , null, 0, 0, 0, , , , 0, 0, 0, 0, af0df8ffbe1d4786be2ce877adfb1215, 0, 2, 0).][*]Wykonanie zapytania SQL nie udało się "INSERT INTO items (itemid,name,type,snmp_oid,delay,units,description ,uuid,value_type,history,trends,status,inventory_l ink,timeout,key_,hostid,valuemapid,flags,params) VALUES ('58196','CPU temperature','20','.1.3.6.1.4.1.24681.1.3.5.0','60 ','C','CPU temperature in centigrade','af0df8ffbe1d4786be2ce877adfb1215','3' ,'90d','365d','0','0','','CPU-TemperatureEX','10701',NULL,'0',''),('58197','Devi ce system name','20','.1.3.6.1.4.1.24681.1.3.13.0','3600','' ,'Host name','605bbae4f5cc4add948ec2d0e7ba53c5','4','7d', '0','0','0','','HostNameEX','10701',NULL,'0',''),( '58198','Device model name','20','.1.3.6.1.4.1.24681.1.3.12.0','10800',' ','','9ed2355e9de4454588b3fbb89b6b5b09','4','1d',' 0','0','0','','ModelNameEX','10701',NULL,'0',''),( '58199','Device firmware revision','20','.1.3.6.1.2.1.47.1.1.1.1.9.1','3600 ','','','9434585e656a4d07912221500b554bac','4','90 d','0','0','16','','PhysicalFirmwareRev','10701',N ULL,'0',''),('58200','Device hardware revision','20','.1.3.6.1.2.1.47.1.1.1.1.8.1','1080 0','','','ff7bd7486c284941968c4eb537e17518','4','1 d','0','0','14','','PhysicalHardwareRev','10701',N ULL,'0',''),('58201','Device software revision','20','.1.3.6.1.2.1.47.1.1.1.1.10.1','360 0','','','651f8c01027948a9be6ef63dc7ab021f','4','9 0d','0','0','0','','PhysicalSoftwareRev','10701',N ULL,'0',''),('58202','Device vendor name','20','.1.3.6.1.2.1.47.1.1.1.1.12.1','3600',' ','','347cf9b46a68416e96233dac88b08cf0','4','1d',' 0','0','31','','sysPhysicalMfgName','10701',NULL,' 0',''),('58203','CPU usage','20','.1.3.6.1.4.1.24681.1.3.1.0','60','%', 'System CPU usage','4077fd60261f416c801ac4b6127fa5ee','3','90d ','365d','0','0','','SystemCPU-UsageEX','10701',NULL,'0',''),('58204','System free memory','20','.1.3.6.1.4.1.24681.1.3.3.0','60','b' ,'','30e1525a8699402b844873117187ff7f','3','90d',' 365d','0','0','','SystemFreeMemEX','10701',NULL,'0 ',''),('58205','System free memory (%)','15','','60','%','','0033dee08a1f4424b779450d 36352adc','0','90d','365d','0','0','','SystemPFree MemEX','10701',NULL,'0','last(//SystemFreeMemEX)/(last(//SystemTotalMemEX)/100)'),('58206','System temperature','20','.1.3.6.1.4.1.24681.1.3.6.0','60 ','C','System temperature in centigrade','1489579e989847ed8a16a76552cc1e4d','3' ,'90d','365d','0','0','','SystemTemperatureEX','10 701',NULL,'0',''),('58207','System total memory','20','.1.3.6.1.4.1.24681.1.3.2.0','10800', 'b','','d6612660f23a4b9eb531538cef06ffda','3','1d' ,'365d','0','0','','SystemTotalMemEX','10701',NULL ,'0',''),('58208','System uptime','20','.1.3.6.1.2.1.25.1.1.0','60s','uptime ','','0a62a8a403524d59ac83e3bba03dae88','3','1d',' 365d','0','0','','sysUptime','10701',NULL,'0','')" .

I'll just add, because it's definitely crucial, that a few months ago I converted my database and switched from MySQL to PostgreSQL.

Everything else is working fine.

Please help and advise me on what I should change to fix importing

[PrisonHunter]

Thank you for your reply! Regarding those spaces, they only appeared in HTTP.
They weren't present in the logs.

I decided to remove the "not null" constraint in four columns of the "items" table:

ALTER TABLE items ALTER COLUMN query_fields DROP NOT NULL;
ALTER TABLE items ALTER COLUMN posts DROP NOT NULL;
ALTER TABLE items ALTER COLUMN headers DROP NOT NULL;
ALTER TABLE items ALTER COLUMN params DROP NOT NULL;

I don't know if this is the correct solution to the problem, but the import started working fine.
I managed to import several templates and they work great!

[PrisonHunter]

I forgot about this

Debian: 13.6
Zabbix: 7.0.28-1+debian13
PostgreSQL: 18+291.pgdg13+1

[Brambo]

I can only tell you about what i see in the error message as I don't have any experience in migration.
null value in column query_fields, of relation ,items, violates not-null constraint.

Just to verify as you try to import something, is this template coming from Zabbix 6.x or higher version system or something downloaded and maybe build on a older version?
And to verify if that is the case download a 7.x template from the zabbix repo and look if that import works fine

BTW not sure why those random space are in you provide error message (maybe a zabbix thing or actual :? )

[PrisonHunter]

Thank you for your reply! Regarding those spaces, they only appeared in HTTP.
They weren't present in the logs.

I decided to remove the "not null" constraint in four columns of the "items" table:

ALTER TABLE items ALTER COLUMN query_fields DROP NOT NULL;
ALTER TABLE items ALTER COLUMN posts DROP NOT NULL;
ALTER TABLE items ALTER COLUMN headers DROP NOT NULL;
ALTER TABLE items ALTER COLUMN params DROP NOT NULL;

I don't know if this is the correct solution to the problem, but the import started working fine.
I managed to import several templates and they work great!