Can't import template because: column "name_upper" of relation "hosts" does not exist

[franZabbixFT]

pg_query(): Query failed: ERROR: column "name_upper" of relation "hosts" does not exist
LINE 1: update hosts set name_upper=upper(name) ^ QUERY: update hosts set name_upper=upper(name) where hostid=new.hostid CONTEXT: PL/pgSQL function hosts_name_upper_upper() line 3 at SQL statement [zabbix.php:22 → 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->processTemplates() → CTemplateImporter->import() → CTemplate->create() → DB::insert() → DB::insertBatch() → DBexecute() → pg_query() in include/db.inc.php:373][*]
Error in query [INSERT INTO hosts (hostid,uuid,host,name,description,status) VALUES ('10595','fa664cb5405943759507ae2835e56ff7','RR - OLT V-SOLUTIONS','RR - OLT V-SOLUTIONS','','3')] [ERROR: column "name_upper" of relation "hosts" does not exist LINE 1: update hosts set name_upper=upper(name) ^ QUERY: update hosts set name_upper=upper(name) where hostid=new.hostid CONTEXT: PL/pgSQL function hosts_name_upper_upper() line 3 at SQL statement][*]SQL statement execution has failed "INSERT INTO hosts (hostid,uuid,host,name,description,status) VALUES ('10595','fa664cb5405943759507ae2835e56ff7','RR - OLT V-SOLUTIONS','RR - OLT V-SOLUTIONS','','3')".

And in tail -f /var/log/zabbix/zabbix_server.log I can see:

25523:20230117:035101.587 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: column "name_upper" of relation "items" does not exist

LINE 1: update items set name_upper=upper(name)

^

QUERY: update items set name_upper=upper(name)

where itemid=new.itemid

[cyber]

Does your DB version match your Zabbix version?

[franZabbixFT]

Hi thanks for answering this is my PostgreSQL version:

psql (PostgreSQL) 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1)

And zabbix-server --version:

zabbix_server (Zabbix) 6.2.6

Revision 6981d8b729a 5 December 2022, compilation time: Dec  5 2022 08:44:25
​

[cyber]

I was more referring to the db schema version... Error says you don't have specific table columns... Just verify, is it really so...

[wooch]

Hello everyone, I have the same problem after migration from 4.2.1 to 6.2.7.
Log contains these strings:

301283:20230307:064750.436 using configuration file: /etc/zabbix/zabbix_server.conf
301283:20230307:064751.733 current database version (mandatory/optional): 06020000/06020014
301283:20230307:064751.733 required mandatory version: 06020000
301283:20230307:064752.034 database could be upgraded to use primary keys in history tables
301283:20230307:064752.082 database is not upgraded to use double precision values
​

But I cannot change anything in hosts or templates.

[cyber]

Same? your log looks absolutely OK. Just couple of suggestions to apply patches for primary keys and double precision...

https://www.zabbix.com/documentation/6.2/en/manual/appendix/install/db_primary_keys

And there is a double.sql script in install package, which updates trends table also...

[wooch]

Same? your log looks absolutely OK. Just couple of suggestions to apply patches for primary keys and double precision...
https://www.zabbix.com/documentation...b_primary_keys

And there is a double.sql script in install package, which updates trends table also...

Thank you for a quick reply. They looks ok but don't work.
I couldn't see the column "name_upper" at the table "host" so I edited a script from the sources (schema.sql):

ALTER TABLE hosts
ADD COLUMN IF NOT EXISTS        hostid                   bigint                                    NOT NULL,
ADD COLUMN IF NOT EXISTS        proxy_hostid             bigint                                    NULL,
ADD COLUMN IF NOT EXISTS        host                     varchar(128)    DEFAULT ''                NOT NULL,
ADD COLUMN IF NOT EXISTS        status                   integer         DEFAULT '0'               NOT NULL,
ADD COLUMN IF NOT EXISTS        ipmi_authtype            integer         DEFAULT '-1'              NOT NULL,
ADD COLUMN IF NOT EXISTS        ipmi_privilege           integer         DEFAULT '2'               NOT NULL,
ADD COLUMN IF NOT EXISTS        ipmi_username            varchar(16)     DEFAULT ''                NOT NULL,
ADD COLUMN IF NOT EXISTS        ipmi_password            varchar(20)     DEFAULT ''                NOT NULL,
ADD COLUMN IF NOT EXISTS        maintenanceid            bigint                                    NULL,
ADD COLUMN IF NOT EXISTS        maintenance_status       integer         DEFAULT '0'               NOT NULL,
ADD COLUMN IF NOT EXISTS        maintenance_type         integer         DEFAULT '0'               NOT NULL,
ADD COLUMN IF NOT EXISTS        maintenance_from         integer         DEFAULT '0'               NOT NULL,
ADD COLUMN IF NOT EXISTS        name                     varchar(128)    DEFAULT ''                NOT NULL,
ADD COLUMN IF NOT EXISTS       flags                    integer         DEFAULT '0'               NOT NULL,
ADD COLUMN IF NOT EXISTS        templateid               bigint                                    NULL,
ADD COLUMN IF NOT EXISTS        description              text            DEFAULT ''                NOT NULL,
ADD COLUMN IF NOT EXISTS        tls_connect              integer         DEFAULT '1'               NOT NULL,
ADD COLUMN IF NOT EXISTS        tls_accept               integer         DEFAULT '1'               NOT NULL,
ADD COLUMN IF NOT EXISTS        tls_issuer               varchar(1024)   DEFAULT ''                NOT NULL,
ADD COLUMN IF NOT EXISTS        tls_subject              varchar(1024)   DEFAULT ''                NOT NULL,
ADD COLUMN IF NOT EXISTS        tls_psk_identity         varchar(128)    DEFAULT ''                NOT NULL,
ADD COLUMN IF NOT EXISTS        tls_psk                  varchar(512)    DEFAULT ''                NOT NULL,
ADD COLUMN IF NOT EXISTS        proxy_address            varchar(255)    DEFAULT ''                NOT NULL,
ADD COLUMN IF NOT EXISTS        auto_compress            integer         DEFAULT '1'               NOT NULL,
ADD COLUMN IF NOT EXISTS        discover                 integer         DEFAULT '0'               NOT NULL,
ADD COLUMN IF NOT EXISTS        custom_interfaces        integer         DEFAULT '0'               NOT NULL,
ADD COLUMN IF NOT EXISTS        uuid                     varchar(32)     DEFAULT ''                NOT NULL,
ADD COLUMN IF NOT EXISTS        name_upper               varchar(128)    DEFAULT ''  ​               NOT NULL;

