Zabbix upgrade 5.0 LTS to 5.4: Database upgrade error

[BlueBull]

Hello

I have tried updating Zabbix from version 5.0 LTS (with Apache and MySQL) to version 5.4 (with Apache and MySQL) and that went okay. However the frontend is complaining that the "frontend does not match the Zabbix database". So I checked the zabbix_server.log and it is showing this error in the automatic database upgrade process

Unsupported DB! MariaDB version is 50505 which is smaller than minimum of 100037
current database version (mandatory/optional): 05030069/05030069
required mandatory version: 05040000
starting automatic database upgrade
[Z3005] query failed: [1832] Cannot change column 'scriptid': used in a foreign key constraint 'c_opcommand_2' [alter table opcommand modify `scriptid` bigint unsigned not null]
database upgrade failed

Any ideas? I already tried running through the upgrade process again but all components have been upgraded correctly. This is in our development environment and I have backups so it isn't a problem but this was a test-run for our production environment and I would like to be able to upgrade at some point

Thank you!

[BlueBull]

I forgot to mention, I am on MariaDB version 10.1.48, which should be sufficient for Zabbix 5.4. I saw in an earlier thread that it was advised to update MariaDB but this should not be necessary?

The other thread is here so this seems to be an issue that is common and not specific to my environment. Should I file a bug report perhaps?

[markfree]

Have you followed the upgrade notes and procedure specific to your version?

What system are you using?

"/var/log/mysql/error.log" might give you more info on what's going on.

[BlueBull]

Have you followed the upgrade notes and procedure specific to your version?

What system are you using?

"/var/log/mysql/error.log" might give you more info on what's going on.

Indeed I have. I have also gone over the software requirements but I can't seem to spot anything out of the ordinary. I have also updated all other applications system-wide, however I did this after the error first popped up so the application updates aren't the cause of this.

I have discussed this with our database architect and he told me that probably the database upgrade script has a bug where the foreign keys aren't dropped before editing the column and re-adding the foreign keys, which is how it's supposed to be done. He wasn't sure of this at all though, he isn't an expert on MySQL as we don't use that DB engine except for with Zabbix

I have glanced over the MySQL error log and could not spot anything out of the ordinary but I will do that again on my next workday, I have finished work for the day

[markfree]

When you get back to work, try showing your OPCOMMAND table info.

MariaDB [zabbix]> show columns from zabbix.opcommand;

[BlueBull]

When you get back to work, try showing your OPCOMMAND table info.

MariaDB [zabbix]> show columns from zabbix.opcommand;

I will do that and come back with the output in this thread. Thank you

By the way, the system I am using is: Ubuntu 18.04.5 LTS Bionic Beaver

[cybermcm]

I have the same issue, using Zabbix for my home lab with a Raspi OS docker installation (zabbix and MariaDB with docker containers)
MariaDB ver 10.1.48

command output:

MariaDB [(none)]> show columns from zabbix.opcommand;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| operationid | bigint(20) unsigned | NO | PRI | NULL | |
| type | int(11) | NO | | 0 | |
| scriptid | bigint(20) unsigned | YES | MUL | NULL | |
| execute_on | int(11) | NO | | 0 | |
| port | varchar(64) | NO | | | |
| authtype | int(11) | NO | | 0 | |
| username | varchar(64) | NO | | | |
| password | varchar(64) | NO | | | |
| publickey | varchar(64) | NO | | | |
| privatekey | varchar(64) | NO | | | |
| command | text | NO | | NULL | |
+-------------+---------------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

[markfree]

This is what I get from the given query after upgrading the server.

MariaDB [(none)]> show columns from zabbix.opcommand;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| operationid | bigint(20) unsigned | NO | PRI | NULL | |
| scriptid | bigint(20) unsigned | NO | MUL | NULL | |
+-------------+---------------------+------+-----+---------+-------+

Your "scriptid" column is "null" allowed.

I believe that Zabbix DB update script was not capable of updating this column to "not null". Maybe it has null values.

You could try to alter it manually. But backup everything first.
I also suggest stoping Zabbix Server first.

ALTER TABLE opcommand MODIFY scriptid bigint unsigned NOT NULL;

[BlueBull]

This is what I get from the given query after upgrading the server.

MariaDB [(none)]> show columns from zabbix.opcommand;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| operationid | bigint(20) unsigned | NO | PRI | NULL | |
| scriptid | bigint(20) unsigned | NO | MUL | NULL | |
+-------------+---------------------+------+-----+---------+-------+

