Zabbix DB upgrade to 2.4 fails

[jasonc]

I am attempting to upgrade Zabbix Server from 2.2.6 to 2.4.7. I'm running on CentOS 6.7 and used the Zabbix repos for yum to do the upgrade.

The first attempt at upgrading the database, Zabbix complained it couldn't create a foreign key constraint:

Z3005] query failed: [1005] Can't create table 'zabbix.#sql-dd6_3' (errno: 150) [alter table trigger_discovery add constraint c_trigger_discovery_1 foreign key (triggerid) references triggers (triggerid) on delete cascade]

I discovered that the engine on the triggers table was MyISAM and trigger_discovery was InnoDB. I altered the triggers table to InnoDB.

Now I get the error:

[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)]

This server was the primary node in a two-node distributed system. If I query data in the triggers_discovery_tmp table and compare it to the triggers table, it has triggerid's that are not in the triggers table thus preventing the creation of the contraint.

Help!!!

[ingus.vilnis]

Hello and welcome to Zabbix forums!

Looks like your database was not created correctly when setting up initially.

Please be advised that failing to perform proper upgrade might result in total loss of your Zabbix data so please be very careful. It would be very recommended if you could try the upgrade on some test machine first.

Now that you have been warned here is what you can try.

1. Stop your Zabbix server
2. Backup your current DB!
3. Backup all Zabbix configuration files!
4. Export current Zabbix database without DB structure

5. mysqldump -uzabbix -p --no-create-info zabbix > zabbixdata.sql

6. Drop Zabbix database
7. Make sure all new databases will now be created with InnoDB
8. Create new Zabbix database

9. create database zabbix character set utf8 collate utf8_bin;

10. Uninstall Zabbix server
11. Install new Zabbix 2.2 server
12. Import default MySQL schema from downloaded Zabbix packages

13. mysql -uroot -p zabbix < schema.sql

14. Import dump from your old Zabbix DB

15. mysql -uroot -p zabbix < zabbixdata.sql

16. Try upgrading to 2.4 again

But again, please try this on a test machine before running it all on production DB.
Hope this helps.

Best Regards,
Ingus

[jasonc]

Thanks for the info!!

I cloned the zabbix vm so I could test the upgrade. In addition to the schema.sql, I had to run the images.sql and data.sql imports as well. I ensured the zabbix mysql user was created with all privs and granted localhost access. I started the zabbix-server service before I tried to re-import my data to ensure that it was running properly, and it is (of course, I copied my zabbix_server.conf back in place).

However, I think I still have garbage data problems, because I cannot import my data:

ERROR 1062 (23000) at line 785: Duplicate entry '1' for key 'PRIMARY'

Previously I was running 2.2.6, and the repo installed 2.2.11. Could this be a problem?

At this point I'm not averse to loosing any historical data, but I would like to not have to re-input all of my hosts, items, triggers, etc.

Any ideas? MUCH thanks in advance!

[ingus.vilnis]

Hi,

Now that is another error than you had before.

Well, it can be fixed but requires some more modifications to the DB.
In that case the best you can do is to search MySQL docs and help and try dropping and creating those indexes. I know it is time consuming but that might be your only way. 2.2.11 instead of 2.2.6 should not be the problem in this case.

However if you are really fine with losing all your item history then really you have a chance to have a fresh start, install latest OS and DB, and create Zabbix with unmodified DB schema. Also if you are willing to wait a month or two you can install Zabbix 3.0.

Exporting all your hosts and templates is easy.

1. Just go to Configuration -> Hosts
2. Select all hosts with checkboxes on the left.
3. Choose Export selected in bottom left dropdown and click Go
4. You will be able to save all hosts in XML file.
5. Do this multiple times if you have hosts in multiple pages.
6. And do the same on Templates page.

Then import that in the new Zabbix.
What will not be exported - actions, discovery rules, users. There you might want to pick them later from DB backup.

Fingers crossed you'll be able to upgrade successfully!

Best Regards,
Ingus

[jasonc]

Thanks Ingus for all your help!!

What finally worked was to follow your instructions, but delete the duplicate rows in the two ..._tmp tables that were causing the problem. As it turns out, because I had a child node, the upgrade prepended a series of digits to some fields to preserve their values from the child node, but that caused duplicate entries when trying to upgrade the database.

I'm at 2.4 and running smooth.

Hope this helps anyone else with like problems.

Cheers,

Jason

[ingus.vilnis]

Hi Jason,

I'm glad you finally were able to upgrade.
Yes, nodes might make the process whole lot more complicated but great that you managed it.

Best Regards,
Ingus

[GiovanniFormisano]

Hi guys,

I have a question , it's possible export and import hosts from zabbix 1.8 to zabbix 4.4 ? When i try this operation i receive this error: Invalid tag "/zabbix_export/hosts/host(1)/items/item(1)": the tag "port" is missing.

It's possible bypassed or this operation it's not permitted given that two versions are too different ?

Thank you so much
Regards
Giovanni.