Upgrade database to 2.2 fails

[patrick]

Hi,
I've installed Zabbix 2.2 on a new server (CentOS 6). Everything seems OK but when I run zabbix Server it fails attempting to upgrade my database
I don't know why
Who could help?
Thanks

5748:20131115:113855.789 Starting Zabbix Server. Zabbix 2.2.0 (revision 40163).
5748:20131115:113855.798 ****** Enabled features ******
5748:20131115:113855.799 SNMP monitoring: YES
5748:20131115:113855.799 IPMI monitoring: YES
5748:20131115:113855.799 WEB monitoring: YES
5748:20131115:113855.799 VMware monitoring: YES
5748:20131115:113855.799 Jabber notifications: YES
5748:20131115:113855.799 Ez Texting notifications: YES
5748:20131115:113855.799 ODBC: YES
5748:20131115:113855.799 SSH2 support: YES
5748:20131115:113855.799 IPv6 support: YES
5748:20131115:113855.799 ******************************
5748:20131115:113855.799 using configuration file: /etc/zabbix/zabbix_server.conf
5748:20131115:113856.006 current database version (mandatory/optional): 02010000/02010000
5748:20131115:113856.006 required mandatory version: 02020000
5748:20131115:113856.006 starting automatic database upgrade
5748:20131115:113856.036 completed 0% of database upgrade
5748:20131115:113856.068 completed 1% of database upgrade
5748:20131115:113856.122 completed 2% of database upgrade
5748:20131115:113856.182 completed 3% of database upgrade
...
5748:20131115:113901.331 completed 36% of database upgrade
5748:20131115:113901.465 completed 37% of database upgrade
5748:20131115:113901.714 [Z3005] query failed: [1005] Can't create table 'zabbix.#sql-b6a_684' (errno: 150) [alter table application_template add constraint c_application_template_1 foreign key (applicationid) references applications (applicationid) on delete cascade]
5748:20131115:113901.714 database upgrade failed

[patrick]

A solution


I have looked at the Zabbix database tables with phpmyadmin while upgrading and I saw that when I wanted to work with a new table (For example "application_template") it had been created as "innoDB/utf_general_ci", but the other tables of my zabbix database are "MyISAM/utf8_bin".
So with phpMyAdmin, I have change the new table and re-run zabbix-server. I have done that for all the new tables created by the database upgrade process and now it's OK.
What do you think about that? I don't know if it's a good method.

[DidierJ]

Works on Debian 7 too

Hello

I had the same trouble using a Debian-7 server.
Zabbix 2.0.9 was installed from official Zabbix packages, upgrade to 2.2.0 was performed with packages too.
Restarting zabbix-server right after the upgrade fails : log shows it stops when trying to modify a table. Messages are of the kind "[Z3005] query failed: [1005] Can't create table 'zabbix.#sql-cc4_13e7a8' (errno: 150) [alter table XXTABLEXX add constraint c_host_discovery_1 foreign key (hostid) references hosts (hostid) on delete cascade]"

I fired up PhpMyAdmin and noticed all new tables are created in InnoDB by default, but fail. Using PhpMyAdmin, each new table has been switched to MyISAM, and progressively, table after table, the database upgrade succeeded and the service fully started. There's some 10 tables to modify this way to reach the completed 100% of database upgrade / database upgrade fully completed message.

Thanks a lot for the tip

[richlv]

possibly related issue - https://support.zabbix.com/browse/ZBX-7640