Database upgrade error

[ascarneiro]

Hello,

Im using Zabbix for more than 12 years, from source.

To upgrade I backup everything, compile and install the binary, upgrade php files, and done.

Today I try upgrade from 4.0.3 to 4.4.0 and zabbix_server show the error on log:

493:20191026:164751.353 [Z3005] query failed: [1091] Can't DROP 'c_dchecks_1'; check that column/key exists [alter table dchecks drop foreign key c_dchecks_1]

I search and found this solution: https://support.zabbix.com/browse/ZBX-16141

This fix this issue, then zabbix server stop again on database upgrade, new error:

1906:20191026:165919.883 [Z3005] query failed: [1091] Can't DROP 'c_host_inventory_1'; check that column/key exists [alter table host_inventory drop foreign key c_host_inventory_1]

Any advice?

Thank very much

ascarneiro

[rharaschak]

Same issue here. Went from 4.0.10 to 4.4.0.

[rharaschak]

Can't edit my post but I found a fix. This query should help:

alter table host_inventory ADD CONSTRAINT `c_host_inventory_1` FOREIGN KEY (`hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE

[zabbixuser321]

please help with this error



20545:20210409:092407.371 using configuration file: /etc/zabbix/zabbix_server.c onf
20545:20210409:092407.380 current database version (mandatory/optional): 050100 44/05010044
20545:20210409:092407.380 required mandatory version: 05020000
20545:20210409:092407.380 starting automatic database upgrade
20545:20210409:092407.398 [Z3005] query failed: [1091] Can't DROP 'c_screens_1' ; check that column/key exists [alter table screens drop foreign key c_screens_1 ]
20545:20210409:092407.399 database upgrade failed
20567:20210409:092417.621 Starting Zabbix Server. Zabbix 5.2.6 (revision 798506 596c).
20567:20210409:092417.622 ****** Enabled features ******
20567:20210409:092417.622 SNMP monitoring: YES
20567:20210409:092417.622 IPMI monitoring: YES
20567:20210409:092417.622 Web monitoring: YES
20567:20210409:092417.622 VMware monitoring: YES
20567:20210409:092417.622 SMTP authentication: YES
20567:20210409:092417.622 ODBC: YES
20567:20210409:092417.622 SSH support: YES
20567:20210409:092417.622 IPv6 support: YES
20567:20210409:092417.622 TLS support: YES
20567:20210409:092417.622 ******************************
20567:20210409:092417.622 using configuration file: /etc/zabbix/zabbix_server.c onf
20567:20210409:092417.632 current database version (mandatory/optional): 050100 44/05010044
20567:20210409:092417.633 required mandatory version: 05020000
20567:20210409:092417.633 starting automatic database upgrade
20567:20210409:092417.654 [Z3005] query failed: [1091] Can't DROP 'c_screens_1' ; check that column/key exists [alter table screens drop foreign key c_screens_1 ]

[dimir]

Looks like someone/something was messing with your database. You have missing foreign key. Restore it and restart zabbix server again.

In order to restore the foreign key, connect to your database (assuming MySQL) and run:

ALTER TABLE `screens` ADD CONSTRAINT `c_screens_1` FOREIGN KEY (`templateid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE;

[Johnson425]

[Z3005] query failed: [1060] Duplicate column name 'p_eventid' [alter table alerts add p_eventid bigint unsigned] means that the upgrade script is trying to add a column to the alerts table but a column with that name currently exists! (Probably from a previously failed attempt at upgrade)

You need to mitigate all other issues that caused it to fail in the first place and then drop that column with

alter table alerts drop p_eventid;

and try running zabbix_server again to retry the database upgrade.



TelltheBell

[ch.a.sh]

Looks like someone/something was messing with your database. You have missing foreign key. Restore it and restart zabbix server again.

In order to restore the foreign key, connect to your database (assuming MySQL) and run:

ALTER TABLE `screens` ADD CONSTRAINT `c_screens_1` FOREIGN KEY (`templateid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE;

Hi, I stumbled with the same error, it gives me this error

ERROR 1005 (HY000): Can't create table `zabbix`.`screens` (errno: 121 "Duplicate key on write or update")