[Bug]: upgrade 27.1.5.1 to 28.0.1.1 foreign key oc_polls_share

[jaburio]

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

Exception: Database error when running migration 28000Date20230906104802 for app core Foreign key name "FK_1C85E16C3C947C0F" for table "oc_polls_share" collides with the constraint on table "oc_polls_share".

Steps to reproduce

didn't try on a fresh system

Expected behavior

correct update of db scheme

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?

Upgraded to a MAJOR version (ex. 22 to 23)

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": {
        "blacklisted_files": [],
        "updatechecker": true,
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "***REMOVED SENSITIVE VALUE***"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "overwrite.cli.url": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "mysql",
        "version": "27.1.5.1",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "filelocking.enabled": true,
        "memcache.local": "\\OC\\Memcache\\Redis",
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 0,
            "timeout": 0,
            "password": "***REMOVED SENSITIVE VALUE***",
            "dbindex": 0
        },
        "loglevel": 2,
        "logtimezone": "Europe\/Berlin",
        "log_rotate_size": 104857600,
        "installed": true,
        "theme": "",
        "maintenance": false,
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpmode": "sendmail",
        "files_antivirus.av_cmd_options": "",
        "files_antivirus.av_path": "\/usr\/bin\/clamscan",
        "allow_user_to_change_mail_address": "",
        "mysql.utf8mb4": true,
        "mail_sendmailmode": "smtp",
        "default_phone_region": "DE",
        "enable_previews": true,
        "enabledPreviewProviders": [
            "OC\\Preview\\Movie",
            "OC\\Preview\\PNG",
            "OC\\Preview\\JPEG",
            "OC\\Preview\\GIF",
            "OC\\Preview\\BMP",
            "OC\\Preview\\XBitmap",
            "OC\\Preview\\MP3",
            "OC\\Preview\\MP4",
            "OC\\Preview\\TXT",
            "OC\\Preview\\MarkDown",
            "OC\\Preview\\PDF"
        ],
        "app_install_overwrite": [
            "richdocumentscode",
            "ransomware_protection",
            "files_downloadactivity"
        ],
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "25",
        "updater.secret": "***REMOVED SENSITIVE VALUE***"
    }
}

List of activated Apps

Enabled:
  - activity: 2.19.0
  - admin_audit: 1.17.0
  - announcementcenter: 6.7.0
  - audioplayer: 3.4.1
  - bruteforcesettings: 2.7.0
  - calendar: 4.6.2
  - 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
  - external: 5.2.1
  - federatedfilesharing: 1.17.0
  - files: 1.22.0
  - files_antivirus: 5.4.0
  - files_downloadactivity: 1.16.0
  - files_external: 1.19.0
  - files_pdfviewer: 2.8.0
  - files_reminders: 1.0.0
  - files_sharing: 1.19.0
  - files_texteditor: 2.15.1
  - files_trashbin: 1.17.0
  - files_versions: 1.20.0
  - firstrunwizard: 2.16.0
  - impersonate: 1.14.0
  - integration_openstreetmap: 1.0.6
  - logreader: 2.12.0
  - lookup_server_connector: 1.15.0
  - maps: 1.2.0
  - nextcloud_announcements: 1.16.0
  - notes: 4.9.2
  - notifications: 2.15.0
  - oauth2: 1.15.1
  - password_policy: 1.17.0
  - photos: 2.3.0
  - polls: 5.4.2
  - privacy: 1.11.0
  - provisioning_api: 1.17.0
  - quota_warning: 1.18.0
  - ransomware_protection: 1.14.0
  - recognize: 5.0.3
  - recommendations: 1.6.0
  - related_resources: 1.2.0
  - richdocuments: 8.2.3
  - richdocumentscode: 23.5.604
  - serverinfo: 1.17.0
  - settings: 1.9.0
  - sharebymail: 1.17.0
  - spreed: 17.1.4
  - stt_whisper: 1.0.7
  - support: 1.10.0
  - survey_client: 1.15.0
  - suspicious_login: 5.0.0
  - systemtags: 1.17.0
  - tasks: 0.15.0
  - text: 3.8.0
  - theming: 2.2.0
  - twofactor_backupcodes: 1.16.0
  - updatenotification: 1.17.0
  - user_usage_report: 1.11.1
  - viewer: 2.1.0
  - weather_status: 1.7.0
  - workflowengine: 2.9.0
Disabled:
  - encryption: 2.16.0
  - federation: 1.18.0 (installed 1.17.0)
  - sharepoint: 1.15.0 (installed 1.15.0)
  - twofactor_totp: 10.0.0-beta.2
  - user_ldap: 1.19.0
  - user_status: 1.8.1 (installed 1.5.0)

Nextcloud Signing status

upgrade stucked

Nextcloud Logs

{
	"reqId": "uHAbKnMedDkHYviN4viE",
	"level": 3,
	"time": "2024-01-12T13:20:09+01:00",
	"remoteAddr": "***REMOVED SENSITIVE VALUE***",
	"user": "--",
	"app": "updater",
	"method": "GET",
	"url": "/core/ajax/update.php?requesttoken=***REMOVED SENSITIVE VALUE***",
	"message": "\\OC\\Updater::failure: Exception: Database error when running migration 28000Date20230906104802 for app core\nForeign key name \"FK_1C85E16C3C947C0F\" for table \"oc_polls_share\" collides with the constraint on table \"oc_polls_share\".",
	"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:121.0) Gecko/20100101 Firefox/121.0",
	"version": "27.1.5.1",
	"data": {
		"app": "updater"
	}
}

