Zabbix upgrade issues - 2.2 to 3.2

[elikatz79]

Hey all,

I'm trying to have a "dry run" of an upgrade we need to perform in prod, here are the details:
current server (physical server) - CentOS 7, Zabbix 2.2.9
test server (vm) - CentOS 7, Zabbix 2.2.18 (that's what the repos installed)

Trying to upgrade to the latest using this procedure:

https://www.zabbix.com/documentation/3.2/manual/installation/upgrade_packages/rhel_centos

When starting the zabbix-server I'm running into this error - which I fix by running SET GLOBAL FOREIGN_KEY_CHECKS=0; :
21427:20170508:171505.502 Got signal [signal:15(SIGTERM),sender_pid:1,sender_uid:0,reaso n:0]. Exiting ...
21430:20170508:171505.502 Got signal [signal:15(SIGTERM),sender_pid:1,sender_uid:0,reaso n:0]. Exiting ...
21434:20170508:171505.503 Got signal [signal:15(SIGTERM),sender_pid:1,sender_uid:0,reaso n:0]. Exiting ...
21437:20170508:171505.503 Got signal [signal:15(SIGTERM),sender_pid:1,sender_uid:0,reaso n:0]. Exiting ...
21407:20170508:171505.503 Got signal [signal:15(SIGTERM),sender_pid:16884,sender_uid:0,r eason:0]. Exiting ...
21407:20170508:171507.505 syncing history data...
21407:20170508:171507.514 syncing history data done
21407:20170508:171507.514 syncing trends data...
21407:20170508:171507.518 syncing trends data done
21407:20170508:171507.519 Zabbix Server stopped. Zabbix 2.2.18 (revision 67443).
17257:20170508:173144.209 Starting Zabbix Server. Zabbix 3.2.6 (revision 67849).
17257:20170508:173144.209 ****** Enabled features ******
17257:20170508:173144.209 SNMP monitoring: YES
17257:20170508:173144.209 IPMI monitoring: YES
17257:20170508:173144.209 Web monitoring: YES
17257:20170508:173144.209 VMware monitoring: YES
17257:20170508:173144.209 SMTP authentication: YES
17257:20170508:173144.209 Jabber notifications: YES
17257:20170508:173144.209 Ez Texting notifications: YES
17257:20170508:173144.209 ODBC: YES
17257:20170508:173144.209 SSH2 support: YES
17257:20170508:173144.209 IPv6 support: YES
17257:20170508:173144.209 TLS support: YES
17257:20170508:173144.209 ******************************
17257:20170508:173144.209 using configuration file: /etc/zabbix/zabbix_server.conf
17257:20170508:173144.220 current database version (mandatory/optional): 02020000/02020001
17257:20170508:173144.220 required mandatory version: 03020000
17257:20170508:173144.220 starting automatic database upgrade
17257:20170508:173144.240 completed 0% of database upgrade
17257:20170508:173144.499 completed 1% of database upgrade
17257:20170508:173144.607 completed 2% of database upgrade
17257:20170508:173144.718 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`trigger_discovery`, CONSTRAINT `c_trigger_discovery_1` FOREIGN KEY (`triggerid`) REFERENCES `triggers` (`triggerid`) ON DELETE CASCADE) [insert into trigger_discovery (select triggerid,parent_triggerid from trigger_discovery_tmp)]
17257:20170508:173144.719 database upgrade failed


Now I'm running into a new issue which I can't resolve:

17289:20170508:173215.422 database upgrade failed
17300:20170508:173225.497 Starting Zabbix Server. Zabbix 3.2.6 (revision 67849).
17300:20170508:173225.498 ****** Enabled features ******
17300:20170508:173225.498 SNMP monitoring: YES
17300:20170508:173225.498 IPMI monitoring: YES
17300:20170508:173225.498 Web monitoring: YES
17300:20170508:173225.498 VMware monitoring: YES
17300:20170508:173225.498 SMTP authentication: YES
17300:20170508:173225.498 Jabber notifications: YES
17300:20170508:173225.498 Ez Texting notifications: YES
17300:20170508:173225.498 ODBC: YES
17300:20170508:173225.498 SSH2 support: YES
17300:20170508:173225.498 IPv6 support: YES
17300:20170508:173225.498 TLS support: YES
17300:20170508:173225.498 ******************************
17300:20170508:173225.498 using configuration file: /etc/zabbix/zabbix_server.conf
17300:20170508:173225.500 current database version (mandatory/optional): 02030020/02030020
17300:20170508:173225.500 required mandatory version: 03020000
17300:20170508:173225.500 starting automatic database upgrade
17300:20170508:173225.500 [Z3005] query failed: [1050] Table 'item_condition' already exists [create table item_condition (
item_conditionid bigint unsigned not null,
itemid bigint unsigned not null,
operator integer default '8' not null,
macro varchar(64) default '' not null,
value varchar(255) default '' not null,
primary key (item_conditionid)
) engine=innodb]
17300:20170508:173225.500 database upgrade failed


