Database error when running migration ... General error: 1709 Index column size too large. The maximum column size is 767 bytes.

LJojo commented 1 year 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 (I've searched it).
[X] Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
[X] Nextcloud Server is running on 64bit capable CPU, PHP and OS.
[X] I agree to follow Nextcloud's Code of Conduct.

Bug description

I did the update to Nextcloud 25.0.2 When following error occures:

Exception: Database error when running migration 20000Date20220727125801 for app photos An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes.

Update vorbereiten Log-Level auf "debug" gesetzt Wartungsmodus eingeschaltet Reparaturschritt: Repair MySQL collation Reparaturinformation: All tables already have the correct collation -> nothing to do Reparaturschritt: Repair SQLite autoincrement Reparaturschritt: Copy data from accounts table when migrating from ownCloud Reparaturschritt: Drop account terms table when migrating from ownCloud Das Datenbankschema wird aktualisiert Datenbank aktualisiert Exception: Database error when running migration 20000Date20220727125801 for app photos An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes.

I searched the Internet, found that some people had some similar problems in the past. But the descriped fixis won't help. What can I doo?

Steps to reproduce

I just press the "actualice" button on the web page.

Expected behavior

Installation will work.

Installation method

None

Operating system

Debian/Ubuntu

PHP engine version

PHP 7.4

Web server

Apache (supported)

Database engine version

MariaDB

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

Updated to a major version (ex. 22.2.3 to 23.0.1)

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

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
{
    "system": {
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "***REMOVED SENSITIVE VALUE***"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "dbtype": "mysql",
        "version": "24.0.8.2",
        "overwrite.cli.url": "https:\/\/***REMOVED SENSITIVE VALUE***",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "memcache.local": "\\OC\\Memcache\\APCu",
        "maintenance": false,
        "mysql.utf8mb4": true,
        "theme": "",
        "loglevel": 2,
        "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_smtpport": "465",
        "mail_smtpauthtype": "LOGIN",
        "mail_smtpauth": 1,
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "default_phone_region": "DE",
        "app_install_overwrite": [
            "customproperties"
        ],
        "updater.secret": "***REMOVED SENSITIVE VALUE***"
    }
}

List of activated Apps

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
Enabled:
  - activity: 2.17.0
  - analytics: 4.6.0
  - bruteforcesettings: 2.5.0
  - circles: 25.0.0
  - cloud_federation_api: 1.8.0
  - comments: 1.15.0
  - contacts: 4.2.3
  - contactsinteraction: 1.6.0
  - dashboard: 7.5.0
  - dav: 1.24.0
  - federatedfilesharing: 1.15.0
  - federation: 1.15.0
  - files: 1.20.1
  - files_pdfviewer: 2.6.0
  - files_rightclick: 1.4.0
  - files_sharing: 1.17.0
  - files_trashbin: 1.15.0
  - files_versions: 1.18.0
  - firstrunwizard: 2.14.0
  - gestion: 2.2.2
  - guests: 2.3.0
  - logreader: 2.10.0
  - lookup_server_connector: 1.13.0
  - mail: 1.15.0
  - maps: 0.2.1
  - nextcloud_announcements: 1.14.0
  - notes: 4.5.1
  - notifications: 2.13.1
  - oauth2: 1.13.0
  - password_policy: 1.15.0
  - passwords: 2022.12.11
  - photos: 1.6.0
  - privacy: 1.8.0
  - provisioning_api: 1.14.0
  - qownnotesapi: 22.10.0
  - recommendations: 1.3.0
  - serverinfo: 1.14.0
  - settings: 1.6.0
  - sharebymail: 1.15.0
  - support: 1.7.0
  - survey_client: 1.12.0
  - systemtags: 1.15.0
  - text: 3.5.1
  - theming: 2.0.1
  - twofactor_backupcodes: 1.13.0
  - updatenotification: 1.14.0
  - user_status: 1.4.0
  - viewer: 1.8.0
  - weather_status: 1.4.0
  - welcome: 1.0.6
  - workflowengine: 2.7.0
Disabled:
  - admin_audit
  - apporder: 0.15.0
  - calendar: 3.5.4
  - cookbook: 0.9.18
  - customproperties: 2.0.4
  - deck: 1.7.3
  - encryption
  - files_automatedtagging: 1.14.0
  - files_external
  - files_markdown: 2.3.6
  - forms: 2.5.1
  - groupfolders: 12.0.2
  - related_resources
  - spreed: 14.0.7
  - suspicious_login
  - twofactor_totp
  - user_ldap

Nextcloud Signing status

No response

Nextcloud Logs

No response

Additional info

No response

st80kafe commented 1 year ago

experienced same error following upgrade to 25.0.2

2023-01-15T14:42:26+00:00 Setting log level to debug 2023-01-15T14:42:26+00:00 Repair step: Repair MySQL collation 2023-01-15T14:42:27+00:00 Repair info: All tables already have the correct collation -> nothing to do 2023-01-15T14:42:27+00:00 Repair step: Repair SQLite autoincrement 2023-01-15T14:42:27+00:00 Repair step: Copy data from accounts table when migrating from ownCloud 2023-01-15T14:42:27+00:00 Repair step: Drop account terms table when migrating from ownCloud 2023-01-15T14:42:27+00:00 Updating database schema 2023-01-15T14:42:27+00:00 Updated database 2023-01-15T14:42:27+00:00 Updating ... 2023-01-15T14:42:27+00:00 Exception: Database error when running migration 20000Date20220727125801 for app photos An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes. 2023-01-15T14:42:27+00:00 Update failed 2023-01-15T14:42:27+00:00 Maintenance mode is kept active 2023-01-15T14:42:27+00:00 Resetting log level

simonspa commented 1 year ago

Hi,

your database should allow dynamic row sizes, otherwise you might also run into problems when for example adding missing indices using the occ db:add-missing-indices command.

This requires innodb_default_row_format=dynamic in your DB configuration (https://mariadb.com/kb/en/innodb-row-formats-overview/) or an ALTER TABLE oc_tablename ROW_FORMAT=DYNAMIC; on existing tables.

Best, /S

Madko commented 1 year ago

Same problem here, all my tables are already in dynamic row format. The problem is on the index column size that is still fixed on 767b. Had to comment (L66) the index creation in this migration apps/photos/lib/Migration/Version20000Date20220727125801.php

#$table->addIndex(['user'], 'pa_user');

Don't see any drawback and my nextcloud is back now.

nextcloud / photos