Update to 30.0.2 failed: `Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes`

lucullaus · November 9, 2024, 2:07pm

[/details]

Nextcloud version (eg, 29.0.5): 29.0.9.2
Operating system and version (eg, Ubuntu 24.04): CentOS Linux 7.9.2009 (Core)
Apache or nginx version (eg, Apache 2.4.25): Apache/2.4.6 (CentOS)
PHP version (eg, 8.3): 7.4.33

The issue you are facing: I tried updating from version 29.0.9.2 to 30.0.2 when I ran into this error. Now my Nextcloud installation seems to be stuck. Can I set it back to 29.0.9.2?

Is this the first time you’ve seen this error? (Y/N): Y

Steps to replicate it:

Click on “Start update” in the web-based updater.

The following log is displayed:

Update to 30.0.2

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
Detailed logs

Preparing update

Set log level to debug

Turned on maintenance mode

Repair step: Repair MySQL collation

Repair info: All tables already have the correct collation -> nothing to do

Repair step: Copy data from accounts table when migrating from ownCloud

Repair step: Drop account terms table when migrating from ownCloud

Updating database schema

Updated database

Updated "federation" to 1.20.0

Updated "lookup_server_connector" to 1.18.0

Repair step: Update OAuth token expiration times

Updated "oauth2" to 1.18.1

Updated "password_policy" to 2.0.0

Repair step: init metadata

Updated "photos" to 3.0.2

Updated "suspicious_login" to 8.0.0

Updated "files" to 2.2.0

Updated "activity" to 3.0.0

Repair step: Upgrading Circles App

Updated "circles" to 30.0.0

Updated "cloud_federation_api" to 1.13.0

Repair step: Fix component of birthday calendars

Repair info: 5 birthday calendars updated.

Repair step: Regenerating birthday calendars to use new icons and fix old birthday events without year

Repair info: Repair step already executed

Repair step: Fix broken values of calendar objects

[0 / 0]: Fix broken values of calendar objects

Repair step: Registering building of calendar search index as background job

Repair info: Repair step already executed

Repair step: Register building of social profile search index as background job

Repair info: Add background job

Repair step: Registering background jobs to update cache for webcal calendars

Repair info: Added 0 background jobs to update webcal calendars

Repair step: Registering building of calendar reminder index as background job

Repair info: Repair step already executed

Repair step: Clean up orphan event and contact data

Repair info: 0 events without a calendar have been cleaned up

Repair info: 0 properties without an events have been cleaned up

Repair info: 0 changes without a calendar have been cleaned up

Repair info: 0 cached events without a calendar subscription have been cleaned up

Repair info: 0 changes without a calendar subscription have been cleaned up

Repair info: 0 contacts without an addressbook have been cleaned up

Repair info: 0 properties without a contact have been cleaned up

Repair info: 0 changes without an addressbook have been cleaned up

Repair step: Remove activity entries of private events

Repair info: Removed 0 activity entries

Repair step: Clean up old calendar subscriptions from deleted users that were not cleaned-up

[0 / 0]: Clean up old calendar subscriptions from deleted users that were not cleaned-up

Repair info: 0 calendar subscriptions without an user have been cleaned up

Repair step: Remove invalid object properties

Repair info: 0 invalid object properties removed.

Updated "dav" to 1.31.1

Repair step: Fix the share type of guest shares when migrating from ownCloud

Repair step: Copy the share password into the dedicated column

Repair step: Set existing shares as accepted

Updated "files_sharing" to 1.22.0

Updated "files_trashbin" to 1.20.1

Updated "files_versions" to 1.23.0

Updated "sharebymail" to 1.20.0

Repair step: Populating added database structures for workflows

Updated "workflowengine" to 2.12.0

Updated "comments" to 1.20.1

Updated "firstrunwizard" to 3.0.0

Updated "logreader" to 3.0.0

Updated "nextcloud_announcements" to 2.0.0

Updated "notifications" to 3.0.0

Updated "systemtags" to 1.20.0

Repair step: Initialize migration of background images from dashboard to theming app

Updated "theming" to 2.5.0

Updated "contactsinteraction" to 1.11.0

Updated "dashboard" to 7.10.0

Updated "federatedfilesharing" to 1.20.0

Updated "files_downloadlimit" to 3.0.0

Updated "files_pdfviewer" to 3.0.0

Updated "files_reminders" to 1.3.0

Updated "privacy" to 2.0.0

