App upgrade triggered by upgrade to Nextcloud v27.0.2 fails with SQL Error

[tmlmt]

Steps to reproduce

Well, don't know how reproducible that is but what happened is:

1. Launched the updater from Nextcloud Administration Settings, to upgrade to v27.0.2
2. Decided to go manual and ran occ upgrade locally: success
3. Went back to my online instance, where it was still asking me to Launch the update from there, which I then did: failure
4. Tried occ upgrade locally again, led to the same error
5. Disabled the mail app with occ app:disable mail and Nextcloud magically loaded online again (but with the Mail app disabled)

Expected behavior

Nextcloud and apps should have updated succesfully

Actual behavior

Log from Step no. 2 above:

Setting log level to debug
Turned on maintenance mode
Updating database schema
Updated database
Disabled incompatible app: apporder
Update app calendar from App Store
Update app contacts from App Store
Update app deck from App Store
Update app mail from App Store
Update app passwords from App Store
Update app spreed from App Store
Starting code integrity check...
Finished code integrity check
Update successful
Turned off maintenance mode
Resetting log level

Shouldn't it be the end of the upgrade? Why did it still ask me online to Launch the update?

Log of any subsequent attempts (step no. 3 or 4 above, or trying to now re-enable Mail on Nextcloud v27.0.2)

Setting log level to debug
Turned on maintenance mode
Updating database schema
Updated database
Updating <mail> ...
Exception: Database error when running migration 3300Date20230801124717 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
Maintenance mode is kept active
Resetting log level

Mail app version

3.4.0-beta.2

Mailserver or service

N/A

Operating system

Ubuntu 20.04.6

PHP engine version

PHP 8.1

Web server

Nginx

Database

MySQL

Additional info

How can I re-enable the Mail in its latest version? Remove and re-install?

[ChristophWurst]

👀 @st3iny

https://github.com/nextcloud/mail/blob/main/lib/Migration/Version3300Date20230801124717.php

[ChristophWurst]

The database might lack 4-byte support

[st3iny]

Christoph is right. Please make sure that your database and the oc_mail_messages_retention table use the correct utf8mb4 charset. Please refer to the admin documentation for more information.

On a side note: I just learned that creating an index on such a wide VARCHAR column is not a good idea in general as it will get quite bulky (and thus inefficient).

[tmlmt]

Christoph is right. Please make sure that your database and the oc_mail_messages_retention table use the correct utf8mb4 charset. Please refer to the admin documentation for more information.

Hey @st3iny, thanks for referring to these MySQL parameter tweaking instructions. I followed the guide (i.e. switching the collabotion of the database and its table to utf8mb4), but the error still persists. Logs:

Confirming default charset/collation is utf8mb4:

mysql> USE nextcloud;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_general_ci   |
+--------------------------+----------------------+
1 row in set (0.00 sec)

Same error when trying to re-enable mail:

tmlmt@server$ sudo -u www-data php occ app:enable mail
Nextcloud is in maintenance mode, no apps are loaded.
Commands provided by apps are unavailable.
Database error when running migration 3300Date20230801124717 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

Is it linked with having 4 bytes enabled or not, though? If your idea is to add an index on column 'message_id' defined as below: https://github.com/nextcloud/mail/blob/49b34a6c78efe446979ab41c899a4b3c3877e11d/lib/Migration/Version3300Date20230801124717.php#L62-L64 Then with 4 bytes characters, and a VARCHAR(1024), aren't you getting 4*1024+1 > 3072 as key length? Additional hints in this Stackoverflow post maybe?

Coming back to your other comment:

On a side note: I just learned that creating an index on such a wide VARCHAR column is not a good idea in general as it will get quite bulky (and thus inefficient).

Indeed a remark made 13 years ago on Stackoverflow:

Think about the meaning of an index. It's quick access to a row when all your indexed fields are within the where clause. If an index is to long (in case of mysql more than 1000 bytes), it makes no sense to use an index, because it's probably slower than accessing the complete table with a full table scan.

Another side note from me about Nextcloud's docs on database configuration. The docs are not very clear / explicit about what are the actual requirements. The text says:

Your /etc/mysql/my.cnf could look like this:

followed by a bunch of parameters in a code block. And the page you shared is presenting its content as something:

In order to use Emojis (textbased smilies) on your Nextcloud server with a MySQL database

which sound to me like a nice-to-have than a requirerement.

[kristofejro]

It looks the same as 8706