Upgrade from 2.2 -> 3.0

[danims]

Hello

I'm trying to upgrade from Zabbix 2.2 to 3.0. The manual says this is supported, although it didn't work. So my plan was to upgrade 2.2 -> 2.4, and then from 2.4 to 3.0.

I've created a snapshot of the VM so I can safely go back if something fails.

I've uninstalled 2.2 and installed 2.4. When I start zabbix_server, it seems the database upgrade fails:

  4992:20160904:234132.699 Starting Zabbix Server. Zabbix 2.4.8 (revision 59539).
  4992:20160904:234132.699 ****** Enabled features ******
  4992:20160904:234132.699 SNMP monitoring:           YES
  4992:20160904:234132.699 IPMI monitoring:           YES
  4992:20160904:234132.699 WEB monitoring:            YES
  4992:20160904:234132.699 VMware monitoring:         YES
  4992:20160904:234132.699 Jabber notifications:      YES
  4992:20160904:234132.699 Ez Texting notifications:  YES
  4992:20160904:234132.699 ODBC:                      YES
  4992:20160904:234132.699 SSH2 support:              YES
  4992:20160904:234132.699 IPv6 support:              YES
  4992:20160904:234132.699 ******************************
  4992:20160904:234132.699 using configuration file: /etc/zabbix/zabbix_server.conf
  4992:20160904:234132.705 current database version (mandatory/optional): 02020000/02020001
  4992:20160904:234132.705 required mandatory version: 02040000
  4992:20160904:234132.705 starting automatic database upgrade
  4992:20160904:234132.708 completed 0% of database upgrade
  4992:20160904:234132.712 completed 1% of database upgrade
  4992:20160904:234132.740 completed 2% of database upgrade
  4992:20160904:234132.744 completed 3% of database upgrade
  4992:20160904:234132.752 completed 4% of database upgrade
  4992:20160904:234132.760 completed 5% of database upgrade
  4992:20160904:234132.803 completed 6% of database upgrade
  4992:20160904:234132.813 completed 7% of database upgrade
  4992:20160904:234132.813 [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)]
  4992:20160904:234132.813 database upgrade failed

What am I doing wrong, or what information should I post here?

Thanks
Daniel

[bbrendon]

Ick. Compare your 2.2 schema with the default and see if there are any mismatches.

[danims]

Thank you for your suggestion. Indeed, there are differences (a lot, not only in this particular table):



Left is my 2.2.11 installation, right is what I've downloaded from the 2.2.11 sources.

Not sure how I should proceed. I'm not a database guy. I guess it won't work to just import the data to the new scheme, but I'll try anyway.

[danims]

Did the following: I created a new zabbix database, imported the schema from the latest 2.2 release, and imported my data into this schema. This has completed without errors.

Unfortunately, the same error appears when I try to install Zabbix 3.0. The docs say it's possible to upgrade 2.2->3.0, so I guess I don't have to do the extra mile and install 2.4 first.

The error still is:

  3135:20160907:153245.750 starting automatic database upgrade
  3135:20160907:153245.750 [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)]
  3135:20160907:153245.750 database upgrade failed

[asteroidyorkton]

Not sure if this method is right.. Since you've built a new DB, I'd say you could try,
Create New Database, import new schema (3.0 source files has a copy of DB schema.sql), run upgrade and then import data? :-)

  3135:20160907:153245.750 starting automatic database upgrade
  3135:20160907:153245.750 [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)]
  3135:20160907:153245.750 database upgrade failed

Otherwise you could just drop constraint on those tables and let the upgrade finish and you can re-create it. Remember you'll get this error for 2 or 3 more tables. I had dropped constraint and upgraded it successfully. Later created it back.