Any help is much appreciated!
Thanks!

[elikatz79]

Is there a solution for this?

[elikatz79]

Any ideas?

Anyone else had the same issue? and was able to resolve it and keep the original data?

[okoester]

Same problem here

Hi,

I'm running exactly into the same problem while upgrading from Zabbix 2.2 to Zabbix 3.2 After 6% upgrading I get this result

17050:20170521:101421.241 query [txnlev:1] [create table item_condition (
item_conditionid bigint unsigned not null,
itemid bigint unsigned not null,
operator integer default '8' not null,
macro varchar(64) default '' not null,
value varchar(255) default '' not null,
primary key (item_conditionid)
) engine=innodb]
17050:20170521:101421.319 [Z3005] query failed: [1050] Table 'item_condition' already exists [create table item_condition (
item_conditionid bigint unsigned not null,
itemid bigint unsigned not null,
operator integer default '8' not null,
macro varchar(64) default '' not null,
value varchar(255) default '' not null,
primary key (item_conditionid)
) engine=innodb]
17050:20170521:101421.319 query [create table item_condition (
item_conditionid bigint unsigned not null,
itemid bigint unsigned not null,
operator integer default '8' not null,
macro varchar(64) default '' not null,
value varchar(255) default '' not null,
primary key (item_conditionid)
) engine=innodb] failed, setting transaction as failed
17050:20170521:101421.319 query [txnlev:1] [rollback;]
17050:20170521:101421.320 database upgrade failed
17050:20170521:101421.320 End of DBcheck_version():FAIL

Just dropped DB again (I think 10th time after some other issues).
Here's what I did this time (in MySQL):

drop database zabbix;
create database zabbix character set uf8 collate utf8_bin;

Then (from command prompt) while zabbix.sql is my former zabbix2.2 database:

mysql -u root -p zabbix < zabbix.sql

Unsure if it would be an option to drop table 'item_condition'.

Any ideas or mentions?

w/regards
Olaf

[elikatz79]

Might have found a solution

Hi Olaf,

Late afternoon on Friday I was able to find a solution using this following link:

https://www.zabbix.com/forum/showthread.php?t=47522

(I don't understand Russian but Google translate was good enough...)

and using the Zabbix instruction for the rest of the process:

https://www.zabbix.com/documentation/3.2/manual/installation/upgrade_packages/rhel_centos

Here is the procedure I ran:
1. Install a new server with version 2.2.x (our original is 2.2.9, I installed 2.2.18)
2. Drop the data base schema that was created with the new installation using the link i added. (bottom of the page)
3. Import the production backup to the new server.
4. Make sure the zabbix server is stopped: systemctl stop zabbix-server
5. Install the 3.x repo: rpm -Uvh http://repo.zabbix.com/zabbix/3.2/rh...el7.noarch.rpm
6. Upgrade Zabbix: yum upgrade zabbix-server-mysql zabbix-web-mysql zabbix-agent
7. Login to the data base and disable foreign keys checks: SET GLOBAL FOREIGN_KEY_CHECKS=0;
8. Start Zabbix (which upgrades the data base, tail your logs): systemctl start zabbix-server
9. Enable the foreign keys checks: SET GLOBAL FOREIGN_KEY_CHECKS=1;

I ran this process successfully twice on my dry run server, will run the actual upgrade this coming Friday.

[okoester]

Another solution

Hi elikatz,

I might have another solution on this:

When I wrote my last thread I already did the dop database; create database thing.

On my first try I just said

create database zabbix;

That did not work.

But when I said

create database zabbix character set uf8 collate utf8_bin;

it worked. So I think that creating the database with explicitly telling to use utf8 did the trick.
My former installation told me that there was something like swedish latin1 stuff as character set

Now everything is working very well, all data was imported and things are running smoothly with Zabbix 3.2 and MySQL 5.7.18

regards