Updated "provisioning_api" to 1.20.0

Updated "recommendations" to 3.0.0

Updated "related_resources" to 1.5.0

Updated "serverinfo" to 2.0.0

Updated "settings" to 1.13.0

Repair step: Switches from default updater server to the customer one if a valid subscription is available

Repair info: Repair step already executed

Updated "support" to 2.0.0

Repair step: Send an admin notification if monthly report is disabled

Updated "survey_client" to 2.0.0

Repair step: Force-reset all Text document sessions

Updated "text" to 4.1.0

Repair step: Add background job to check for backup codes

Updated "twofactor_backupcodes" to 1.19.0

Updated "updatenotification" to 1.20.0

Updated "user_status" to 1.10.0

Updated "viewer" to 3.0.0

Updated "weather_status" to 1.10.0

Update app "calendar" from App Store

Update app "contacts" from App Store

Doctrine\DBAL\Exception\DriverException: An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

The update was unsuccessful. Please report this issue to the Nextcloud community.

chrissi55 · November 9, 2024, 2:13pm

maybe this is helpful

in combi with the admin manual pages

https://docs.nextcloud.com/server/15/admin_manual/configuration_database/mysql_4byte_support.html

lucullaus · November 27, 2024, 9:09pm

Thanks for the hints!
I tried changing the settings in my mariadb (5.5.68-MariaDB) as suggested in the nextcloud manual under “Enabling MySQL 4-byte support” (SET GLOBAL innodb_file_format=Barracuda; SET GLOBAL innodb_file_per_table=TRUE;), but the settings do not survive a restart of the mariadb server.
I guess then there’s no point in continuing with changing the databases character set and collation.
Any suggestions how I can make these changed settings permanent?

jtr · November 27, 2024, 9:52pm

Please use the manual for a supported version of Server. The above link is for v15 and very different from today’s manual.

See latest/v31 or the appropriate one for your specific version of Server here.

SET GLOBAL is not persistent in Mysql/MariaDB by default. It’s not the recommended approach in any current versions of the Server docs.

Rudibert · January 11, 2025, 5:26pm

‘mysql.utf8mb4’ => true,

is activated since ages, but every now and then Nextcloud is coming up with these problems during an actualization - annoying. Never could figure out, what really is the reason for that.
Now it seems unrepairable for me.
The problem is that most of the help is only useful for people having their own server anywhere, but I think that there are a lot of people using free space on homepage hostings (shared Web hosting) to drive a nextcloud installation.
So most of the answers are useless for us.
It would be nice if more information about what to do in these cases, would be included in all the answers.

It would be very helpful to know which table is causing the error, so that we could do anything through phpMyAdmin for example.

jtr · January 11, 2025, 6:28pm

If you do the ALTER DATABASE, change the mysql.utf8mb4 parameter in your Nextcloud config, then run occ maintenance:repair all the tables are converted.

I think some people get overwhelmed by the other bits in the current docs. The rest of the changes are essentially defaults in MySQL and MariaDB these days. I’ve been meaning to submit a PR to streamline that doc section a bit.

P.S. Make sure you’re looking at the docs for currently supported versions of Nextcloud. The older docs have a lot of no longer applicable steps in that section.

Rudibert · January 11, 2025, 7:22pm

Thank you, for the try, but I thought I made clear, that I’m on a Shared Hoster. So it’s Not posible to execute occ-orders. That is exactly the problem I’m talking about.

jtr · January 11, 2025, 7:49pm

Many shared hosting environments have command-line access. There are also a million ways to still run shell commands even without ssh access.

I don’t think we formally support environments where one doesn’t have command-line access.

But if that’s really the concern, the answer is “any existing table” in your database that isn’t set to utf8mb4 already. You can check this in phyMyAdmin and similar tools. You can also readily use your favorite search engine to find SQL commands to do so. It’s not a Nextcloud specific matter. Many web host providers even have knowledge base articles on the topic.

If you want to see what the Nextcloud implementation does (via occ), including the raw SQL commands used to identify tables that need to be converted, check here. Then check here for the commands used to convert any existing tables discovered.

If someone wants to translate this to a clean addition to add to the official docs, click the “Edit” button in the upper right and/or post a How-To here in the forum.

Rudibert · January 11, 2025, 8:07pm

As I said formerly, thanx for your advice again. All tables are and has been utf8mb4. The problem is, that I can’t figure out, what causes the error anyway.