Additional info

No response

[jaburio]

Workaround:

1. occ:app disable polls
2. in nc db (first make backup!!!): DROP TABLE oc_polls_comments, oc_polls_log, oc_polls_notif, oc_polls_options, oc_polls_polls, oc_polls_preferences, oc_polls_share, oc_polls_votes, oc_polls_watch;
3. occ upgrade
4. restore tables oc_polls_* from backup

[dartcafe]

@kesselb Can you explain this error to me?
What the hell has this migration do to with the polls app?
migration 28000Date20230906104802 for app core

This is a migration method from the core migration.

[jaburio]

Nc first checks for incompatible apps and shows them before starting the upgrade process. Part of the process is upgrading db structure. So if an app is incompatible and should be deactivated to prevent trouble, i think no errors should appear in the upgrade process. The problem is in the app, that's right. But it should never make trouble because it was detected as incompatible and deactivated. Don't you see it that way? Am 12. Januar 2024 23:12:34 MEZ schrieb "René Gieling" ***@***.***>:

…

@kesselb Can you explain this error to me? What the hell has this migration do to with the polls app? `migration 28000Date20230906104802 for app core` This is a migration method from the core migration. -- Reply to this email directly or view it on GitHub: #43173 You are receiving this because you authored the thread. Message ID: ***@***.***>

[kesselb]

Foreign key name "FK_1C85E16C3C947C0F" for table "oc_polls_share" collides with the constraint on table "oc_polls_share".

#39488
#39506

What the hell has this migration do to with the polls app?

Nothing, but it does trigger the schema validation and run into the index name problem.

[dartcafe]

If I understand you right, the migration wanted to add an unique index and this index got the name of an existing index in the table oc_polls_share?

And now?

[kesselb]

the migration wanted to add an unique index

Yes

and this index got the name of an existing index in the table oc_polls_share?

Looks unlikely to me because the index names in https://github.com/nextcloud/server/blob/master/core/Migrations/Version28000Date20230906104802.php are different.

28000Date20230906104802 is the first 28 migration and will trigger this new check to find conflicting index names.
The executed migration is probably not related to the actual error message.

The check

server/lib/private/DB/MigrationService.php

Lines 676 to 681 in 31df9c4

	foreach ($table->getForeignKeys() as $thing) {
	if (isset($constraintNames[$thing->getName()])) {
	throw new \InvalidArgumentException('Foreign key name "' . $thing->getName() . '" for table "' . $table->getName() . '" collides with the constraint on table "' . $constraintNames[$thing->getName()] . '".');
	}
	$constraintNames[$thing->getName()] = $table->getName();
	}

thinks that https://github.com/nextcloud/polls/blob/1bb60d145b39fd5be8d24d6d1368b8de99cc75b2/lib/Migration/TableSchema.php#L68 and https://github.com/nextcloud/polls/blob/1bb60d145b39fd5be8d24d6d1368b8de99cc75b2/lib/Migration/TableSchema.php#L55 could conflict.

I agree that's a bit weird because the unique index should use a different name already.

@jaburio are you using the polls app for quite a while? Maybe the unique key for your installation has no different name in your schema.

[dartcafe]

@kesselb Since I feel a little bit unguilty, I would like to close this issue. I don't see that as a task in polls. Or move this issue back to the server repo, if it has to be fixed there.

[dartcafe]

Nc first checks for incompatible apps and shows them before starting the upgrade process. Part of the process is upgrading db structure. So if an app is incompatible and should be deactivated to prevent trouble, i think no errors should appear in the upgrade process. The problem is in the app, that's right. But it should never make trouble because it was detected as incompatible and deactivated. Don't you see it that way?

This error is not related to the compatibily check. The error is thrown while trying to update another table's indexes. But the conflict should not occure, since the index names are generated by the database and should be unique by definition.

[jaburio]

Hi René, didn't search for the root but all my thoughts are written above. Of course obviously the error was thrown by the db but i think my config is not that unusual (migrated from oc, no untested apps). So an upgrade should not stop means the updater should be able to handle that problem. Thanks for your feedback and happy we! :) Am 27. Januar 2024 09:45:40 MEZ schrieb "René Gieling" ***@***.***>:

…

> Nc first checks for incompatible apps and shows them before starting the upgrade process. Part of the process is upgrading db structure. So if an app is incompatible and should be deactivated to prevent trouble, i think no errors should appear in the upgrade process. The problem is in the app, that's right. But it should never make trouble because it was detected as incompatible and deactivated. Don't you see it that way? This error is not related to the compatibily check. The error is thrown while trying to update another table's indexes. But the conflict should not occure, since the index names are generated by the database and should be unique by definition. -- Reply to this email directly or view it on GitHub: #43173 You are receiving this because you were mentioned. Message ID: ***@***.***>

[kesselb]

@jaburio please share the follwing sql output with us:

show create table nextcloud.oc_polls_share;

You may have to replace "nextcloud" with your actual database name or select the database upfront.
A screenshot from the phpmyadmin structure tab would also work.

[kesselb]

We got a similar report in #41253 (comment), but for a different table.

The user said he created the index manually a while ago because the migration failed.
Is it possible that the same happened to you?

Adding the "needs info" label because we are still waiting for the sql schema.

This issue is related to #41253.

[dartcafe]

nice