Specified key was too long; max key length is 3072 bytes; migration 5006Date20250927130132 - mail_mailboxes.name

[blizzz]

Steps to reproduce

1. Upgrade to 5.5.9

Expected behavior

Upgrade succeeds

Actual behavior

Exception: Database error when running migration 5006Date20250927130132 for app mail
An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes
Update failed

This is about modifying column name of mail_mailboxes.

{
  "reqId": "G5RuqTtIOPSh39aW2nCM",
  "level": 3,
  "time": "2025-10-14T21:49:36+00:00",
  "remoteAddr": "",
  "user": "--",
  "app": "updater",
  "method": "",
  "url": "--",
  "message": "\\OC\\Updater::failure: Exception: Database error when running migration 5006Date20250927130132 for app mail\nAn exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 3072 bytes",
  "userAgent": "--",
  "version": "31.0.9.1",
  "data": {
    "app": "updater"
  }
}

Mail app version

5.5.9

Nextcloud version

31.0.9

Mailserver or service

No response

Operating system

No response

PHP engine version

None

Nextcloud memory caching

No response

Web server

None

Database

MySQL 8.0.43 (yes, sorry)

Additional info

No response

[blizzz]

It is weird, I don't see it being used in a key on first sight together with account_id.

Manually patching down to 512 (something <=764 should work as well tho) is a workaround (and decreasing file name and class by 1 😁).

[csriebel08]

Could you explain the workaround or fix to this, I am not sure that I follow. I am having the same issue with the mail app upgrade with basically the same specs as OP.

[ChristophWurst]

Caused by #11761

[ChristophWurst]

@blizzz @csriebel08 could we compare our table definition? I wonder why the migration succeeded for my personal prod.

MariaDB [nextcloud]> show create table oc_mail_mailboxes \G;
*************************** 1. row ***************************
       Table: oc_mail_mailboxes
Create Table: CREATE TABLE `oc_mail_mailboxes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(1024) NOT NULL,
  `account_id` int(11) NOT NULL,
  `attributes` varchar(255) NOT NULL DEFAULT '[]',
  `delimiter` varchar(1) DEFAULT NULL,
  `messages` int(11) NOT NULL,
  `unseen` int(11) NOT NULL,
  `selectable` tinyint(1) DEFAULT 0,
  `special_use` varchar(255) NOT NULL DEFAULT '[]',
  `sync_new_lock` int(11) DEFAULT NULL,
  `sync_changed_lock` int(11) DEFAULT NULL,
  `sync_vanished_lock` int(11) DEFAULT NULL,
  `sync_new_token` varchar(255) DEFAULT NULL,
  `sync_changed_token` varchar(255) DEFAULT NULL,
  `sync_vanished_token` varchar(255) DEFAULT NULL,
  `sync_in_background` tinyint(1) DEFAULT 0,
  `my_acls` varchar(32) DEFAULT NULL,
  `shared` tinyint(1) DEFAULT 0,
  `name_hash` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mail_mb_account_id_name_hash` (`account_id`,`name_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=78 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC
1 row in set (0,000 sec)

do you use mb4 and the dynamic row format?

[nickvergessen]

do you use mb4

Sounds a bit like in the old days (mysql < 8 and mariadb < 10.2) where one has to enable long prefix

https://docs.nextcloud.com/server/20/admin_manual/configuration_database/mysql_4byte_support.html#mariadb-10-2-or-earlier

[blizzz]

@ChristophWurst I have a unique key spanning account_id and name.

mysql> show create table mail_mailboxes \G
*************************** 1. row ***************************
       Table: mail_mailboxes
Create Table: CREATE TABLE `mail_mailboxes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(512) COLLATE utf8mb4_bin NOT NULL,
  `account_id` int NOT NULL,
  `attributes` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '[]',
  `delimiter` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `messages` int NOT NULL,
  `unseen` int NOT NULL,
  `selectable` tinyint(1) DEFAULT '0',
  `special_use` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '[]',
  `sync_new_lock` int DEFAULT NULL,
  `sync_changed_lock` int DEFAULT NULL,
  `sync_vanished_lock` int DEFAULT NULL,
  `sync_new_token` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `sync_changed_token` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `sync_vanished_token` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `sync_in_background` tinyint(1) DEFAULT '0',
  `my_acls` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `shared` tinyint(1) DEFAULT '0',
  `name_hash` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_45754FF89B6B5FBA5E237E06` (`account_id`,`name`),
  UNIQUE KEY `mail_mb_account_id_name_hash` (`account_id`,`name_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=196 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

[blizzz]

I guess this is that index:

mail/lib/Migration/Version0161Date20190902103701.php

Lines 79 to 82 in d04c393

	$mailboxTable->addUniqueIndex([
	'account_id',
	'name',
	]);

And this one tried to drop it:

mail/lib/Migration/Version3500Date20231115184458.php

Line 35 in d04c393

$mailboxesTable->dropIndex($indexOld);

But the name does not match:

UNIQ_45754FF89B6B5FBA5E237E06 (mine)
UNIQ_22DEBD839B6B5FBA5E237E06 (Version3500Date20231115184458)

[blizzz]

@csriebel08 my suggestion now is to drop that old index with that funny name.

DROP INDEX UNIQ_45754FF89B6B5FBA5E237E06 on oc_mail_mailboxes;

⚠️ always do a database backup before manually tampering with it 😉

Your index might be called differently. Confirm by checking the output of show create table mail_mailboxes \G (you can compare with the output from my comment above).

After running this, rerun the upgrade procedure.

But you might wanna wait for a confirmation by @ChristophWurst