Ad Widget

**emsmith** · 09-03-2022, 23:31

There's notes in the upgrade docs about this issue - you're going to have to manually go update the history tables.

https://www.zabbix.com/documentation/6.0/en/manual/installation/upgrade_notes_600

Note the "CRITICAL" database changes - looks like you're using mysql so the instructions for the fix are here

https://www.zabbix.com/documentation/6.0/en/manual/appendix/install/db_primary_keys#mysql

You can follow the instructions - or just copy the sql out of the history_pk_prepare.sql file and run that in whatever you use to manage your database.

**tim.mooney** · 10-03-2022, 22:52

Originally posted by emsmith

There's notes in the upgrade docs about this issue - you're going to have to manually go update the history tables.

10 Upgrade notes for 6.0.0

https://www.zabbix.com/documentation/6.0/en/manual/installation/upgrade_notes_600

Note the "CRITICAL" database changes - looks like you're using mysql so the instructions for the fix are here

https://www.zabbix.com/documentation/6.0/en/manual/appendix/install/db_primary_keys#mysql

The information you've provided about adding primary keys to the history-related tables is good information, but it's not related to the problem the original poster is having. The last line in the output from the logs, which your information relates to, is a notification about a separate issue.

The problem causing the upgrade failure is two lines earlier:

Code:

8:20220309:130504.855 [Z3005] query failed: [1091] Can't DROP FOREIGN KEY `c_services_1`; check that it exists [alter table services drop foreign key c_services_1]

Zabbix's database upgrade code believes there should be a foreign key constraint named `c_services_1` on the services table, but it's not present. That's what's causing the upgrade to fail.

Looking at my database for 5.0.20 (not 5.4, I'm still on 5.0 LTS):

Code:

MariaDB [zabbix]> SHOW CREATE TABLE services\G
*************************** 1. row ***************************
Table: services
Create Table: CREATE TABLE `services` (
`serviceid` bigint(20) unsigned NOT NULL,
`name` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',
`status` int(11) NOT NULL DEFAULT 0,
`algorithm` int(11) NOT NULL DEFAULT 0,
`triggerid` bigint(20) unsigned DEFAULT NULL,
`showsla` int(11) NOT NULL DEFAULT 0,
`goodsla` double NOT NULL DEFAULT 99.9,
`sortorder` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`serviceid`),
KEY `services_1` (`triggerid`),
CONSTRAINT `c_services_1` FOREIGN KEY (`triggerid`) REFERENCES `triggers` (`triggerid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC

My Zabbix services table does have the `c_services_1` foreign key constraint that the 6.0 upgrade procedure is expecting.

Sc0thu , has your database been through many Zabbix upgrades, like if you started with something like 3.0, then went to 3.2.x, then 4.0.x, then 5.0.x, then 5.4.x, etc? I've posted in these forums previously about a bunch of database differences my database had accumulated over the course of many upgrades (we started with 2.0.x).

If your database has been through many upgrades, you may want to use a tool like 'mysqldiff' (works against MariaDB too) to diff your database schema with a blank, freshly created 5.4 database, to see where your schema differs from what 5.4 is expected to have. Once you've identified the differences, you may be able to issue manual ALTER statements to get your schema to look like what it's supposed to look like.

**Sc0thu** · 11-03-2022, 02:01

emsmith thank you for your response, however the primary key updates on the history tables are unrelated, I do read the docs.

Thank you tim.mooney you were on point there, and yes I am being 'punished' for being a long time user - mysqldiff is a good shout.

It was an easy fix with -

Code:

ALTER TABLE services ADD CONSTRAINT c_services_1 FOREIGN KEY (triggerid) REFERENCES triggers (triggerid) ON DELETE CASCADE;

My point (badly made) was that this happens every time, and has numerous similar mentions in the forums, yet helpful checks/error messages or upgrade time remediation are still missing.

Ad Widget

5.4.11 -> 6.0.0 mysql upgrade fails.

5.4.11 -> 6.0.0 mysql upgrade fails.

Comment

Comment

Comment