Your "scriptid" column is "null" allowed.

I believe that Zabbix DB update script was not capable of updating this column to "not null". Maybe it has null values.

You could try to alter it manually. But backup everything first.
I also suggest stoping Zabbix Server first.

ALTER TABLE opcommand MODIFY scriptid bigint unsigned NOT NULL;

Awesome. I will try that on Tuesday if my command output shows the same thing. I might consult with our database architect to ask his thoughts before I try it and will feed back if he suggests something else or if he approves. We luckily have a development environment and I have thorough backups even of that one so I can play around and try things as much as I want. If it's likely a problem with the script, do you think the community could benefit from me filing a bug report? I would be willing to do that if it helps. Seems to be a common problem because so far there's 4 people with the same issue in the last few threads on the forum and across different operating systems (though all Debian-based, Ubuntu and Raspbian)

[cybermcm]

ALTER TABLE opcommand MODIFY scriptid bigint unsigned NOT NULL;

that didn't work at least for me

ALTER TABLE opcommand MODIFY scriptid bigint unsigned NOT NULL
[B]MySQL said: [/B][URL=http://raspi02.corp.xamb.net:8080/url.php?url=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Fserver-error-reference.html][IMG2=JSON]{"data-align":"none","data-size":"full","src":"http:\/\/raspi02.corp.xamb.net:8080\/themes\/dot.gif"}[/IMG2][/URL]
#1832 - Cannot change column 'scriptid': used in a foreign key constraint 'c_opcommand_2'

[BlueBull]

that didn't work at least for me

ALTER TABLE opcommand MODIFY scriptid bigint unsigned NOT NULL
[B]MySQL said: [/B][URL=http://raspi02.corp.xamb.net:8080/url.php?url=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Fserver-error-reference.html][IMG2=JSON]{"data-align":"none","data-size":"full","src":"http:\/\/raspi02.corp.xamb.net:8080\/themes\/dot.gif"}[/IMG2][/URL]
#1832 - Cannot change column 'scriptid': used in a foreign key constraint 'c_opcommand_2'

Hmmmm that's the same error as in the zabbix_server.log so I assume the script is trying to do the exact same thing. Maybe then, like my database architect suggested, we need to drop foreign keys (or maybe just one foreign key, 'c_opcommand_2') before we try to make that change? Though I would have no idea how to do that, and I'm hesitant to try because I will likely break the database, because the foreign keys also need to be added again afterwards. I'm not versed enough in database management but I'll look into it. Thanks for the feedback, big help

[cybermcm]

Thanks for pointing me into the right direction, I managed to get it working again...
Stop Zabbix server before executing the commands and take your time to back up your data!

ALTER TABLE zabbix.opcommand DROP FOREIGN KEY c_opcommand_2;
ALTER TABLE `opcommand` CHANGE `scriptid` `scriptid` BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE `opcommand` ADD CONSTRAINT `c_opcommand_2` FOREIGN KEY (`scriptid`) REFERENCES `scripts`(`scriptid`) ON DELETE RESTRICT ON UPDATE RESTRICT;

Hopefully someone can confirm that this is a valid way to solve this issue.

[markfree]

I could not test the issue here because I didn't have it. But I'm glad it worked out for you. I hope BlueBull has the same outcome.

[Googs]

This sorted the problem for me too. Thanks everybody, saved an otherwise failed upgrade

[BlueBull]

Thanks for pointing me into the right direction, I managed to get it working again...
Stop Zabbix server before executing the commands and take your time to back up your data!

ALTER TABLE zabbix.opcommand DROP FOREIGN KEY c_opcommand_2;
ALTER TABLE `opcommand` CHANGE `scriptid` `scriptid` BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE `opcommand` ADD CONSTRAINT `c_opcommand_2` FOREIGN KEY (`scriptid`) REFERENCES `scripts`(`scriptid`) ON DELETE RESTRICT ON UPDATE RESTRICT;

Hopefully someone can confirm that this is a valid way to solve this issue.

Good job, nicely done. I will confirm on Tuesday if it worked for me. If it does, I think I'm going to send a mail or a bug report to the devs, this will pop up for other people as well because not everyone posts on a forum if they have issues

[BlueBull]

Here is the bug tracker link: https://support.zabbix.com/browse/ZBX-19435

[Baymon]

I can confirm this problem. Exactly same error message after updating from 5.0 to 5.4. The three ALTER TABLE statements solved the problem for me. Thank you very much for it.