[Bug]: `occ db:convert-type` fails on multiple tables when migrating from PostgreSQL to MySQL (foreign key issues, invalid timestamps, encoding, etc.)

[UltimateByte]

⚠️ 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

Migrating from PostgreSQL to MySQL using occ db:convert-type in Nextcloud 31.0.5 caused multiple issues. Although the tool worked in general, several problems required manual intervention, making the process fragile and time-consuming.

Context:

• Nextcloud 31.0.5
• PostgreSQL 13 → MariaDB 10.11
• PHP 8.2 on Debian 11

Despite planning for ~2h including debugging, the process took 3h due to the following blockers:

• UNSIGNED BIGINT errors on oc_calendarobjects.lastoccurence due to a negative timestamp.
• Encoding errors related to emojis or special characters in text fields (oc_activity.file, etc.)
• Foreign key constraint failures with oc_mail_attachments despite valid-looking data.
• --clear-schema does not reliably reset the target DB.
• Migration is not resumable, requiring full reset after failure.
• Lack of dry-run/logging makes debugging hard on large instances.

Steps to reproduce

1. Run occ db:convert-type on an actually long time prod instance using PostgreSQL with production data
2. Encounter one or more of:
   • SQLSTATE[22003]: Numeric value out of range on calendar data
   • Incorrect string value on fields with emoji
   • SQLSTATE[23000]: Integrity constraint violation during oc_mail_attachments migration
3. Retry migration → observe it does not resume and fails again if schema still exists, so you can't fix the MySQL part, you gotta fix from source PostgreSQL which may not be possible in some cases.

Expected behavior

• Migration should:
  • Pre-check data for incompatible types or encoding
  • Use mysql.utf8mb4 by default for MySQL
  • Log problematic rows for user review
  • Be resumable or able to skip already-migrated tables
  • Respect --clear-schema, or offer a safe, schema-reset option
  • Provide optional --dry-run and --log-errors features

Nextcloud Server version

31

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.2

Web server

Apache (supported)

Database engine version

MySQL

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

None

Are you using the Nextcloud Server Encryption module?

Encryption is Disabled

What user-backends are you using?

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

Configuration report

{
    "system": {
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "cloud.lesusines.fr"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "mysql",
        "version": "31.0.5.1",
        "overwrite.cli.url": "https:\/\/cloud.lesusines.fr",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "default_language": "fr",
        "default_locale": "fr_FR",
        "default_phone_region": "FR",
        "theme": "",
        "loglevel": 2,
        "maintenance": false,
        "maintenance_window_start": 100,
        "updater.release.channel": "stable",
        "memcache.local": "\\OC\\Memcache\\Redis",
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "filelocking.enabled": true,
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 6379,
            "timeout": 0
        },
        "app_install_overwrite": [
            "keeweb",
            "files_rightclick"
        ],
        "mail_smtpmode": "smtp",
        "mail_smtpsecure": "ssl",
        "mail_sendmailmode": "smtp",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpauth": 1,
        "mail_smtpport": "465",
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "mysql.utf8mb4": true
    }
}

List of activated Apps

Enabled:
  - activity: 4.0.0
  - admin_audit: 1.21.0
  - app_api: 5.0.2
  - bruteforcesettings: 4.0.0
  - calendar: 5.3.2
  - calendar_resource_management: 0.9.0
  - circles: 31.0.0
  - cloud_federation_api: 1.14.0
  - comments: 1.21.0
  - contacts: 7.1.3
  - contactsinteraction: 1.12.0
  - dashboard: 7.11.0
  - dav: 1.33.0
  - deck: 1.15.1
  - event_update_notification: 2.6.1
  - federatedfilesharing: 1.21.0
  - federation: 1.21.0
  - files: 2.3.1
  - files_accesscontrol: 2.0.0
  - files_downloadlimit: 4.0.0
  - files_pdfviewer: 4.0.0
  - files_reminders: 1.4.0
  - files_sharing: 1.23.1
  - files_trashbin: 1.21.0
  - files_versions: 1.24.0
  - forms: 5.1.0
  - groupfolders: 19.1.0
  - keeweb: 0.6.21
  - logreader: 4.0.0
  - lookup_server_connector: 1.19.0
  - mail: 5.1.3
  - nextcloud_announcements: 3.0.0
  - notifications: 4.0.0
  - oauth2: 1.19.1
  - password_policy: 3.0.0
  - photos: 4.0.0-dev.1
  - polls: 7.4.3
  - privacy: 3.0.0
  - profile: 1.0.0
  - provisioning_api: 1.21.0
  - quota_warning: 1.21.0
  - recommendations: 4.0.0
  - related_resources: 2.0.0
  - richdocuments: 8.7.0
  - serverinfo: 3.0.0
  - settings: 1.14.0
  - sharebymail: 1.21.0
  - spreed: 21.1.0
  - systemtags: 1.21.1
  - text: 5.0.0
  - theming: 2.6.1
  - twofactor_backupcodes: 1.20.0
  - updatenotification: 1.21.0
  - user_status: 1.11.0
  - viewer: 4.0.0
  - weather_status: 1.11.0
  - webhook_listeners: 1.2.0
  - welcome: 1.2.1
  - workflowengine: 2.13.0
Disabled:
  - appointments: 2.4.5 (installed 2.4.5)
  - encryption: 2.19.0
  - files_external: 1.23.0
  - files_rightclick: 0.15.1 (installed 0.15.1)
  - firstrunwizard: 4.0.0 (installed 2.18.0)
  - support: 3.0.0 (installed 1.12.0)
  - survey_client: 3.0.0 (installed 1.17.0)
  - suspicious_login: 9.0.1 (installed 7.0.0)
  - twofactor_nextcloud_notification: 5.0.0
  - twofactor_totp: 13.0.0-dev.0
  - user_ldap: 1.22.0 (installed 1.20.0

Nextcloud Signing status

https://cloud.lesusines.fr/index.php/settings/integrity/failed
No errors have been found.

Nextcloud Logs

Additional info

We worked around the issues manually (e.g. setting bad calendar rows to NULL, deleting orphaned email data, manually dropping tables). But this shouldn't be required, especially for production use (and despite this kind of intervention being maintenance planed, this is still a bad time to endure).

Thanks again for the progress on this feature — it’s powerful but still needs polish to be fully robust.

[solracsf]

Related: #43290 #51749 #36789 #53052 #28875