[Bug]: Error upgrading DB schema from 27.1.4 to 28.0.0

arnaudpn commented 10 months ago

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

After upgrading from 27.1.4 to 28.0.0, then I'm encountering a database upgrade error when running the upgrade script:

# su -m www -c "php ./occ upgrade"
Nextcloud or one of the apps require upgrade - only a limited number of commands are available
You may use your browser or the occ upgrade command to do the upgrade
Setting log level to debug
Turned on maintenance mode
Updating database schema
Exception: Database error when running migration 28000Date20230906104802 for app core
Index name "fs_storage_size" for table "oc_filecache" collides with the constraint on table "oc_oc_filecache_mchr7a6sqogjj".
Update failed
Maintenance mode is kept active
Resetting log level

Nextcloud flavor used is the one provided by an up2date Freshports on a FreeBSD 13.2-RELEASE-p4.

Steps to reproduce

Upgrade Nextcloud ;
Upgrade the database schema ;
Encounter the schema upgrade error.

Expected behavior

The expected behavior is that the DB schema should be upgraded w/o error.

Installation method

Other Community project

Nextcloud Server version

28

Operating system

Other

PHP engine version

PHP 8.1

Web server

Apache (supported)

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?

None

What user-backends are you using?

[X] Default user-backend (database)
[ ] LDAP/ Active Directory
[ ] SSO - SAML
[ ] Other

Configuration report

{
    "system": {
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "mysql",
        "version": "27.1.4.1",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "loglevel": 4,
        "logdateformat": "F j, Y H:i:s",
        "logtimezone": "Europe\/Brussels",
        "maintenance": true,
        "theme": "",
        "trusted_domains": [
            "***REMOVED SENSITIVE VALUE***",
            "***REMOVED SENSITIVE VALUE***"
        ],
        "secret": "***REMOVED SENSITIVE VALUE***",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpmode": "smtp",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "trusted_proxies": "***REMOVED SENSITIVE VALUE***",
        "forwarded_for_headers": [
            "HTTP_X_FORWARDED_FOR"
        ],
        "trashbin_retention_obligation": "auto",
        "overwrite.cli.url": "***REMOVED SENSITIVE VALUE***",
        "apps_paths": [
            {
                "path": "\/usr\/local\/www\/nextcloud\/apps",
                "url": "\/apps",
                "writable": true
            },
            {
                "path": "\/usr\/local\/www\/nextcloud\/apps-pkg",
                "url": "\/apps-pkg",
                "writable": false
            }
        ],
        "enable_previews": true,
        "enabledPreviewProviders": [
            "OC\\Preview\\PNG",
            "OC\\Preview\\JPEG",
            "OC\\Preview\\GIF",
            "OC\\Preview\\BMP",
            "OC\\Preview\\XBitmap",
            "OC\\Preview\\MP3",
            "OC\\Preview\\TXT",
            "OC\\Preview\\MarkDown",
            "OC\\Preview\\Movie"
        ],
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "25",
        "app_install_overwrite": [
            "contacts"
        ],
        "encryption.legacy_format_support": true,
        "encryption.key_storage_migrated": false
    }
}

List of activated Apps

Enabled:
  - activity: 2.19.0
  - circles: 27.0.1
  - cloud_federation_api: 1.10.0
  - comments: 1.17.0
  - contacts: 5.4.2
  - contactsinteraction: 1.8.0
  - dashboard: 7.7.0
  - dav: 1.27.0
  - encryption: 2.15.0
  - federatedfilesharing: 1.17.0
  - federation: 1.17.0
  - files: 1.22.0
  - files_external: 1.19.0
  - files_pdfviewer: 2.8.0
  - files_reminders: 1.0.0
  - files_rightclick: 1.6.0
  - files_sharing: 1.19.0
  - files_versions: 1.20.0
  - firstrunwizard: 2.16.0
  - logreader: 2.12.0
  - lookup_server_connector: 1.15.0
  - nextcloud_announcements: 1.16.0
  - notifications: 2.15.0
  - oauth2: 1.15.1
  - password_policy: 1.17.0
  - photos: 2.3.0
  - privacy: 1.11.0
  - provisioning_api: 1.17.0
  - recommendations: 1.6.0
  - related_resources: 1.2.0
  - serverinfo: 1.17.0
  - settings: 1.9.0
  - sharebymail: 1.17.0
  - support: 1.10.0
  - survey_client: 1.15.0
  - systemtags: 1.17.0
  - text: 3.8.0
  - theming: 2.2.0
  - twofactor_backupcodes: 1.16.0
  - updatenotification: 1.17.0
  - user_status: 1.7.0
  - viewer: 2.1.0
  - weather_status: 1.7.0
  - workflowengine: 2.9.0
Disabled:
  - admin_audit: 1.18.0
  - bruteforcesettings: 2.8.0 (installed 1.4.0)
  - external: 2.0.3 (installed 2.0.3)
  - files_trashbin: 1.18.0 (installed 1.4.1)
  - spreed: 5.0.0 (installed 5.0.0)
  - suspicious_login: 6.0.0
  - twofactor_totp: 10.0.0-beta.2
  - user_ldap: 1.19.0

Nextcloud Signing status

No response

Nextcloud Logs

No response

Additional info

No response

qugebert commented 10 months ago

I can confirm this. I get some similiar error

Setting log level to debug
Turned on maintenance mode
Updating database schema
Exception: Database error when running migration 28000Date20230906104802 for app core
Foreign key name "FK_3710B4A43C947C0F" for table "oc_polls_log" collides with the constraint on table "oc_polls_log".
Update failed
Maintenance mode is kept active
Resetting log level

joshtrichards commented 10 months ago

Duplicate of #41253

arnaudpn commented 10 months ago

Thanks for having linked my issue to #41253 @joshtrichards and sorry for the duplicate ...

I understand that my oc_oc_filecache_mchr* table is an old useless table (a leftover table from owncloud which got migrated long ago to nextcloud).

It's now solved:

drop table oc_oc_filecache_mchr7a6sqogjj;
su -m www -c "php ./occ upgrade"
su -m www -c "php ./occ add-missing-indices"
maintenance flag in config/config.php changed from true to false.

simogeo commented 5 months ago

Hi all, I have a similar problem. I’m runing into trouble when trying to update to nextcloud server 29.0.1

I have the following message : InvalidArgumentException: Index name “fs_storage_size” for table “oc_oc_filecache_9txz61m88l70o” collides with the constraint on table “oc_filecache”.

The question is : can I drop oc_oc_filecache_9txz61m88l70o safely ? But it seems "oc_oc_filecache_9txz61m88l70o" table does not exist !

edit : I don't why, I don't know how but I've got it worked ...

nextcloud / server