[ZBX-15805] Database upgrade failed 3.2 to 4.0 (alter table dservices drop foreign key c_dservices_2) Created: 2019 Mar 12  Updated: 2019 Mar 15  Resolved: 2019 Mar 15

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: 4.0.5
Fix Version/s: None

Type: Problem report Priority: Minor
Reporter: Tiago Cruz Assignee: Zabbix Support Team
Resolution: Commercial support required Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

from: zabbix-server-mysql-3.2.11-1.el7.x86_64
to: zabbix-server-mysql-4.0.5-1.el7.x86_64


 Description   

Steps to reproduce:

  1. Install Centos 7 and mariadb-server-5.5.60-1.el7_5.x86_64
  2. Use AWS DMS to import data from RDS (Zabbix 3.2) and export to this new database server
  3. Install zabbix-server-mysql and start the daemon
  4. ...

Result:

88278:20190312:185844.378 Starting Zabbix Server. Zabbix 4.0.5 (revision 90164).
 88278:20190312:185844.378 ****** Enabled features ******
 88278:20190312:185844.379 SNMP monitoring: YES
 88278:20190312:185844.379 IPMI monitoring: YES
 88278:20190312:185844.379 Web monitoring: YES
 88278:20190312:185844.379 VMware monitoring: YES
 88278:20190312:185844.379 SMTP authentication: YES
 88278:20190312:185844.379 Jabber notifications: YES
 88278:20190312:185844.379 Ez Texting notifications: YES
 88278:20190312:185844.379 ODBC: YES
 88278:20190312:185844.379 SSH2 support: YES
 88278:20190312:185844.379 IPv6 support: YES
 88278:20190312:185844.379 TLS support: YES
 88278:20190312:185844.379 ******************************
 88278:20190312:185844.379 using configuration file: /etc/zabbix/zabbix_server.conf
 88278:20190312:185844.419 current database version (mandatory/optional): 03020000/03020001
 88278:20190312:185844.419 required mandatory version: 04000000
 88278:20190312:185844.419 optional patches were found
 88278:20190312:185844.419 starting automatic database upgrade
 88278:20190312:185844.434 completed 0% of database upgrade
 88278:20190312:185847.340 slow query: 2.905735 sec, "alter table `items` modify `snmp_oid` varchar(512) default '' not null"
 88278:20190312:185847.400 completed 1% of database upgrade
 88278:20190312:185847.427 [Z3005] query failed: [1025] Error on rename of './zabbix/dservices' to './zabbix/#sql2-1b6ec-17' (errno: 152) [alter table dservices drop foreign key c_dservices_2]
 88278:20190312:185847.427 database upgrade failed

Expected:
Database upgrade with success 

 Comments   
Comment by Tiago Cruz [ 2019 Mar 12 ]

Similar to

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

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

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

Current error:

MariaDB [zabbix]> ALTER TABLE `sessions` ADD CONSTRAINT `c_sessions_1` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON DELETE CASCADE;
Query OK, 207476 rows affected (2.14 sec)
Records: 207476 Duplicates: 0 Warnings: 0

 97443:20190312:194749.150 starting automatic database upgrade
 97443:20190312:194749.151 [Z3005] query failed: [1091] Can't DROP 'dservices_1'; check that column/key exists [drop index dservices_1 on dservices]
 97443:20190312:194749.151 database upgrade failed

 

------------------------
LATEST FOREIGN KEY ERROR
------------------------
190312 19:49:19 Error in dropping of a foreign key constraint of table "zabbix"."dservices",
in SQL command
ALTER TABLE dservices DROP FOREIGN KEY _c_dservices_2
Cannot find a constraint with the given id "_c_dservices_2".

 

 

MariaDB [zabbix]> show create table dservices\G;
*************************** 1. row ***************************
 Table: dservices
