-
Problem report
-
Resolution: Won't fix
-
Critical
-
None
-
3.4.1
-
None
-
None
-
debian 8, postgres 9.4
When upgrading from v3.2.0 to v3.4.1 I get next error:
62928:20170919:092214.034 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: insert or update on table "alerts" violates foreign key constraint "c_alerts_5"
DETAIL: Key (p_eventid)=(12279274) is not present in table "events".
[update alerts set p_eventid=12271751 where eventid=12272265;
...
update alerts set p_eventid=12287860 where eventid=12288925;
] failed, setting transaction as failed
62928:20170919:092214.042 query [txnlev:1] [rollback;]
62928:20170919:092214.048 database upgrade failed
62928:20170919:092214.048 End of DBcheck_version():FAIL
Current schema:
\d+ alerts; : Table "public.alerts" Column | Type | Modifiers | Storage | Stats target | Description -------------+------------------------+----------------------------------------+----------+--------------+------------- alertid | bigint | not null | plain | | actionid | bigint | not null | plain | | eventid | bigint | not null | plain | | userid | bigint | | plain | | clock | integer | not null default 0 | plain | | mediatypeid | bigint | | plain | | sendto | character varying(100) | not null default ''::character varying | extended | | subject | character varying(255) | not null default ''::character varying | extended | | message | text | not null default ''::text | extended | | status | integer | not null default 0 | plain | | retries | integer | not null default 0 | plain | | error | character varying(128) | not null default ''::character varying | extended | | esc_step | integer | not null default 0 | plain | | alerttype | integer | not null default 0 | plain | | Indexes: "alerts_pkey" PRIMARY KEY, btree (alertid) "alerts_1" btree (actionid) "alerts_2" btree (clock) "alerts_3" btree (eventid) "alerts_4" btree (status, retries) "alerts_5" btree (mediatypeid) "alerts_6" btree (userid) Foreign-key constraints: "c_alerts_1" FOREIGN KEY (actionid) REFERENCES actions(actionid) ON DELETE CASCADE "c_alerts_2" FOREIGN KEY (eventid) REFERENCES events(eventid) ON DELETE CASCADE "c_alerts_3" FOREIGN KEY (userid) REFERENCES users(userid) ON DELETE CASCADE "c_alerts_4" FOREIGN KEY (mediatypeid) REFERENCES media_type(mediatypeid) ON DELETE CASCADE
As I understood by https://support.zabbix.com/browse/ZBX-12625 I have to manually change DB schema, for example run the command:
ALTER TABLE ONLY alerts ADD CONSTRAINT c_alerts_5 FOREIGN KEY (p_eventid) REFERENCES events (eventid) ON DELETE CASCADE;
Of maybe minor upgrade from 3.2.0 to 3.2.6 and then to 3.4.1 can help too?
Thanks in advance.