Import Data From Zabbix 2.2.7 to 2.4.1

[chrisw]

Hello All!

I recently had a horribly failed update from 2.2.7 to 2.4.1 which more or less required me to blow away the old Zabbix 2.2.7 DB. I have 2.4.1 up and running now, and I was able to import 90% of my things from 2.2.7 exports, with exception to 2 things.

My Screens will not import, as they say pretty much every graph is missing. I had 9 fairly comprehensive screens set up for my network, and I was working on building more, but none of them will import. Is there any way to further troubleshoot this or force them to import? I really don't want to have to re-create all those screens.

My Historical Data is missing, of course due to blowing away the old DB. I still have backups of the old DB, and I copied my old 2.2.7 installation to a new folder / old DB to a new DB, and the historical data is still intact. Is there any way to "export / import", whether at the Zabbix or DB level, the historical data from 2.2.7 to 2.4.1? Were there any changes to that part of the DB that I would have to apply?

Neither one is truly the end of the world if they can't be recovered, but the Screens are a whole wack of work, and the historical data I like to have for identifying new trends / issues / baselines.

Any assistance would be greatly appreciated! Thanks!

[ArtemK]

2.4.1 should upgrade your 2.2.7 database. So, if you have 2.2.7 DB backup, restore it to new database and point zabbix to it, 2.4 server should automatically upgrade your DB and you'll get your historical data, screens, etc...

[chrisw]

Hi ArtemK,

The Automatic Upgrade is what got me in this situation in the first place as it failed halfway through and caused me to have to restore from the backup. That is why I'm looking to do it manually.

[kloczek]

Hi ArtemK,

The Automatic Upgrade is what got me in this situation in the first place as it failed halfway through and caused me to have to restore from the backup. That is why I'm looking to do it manually.

Failed with what message in server log?

[chrisw]

69807-  2032:20141121:124000.095 Zabbix Server stopped. Zabbix 2.2.7 (revision 50148).
69808- 16043:20141121:130112.837 Starting Zabbix Server. Zabbix 2.4.1 (revision 49643).
69809- 16043:20141121:130112.938 ****** Enabled features ******
69810- 16043:20141121:130112.939 SNMP monitoring:           YES
69811- 16043:20141121:130112.939 IPMI monitoring:            NO
69812- 16043:20141121:130112.939 WEB monitoring:            YES
69813- 16043:20141121:130112.939 VMware monitoring:          NO
69814- 16043:20141121:130112.939 Jabber notifications:       NO
69815- 16043:20141121:130112.939 Ez Texting notifications:  YES
69816- 16043:20141121:130112.939 ODBC:                       NO
69817- 16043:20141121:130112.939 SSH2 support:               NO
69818- 16043:20141121:130112.939 IPv6 support:               NO
69819- 16043:20141121:130112.939 ******************************
69820- 16043:20141121:130112.939 using configuration file: /etc/zabbix/zabbix_server.conf
69821: 16043:20141121:130113.029 current database version (mandatory/optional): 02020000/02020001
69822- 16043:20141121:130113.029 required mandatory version: 02040000
69823: 16043:20141121:130113.029 starting automatic database upgrade
69824: 16043:20141121:130113.030 completed 0% of database upgrade
69825: 16043:20141121:130113.165 completed 1% of database upgrade
69826: 16043:20141121:130113.397 completed 2% of database upgrade
69827: 16043:20141121:130113.566 completed 3% of database upgrade
69828: 16043:20141121:130113.567 completed 4% of database upgrade
69829: 16043:20141121:130113.733 completed 5% of database upgrade
69830- 16043:20141121:130114.172 [Z3005] query failed: [1005] Can't create table 'zabbix.#sql-3db2_80' (errno: 150) [alter table trigger_discovery add constraint c_trigger_discovery_1 foreign key (triggerid) references triggers (triggerid) on delete cascade]
69831: 16043:20141121:130114.173 database upgrade failed

[ArtemK]

Did you dropped any indexes from your 2.2.7 db?

[chrisw]

Did not manually touch anything, just attempted the upgrade for those log lines.

[nick0909]

Seems like the same thing happened to this guy. It looks like from the documentation that zabbix will upgrade the database if it detects an old version, but it looks like that is the part failing for you. I downloaded the source and they have a note that they no longer include manual database upgrade scripts, which is too bad. If you restart the zabbix service does it error out at the same point again? I guess if it keeps failing the only option is to file a bug report. I am still on 2.2.5 and not planning on going to 2.4 for a while, so can't try it out myself.

[Shurman]

Ran into same issue today.
Found an open issue: https://support.zabbix.com/browse/ZBX-9111
Too bad, my current 2.2.4 hangs periodically. :-(

[kloczek]

69830- 16043:20141121:130114.172 [Z3005] query failed: [1005] Can't create table 'zabbix.#sql-3db2_80' (errno: 150) [alter table trigger_discovery add constraint c_trigger_discovery_1 foreign key (triggerid) references triggers (triggerid) on delete cascade]
69831: 16043:20141121:130114.173 database upgrade failed

Seems it was not possible to create temporary file.
Possible cause: lack enough free disk space

[Shurman]

Seems it was not possible to create temporary file.
Possible cause: lack enough free disk space

If you search the internet, you find it is foreign key related.

http://forums.mysql.com/read.php?22,19755,19755

http://www.eliacom.com/mysql-gui-wp-errno-150.php

[Shurman]

Too bad that a blocker issue ZBX-9111 was discovered a month ago and still is not fixed :-(

[kloczek]

If you search the internet, you find it is foreign key related.

http://forums.mysql.com/read.php?22,19755,19755

http://www.eliacom.com/mysql-gui-wp-errno-150.php

Again: was not possible to create file.
Did you check how much free disk space you had when mysqld stopped?

[Shurman]

Again: was not possible to create file.
Did you check how much free disk space you had when mysqld stopped?

Again, please read the links I provided above.

Ofcourse I have ~40Gb free, my DB is ~600Mb, mysql variables related to temp tables are default, so temp tables are created on disk, size is not restricted.

[Shurman]

For whom it may concern... I solved the problem.
This comment helped.

The problem is that upgrade process creates a copy of upgraded tables, copied data, drops original tables, then renames temp tables to the correct name. But these tables are created with InnoDB storage, so in case we use some other storage engine (I use MyISAM), the constraints fail to be created (constraints across different storage engines are not allowed). I just disabled InnoDB and restarted zabbix server and all went fine. The only thing - I had an orphaned trigger_discovery_tmp table, so I had to run 'mysqladmin flush-tables' as root first, since the installer thought that table exists, while 'show tables' did not show the table.

Not enough space, huh?