Create Table: CREATE TABLE `dservices` (
 `dserviceid` bigint(20) unsigned NOT NULL,
 `dhostid` bigint(20) unsigned NOT NULL,
 `type` int(11) NOT NULL,
 `key_` varchar(255) NOT NULL,
 `value` varchar(255) NOT NULL,
 `port` int(11) NOT NULL,
 `status` int(11) NOT NULL,
 `lastup` int(11) NOT NULL,
 `lastdown` int(11) NOT NULL,
 `dcheckid` bigint(20) unsigned NOT NULL,
 `ip` varchar(39) NOT NULL,
 `dns` varchar(64) NOT NULL,
 PRIMARY KEY (`dserviceid`),
 KEY `c_dservices_1` (`dhostid`),
 KEY `c_dservices_2` (`dcheckid`),
 CONSTRAINT `c_dservices_2` FOREIGN KEY (`dcheckid`) REFERENCES `dchecks` (`dcheckid`) ON DELETE CASCADE,
 CONSTRAINT `c_dservices_1` FOREIGN KEY (`dhostid`) REFERENCES `dhosts` (`dhostid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

 

Comment by Edgar Akhmetshin [ 2019 Mar 13 ]

Hello Tiago,

Thank you for reporting the issue. Please, add index and try to upgrade once again:

CREATE UNIQUE INDEX `dservices_1` ON `dservices`(`dcheckid`,`ip`,`port`);

Regards,
Edgar

Comment by Tiago Cruz [ 2019 Mar 13 ]

Hello @edgar,

 

MariaDB [zabbix]> CREATE UNIQUE INDEX `dservices_1` ON `dservices`(`dcheckid`,`ip`,`port`);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0


MariaDB [zabbix]> SHOW ENGINE INNODB STATUS;
------------------------
LATEST FOREIGN KEY ERROR
------------------------
190312 20:10:16 Error in dropping of a foreign key constraint of table "zabbix"."dservices",
in SQL command
ALTER TABLE dservices DROP FOREIGN KEY _c_dservices_1
Cannot find a constraint with the given id "_c_dservices_1".

 

  • Logs:
71823:20190313:111608.641 using configuration file: /etc/zabbix/zabbix_server.conf
 71823:20190313:111608.649 current database version (mandatory/optional): 03030005/03030005
 71823:20190313:111608.649 required mandatory version: 04000000
 71823:20190313:111608.649 optional patches were found
 71823:20190313:111608.649 starting automatic database upgrade
 71823:20190313:111608.650 [Z3005] query failed: [1553] Cannot drop index 'dservices_1': needed in a foreign key constraint [drop index dservices_1 on dservices]

 

  • Table:
MariaDB [zabbix]> show create table dservices\G;
*************************** 1. row ***************************
 Table: dservices
Create Table: CREATE TABLE `dservices` (
 `dserviceid` bigint(20) unsigned NOT NULL,
 `dhostid` bigint(20) unsigned NOT NULL,
 `type` int(11) NOT NULL,
 `key_` varchar(255) NOT NULL,
 `value` varchar(255) NOT NULL,
 `port` int(11) NOT NULL,
 `status` int(11) NOT NULL,
 `lastup` int(11) NOT NULL,
 `lastdown` int(11) NOT NULL,
 `dcheckid` bigint(20) unsigned NOT NULL,
 `ip` varchar(39) NOT NULL,
 `dns` varchar(64) NOT NULL,
 PRIMARY KEY (`dserviceid`),
 UNIQUE KEY `dservices_1` (`dcheckid`,`ip`,`port`),
 KEY `c_dservices_1` (`dhostid`),
 CONSTRAINT `c_dservices_2` FOREIGN KEY (`dcheckid`) REFERENCES `dchecks` (`dcheckid`) ON DELETE CASCADE,
 CONSTRAINT `c_dservices_1` FOREIGN KEY (`dhostid`) REFERENCES `dhosts` (`dhostid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

 

Comment by Tiago Cruz [ 2019 Mar 14 ]

Hello guys,

 

Is really safe to upgrade from 3.2 to 4.0? There is some easy way to restore all the templates and configurations in some new installation from scratch?

Looks like the "automatic database upgrade" process can't handle this properly...

 

After 25 hours running (175G of data - 04 vCPU / 16GB RAM), not I've got: "query failed: [1091] Can't DROP 'alerts_4'; check that column/key exists [drop index alerts_4 on alerts]"

 

  • Logs:
72471:20190313:112508.299 using configuration file: /etc/zabbix/zabbix_server.conf
 72471:20190313:112508.308 current database version (mandatory/optional): 03030005/03030005
 72471:20190313:112508.308 required mandatory version: 04000000
 72471:20190313:112508.308 optional patches were found
 72471:20190313:112508.308 starting automatic database upgrade
 72471:20190313:112508.311 completed 0% of database upgrade
 72471:20190313:112508.327 completed 1% of database upgrade
[...]
 94887:20190314:122539.642 completed 19% of database upgrade
 94887:20190314:122541.751 slow query: 2.108764 sec, "alter table `items` modify `history` varchar(255) default '90d' not null"
 94887:20190314:122543.889 slow query: 2.133125 sec, "alter table `items` modify `trends` varchar(255) default '365d' not null"
 94887:20190314:122543.890 completed 20% of database upgrade
 94887:20190314:122605.963 slow query: 2.077040 sec, "alter table `items` modify `lifetime` varchar(255) default '30d' not null"
 94887:20190314:122623.712 completed 29% of database upgrade
 94887:20190314:122623.723 completed 30% of database upgrade
 94887:20190314:122623.739 completed 31% of database upgrade
 94887:20190314:122623.821 completed 32% of database upgrade
 94887:20190314:122625.899 slow query: 2.074294 sec, "alter table `items` add `jmx_endpoint` varchar(255) default '' not null"
 94887:20190314:122627.496 completed 33% of database upgrade
 94887:20190314:122627.516 completed 34% of database upgrade
 94887:20190314:122627.518 [Z3005] query failed: [1091] Can't DROP 'alerts_4'; check that column/key exists [drop index alerts_4 on alerts]
 94887:20190314:122627.518 database upgrade failed

 

Thanks a lot!

Comment by Edgars Melveris [ 2019 Mar 15 ]

Hello Tiago,
yes, it's mostly safe to upgrade, unless manual changes to DB have been done. Which seems to be your situation. Such setups are not supported and not considered as bug.
Also, the DB upgrade script is not meant to deal with such problems.
You will just have to manually do all the missing steps. I'm closing this as commercial support needed.

Generated at Sun Aug 31 23:45:40 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.