Zabbix Database

[wtdrisco]

Today 6/3/2024, I ran the usual sudo apt update / upgrade on the computer running Zabbix. Then the Ubuntu updater stated it updated a security update. During the apt upgrade i received a prompt to maintain my zabbix.config file. I stated yes. When the computer was prompted to reboot, it hung on stopping the Zabbix Service for 10 minutes. I power cycled the computer and now when I try to start up ZABBIX i get this message. Database error

• The Zabbix database version does not match current requirements. Your database version: 6050257. Required version: 6050305. Please contact your system administrator.

How do I fix this? I am running the 7.0 Pre-release.
The upgrades seem to be very time consuming with ZABBIX. But this time, the updates/upgrades appeared to push out some Zabbix updates.

[Markku]

One tip: never upgrade the running Zabbix packages so that you just reboot after the upgrade process. Always do the service restart first manually so that you control and see exactly what is happening.

Is your database on another server? I think at some point there was a dependency issue between the Zabbix server service and database service, causing confusion if database service was not running.

But, does it not upgrade the database automatically?

Markku

[Asfanbaloch]

Have you tried manually upgrading the Zabbix database schema using the zabbix_server-mysql or zabbix_server-pgsql utility (depending on your database type)?pen_spark

[wtdrisco]

Markku - thank you for responding. I was just checking the Ubuntu updates. That is all I was doing. The Ubuntu Pro kept trying to have me install the latest version of Ubuntu. But when it was running the updates (Ubuntu) that is when I got the response on the zabbix.conf document. It looked like Zabbix was getting an update from that. I see the 7.0 release is out today. I guess I can try to run that and see what happens. Wish upgrades on Open Source application were a little easier to perform, then having to be a mainframe administrator! LOL. You figure they could program a cleaner upgrade process.

[Keulix]

I see the 7.0 release is out today. I guess I can try to run that and see what happens.

I have a very similar problem after following the upgrade instructions from 6.4 to 7.0, just the DB version numbers were different. I thought I have followed the instructions for the "upgrade from package" for AlmaLinux (RHEL8) for the 6.4 (.15) appliance, which is running as a VM in Proxmox very nicely.

systemctl stop zabbix-agent
systemctl stop zabbix-server

added a line to /etc/my.cnf (for MySQL 8.0.36):
log_bin_trust_function_creators=1

I set the log_bin_trust_function_creators=1, even in my.cnf, restarted mysqld, queried the variable again

SELECT @@log_bin_trust_function_creators;

just to see it "ON". I shut down zabbix services, installed php 8.2 from "remi" repo, included the new zabbix repo (for RHEL8), upgraded and updated the modules, and finally ended up with that database requirements version error message after starting the services again.

systemctl restart mysqld

dnf install http://rpms.remirepo.net/enterprise/remi-release-8.rpm -y
dnf install yum-utils -y
dnf module reset php -y
dnf module install php:remi-8.2 -y
dnf update -y

rpm -Uvh https://repo.zabbix.com/zabbix/7.0/rhel/8/x86_64/zabbix-release-7.0-1.el8.noarch.rpm
dnf upgrade zabbix-server-mysql zabbix-web-mysql zabbix-agent -y
dnf update -y

systemctl start zabbix-server
systemctl start zabbix-agent

The server service seems to start but pretty quickly stops again. What is wrong here? Does this not work for the appliance? How is the DB upgraded to 7 - not "automatically" on first server service start after upgrading the service?

[Markku]

The appliance documentation says:

5 Upgrading

The Zabbix appliance packages may be upgraded. To do so, run:

dnf update zabbix*

Otherwise, if you have problems maintaining and upgrading the Zabbix appliance, maybe just take a database dump and copies of the relevant configuration files, install a new appliance and then import the data there. Usually the Zabbix appliance is used just for evaluation, not necessarily in long-term use.

Also, I'd highly recommend using Debian instead of Ubuntu, as Ubuntu apparently tends to upgrade things uncontrolled and unexpected. That's probably related to the detail that the major Debian versions are called "stable", while (some) Ubuntu versions are just "long-term support".

Markku

[Keulix]

Thanks for pointing out that the packages of the Zabbix appliance may indeed be upgraded. However, the command "dnf update zabbix*" does "nothing" with 6.4.15, as this seems to be the latest version (of release 6). I assume this command alone does not help for upgrading (and migrating) from one major version to next higher major version, e.g., 6.4.15 to 7.0.1. I actually want to evaluate/test the upgrade/migration scenario with this appliance, however, this seems to not play very well...

[wtdrisco]

Good point about Ubuntu and Debian!!!

[Markku]

I'd say a major version upgrade process evaluation is not one of the use cases for Zabbix appliance. That's because the appliance is built on some specific OS with it's packages (deployment simplicity in mind), and the new Zabbix major version can have very different package requirements, thus requiring upgrade of the appliance OS itself.

The best bet for testing Zabbix major version upgrades is using a self-installed OS, that way you can control all the parameters reliably.

Markku

[wtdrisco]

Today I went through the upgrade steps for UBUNTU following the steps.2 Debian/Ubuntu (zabbix.com)
When I completed and started the services and even rebooted the zabbix server I still get a database error.

I upgrade from the 7.0 Pre-Release that has been out for awhile... to the latest version

[marcuspax]

I was able to resolve this by:

mysql -u root
use zabbix
SET GLOBAL log_bin_trust_function_creators = 1;

[Markku]

