Foreign key constraint fails after 4.2.5 => 4.3.0 update

Pydio Cells
1

Describe your issue in detail

I started to observing the following errors keeping me from updating many of the users (and they can’t connect anymore) which started right after I upgraded from 4.2.5 to 4.3.0

Error 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`pydio`.`idm_user_attributes`, CONSTRAINT `idm_user_attributes_ibfk_1` FOREIGN KEY (`uuid`) REFERENCES `idm_user_idx_tree` (`uuid`) ON DELETE CASCADE)

Error 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`pydio`.`idm_user_roles`, CONSTRAINT `idm_user_roles_ibfk_1` FOREIGN KEY (`uuid`) REFERENCES `idm_user_idx_tree` (`uuid`) ON DELETE CASCADE)

Another (non-technical) admin may have slightly touched some of the user/role configuration and I don’t exactly how, but no one system/mysql/maintenance/upgrade or any other intrusive operation.

What version of Cells are you using?

4.3.0

What is the server OS? Database name/version?

Debian bookworm, Mariadb 10.11.4

What steps have you taken to resolve this issue already?

None, I fear I may create deeper problem if I try to manually fix the database (in the event I could actually understand what the underlying DB problem is).

Additional information

Taking the user “a2fb3943-a412-49f7-aa47-1bd3f087a9ea” as an example: He can’t connect and I can’t update it (not even change its password.
It’s entry inside the idm_user_idx_tree table seems fine and it has existing entries in idm_user_roles:

select * from idm_user_roles where uuid = 'a2fb3943-a412-49f7-aa47-1bd3f087a9ea';
+--------------------------------------+--------+--------+
| uuid                                 | role   | weight |
+--------------------------------------+--------+--------+
| a2fb3943-a412-49f7-aa47-1bd3f087a9ea | editor |      0 |
+--------------------------------------+--------+--------+

and idm_user_attributes:

select * from idm_user_attributes where uuid = 'a2fb3943-a412-49f7-aa47-1bd3f087a9ea';
+--------------------------------------+-----------------+----------+
| uuid                                 | name            | value    |
+--------------------------------------+-----------------+----------+
| a2fb3943-a412-49f7-aa47-1bd3f087a9ea | displayName     | foo      |
| a2fb3943-a412-49f7-aa47-1bd3f087a9ea | email           | bar      |
| a2fb3943-a412-49f7-aa47-1bd3f087a9ea | profile         | standard |
| a2fb3943-a412-49f7-aa47-1bd3f087a9ea | pydio:labelLike | foo bar  |
+--------------------------------------+-----------------+----------+

I found that I unset its “Editor” profile before saving, instead of the idm_user_roles FK error, I get the idm_user_attributes one.

DB structure:

| idm_user_roles | CREATE TABLE `idm_user_roles` (
  `uuid` varchar(128) NOT NULL,
  `role` varchar(255) NOT NULL,
  `weight` int(11) DEFAULT 0,
  PRIMARY KEY (`uuid`,`role`),
  CONSTRAINT `idm_user_roles_ibfk_1` FOREIGN KEY (`uuid`) REFERENCES `idm_user_idx_tree` (`uuid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

| idm_user_attributes | CREATE TABLE `idm_user_attributes` (
  `uuid` varchar(128) NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` longtext DEFAULT NULL,
  PRIMARY KEY (`uuid`,`name`),
  CONSTRAINT `idm_user_attributes_ibfk_1` FOREIGN KEY (`uuid`) REFERENCES `idm_user_idx_tree` (`uuid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

| idm_user_idx_tree | CREATE TABLE `idm_user_idx_tree` (
  `uuid` varchar(128) NOT NULL,
  `level` smallint(6) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `leaf` tinyint(1) NOT NULL DEFAULT 0,
  `mtime` int(11) NOT NULL,
  `etag` varchar(255) NOT NULL DEFAULT '',
  `size` bigint(20) NOT NULL DEFAULT 0,
  `mode` varchar(10) NOT NULL DEFAULT '',
  `mpath1` varchar(255) NOT NULL,
  `mpath2` varchar(255) NOT NULL,
  `mpath3` varchar(255) NOT NULL,
  `mpath4` varchar(255) NOT NULL,
  `hash` varchar(40) NOT NULL,
  `hash2` varchar(50) NOT NULL,
  PRIMARY KEY (`uuid`),
  UNIQUE KEY `idm_user_idx_tree_u1` (`hash`),
  UNIQUE KEY `idm_user_idx_tree_u2` (`hash2`),
  KEY `idm_user_idx_tree_mpath1_idx` (`mpath1`),
  KEY `idm_user_idx_tree_mpath2_idx` (`mpath2`),
  KEY `idm_user_idx_tree_mpath3_idx` (`mpath3`),
  KEY `idm_user_idx_tree_mpath4_idx` (`mpath4`),
  KEY `idm_user_idx_tree_name_idx` (`name`(128)),
  KEY `idm_user_idx_tree_level_idx` (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_general_ci

Could it be a missing schema-update? What do you suggest to elegantly resolve this frightening problem?

2

Ok, this is typical of a Collation issue.

As you can see, idm_user_roles.uuid and idm_user_attributes.uuid have their table default collation (utf8mb4) and they declare a foreign key toward idm_user_idx_tree.uuid which is ASCII.

Normally a previous upgrade should have forced the two first ones to ASCII :

ALTER TABLE idm_user_attributes MODIFY uuid VARCHAR(128) CHARACTER SET ASCII;
ALTER TABLE idm_user_roles MODIFY uuid VARCHAR(128) CHARACTER SET ASCII;

You may have to drop the foreign key before doing the change, which you can re-add with

ALTER TABLE idm_user_attributes ADD FOREIGN KEY (uuid) REFERENCES idm_user_idx_tree(uuid) ON DELETE CASCADE;
ALTER TABLE idm_user_roles ADD FOREIGN KEY (uuid) REFERENCES idm_user_idx_tree(uuid) ON DELETE CASCADE;

The “typical” case is that you have exported/imported the DB (with mysqldump) for a server change or a backup and it sometimes loose these fields details.
Or you have come back and forth and slightly messed up the migration process.

But nothing to worry about.

3

Wow, it worked! Thank you very much. I hope other upgrades aren’t missing that could bite me in the back again. Any command-line to safely (and exhaustively) “upgrade the schema”?