Database upgrade fails on 5.2 to 5.4

[ric91]

Hallo all, hope you can help me with a database upgrade problem.

I'm running a Zabbix server on a CentOS Stream 8 machine since a few years, mysql Ver 8.0.26, Kernel Linux 4.18.0-365.el8.x86_64.

Today I've done an upgrade from 5.2 to 5.4 and while starting the server an error occurs on upgrading database:

77745:20220212:154401.474 Starting Zabbix Server. Zabbix 5.4.10 (revision db03ff023d).
77745:20220212:154401.475 ****** Enabled features ******
77745:20220212:154401.475 SNMP monitoring: YES
77745:20220212:154401.475 IPMI monitoring: YES
77745:20220212:154401.475 Web monitoring: YES
77745:20220212:154401.475 VMware monitoring: YES
77745:20220212:154401.475 SMTP authentication: YES
77745:20220212:154401.475 ODBC: YES
77745:20220212:154401.475 SSH support: YES
77745:20220212:154401.475 IPv6 support: YES
77745:20220212:154401.475 TLS support: YES
77745:20220212:154401.475 ******************************
77745:20220212:154401.475 using configuration file: /etc/zabbix/zabbix_server.conf
77745:20220212:154401.478 current database version (mandatory/optional): 05030094/05030094
77745:20220212:154401.478 required mandatory version: 05040000
77745:20220212:154401.478 optional patches were found
77745:20220212:154401.478 starting automatic database upgrade
77745:20220212:154401.489 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`widget_field`, CONSTRAINT `c_widget_field_4` FOREIGN KEY (`value_itemid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE) [insert into widget_field (widget_fieldid,widgetid,type,name,value_int,value _str,value_itemid,value_graphid,value_groupid,valu e_hostid,value_sysmapid) values (271,115,5,'itemid',0,'',28671,null,null,null,null )]
77745:20220212:154401.490 database upgrade failed

I've done searches and found similar problems while upgrading, but no solutions helps me out there.

Thanks in advance for any help or hint. Ric

[tim.mooney]

78131:20220214:094237.901 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`widget_field`, CONSTRAINT `c_widget_field_4` FOREIGN KEY (`value_itemid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE) [insert into widget_field (widget_fieldid,widgetid,type,name,value_int,value _str,value_itemid,value_graphid,value_groupid,valu e_hostid,value_sysmapid) values (271,115,5,'itemid',0,'',28671,null,null,null,null )]
78131:20220214:094237.902 database upgrade failed

In Zabbix 5.4, the MySQL/MariaDB/Percona schema definition (from create.sql) for the "widget_field" table is:

CREATE TABLE `widget_field` (
`widget_fieldid` bigint unsigned NOT NULL,
`widgetid` bigint unsigned NOT NULL,
`type` integer DEFAULT '0' NOT NULL,
`name` varchar(255) DEFAULT '' NOT NULL,
`value_int` integer DEFAULT '0' NOT NULL,
`value_str` varchar(255) DEFAULT '' NOT NULL,
`value_groupid` bigint unsigned NULL,
`value_hostid` bigint unsigned NULL,
`value_itemid` bigint unsigned NULL,
`value_graphid` bigint unsigned NULL,
`value_sysmapid` bigint unsigned NULL,
PRIMARY KEY (widget_fieldid)
) ENGINE=InnoDB;
CREATE INDEX `widget_field_1` ON `widget_field` (`widgetid`);
CREATE INDEX `widget_field_2` ON `widget_field` (`value_groupid`);
CREATE INDEX `widget_field_3` ON `widget_field` (`value_hostid`);
CREATE INDEX `widget_field_4` ON `widget_field` (`value_itemid`);
CREATE INDEX `widget_field_5` ON `widget_field` (`value_graphid`);
CREATE INDEX `widget_field_6` ON `widget_field` (`value_sysmapid`);