Note that this error is from GUI, not from Zabbix server service. The frontend GUI does not do any upgrades to the database, it just states the fact that frontend is 7.0.0 and your database is still 7.0.0rc-level.

If you restart the zabbix-server service once more, what does /var/log/zabbix/zabbix_server.log say?

Markku

[wtdrisco]

I will review.. I tried the many steps above with no change... so I started to rebuild a better server setup from the desktop PC I had running. I can pull that out and restart it and see what I can find..
Also, if you move to a new server setup, is there a clean way to transfer all the items over? so you do not need to recreate them?

[Keulix]

If you restart the zabbix-server service once more, what does /var/log/zabbix/zabbix_server.log say?

Thank you for this most valueable reminder! In a second terminal window I had that log-file "live" (tail -f -n20 /var/log/zabbix/zabbix_server.log), while starting zabbix-server in the first window. And guess what: of course there were errors that made DB-upgrade fail and stop the server service, like this one:

[Z3005] query failed: [1142] TRIGGER command denied to user 'zabbix_srv'@'localhost' for table 'proxy' [create trigger proxy_insert after insert on proxy for each row insert into changelog (object,objectid,operation,clock) values (19,new.proxyid,1,unix_timestamp())]

Although I had "SET GLOBAL log_bin_trust_function_creators = 1;" active before even starting the upgrade process, I had to GRANT TRIGGER privileges (MySQL 8.0.36) to 5 tables finally, like this (logged in as "root" in mySQL):

GRANT TRIGGER ON zabbix.hosts to `zabbix_srv`@`localhost`;
GRANT TRIGGER ON zabbix.items to `zabbix_srv`@`localhost`;
GRANT TRIGGER ON zabbix.proxy to `zabbix_srv`@`localhost`;
GRANT TRIGGER ON zabbix.proxy_group to `zabbix_srv`@`localhost`;
GRANT TRIGGER ON zabbix.host_proxy to `zabbix_srv`@`localhost`;​

As soon as the stalled service was autorestarting again, the process resumed pretty quickly and ran through smoothly.
I have no idea where this problem originated as I haven't "fumbled" with the DB at all before. My "upgraded 7.0.0 appliance" seems to run pretty well now. Thanks also to Vitaly Tokarenko and his contribution about this!

[Markku]

Also, if you move to a new server setup, is there a clean way to transfer all the items over? so you do not need to recreate them?

Absolutely, just make a dump of the old database and import that to the new (empty, non-Zabbix-initialized) database.

Markku

[zmattx]

This is how I solved all of this mess:

To drop the table creation commands from the upgrade script itself (rather than dropping the tables from the database), you can follow these steps:

### 1. **Enable `log_bin_trust_function_creators`:**
Allow the upgrade process to handle stored functions.

```bash
mariadb -u root -p"" -e "SET GLOBAL log_bin_trust_function_creators = 1;"
```

### 2. **Drop and Recreate the Zabbix Database:**
Ensure you start with a clean database.

```bash
mariadb -u root -p"" -e "DROP DATABASE IF EXISTS zabbix; CREATE DATABASE zabbix CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
```

### 3. **Restore the Zabbix 5.0 Database Backup:**
Restore your Zabbix 5.0 backup.

```bash
mariadb -u zabbix -p'tkl#123zabbix' zabbix < /root/zabbix_backup.sql
```

### 4. **Extract and Edit the Upgrade Script:**

- **Extract the Script:**

```bash
gunzip /usr/share/zabbix-sql-scripts/mysql/server.sql.gz
```

- **Edit the Script to Remove Table Creation Commands:**



(((( KOMANDAS PALEIST:

gunzip -c /usr/share/zabbix-sql-scripts/mysql/server.sql.gz | sed '/CREATE TABLE/,/);/d' | gzip > /tmp/server.sql.gz && mv /tmp/server.sql.gz /usr/share/zabbix-sql-scripts/mysql/server.sql.gz

gunzip -c /usr/share/zabbix-sql-scripts/mysql/server.sql.gz > /usr/share/zabbix-sql-scripts/mysql/server.sql

))))

Open the script in a text editor and comment out or delete any `CREATE TABLE` statements that are causing conflicts.

```bash
nano /usr/share/zabbix-sql-scripts/mysql/server.sql
```

For example, locate lines that look like this:

```sql
CREATE TABLE `maintenances` (
...
);
```

And either comment them out or delete them:

```sql
-- CREATE TABLE `maintenances` (
-- ...
-- );
```

- **Save and Exit the Editor.**

### 5. **Run the Modified Schema Upgrade Script:**

Apply the modified schema upgrade script to update the database without recreating existing tables.

```bash
mariadb -u zabbix -p'tkl#123zabbix' zabbix < /usr/share/zabbix-sql-scripts/mysql/server.sql
```

### 6. **Disable `log_bin_trust_function_creators`:**

Reset the global setting after the upgrade.

```bash
mariadb -u root -p"" -e "SET GLOBAL log_bin_trust_function_creators = 0;"
```

### 7. **Restart Zabbix Services:**

Restart Zabbix and related services.

```bash
systemctl restart zabbix-server zabbix-agent apache2
```

### 8. **Optional Reboot:**

Reboot the system to ensure all services are running correctly.

```bash
reboot
```

---

### **Summary of Actions:**
- This script walks you through enabling required settings, restoring your old data, editing the upgrade script to prevent conflicts, applying the upgrade, and restarting services.
- By editing the script directly, you avoid losing any existing data while still performing the necessary schema upgrades.