Database upgrade script issues after upgrading to 5.0

[nktech1135]

I'm trying to upgrade zabbix from 4.4 to 5.0 and getting the following errors.
On the web ui,

–
The frontend does not match Zabbix database. Current database version (mandatory/optional): 4040000/4040002. Required mandatory version: 5000000. Contact your system administrator.

The zabbix server log.

24136:20200520:163627.855 starting automatic database upgrade
24136:20200520:163627.856 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: constraint "c_items_1" of relation "items" does not exist
[alter table items drop constraint c_items_1]
24136:20200520:163627.856 database upgrade failed


System, clearos 7.7.2
postgres 12.1


How can i fix the query the script is running to complete my upgrade?

[tim.mooney]

I understand that you're trying to upgrade from 4.4.x to 5.0, but has your Zabbix server (and your database) been upgraded several times before? In other words, have you been using the product for quite a while, so the database schema has been upgraded many times over the years?

I ask because while preparing my site's database (which is MariaDB, not PostgreSQL) for the 5.0 upgrade, I discovered a couple of classes of issues where my production database's schema (after having been upgraded many times over the years) differed from a pristine/fresh schema for a 4.4.7 (the version we're currently using) install. It seems like some upgrades over the years didn't correctly clean up certain schema changes.

What I think you should do is

1. revert your software version to whatever 4.4 point release you were using, for all the Zabbix components
2. restore your database from the backup you made before doing the upgrade.

This should get you back to a functioning 4.4.x Zabbix install.

Then, before you attempt to upgrade again, you need to get your database schema fixed so that it matches what the upgrade is expecting. How you do that is going to depend on how big your database is and how comfortable you are with SQL.

The easiest thing to do would be to create a new, fresh database using the database/postgresql/schema.sql from the exact 4.4.x Zabbix version you're running. Once you have a fresh database schema in a known-good state, you would have to load/import a backup/dump of your production data *without* letting your production schema replace the new schema. For a large database, that could take a long time, but it's the most straightforward method to get to a known-good state for your schema. Once everything is imported, either drop the zabbix database with the incorrect schema and rename the new one to take its place, or point your server and web config at the new database name.

An alternate method, that doesn't involve a dump & reload, is to figure out a way to compare or "diff" the freshly-created schema with yours, to see where your production DB schema has diverged from what it's supposed to be. You already know that your production DB is missing a CONSTRAINT named "c_items_1" that zabbix is expecting to be there, but you don't know what else it's missing (or, what else it has that it should not). I don't know what tools are available for PostgreSQL to diff two schemas, so I'm not sure how you easily figure this out. Once you know where the schema diverges, though, figuring out the ALTER TABLE statements shouldn't be too bad (assuming you're somewhat comfortable with SQL).

For MySQL (and presumably it would work with MariaDB too), Oracle has a 'mysqldiff' tool. I didn't know about it when I was fixing my production schema, so I instead used 'mysqldump' with the '--no-data' option and a few other arguments, to dump just the schema in a predictable format. I did this for the fresh DB and for the production DB, and then used text comparison tools (I prefer GNU diff, you may like something else better) to find all the differences between my existing DB schema and what it should have been. There were a lot.

Once I knew what they all were, though, the rest was straightforward: generate "ALTER TABLE" statements to alter my existing schema so that it is as close as possible to a fresh install.

[nktech1135]

Thanks for this.
Yes, this is indeed an older install, i don't know how old because i didn't do it back then. I managed to revert to a working 4.4 setup and am now migrating the system so will clean up the db.

Thanks for the pointers.