zabbix upgrade 3.2 failed with mysql error duplicate entry

[mazdajai]

We are trying to upgrade from 2.2 to 3.2 running Centos 7 with MariaDB but it failed with the following error.

It appears that the upgrade is trying to drop column id. Therefore we manually drop the primary key so 'id' can go. However, we ran into 'Foreign key constraint' issues (see bottom).

Any thoughts? Does anyone has the database upgrade script so I can better understand what the upgrade trying to do?

Duplicate entry 'xxx' for key 'PRIMARY' [alter table history_text drop column id]

Create Table: CREATE TABLE `history_text` (
`id` bigint(20) unsigned NOT NULL,
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` text NOT NULL,
`ns` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`itemid`,`id`,`clock`),
KEY `history_text_1` (`itemid`,`clock`)

MariaDB [zabbixdb]> describe history_text;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id | bigint(20) unsigned | NO | PRI | NULL | |
| itemid | bigint(20) unsigned | NO | PRI | NULL | |
| clock | int(11) | NO | PRI | 0 | |
| value | text | NO | | NULL | |
| ns | int(11) | NO | | 0 | |
+--------+---------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

alter table history_text drop primary key;

MariaDB [zabbixdb]> describe history_text;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| itemid | bigint(20) unsigned | NO | MUL | NULL | |
| clock | int(11) | NO | | 0 | |
| value | text | NO | | NULL | |
| ns | int(11) | NO | | 0 | |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)


4155:20170207:013603.054 Starting Zabbix Server. Zabbix 3.2.2 (revision 64245).
4155:20170207:013603.054 ****** Enabled features ******
4155:20170207:013603.054 SNMP monitoring: YES
4155:20170207:013603.054 IPMI monitoring: YES
4155:20170207:013603.054 Web monitoring: YES
4155:20170207:013603.054 VMware monitoring: YES
4155:20170207:013603.054 SMTP authentication: YES
4155:20170207:013603.054 Jabber notifications: YES
4155:20170207:013603.054 Ez Texting notifications: YES
4155:20170207:013603.054 ODBC: YES
4155:20170207:013603.054 SSH2 support: YES
4155:20170207:013603.054 IPv6 support: YES
4155:20170207:013603.054 TLS support: YES
4155:20170207:013603.054 ******************************
4155:20170207:013603.055 using configuration file: /etc/zabbix/zabbix_server.conf
4155:20170207:013603.061 current database version (mandatory/optional): 03010010/03010010
4155:20170207:013603.061 required mandatory version: 03020000
4155:20170207:013603.061 starting automatic database upgrade
4155:20170207:013603.074 [Z3005] query failed: [1005] Can't create table `zabbixdb`.`#sql-2fa_6527` (errno: 150 "Foreign key constraint is incorrectly formed") [alter table event_tag add constraint c_event_tag_1 foreign key (eventid) references events (eventid) on delete cascade]
4155:20170207:013603.074 database upgrade failed

[batchenr]

hi

can you delete this keys ?

alter table history_text drop foreign key FOREIGNKEYNAME

and this is my zabbix 3.0 tables if it helps :

mysql> describe history_text; 
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id     | bigint(20) unsigned | NO   | PRI | NULL    |       |
| itemid | bigint(20) unsigned | NO   | MUL | NULL    |       |
| clock  | int(11)             | NO   |     | 0       |       |
| value  | text                | NO   |     | NULL    |       |
| ns     | int(11)             | NO   |     | 0       |       |
+--------+---------------------+------+-----+---------+-------+

CREATE TABLE `history_text` (
  `id` bigint(20) unsigned NOT NULL,
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` text NOT NULL,
  `ns` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `history_text_2` (`itemid`,`id`),
  KEY `history_text_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

[mazdajai]

Thanks batchenr!

We ended up fixed this with the following. We have partitions on some of the tables and we suspect this is the issue.

alter table events drop key eventid;
alter table events remove partitioning;
alter table events add constraint primary key (eventid);

CREATE TABLE `events` (
  `eventid` bigint(20) unsigned NOT NULL,
  `source` int(11) NOT NULL DEFAULT '0',
  `object` int(11) NOT NULL DEFAULT '0',
  `objectid` bigint(20) unsigned NOT NULL DEFAULT '0',
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` int(11) NOT NULL DEFAULT '0',
  `acknowledged` int(11) NOT NULL DEFAULT '0',
  `ns` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`eventid`),
  KEY `events_1` (`source`,`object`,`objectid`,`clock`),
  KEY `events_2` (`source`,`object`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

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