Ad Widget

**xanadu** · 29-08-2017, 09:49

snippet of my zabbix tables:

**kloczek** · 29-08-2017, 10:49

Originally posted by xanadu

12852:20170829:093103.774 starting automatic database upgrade
12852:20170829:093103.776 [Z3005] query failed: [1005] Can't create table 'zabbix.#sql-9fb_63' (errno: 121) [alter table alerts add constraint c_alerts_5 foreign key (p_eventid) references events (eventid) on delete cascade]
12852:20170829:093103.776 database upgrade failed

IIRC err 121 it is about duplicated keys in table with unique keys.
You need to resolve this issue first.

**xanadu** · 29-08-2017, 11:02

Originally posted by kloczek

IIRC err 121 it is about duplicated keys in table with unique keys.
You need to resolve this issue first.

I've created a zabbix bug ticket:

Loading...

https://support.zabbix.com/browse/ZBX-12625

Does anyone know how I can correct the default schema? I don't have any knowledge on that..

**xanadu** · 29-08-2017, 12:05

I've managed to alter the "alerts" table with HeidiSQL. I checked the "CREATE" output accordingly. After that the database upgrade ran fine.

**kloczek** · 29-08-2017, 15:40

Originally posted by xanadu

I've created a zabbix bug ticket:

Loading...

https://support.zabbix.com/browse/ZBX-12625

Does anyone know how I can correct the default schema? I don't have any knowledge on that..

And they will advise you to delete duplicated entries in alerts table manually.
In some exact conditions not finished transactions or some DB/OS crashes you may end up with some corruptions in database like duplicated record on key which should be unique.

In your case it is problem with alerts table

Code:

mysql> SHOW CREATE TABLE alerts\G;
*************************** 1. row ***************************
alerts
CREATE TABLE `alerts` (
  `alertid` bigint(20) unsigned NOT NULL,
  `actionid` bigint(20) unsigned NOT NULL,
  `eventid` bigint(20) unsigned /OS DB NOT NULL,
  `userid` bigint(20) unsigned DEFAULT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `mediatypeid` bigint(20) unsigned DEFAULT NULL,
  `sendto` varchar(100) NOT NULL DEFAULT '',
  `subject` varchar(255) NOT NULL DEFAULT '',
  `message` text NOT NULL,
  `status` int(11) NOT NULL DEFAULT '0',
  `retries` int(11) NOT NULL DEFAULT '0',
  `error` varchar(2048) NOT NULL DEFAULT '',
  `esc_step` int(11) NOT NULL DEFAULT '0',
  `alerttype` int(11) NOT NULL DEFAULT '0',
  `p_eventid` bigint(20) unsigned DEFAULT NULL,
  `acknowledgeid` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`alertid`),
  KEY `alerts_1` (`actionid`),
  KEY `alerts_2` (`clock`),
  KEY `alerts_3` (`eventid`),
  KEY `alerts_5` (`mediatypeid`),
  KEY `alerts_6` (`userid`),
  KEY `alerts_7` (`p_eventid`),
  KEY `alerts_4` (`status`),
  KEY `c_alerts_6` (`acknowledgeid`),
  CONSTRAINT `c_alerts_1` FOREIGN KEY (`actionid`) REFERENCES `actions` (`actionid`) ON DELETE CASCADE,
  CONSTRAINT `c_alerts_2` FOREIGN KEY (`eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE,
  CONSTRAINT `c_alerts_3` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON DELETE CASCADE,
  CONSTRAINT `c_alerts_4` FOREIGN KEY (`mediatypeid`) REFERENCES `media_type` (`mediatypeid`) ON DELETE CASCADE,
  CONSTRAINT `c_alerts_5` FOREIGN KEY (`p_eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE,
  CONSTRAINT `c_alerts_6` FOREIGN KEY (`acknowledgeid`) REFERENCES `acknowledges` (`acknowledgeid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

(above is from zabbix DB used by 3.4.x so it will be a bit different in your case)
So as you see 'alerts' table has primary key on alertid column.
Try:

$ mysql <zabbix_db_name> -e "SELECT alertid FROM alerts GROUP BY alertid HAVING COUNT(*)>1;"

As long as you will not remove duplicated alert you will be not able to push DB schema upgrade.
When you will find rows with the same alertid you need to decide which one ou are going to remove using DELETE query. You as well just delete all rows with exact alertid.

PS. try to find something using google about general methodology repairing tables with dup keys with primary key
PS2. I think that it would be good if zabbix developers will provide some script with some series of queries doing DB consistency checks which could be executed on current version before upgrade.

**danil_** · 23-09-2017, 23:27

I was having exactly the same issue.
It was a surprise for me as several other instances of 3.2 were updated to 3.4 the same way without any trouble..

I tried to remove the foreign key ("drop foreign key ..."), and it seems to have helped. Upgrade went fine.

**kloczek** · 24-09-2017, 11:08

Congratulation. By remove foreign key you just started introducing even more garbage to your zabbix database.
SQL foreign keys are used to link two tables together allowing to modify table B on modify table A on some exact conditions.

**danil_** · 24-09-2017, 16:14

Originally posted by kloczek

Congratulation. By remove foreign key you just started introducing even more garbage to your zabbix database.
SQL foreign keys are used to link two tables together allowing to modify table B on modify table A on some exact conditions.

Thanks for noting this, kloczek!
I needed this change for the db-upgrade to finish succesfully I suppose, currently this FK is certainly present in the alerts' DDL. I didn't add it manually.

**oalex** · 26-09-2017, 14:15

I have another mistake
[1091] Can not DROP 'type'; check that column / key exists [alter table proxy_dhistory drop column type]
do I need to remove the key with my hands?

Ad Widget

zabbix db upgrade v3.2.7 to v3.4.1 failed

zabbix db upgrade v3.2.7 to v3.4.1 failed

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment