Zabbix upgrade 5.0 LTS to 5.4: Database upgrade error

[Dylanchr3500]

Hi There,
I had the same issues. I have executed the Alter table statements and this appears to have resolved the issue for myself as well. Thank you all!

[cybermcm]

The linked issue (https://support.zabbix.com/browse/ZBX-19435) was closed (duplicate) which isn't correct in my point of view. But it seems that the workaround is OK

[BlueBull]

The linked issue (https://support.zabbix.com/browse/ZBX-19435) was closed (duplicate) which isn't correct in my point of view. But it seems that the workaround is OK

They have luckily reopened the issue. Nice of them to listen to the feedback, always good to see companies making an effort to provide good support. Kudos to the Zabbix team. Also see my latest comment in the issue, apparently when you upgrade 5.0 -> 5.2 -> 5.4 in stead of 5.0 -> 5.4, the issue doesn't happen someone on another forum told me. So perhaps it is a change in the database upgrade script in 5.4 that causes it, just a hypothesis. I will feed back tomorrow if the solution worked for me as well, but I am almost certain it will

[andreasb]

Just to confirm this is somewhat a common issue, I also had the exact same issues. I upgraded from 5.0.11 to 5.4.0, using the official upgrade instructions. The Alter table statements helped me as well.

[deemass]

Hello. Same issue. What should i do?

operationid	bigint(20) unsigned	NO	PRI	NULL
scriptid	bigint(20) unsigned	NO	MUL	NULL

[deemass]

The main server is good. The problem appear on zabbix-proxy from 5.2 to 5.4 upgrade

[BlueBull]

Hello. Same issue. What should i do?

operationid	bigint(20) unsigned	NO	PRI	NULL
scriptid	bigint(20) unsigned	NO	MUL	NULL

Is this the output from the main server or from the proxy that has this issue? Because if it is the proxy, usually when this error happens, the scriptid 'Null' column, the third one, shows 'YES' and in the above output it is showing 'NO'. The alter table statements listed in the previous post change this to 'NO' so if it is already showing 'NO' for you, I'm not sure if this is the same issue. It could very well be though. Is your zabbix_proxy.log file also showing an error similar to the below or not? The below is the main error we see with this issue

[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]

See this post for an example of what it looks like, look at the "scriptid" row

[deemass]

I'm sorry. It was output from main server. I used three alter sql on zabbix proxy database and all seems good. Zabbix proxy updated his database and started.

[BlueBull]

I'm sorry. It was output from main server. I used three alter sql on zabbix proxy database and all seems good. Zabbix proxy updated his database and started.

Ah I see, that explains why the output was not showing 'YES' on that column. Okay, thanks for the feedback and I'm glad this solution worked for you

This issue seems to be quite widespread, more and more people are coming forward with the exact same error message

[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.

Confirmation: in my case the problem was also resolved by executing these statements. The solution works. By the way, one added detail for people reading that want to use this solution, between the first and second ALTER TABLE statements you have to do a

USE zabbix;

So it will be

ALTER TABLE zabbix.opcommand DROP FOREIGN KEY c_opcommand_2;
USE zabbix;
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;

This is to select the zabbix database. If your database is not called "zabbix", change that to the right database name. If you don't do this you will get a "database not selected" error or something like that. Most people will know to do this when they get this error, but for those that don't, this is how you solve that

[kmfreder]

Thanks so much for putting this out there. This solved the problem for me. I found by trial and error the need for the "use zabbix;" but was glad to see it in the last comment here...maybe I should have read to the end before I ran off and tried the fix... I did read through all of the upgrade documentation in my 4.4.9 to 5.4 upgrade...I think it would be helpful to include a link to this thread or some reference to these commands in the official upgrade docs.

[vai]

Hello Everyone,

The Alter statements could not help me to address the issue and ran into the same issues.

Will you please help me to address the issue?


5775:20210730:114403.301 using configuration file: /etc/zabbix/zabbix_server.conf
5775:20210730:114403.309 current database version (mandatory/optional): 05010043/05010043
5775:20210730:114403.309 required mandatory version: 05040000
5775:20210730:114403.309 starting automatic database upgrade
5775:20210730:114403.311 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbixdb`.`widget_field`, CONSTRAINT `c_widget_field_5` FOREIGN KEY (`value_graphid`) REFERENCES `graphs` (`graphid`) ON DELETE CASCADE) [insert into widget_field (widget_fieldid,widgetid,type,name,value_int,value _str,value_itemid,value_graphid) values (3531,977,6,'graphid',0,'',null,433)]
5775:20210730:114403.314 database upgrade failed

[SharcusPoW]

Hi all,

Zabbix is running on Ubuntu 20.4 with containers and using Zabbix 5.4 -> upgrade is to 6.0.
This is the solution for your issue.

Step 1. Backup database ( this is just for safety , or if you wont to migrate database )
Step 2. Stop Zabbix server and front end containers ( I usually upgrade all 4 Zabbix containers, so you can stop 4)
Step3. Run new Zabbix server and front end containers:
Set Environment variable as before -e MYSQL_DATABASE='existing DB name ' -e MYSQL_USER='existing username' -e MYSQL_PASSWORD='old password' -e MYSQL_ROOT_PASSWORD='old password'
When you run this commands dbversion table in Zabbix database will be automatically updated .

Before Step 3 :
mysql> select * from dbversion;
+-----------+----------+
| mandatory | optional |
+-----------+----------+
| 5040000 | 5040001 |
+-----------+----------+

After Step 3 :
mysql> select * from dbversion;
+-------------+-----------+----------+
| dbversionid | mandatory | optional |
+-------------+-----------+----------+
| 1 | 6000000 | 6000000 |
+-------------+-----------+----------+

Now when you open Zabbix frontend everything will be ok

Best,
SharcusPoW