Debian 11
MariaDB 10.8.5
Zabbix server 6.0.9 -> 6.2.3

Hello Marco,

Could you please check for the following query?

SELECT COUNT(*) FROM functions WHERE itemid NOT IN (SELECT itemid FROM items);

Regards,
Edgar

Of course!

MariaDB [zabbix]> SELECT COUNT(*) FROM functions WHERE itemid NOT IN (SELECT itemid FROM items);
+----------+
| COUNT(*) |
+----------+
|    28199 |
+----------+
1 row in set (0.336 sec)

Original schema for 6.0:

CREATE TABLE `functions` (
  `functionid` bigint(20) unsigned NOT NULL,
  `itemid` bigint(20) unsigned NOT NULL,
  `triggerid` bigint(20) unsigned NOT NULL,
  `name` varchar(12) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `parameter` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '0',
  PRIMARY KEY (`functionid`),
  KEY `functions_1` (`triggerid`),
  KEY `functions_2` (`itemid`,`name`,`parameter`),
  CONSTRAINT `c_functions_1` FOREIGN KEY (`itemid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE,
  CONSTRAINT `c_functions_2` FOREIGN KEY (`triggerid`) REFERENCES `triggers` (`triggerid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

Remove leftovers and use original schema.

Please be advised that this section of the tracker is for bug reports only. The case you have submitted can not be qualified as one, so please reach out to [email protected] for commercial support (https://zabbix.com/support) or consultancy services. Alternatively, you can also use our IRC channel or community forum (https://www.zabbix.com/forum) for assistance. With that said, we are closing this ticket. Thank you for understanding.

Interesting, your current schema has the foreign key constraint under different name:

CREATE TABLE `functions` (
  `functionid` bigint(20) unsigned NOT NULL,
  `itemid` bigint(20) unsigned NOT NULL,
  `triggerid` bigint(20) unsigned NOT NULL,
  `name` varchar(12) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `parameter` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '0',
  PRIMARY KEY (`functionid`),
  KEY `functions_1` (`triggerid`),
  KEY `functions_2` (`itemid`,`name`,`parameter`),
  CONSTRAINT `functions_ibfk_1` FOREIGN KEY (`itemid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE,
  CONSTRAINT `functions_ibfk_2` FOREIGN KEY (`triggerid`) REFERENCES `triggers` (`triggerid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Still, somehow it contains unreferenced function records.

Could be related to ZBXNEXT-7554. "ON DELETE CASCADE" was removed from foreign keys in 6.2:

$ grep '_foreign_key("functions", 1' src/libs/zbxdbupgrade/dbupgrade_6010.c -C3

static int      DBpatch_6010083(void)
{
        return DBdrop_foreign_key("functions", 1);
}

static int      DBpatch_6010084(void)
{
        const ZBX_FIELD field = {"itemid", NULL, "items", "itemid", 0, ZBX_TYPE_ID, 0, 0};

        return DBadd_foreign_key("functions", 1, &field);
}

Hi edgar.akhmetshin, I must admit, I'm a bit disappointed by your answer. Let me explain:

First: We did not alter our schema, at least not on purpose. If I had altered it, I wouldn't have attached it for this issue. I know this would be out of scope.

Second: I know that it looks like it was changed, but I honestly have no idea why. We use this very Zabbix server, our main system, for over 12+ years. It started with Zabbix 2.0.0 and was updated since. And this is now the 4th time, we have trouble because of a non-original schema. Most of the time, the constraints suddenly have other key names, and it's always something like: `functions_ibfk_1` (https://dba.stackexchange.com/questions/15530/what-does-ibfk-stand-for-in-mysql). We honestly don't know why this happens again and again.

But during the years, I have found ways to fix this. Normally I export our "mutated" schema, install a fresh Zabbix vanilla system in parallel and export its schema. Then I compare them. Afterwards, I write a repair script. correction.sql And the last three times, that worked very well. But not this time.

Even after I correct the schema back to the vanilla schema, the upgrade still fails. Hence, I created this request, because even with a vanilla schema, it fails.

Third: As you suggested, we seem to have unreferenced function records. So after I corrected the schema, we deleted all unreferenced functions records and Zabbix still works very well on version 6.0.9. You see the number is identical to your original question.

MariaDB [zabbix]> DELETE FROM functions WHERE itemid NOT IN (SELECT itemid FROM items);
Query OK, 28199 rows affected (0.616 sec)

but the upgrade to 6.2.4 still fails.

Any chance, you might give this a second look?

EDIT1: omfg. I just noticed, that the error message is not the same anymore. My fault! It has changed after the schema correction and the deletion of the unreferenced items.

The new error message:

  8768:20221106:111928.945 completed 84% of database upgrade
  8768:20221106:111928.976 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`#sql-alter-37a-d5`, CONSTRAINT `c_functions_2` FOREIGN KEY (`triggerid`) REFERENCES `triggers` (`triggerid`)) [alter table `functions` add constraint c_functions_2 foreign key (`triggerid`) references `triggers` (`triggerid`)]
  8768:20221106:111928.976 database upgrade failed
  8768:20221106:111928.977 database could be upgraded to use primary keys in history tables

Seems like more unreferenced records?

EDIT2:

MariaDB [zabbix]> SELECT COUNT(*) FROM functions WHERE triggerid NOT IN (SELECT triggerid FROM triggers);
+----------+
| COUNT(*) |
+----------+
|      835 |
+----------+
1 row in set (0.152 sec)
MariaDB [zabbix]> DELETE FROM functions WHERE triggerid NOT IN (SELECT triggerid FROM triggers);
Query OK, 835 rows affected (0.279 sec)

New error:

  7747:20221106:113906.992 completed 86% of database upgrade
  7747:20221106:113907.001 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`#sql-alter-370-2b`, CONSTRAINT `c_trigger_tag_1` FOREIGN KEY (`triggerid`) REFERENCES `triggers` (`triggerid`)) [alter table `trigger_tag` add constraint c_trigger_tag_1 foreign key (`triggerid`) references `triggers` (`triggerid`)]
  7747:20221106:113907.002 database upgrade failed
  7747:20221106:113907.003 database could be upgraded to use primary keys in history tables

New command

MariaDB [zabbix]> SELECT COUNT(*) FROM trigger_tag WHERE triggerid NOT IN (SELECT triggerid FROM triggers);
+----------+
| COUNT(*) |
+----------+
|      864 |
+----------+
1 row in set (0.108 sec)

MariaDB [zabbix]> SELECT COUNT(*) FROM trigger_tag;
+----------+
| COUNT(*) |
+----------+
|    99678 |
+----------+
1 row in set (0.014 sec)

MariaDB [zabbix]> DELETE FROM trigger_tag WHERE triggerid NOT IN (SELECT triggerid FROM triggers);
Query OK, 864 rows affected (0.189 sec)

So what's next..

After the three delete statements, I was able to upgrade a copy of our prod instance from 6.0.9 to 6.2.4. For further reference, if anybody should ever encounter such a problem, I attach the tasks I had to do for reference:

1. Fix the constraint / foreign key: correction.sql
2. Search for unreferenced DB entries according to the error message(s) and delete them. We had to do this three times, afterwards the upgrade finished without further errors. Unable to embed resource: constraint.sql