ALTER TABLE `widget_field` ADD CONSTRAINT `c_widget_field_1` FOREIGN KEY (`widgetid`) REFERENCES `widget` (`widgetid`) ON DELETE CASCADE;
ALTER TABLE `widget_field` ADD CONSTRAINT `c_widget_field_2` FOREIGN KEY (`value_groupid`) REFERENCES `hstgrp` (`groupid`) ON DELETE CASCADE;
ALTER TABLE `widget_field` ADD CONSTRAINT `c_widget_field_3` FOREIGN KEY (`value_hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE;
ALTER TABLE `widget_field` ADD CONSTRAINT `c_widget_field_4` FOREIGN KEY (`value_itemid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE;
ALTER TABLE `widget_field` ADD CONSTRAINT `c_widget_field_5` FOREIGN KEY (`value_graphid`) REFERENCES `graphs` (`graphid`) ON DELETE CASCADE;
ALTER TABLE `widget_field` ADD CONSTRAINT `c_widget_field_6` FOREIGN KEY (`value_sysmapid`) REFERENCES `sysmaps` (`sysmapid`) ON DELETE CASCADE;

Leaving out the first part of the error message and looking at just the insert statement and the values, we see (reformatted just a bit to make it easier to read):

          INSERT INTO widget_field (widget_fieldid,    widgetid,    type,    name,    value_int,    value _str,    value_itemid,    value_graphid,    value_groupid,    value_hostid,    value_sysmapid)
          VALUES                   (           271,         115,       5,'itemid',            0,            '',           28671,             null,             null,            null,               null)

That INSERT is inserting some of the columns in a different order than the actual table definition, but that's completely OK because it's specifying the column order in the "INSERT INTO' part. It's also fine that the last four columns are NULL.

Now that its easy to read the INSERT and you know the table schema, look at the error again:

a foreign key constraint fails (`zabbix`.`widget_field`, CONSTRAINT `c_widget_field_4` FOREIGN KEY (`value_itemid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE)

That's saying that "c_widget_field_4" is being violated. c_widget_field_4 is the FOREIGN KEY constraint that says "value_itemid" must exist in the "items" table "itemid" column. Looking at the INSERT again, it's trying to insert "value_itemid" of 28671. Apparently, there is no items.itemid of 28671.

I haven't looked closely enough at the earlier versions of the Zabbix schema to know if the schema just wasn't careful enough to specify constraints, so that it's "easy" for this to have happened or if there should have always been referential integrity between the items table and other tables that would have referenced it. Either way, though, 5.4 has a schema that is expecting that if you want a "value_itemid" of 28671 in the "widget_field" table, then there better be an items.itemid of 28671.

I'm not running 5.4 yet at my site, but from memory there were some big changes in the UI. In particular, the concept of "screens" is gone, and has been rolled into "dashboards". I suspect, but am not certain, that it's the conversion of one of your "screens" that's causing this failure. You may want to do some SQL queries with your 5.2 tables to see if you can find anything more related to "28671". You can try looking in your items table, but you already know that it's not going to be there. I would probably next look at the screen-related stuff (looks like there's a "screens_items" tables, which seems pretty promising). I would probably look to see if there's a "screenitemid" of 28671, to try figure out what this rogue ID is.

[naven]

I've had this issue 2 weeks ago.
The mistake I've done was that I did not follow upgrade instructions.
You need to stop zabbix before the upgrade.

systemctl stop zabbix-server

To recover, I stopped zabbix, restored database, started zabbix and let the database schema upgrade.
Observe /var/log/zabbix/zabbix_server.log . This wil tell you when the schema upgrade is done.

[ric91]

Thanks for your anwser. Although I'm sure all services had been stopped I've done a rollback and tried again. But it goes wrong the same way:

1203:20220214:093330.196 Zabbix Server stopped. Zabbix 5.2.7 (revision 91e8333180).
78131:20220214:094235.382 Starting Zabbix Server. Zabbix 5.4.10 (revision db03ff023d).
78131:20220214:094235.382 ****** Enabled features ******
78131:20220214:094235.382 SNMP monitoring: YES
78131:20220214:094235.382 IPMI monitoring: YES
78131:20220214:094235.382 Web monitoring: YES
78131:20220214:094235.382 VMware monitoring: YES
78131:20220214:094235.382 SMTP authentication: YES
78131:20220214:094235.382 ODBC: YES
78131:20220214:094235.382 SSH support: YES
78131:20220214:094235.382 IPv6 support: YES
78131:20220214:094235.382 TLS support: YES
78131:20220214:094235.382 ******************************
78131:20220214:094235.382 using configuration file: /etc/zabbix/zabbix_server.conf
78131:20220214:094235.388 current database version (mandatory/optional): 05020000/05020002
78131:20220214:094235.388 required mandatory version: 05040000
78131:20220214:094235.388 optional patches were found
78131:20220214:094235.388 starting automatic database upgrade
78131:20220214:094235.389 completed 0% of database upgrade
78131:20220214:094235.441 completed 1% of database upgrade
78131:20220214:094235.479 completed 2% of database upgrade
78131:20220214:094235.501 completed 3% of database upgrade
78131:20220214:094235.577 completed 4% of database upgrade
78131:20220214:094235.683 completed 5% of database upgrade
78131:20220214:094236.546 completed 6% of database upgrade
78131:20220214:094236.650 completed 7% of database upgrade
78131:20220214:094236.759 completed 8% of database upgrade
78131:20220214:094236.862 completed 9% of database upgrade
78131:20220214:094236.964 completed 10% of database upgrade
78131:20220214:094237.065 completed 11% of database upgrade
78131:20220214:094237.129 completed 12% of database upgrade
78131:20220214:094237.134 completed 13% of database upgrade
78131:20220214:094237.143 completed 14% of database upgrade
78131:20220214:094237.151 completed 15% of database upgrade
78131:20220214:094237.179 completed 16% of database upgrade
78131:20220214:094237.200 completed 17% of database upgrade
78131:20220214:094237.215 completed 18% of database upgrade
78131:20220214:094237.225 completed 19% of database upgrade
78131:20220214:094237.233 completed 20% of database upgrade
78131:20220214:094237.248 completed 21% of database upgrade
78131:20220214:094237.261 completed 22% of database upgrade
78131:20220214:094237.375 completed 23% of database upgrade
78131:20220214:094237.542 completed 24% of database upgrade
78131:20220214:094237.545 completed 25% of database upgrade
78131:20220214:094237.598 completed 26% of database upgrade
78131:20220214:094237.652 completed 27% of database upgrade
78131:20220214:094237.654 completed 28% of database upgrade
78131:20220214:094237.664 completed 29% of database upgrade
78131:20220214:094237.677 completed 30% of database upgrade
78131:20220214:094237.693 completed 31% of database upgrade
78131:20220214:094237.709 completed 32% of database upgrade
78131:20220214:094237.717 completed 33% of database upgrade
78131:20220214:094237.718 completed 34% of database upgrade
78131:20220214:094237.726 completed 35% of database upgrade
78131:20220214:094237.742 completed 36% of database upgrade
78131:20220214:094237.756 completed 37% of database upgrade
78131:20220214:094237.770 completed 38% of database upgrade
78131:20220214:094237.783 completed 39% of database upgrade
78131:20220214:094237.794 completed 40% of database upgrade
78131:20220214:094237.810 completed 41% of database upgrade
78131:20220214:094237.823 completed 42% of database upgrade
78131:20220214:094237.829 completed 43% of database upgrade
78131:20220214:094237.836 completed 44% of database upgrade
78131:20220214:094237.855 completed 45% of database upgrade
78131:20220214:094237.871 completed 46% of database upgrade
78131:20220214:094237.886 completed 47% of database upgrade
78131:20220214:094237.901 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`widget_field`, CONSTRAINT `c_widget_field_4` FOREIGN KEY (`value_itemid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE) [insert into widget_field (widget_fieldid,widgetid,type,name,value_int,value _str,value_itemid,value_graphid,value_groupid,valu e_hostid,value_sysmapid) values (271,115,5,'itemid',0,'',28671,null,null,null,null )]
78131:20220214:094237.902 database upgrade failed

No agent running, no proxy.

I followed the instructions on https://www.zabbix.com/documentation...es/rhel_centos

Any other ideas?

[tim.mooney]

78131:20220214:094237.901 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`widget_field`, CONSTRAINT `c_widget_field_4` FOREIGN KEY (`value_itemid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE) [insert into widget_field (widget_fieldid,widgetid,type,name,value_int,value _str,value_itemid,value_graphid,value_groupid,valu e_hostid,value_sysmapid) values (271,115,5,'itemid',0,'',28671,null,null,null,null )]
78131:20220214:094237.902 database upgrade failed

In Zabbix 5.4, the MySQL/MariaDB/Percona schema definition (from create.sql) for the "widget_field" table is:

CREATE TABLE `widget_field` (
`widget_fieldid` bigint unsigned NOT NULL,
`widgetid` bigint unsigned NOT NULL,
`type` integer DEFAULT '0' NOT NULL,
`name` varchar(255) DEFAULT '' NOT NULL,
`value_int` integer DEFAULT '0' NOT NULL,
`value_str` varchar(255) DEFAULT '' NOT NULL,
`value_groupid` bigint unsigned NULL,
`value_hostid` bigint unsigned NULL,
`value_itemid` bigint unsigned NULL,
`value_graphid` bigint unsigned NULL,
`value_sysmapid` bigint unsigned NULL,
PRIMARY KEY (widget_fieldid)
) ENGINE=InnoDB;
CREATE INDEX `widget_field_1` ON `widget_field` (`widgetid`);
CREATE INDEX `widget_field_2` ON `widget_field` (`value_groupid`);
CREATE INDEX `widget_field_3` ON `widget_field` (`value_hostid`);
CREATE INDEX `widget_field_4` ON `widget_field` (`value_itemid`);
CREATE INDEX `widget_field_5` ON `widget_field` (`value_graphid`);
CREATE INDEX `widget_field_6` ON `widget_field` (`value_sysmapid`);

ALTER TABLE `widget_field` ADD CONSTRAINT `c_widget_field_1` FOREIGN KEY (`widgetid`) REFERENCES `widget` (`widgetid`) ON DELETE CASCADE;
ALTER TABLE `widget_field` ADD CONSTRAINT `c_widget_field_2` FOREIGN KEY (`value_groupid`) REFERENCES `hstgrp` (`groupid`) ON DELETE CASCADE;
ALTER TABLE `widget_field` ADD CONSTRAINT `c_widget_field_3` FOREIGN KEY (`value_hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE;
ALTER TABLE `widget_field` ADD CONSTRAINT `c_widget_field_4` FOREIGN KEY (`value_itemid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE;
ALTER TABLE `widget_field` ADD CONSTRAINT `c_widget_field_5` FOREIGN KEY (`value_graphid`) REFERENCES `graphs` (`graphid`) ON DELETE CASCADE;
ALTER TABLE `widget_field` ADD CONSTRAINT `c_widget_field_6` FOREIGN KEY (`value_sysmapid`) REFERENCES `sysmaps` (`sysmapid`) ON DELETE CASCADE;

Leaving out the first part of the error message and looking at just the insert statement and the values, we see (reformatted just a bit to make it easier to read):

          INSERT INTO widget_field (widget_fieldid,    widgetid,    type,    name,    value_int,    value _str,    value_itemid,    value_graphid,    value_groupid,    value_hostid,    value_sysmapid)
          VALUES                   (           271,         115,       5,'itemid',            0,            '',           28671,             null,             null,            null,               null)

That INSERT is inserting some of the columns in a different order than the actual table definition, but that's completely OK because it's specifying the column order in the "INSERT INTO' part. It's also fine that the last four columns are NULL.

Now that its easy to read the INSERT and you know the table schema, look at the error again:

a foreign key constraint fails (`zabbix`.`widget_field`, CONSTRAINT `c_widget_field_4` FOREIGN KEY (`value_itemid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE)

That's saying that "c_widget_field_4" is being violated. c_widget_field_4 is the FOREIGN KEY constraint that says "value_itemid" must exist in the "items" table "itemid" column. Looking at the INSERT again, it's trying to insert "value_itemid" of 28671. Apparently, there is no items.itemid of 28671.

I haven't looked closely enough at the earlier versions of the Zabbix schema to know if the schema just wasn't careful enough to specify constraints, so that it's "easy" for this to have happened or if there should have always been referential integrity between the items table and other tables that would have referenced it. Either way, though, 5.4 has a schema that is expecting that if you want a "value_itemid" of 28671 in the "widget_field" table, then there better be an items.itemid of 28671.

I'm not running 5.4 yet at my site, but from memory there were some big changes in the UI. In particular, the concept of "screens" is gone, and has been rolled into "dashboards". I suspect, but am not certain, that it's the conversion of one of your "screens" that's causing this failure. You may want to do some SQL queries with your 5.2 tables to see if you can find anything more related to "28671". You can try looking in your items table, but you already know that it's not going to be there. I would probably next look at the screen-related stuff (looks like there's a "screens_items" tables, which seems pretty promising). I would probably look to see if there's a "screenitemid" of 28671, to try figure out what this rogue ID is.

[ric91]

Thank you sooo much Tim, You directed me into the right direction.

select * from screens_items where resourceid=28671;
+--------------+----------+--------------+------------+-------+--------+---+---+---------+---------+----------+--------+--------+-------+-----+---------+---------------+-------------+-------------+
| screenitemid | screenid | resourcetype | resourceid | width | height | x | y | colspan | rowspan | elements | valign | halign | style | url | dynamic | sort_triggers | application | max_columns |
+--------------+----------+--------------+------------+-------+--------+---+---+---------+---------+----------+--------+--------+-------+-----+---------+---------------+-------------+-------------+
| 231 | 22 | 19 | 28671 | 500 | 100 | 1 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | | 0 | 0 | | 3 |
+--------------+----------+--------------+------------+-------+--------+---+---+---------+---------+----------+--------+--------+-------+-----+---------+---------------+-------------+-------------+

Yes, there is a resourceid 28671, and more digging brings the screen:

select * from screens where screenid=22;
+----------+-----------------+-------+-------+--------+---------+
| screenid | name | hsize | vsize | userid | private |
+----------+-----------------+-------+-------+--------+---------+
| 22 | Free Disk Space | 2 | 4 | 1 | 1 |
+----------+-----------------+-------+-------+--------+---------+

And I found an empty screen called "Free Disk Space". Deleted and the upgrade runs fine.

Again Tim thank you so much for your help and your very detailed answer. This is a dream of a support.