[Bug]: DB conversion `occ db:convert-type` fails with "foreign key constraint"

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration/webserver/proxy issue.
• This issue is not already reported on Github OR Nextcloud Community Forum (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

When trying to convert database from PostgreSQL to MariaDB, an error occurs with Mail tables.

Steps to reproduce

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

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)

Destination database (MariaDB) has utf8mb4 format.

Expected behavior

Database migration should succeed

Installation method

Community Manual installation with Archive

Nextcloud Server version

27

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.2

Web server

Nginx

Database engine version

MariaDB

Is this bug present after an update or on a fresh install?

None

Are you using the Nextcloud Server Encryption module?

None

What user-backends are you using?

• Default user-backend (database)
• LDAP/ Active Directory
• SSO - SAML
• Other

Configuration report

sudo -u www-data php occ config:list system
{
    "system": {
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "cloud.domain.org"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "pgsql",
        "mysql.utf8mb4": true,
        "version": "27.1.5.1",
        "overwrite.cli.url": "https:\/\/cloud.domain.org\/",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "default_language": "fr",
        "logtimezone": "Europe\/Paris",
        "log_rotate_size": "5242880",
        "loglevel": 3,
        "minimum.supported.desktop.version": "3.5.2",
        "memcache.local": "\\OC\\Memcache\\Redis",
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 6379,
            "timeout": 0,
            "dbindex": 0
        },
        "trashbin_retention_obligation": "auto, 31",
        "versions_retention_obligation": "auto, 31",
        "activity_expire_days": 365,
        "mail_smtpmode": "smtp",
        "mail_smtpsecure": "ssl",
        "mail_sendmailmode": "smtp",
        "mail_smtpauth": 1,
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpauthtype": "LOGIN",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "465",
        "default_phone_region": "FR",
        "skeletondirectory": "\/data\/cloud.domain.org\/lydra\/files\/skeleton",
        "templatedirectory": "",
        "maintenance": false,
        "data-fingerprint": "f075c444805c82a60d3fcaf034af671f",
        "integrity.check.disabled": false,
        "theme": "",
        "app_install_overwrite": [
            "apporder"
        ]
    }
}

List of activated Apps

sudo -u www-data php occ app:list
Enabled:
  - activity: 2.19.0
  - appointments: 1.15.5
  - apporder: 0.15.0
  - bruteforcesettings: 2.7.0
  - calendar: 4.6.4
  - circles: 27.0.1
  - cloud_federation_api: 1.10.0
  - comments: 1.17.0
  - contacts: 5.5.1
  - contactsinteraction: 1.8.0
  - dashboard: 7.7.0
  - dav: 1.27.0
  - deck: 1.11.3
  - drawio: 2.1.4
  - federatedfilesharing: 1.17.0
  - federation: 1.17.0
  - files: 1.22.0
  - files_accesscontrol: 1.17.1
  - files_markdown: 2.4.1
  - files_mindmap: 0.0.30
  - files_pdfviewer: 2.8.0
  - files_reminders: 1.0.0
  - files_rightclick: 1.6.0
  - files_sharing: 1.19.0
  - files_trashbin: 1.17.0
  - files_versions: 1.20.0
  - firstrunwizard: 2.16.0
  - impersonate: 1.14.0
  - logreader: 2.12.0
  - lookup_server_connector: 1.15.0
  - mail: 3.5.5
  - notifications: 2.15.0
  - oauth2: 1.15.1
  - onlyoffice: 8.2.4
  - password_policy: 1.17.0
  - passwords: 2023.12.32
  - photos: 2.3.0
  - polls: 5.4.2
  - privacy: 1.11.0
  - provisioning_api: 1.17.0
  - quota_warning: 1.18.0
  - recommendations: 1.6.0
  - related_resources: 1.2.0
  - serverinfo: 1.17.0
  - settings: 1.9.0
  - sharebymail: 1.17.0
  - spreed: 17.1.5
  - systemtags: 1.17.0
  - tasks: 0.15.0
  - text: 3.8.0
  - theming: 2.2.0
  - twofactor_backupcodes: 1.16.0
  - user_status: 1.7.0
  - viewer: 2.1.0
  - weather_status: 1.7.0
  - welcome: 1.0.10
  - workflowengine: 2.9.0
Disabled:
  - admin_audit: 1.17.0
  - encryption: 2.15.0
  - files_external: 1.19.0
  - nextcloud_announcements: 1.16.0 (installed 1.12.0)
  - support: 1.10.0 (installed 1.6.0)
  - survey_client: 1.15.0 (installed 1.11.0)
  - suspicious_login: 5.0.0
  - twofactor_totp: 9.0.0
  - updatenotification: 1.17.0 (installed 1.13.0)
  - user_ldap: 1.17.0

Nextcloud Signing status

No errors have been found.

Nextcloud Logs

No response

Additional info

Linked to this Mail issue#9284.

Start by reproducing the occ db:convert-type migration from PostgreSQL to MariaDB with the Mail app enabled. Read the handling of oc_mail_attachments and oc_mail_local_messages, and review linked Mail issue #9284; done means the migration completes without the reported foreign-key violation.