[ZBX-19450] Upgrade 5.2 -> 5.4 Created: 2021 May 25  Updated: 2021 Dec 15

Status: Open
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: 5.4.0
Fix Version/s: None

Type: Problem report Priority: Trivial
Reporter: Florian Requardt Assignee: Arturs Lontons
Resolution: Unresolved Votes: 0
Labels: database, update, zabbix-server-pgsql
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Debian GNU/Linux 10 4.19.160-2
PostgreSQL 12.7
timescaledb-2.2.1~debian10

Attachments: File zabbixDB.sql    

 Description   

Steps to reproduce:

  1. Have zabbix Server 5.2.6 installed
  2. Update to 5.4.0 via PPA-sources
  3. Find message in errorlog

    [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: foreign key constraint "c_items_3" cannot be implementedDETAIL: Key columns "valuemapid" and "valuemapid" are of incompatible types: numeric and bigint. '[alter table items add constraint c_items_3 foreign key (valuemapid) references valuemap (valuemapid)]

    database upgrade failed

  4. Fixing this issue by switching valuemap.valuemapid from BIGINT to NUMERIC
  5. Find next message in errorlog:

    [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: index "users_1" does not exist [drop index users_1]

    database upgrade failed

 Comments   
Comment by Dmitry Krupornitsky [ 2021 Jul 08 ]

Hello, 

Unfortunately I was not able to reproduce this issue. Did you upgrade from version prior to 5.2 (like 4.*) so that your database had several upgrades? Any specific settings?

Usually minor version upgrade comes with no problem.

Comment by Florian Requardt [ 2021 Jul 09 ]

The concerning upgrade step was from 5.2.6 to 5.4.0.

We are using zabbix since v2.2 and have been constantly upgrading though the versions. With the upgrade from 4.x to 5.x I furthermore switched from a partitioned MySQL to PostgreSQL with timescaleDB.

Comment by Florian Requardt [ 2021 Jul 12 ]

As this issue seems to be caused by faulty and/or missing indicies and tablefields I attached our DB scheme to this ticket.

Comment by Stefan Hölzle [ 2021 Dec 15 ]

I noticed the database script was only trying to drop the index users_1, so I resolved the issue by simply creating the missing index users_1 (and adding another index called httptest_1, because it was also missing):

CREATE INDEX users_1 ON users (userid);
CREATE INDEX httptest_1 on httptest (httptestid);

Afterwards the database upgrade succeeded.

Generated at Sun Mar 16 22:15:33 EET 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.