Now I can edit hosts, but I'm not sure is it correct to leave the column empty, and should I do the same with other tables?

[cyber]

There is something wrong with your installation then... If you really installed 6.2.7, then you have to have that column in your db schema... It is definitely present in schema sql for 6.2.7.. And also should appear, if your upgrade procedures went correctly. Do you have logs at hand to look for the first startup of your 6.2.7 server? it should contain all the info about upgrade process... If there is an error somewhere, you need to resolve it...

As you stepped into older topic, maybe you can provide a bit background of your system... OS versions etc... Only thing we know here is, that you attempted upgrade from 4.2.1 to 6.2.7.. Too many empty variables in this equation..

[wooch]

My migration path was Centos 6 + mysql -> postgres 12 on the same server, backup postgres db -> Ubuntu 22.04 + Postgres 14.
Now I can see some errors during db upgrade:

 68531:20230306:113957.792 starting automatic database upgrade
 68531:20230306:113957.799 completed 4% of database upgrade
 68531:20230306:113957.806 completed 9% of database upgrade
 68531:20230306:113957.811 completed 13% of database upgrade
 68531:20230306:113957.822 completed 18% of database upgrade
 68531:20230306:113957.827 completed 22% of database upgrade
 68531:20230306:113957.832 completed 27% of database upgrade
 68531:20230306:113957.838 completed 31% of database upgrade
 68531:20230306:113957.842 completed 36% of database upgrade
 68531:20230306:113957.848 completed 40% of database upgrade
 68531:20230306:113957.853 completed 45% of database upgrade
 68531:20230306:113957.859 completed 50% of database upgrade
 68531:20230306:113957.865 hosts_name_upper_update trigger for table "hosts" already exists, skipping patch of adding "name_upper" column to "hosts" table
 68531:20230306:113957.870 completed 54% of database upgrade
 68531:20230306:113957.871 hosts_name_upper_update trigger for table "hosts" already exists, skipping patch of adding index to "name_upper" column
 68531:20230306:113957.875 completed 59% of database upgrade
 68531:20230306:113957.875 hosts_name_upper_update trigger for table "hosts" already exists, skipping patch of updating "name_upper" column
 68531:20230306:113957.880 completed 63% of database upgrade
 68531:20230306:113957.881 hosts_name_upper_insert trigger for table "hosts" already exists, skipping patch of adding it to "hosts" table
 68531:20230306:113957.885 completed 68% of database upgrade
 68531:20230306:113957.886 hosts_name_upper_update trigger for table "hosts" already exists, skipping patch of adding it to "hosts" table
 68531:20230306:113957.890 completed 72% of database upgrade
 68531:20230306:113957.891 items_name_upper_update trigger for table "items" already exists, skipping patch of adding "name_upper" column to "items" table
 68531:20230306:113957.895 completed 77% of database upgrade
 68531:20230306:113957.897 items_name_upper_update trigger for table "items" already exists, skipping patch of adding index to "name_upper" column
 68531:20230306:113957.901 completed 81% of database upgrade
 68531:20230306:113957.902 items_name_upper_update trigger for table "items" already exists, skipping patch of updating "name_upper" column
 68531:20230306:113957.906 completed 86% of database upgrade
 68531:20230306:113957.907 items_name_upper_insert trigger for table "items" already exists, skipping patch of adding it to "items" table
 68531:20230306:113957.912 completed 90% of database upgrade
 68531:20230306:113957.912 items_name_upper_update trigger for table "items" already exists, skipping patch of adding it to "items" table
 68531:20230306:113957.917 completed 95% of database upgrade
 68531:20230306:113957.922 completed 100% of database upgrade
 68531:20230306:113957.923 database upgrade fully completed

but they are strange...
Anyway I added these columns to the tables and it works for me.

[cyber]

even the mysql to PG conversion... Never done that..

I would say, that this kind of errors in logs should not end up with "db upgrade fully completed". it should break, as it ended up with non-usable system.. Or there is some logic error in patch. Seems it decides based on trigger existence, if it needs to create columns and add triggers... but how your db already has those triggers... I cannot say...

[wooch]

Just for those who will read it later.
Adding columns to the table is not enough: you must fill it out like this:

UPDATE hosts SET name_upper=name;
update hosts set name_upper = Upper(name_upper);

UPDATE items SET name_upper=name;
update items set name_upper = Upper(name_upper);​

As I figured out it relates to search and filters - they work using this column.