DB conversion with "foreign key constraint fails"

[michel-thomas]

Steps to reproduce

1. Nextcloud instance with PostgreSQL database
2. Use occ db:convert-type to migrate to MySQL
3. Fails on oc_mail_attachments table migration

Expected behavior

Database migration should succeed

Actual behavior

Database migration fails with:

 - oc_mail_attachments
 16/16 [============================] 100% < 1 sec/< 1 sec

In ExceptionConverter.php line 58:
An exception occurred while executing a query: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`mydb`.`oc_mail_attachments`, CONSTRAINT `attachment_local_message` FOREIGN KEY (`local_message_id`) REFERENCES `oc_mail_local_messages` (`id`) ON DELETE CASCADE)

In Exception.php line 30:
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`mydb`.`oc_mail_attachments`, CONSTRAINT `attachment_local_message` FOREIGN KEY (`local_message_id`) REFERENCES `oc_mail_local_messages` (`id`) ON DELETE CASCADE)

In Statement.php line 101:
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`mydb`.`oc_mail_attachments`, CONSTRAINT `attachment_local_message` FOREIGN KEY (`local_message_id`) REFERENCES `oc_mail_local_messages` (`id`) ON DELETE CASCADE)

Mail app version

3.5.5

Mailserver or service

OVH

Operating system

Debian 11

PHP engine version

PHP 8.2

Web server

Nginx

Database

PostgreSQL

Additional info

Migration to MySQL utf8mb4 database.

[ChristophWurst]

Foreign keys are not very popular in Nextcloud. It seems the conversation code either needs to do top sort on relations and start with parents, or disable foreign key constraints before the insert, similar to mysqldump.

[michel-thomas]

Sure, but how can I help to resolve this issue? How to get more logs from occ?

[ChristophWurst]

A ticket in https://github.com/nextcloud/server/ would be a start. The code lives in \OC\Core\Command\Db\ConvertType::copyTable if you would like to contribute a fix yourself.