Zabbix upgrade 5.0LTs to 6.0LTS Primary key errors

[maxhandschild]

My project is a HA Zabbix system with a MySQL group replication running in single primary mode. To check which node is the active one, a HAProxy is used. This is linked to the database and Zabbix.

The whole system is currently running on a test system and should then be integrated into a productive one.

The problem with upgrading from 5.0LTS to 6.0LTS is that the schema will not get updated to primary keys. Thats why a group replication is not possible.




MySQL Error after upgrade from 5.0LTS to 6.0LTS succeeded:


MySQL group replication error after upgrade succeeded:


MySQL Error after upgrade from 5.0LTS to 6.0LTS not succeeded:
Zabbix log Error: Table 'item_parameter' already exists [create table item_parameter



Upgrade Process:
MySQL dump was imported from live system to test enviroment.
After successfull import we installed Zabbix 6.0LTS from the repository, zabbix-server, zabbix-agent.
We changed in MySQL set global log_bin_trust_function_creators = 1; and started the zabbix-server.
After many tries we ended up getting this two errors.

Maybe someone has a solution to this?

[tim.mooney]

I'm not using MySQL replication and I don't have Zabbix installed in a HA config, so please take this with a grain of salt.

If this is a test system and is not yet production, why not start with 6.0.13 LTS?

It's my understanding (which again, could be flawed) that one of the reasons why primary keys were added in some places at 6.0.x is specifically because the lack of the primary keys caused the types of problems you're seeing with replication.

If you start with a 6.0.x fresh install (rather than an upgrade), you will have primary keys in all places, so replication should work.

If you are set on upgrading from 5.0 LTS, then I believe you would first have to completely disable the HA setup and disable MySQL/MariaDB replication. If you're not replicating, the 5.0 -> 6.0 database upgrade should work. Then, after upgrading to 6.0.x LTS, apply the post-upgrade primary keys SQL schema change, to create the primary keys you would have had if you had started fresh at 6.0.x. One those are in place, I believe you could then reestablish MySQL replication and your HA setup.

[maxhandschild]

Thank you very much for your answer.
Due to time constraints, we would like to import the MySQL database with the Zabbix 5.0 schema and upgrade to the 6.0 schema with primary keys to start with the preferred MySQL Group Replication.
To make our HA concept work, we currently rely on a master-master replication over three servers.
We have not found an easy migration mechanism, but are open if there is an easy way to migrate the data to a newly set up Zabbix 6.0.
To have a picture of our environment, we currently have 3324 hosts, a total of 232506 items and 108313 triggers.

[cyber]

Those (final) keys were added already before 6.0 I think.. somewhere between 4.4 and 6.0..

Did something get lost from first post? Like some pics with errors or something?

[maxhandschild]

OH Sorry, thanks for letting me know. Here are the errors:

[cyber]

That seems to be somekind of external requirement by plugin... not really related to Zabbix...

Is there an option to break up replication, do all the upgrades and then do full sync or something...??

But TBH, that table should have a primary key...
schema.sql says

CREATE TABLE `acknowledges` (
`acknowledgeid` bigint unsigned NOT NULL,
`userid` bigint unsigned NOT NULL,
`eventid` bigint unsigned NOT NULL,
`clock` integer DEFAULT '0' NOT NULL,
`message` varchar(2048) DEFAULT '' NOT NULL,
`action` integer DEFAULT '0' NOT NULL,
`old_severity` integer DEFAULT '0' NOT NULL,
`new_severity` integer DEFAULT '0' NOT NULL,
[B]PRIMARY KEY (acknowledgeid)[/B]
) ENGINE=InnoDB;
CREATE INDEX `acknowledges_1` ON `acknowledges` (`userid`);
CREATE INDEX `acknowledges_2` ON `acknowledges` (`eventid`);
CREATE INDEX `acknowledges_3` ON `acknowledges` (`clock`);

Another question.. if you imported data from 5 to 6 ... did you create db schema beforehand? and by using which version schema? If upgrade procedures want to create a new table and it already exists, then upgrade breaks, IIRC.. you should have v5 schema in that DB at the moment, when you first time start your v6 server to do upgrades...

[maxhandschild]

Thank you very much for the answer.
We have already tried interrupting the replication and performing the upgrade on one node. Unfortunately, this did not work and should not make any difference whether replication is running or not.


Regarding your question, which schema we use for the upgrade, version 5, unfortunately the tables are not set to primary keys after the upgrade as in your excerpt.

Are there perhaps other possibilities to upgrade to primary keys?


This is our Table after upgrading to v6:

CREATE TABLE `acknowledges` (
  `acknowledgeid` bigint unsigned NOT NULL,
  `userid` bigint unsigned NOT NULL,
  `eventid` bigint unsigned NOT NULL,
  `clock` int NOT NULL DEFAULT '0',
  `message` varchar(2048) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAULT '',
  `action` int NOT NULL DEFAULT '0',
  `old_severity` int NOT NULL DEFAULT '0',
  `new_severity` int NOT NULL DEFAULT '0',
  KEY `acknowledges_1` (`userid`),
  KEY `acknowledges_2` (`eventid`),
  KEY `acknowledges_3` (`clock`),
  KEY `acknowledgesid` (`acknowledgeid`),
  CONSTRAINT `c_acknowledges_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `c_acknowledges_2` FOREIGN KEY (`eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin;
/*!40101 SET character_set_client = @saved_cs_client */;
​

Alternatively, is there a good migration mechanism to import a data export from production server v5 into a new freshly set up zabbix in version 6?
We would like to install the zabbix according to the instructions "https://www.zabbix.com/de/download?z...ysql&ws=apache" and then import the data.
The question is whether it is possible to export the data via Zabbix Frontend or via MySQL Dump and then import it into the newly set up system.

[cyber]

5.0.33 schema.sql... I see primary key present... What exact version you are running there? Maybe it would be better to try to do v5 minor upgrades first until latest possible and then try to upgrade to 6?

CREATE TABLE `acknowledges` (
`acknowledgeid` bigint unsigned NOT NULL,
`userid` bigint unsigned NOT NULL,
`eventid` bigint unsigned NOT NULL,
`clock` integer DEFAULT '0' NOT NULL,
`message` varchar(2048) DEFAULT '' NOT NULL,
`action` integer DEFAULT '0' NOT NULL,
`old_severity` integer DEFAULT '0' NOT NULL,
`new_severity` integer DEFAULT '0' NOT NULL,
PRIMARY KEY (acknowledgeid)
) ENGINE=InnoDB;
CREATE INDEX `acknowledges_1` ON `acknowledges` (`userid`);
CREATE INDEX `acknowledges_2` ON `acknowledges` (`eventid`);
CREATE INDEX `acknowledges_3` ON `acknowledges` (`clock`);

We would like to install the zabbix according to the instructions "https://www.zabbix.com/de/download?z...ysql&ws=apache" and then import the data

Well that's the problem here, you cannot and should not try to import older format to newer DB...

You can do almost all in that page, but where it says to create DB, then find the schema.sql for your current version... (5.0.xx) and use that one. then you should be able to copy all your old data over to new DB without any issues (mysqldump or whatever tool) .... and then do upgrade procedures. It is much less painful than trying to understand what and how to modify, if you select from old and insert to new.. It is all already done for you in upgrade procedures..

When I did my 4.4 to 6 upgrade, I also created 4.4 DB on newer platform and imported data from old instance, then started up new version server, which did all the needed work...

[maxhandschild]

Thank you for your tip.
We will try it and see if it works. If it doesn' t work, we will contact you again.

[maxhandschild]

We tried to insert a mysqldump into the schema of the data package 6.0. Unfortunately, it did not work. The error duplicated Insert on table action occurs.



MySQL insert dump:
mysqldump -u root -p --complete-insert --lock-all-tables --no-create-db --no-create-info --extended-insert zabbix > zabbix5_insert1.sql


Is there perhaps another way to get the database to primary keys?

[tim.mooney]

As cyber said previously, you cannot load data from tables in 5.0 format into tables in a 6.0 database, at least for any table that had schema changes between 5.0 and 6.0.

Also as cyber said previously, the 'acknowledges' table should have already had a PRIMARY KEY even at 5.0, so the error you showed earlier indicates that there's something wrong with your 5.0 schema. Did you make modifications to the schema as part of setting up replication?