Compare schema of various database versions

[Corvus]

After moving a mariadb zabbix DB from one server to another, a database version mismatch prevents the target zabbix server from starting. This seems fairly common. The message is:

The server does not match Zabbix database. Current database version (mandatory/optional): 05050090/05050090. Required mandatory version: 05000000.

Before I get into dynamic table storage, utf8, collation and such, I wish to just locate the exact schema definitions of these two database versions so I can diff them and see what changed.
Is there a place/way that will let me look at versions 05050090 and 05000000 of the database schema and compare them?
E.g., is there a map from database versions to git commit hashes?
Thanks.

[tim.mooney]

I'm not aware of any map.

If you have two running zabbix databases that have different schemas, you can use the "mysqldiff" tool (works with MariaDB too) to compare the running databases. I've also used a method with 'mysqldump' (and --no-data and a few other options, to smooth out differences between two dumps) to dump just the schema and do comparisons in the past.

The easiest thing for you to do that gets you most of what you need is to download the "create.sql" or "schema.sql" (or whatever its called) from each X.Y.0 version, e.g. 5.0.0, 5.2.0, 5.4.0, 6.0.0, 6.2.0, 6.4.0. Those are going to be where the vast majority of the schema changes will be: when moving from one X.Y to a different one.

When you've used the product for a while, you also get familiar with what the database version numbers correspond to. The "Required mandator version" you show above is for 5.0.0 and would be valid for any Zabbix release in the 5.0 LTS series, including 5.0.32.

The "Current database version" you show is for something *after* 5.4.0, but before the final, released schema for 6.0.0. There never was a Zabbix 5.6.x release series, which means that database version 05050090 is an alpha or beta schema for 6.0, prior to the final production release of Zabbix 6.0.

[Corvus]

Thanks, tim.mooney for the advice! Some more details:

I was running zabbix on a Debian testing OS that made a bullseye ⟶ bookworm transition. During this time, zabbix went through the following revisions:
1:4.0.18+dfsg-1 ⟶ 1:5.0.1+dfsg-1 ⟶ 1:5.0.2+dfsg-1 ⟶ 1:5.0.14+dfsg-1 ⟶ 1:6.0.13+dfsg-1+b1 ... all from the official Debian repository. The last upgrade (was likely a dist-upgrade) broke my zabbix installation.
The last working version 1:5.0.14+dfsg-1 claimed to use schema version 05050090.
After failing to get this zabbix DB to work with version 1:6.0.13+dfsg-1+b1, I decided to transplant the DB to a different server that was locked to Debian bullseye (now oldstable). It runs zabbix version 1:5.0.8+dfsg-1.
It is this zabbix-server-mysql that complains thus —

The server does not match Zabbix database. Current database version (mandatory/optional): 05050090/05050090. Required mandatory version: 05000000.

So I have my job cut out now. I have to roll back the DB from 05050090 to 05000000.
(I have three years of archival IoT/BMS data on the zabbix DB that I definitely do not want to lose.)

To do this, I need to find the DB schema for these two DB versions. I could not find any *.sql files from github source https://github.com/zabbix/zabbix where the schema are defined. I could not even find a way to map from Debain versions to commit hashes.
Anyway, so the next avenue is to create an new empty zabbix DB on 1:5.0.8+dfsg-1, dump its schema, compare with the schema dumped from the "production" DB, and see if I can patch the differences.

Is this the correct way to go about this? Anything I can do better?

And how can I avoid such problems in future? Run Debian stable and never do dist-upgrade in-place?

[cyber]

If it helps...
Those schema files are definitely present in source tarballs, if you download them... https://cdn.zabbix.com/zabbix/sources/stable/5.0/
in git there seems to be gen_schema.pl .. (in https://github.com/zabbix/zabbix/tree/master/create/bin) which uses https://github.com/zabbix/zabbix/blo...rc/schema.tmpl

If you try to upgrade from official zabbix repo (latest there is 6.0.18 I think)? maybe it fixes your v6?

[tim.mooney]

I was running zabbix on a Debian testing OS that made a bullseye ⟶ bookworm transition. During this time, zabbix went through the following revisions:
1:4.0.18+dfsg-1 ⟶ 1:5.0.1+dfsg-1 ⟶ 1:5.0.2+dfsg-1 ⟶ 1:5.0.14+dfsg-1 ⟶ 1:6.0.13+dfsg-1+b1 ... all from the official Debian repository. The last upgrade (was likely a dist-upgrade) broke my zabbix installation.
The last working version 1:5.0.14+dfsg-1 claimed to use schema version 05050090.

I don't believe that's correct. I'm on 5.0.34 and Zabbix is happy with my 05000000 schema:

  2660:20230518:182729.138 current database version (mandatory/optional): 05000000/05000007
  2660:20230518:182729.138 required mandatory version: 05000000

I'm guessing the failed upgrade to 6.0.13 is where you ended up with a 05050090 schema version, but that's not what 5.0.x runs with by default.

After failing to get this zabbix DB to work with version 1:6.0.13+dfsg-1+b1, I decided to transplant the DB to a different server that was locked to Debian bullseye (now oldstable). It runs zabbix version 1:5.0.8+dfsg-1.
It is this zabbix-server-mysql that complains thus —

So I have my job cut out now. I have to roll back the DB from 05050090 to 05000000.

Almost anything is possible with enough effort and determination, but trying to somehow "downgrade" the schema after it's gotten to the 05050090 version is going to be a ton of work. That should be your last resort, as there should be other easier paths available.

Can't you just go back to your last backup before the accidental update to 6.0.13, reload the database from that backup, and install the 5.0.14 packages?

If you're in the situation where you don't have backups (oops...), then you'll have an easier time fixing what's wrong and going forward to 6.0.13 than you would somehow manually undoing the schema changes between 5.0.8 (or 5.0.14) and 6.0.13. Do you have logs from the failed upgrade? Have you tried installing a later 6.0.x and seeing what it says when it tries to upgrade the schema? It may be a fairly simple (manual) fix to get the upgrade to proceed.

If you want to understand the schema for each version, you can download the source for something like 6.0.13 (zabbix-6.0.13.tar.gz) and extract it and look in database/mysql/schema.sql.

If you want to know what schema changes Zabbix applies as it upgrades the schema between minor versions, you want to look at the source code, in src/libs/zbxdbupgrade/ and then look at the files there. That will give you an idea of what Zabbix is doing behind the scenes and what's changing, but in most cases it won't give you the actual SQL. You would have to piece that together from the routines in src/libs/zbxdbupgrade/dbupgrade.c, which handle the SQL variances between the supported database backends.

[Markku]

Just to add to what tim.mooney said: This is the correct time to take an export of the database for testing the 6.0.x upgrades (using Debian-provided or Zabbix-provided packages) on another server.

I have always used Zabbix-provided packages on Debian, I haven't found any reason to use the Debian-provided Zabbix packages, they aren't usually very new.

https://packages.debian.org/search?suite=all&section=all&arch=any&searchon=names&keywords=zabbix-server

Markku