[ZBX-17176] Mysql upgrade failed from 4.0.16 to 4.4.4 Created: 2020 Jan 14  Updated: 2020 Jan 20  Resolved: 2020 Jan 20

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: None
Affects Version/s: 4.0.16
Fix Version/s: None

Type: Problem report Priority: Trivial
Reporter: Konst Assignee: Edgars Melveris
Resolution: Commercial support required Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Centos 7 x64


 Description   

Steps to reproduce:

  1. Upgrade rpm to 4.4.4
  2. Start zabbix-server
  3. Problem in log: Can't DROP 'c_dchecks_1'; check that column/key exists [alter table dchecks drop foreign key c_dchecks_1]

Result:
See log file:

current database version (mandatory/optional): 04000000/04000004
required mandatory version: 04040000
starting automatic database upgrade
completed 1% of database upgrade
completed 2% of database upgrade
completed 3% of database upgrade
completed 4% of database upgrade
completed 5% of database upgrade
completed 7% of database upgrade
completed 8% of database upgrade
completed 9% of database upgrade
completed 10% of database upgrade
completed 11% of database upgrade
completed 13% of database upgrade
completed 14% of database upgrade
completed 15% of database upgrade
completed 16% of database upgrade
completed 17% of database upgrade
completed 19% of database upgrade
completed 20% of database upgrade
completed 21% of database upgrade
[Z3005] query failed: [1091] Can't DROP 'c_dchecks_1'; check that column/key exists [alter table dchecks drop foreign key c_dchecks_1]

Tried in mysql:

ALTER TABLE `zabbix`.`dchecks` ADD CONSTRAINT `c_dchecks_1` FOREIGN KEY (`druleid`) REFERENCES `zabbix`.`drules` (`druleid`) ON DELETE CASCADE ON UPDATE RESTRICT;

Error occured:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`#sql-b3e_c3fd`, CONSTRAINT `c_dchecks_1` FOREIGN KEY (`druleid`) REFERENCES `drules` (`druleid`) ON DELETE CASCADE ON UPDATE RESTRICT)

 Comments   
Comment by Edgars Melveris [ 2020 Jan 16 ]

Hello Konst, please provide the output of the following command from DB CLI (if it's mysql):

show create table dchecks \G

Did you do any manual changes in the DB schema?

Comment by Konst [ 2020 Jan 20 ]

Hello!

Server is old enough and two years ago DB crashed and was recovered to new server and may be something was lost during recovery.

mysql> show create table dchecks \G

 Table: dchecks
 Create Table: CREATE TABLE `dchecks` (
 `dcheckid` bigint unsigned NOT NULL,
 `druleid` bigint unsigned NOT NULL,
 `type` int NOT NULL DEFAULT '0',
 `key_` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
 `snmp_community` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
 `ports` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '0',
 `snmpv3_securityname` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
 `snmpv3_securitylevel` int NOT NULL DEFAULT '0',
 `snmpv3_authpassphrase` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
 `snmpv3_privpassphrase` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
 `uniq` int NOT NULL DEFAULT '0',
 `snmpv3_authprotocol` int NOT NULL DEFAULT '0',
 `snmpv3_privprotocol` int NOT NULL DEFAULT '0',
 `snmpv3_contextname` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
 PRIMARY KEY (`dcheckid`),
 KEY `dchecks_1` (`druleid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
 1 row in set (0.00 sec)
Comment by Edgars Melveris [ 2020 Jan 20 ]

The schema is incorrect, there should be a foreign key:

 CONSTRAINT `c_dchecks_1` FOREIGN KEY (`druleid`) REFERENCES `drules` (`druleid`) ON DELETE CASCADE

You need to recreate it before you can continue with the upgrade.
The reason it fails, is that there are probably already records in dchecks, which should have been deleted by the foreign key, you need to remove those to be able to create it.
I'm closing this as not a bug.

Generated at Sat May 03 07:07:32 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.