server
server copied to clipboard
nextcloud
[Bug]: DB conversion with "foreign key constraint fails"
⚠️ This issue respects the following points: ⚠️
- [X] This is a bug, not a question or a configuration/webserver/proxy issue.
- [X] This issue is not already reported on Github OR Nextcloud Community Forum (I've searched it).
- [X] Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
- [X] 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
- Nextcloud instance with PostgreSQL database and Mail app
- Use
occ db:convert-typeto migrate to MySQL - Fails on
oc_mail_attachmentstable 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?
- [X] 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.
