The advised conversion to utf8mb4 screwed up my instance

ℹ️ Support
1

Hello,

Seeing the warning in the admin page saying that my SQL configuration does not support 4-bytes utf8 characters, I followed the guide here: https://docs.nextcloud.com/server/15/admin_manual/configuration_database/mysql_4byte_support.html

And oh boy! that was a very bad idea!

The thing is, even official apps are not ready to do the conversion. For instance, the oc_addressbooks table (which is, I guess, from the official Contacts app) has columns that are both UNIQUE and varchar(255) and this is very problematic.

I am on Ubuntu 18.04.2 LTS and, there, MySQL is configured to have a maximum of 767 bytes for unique keys. When in regular UTF-8, varchar(255) actually takes 765 bytes (255*3), which is less than 767, so we’re good. But when converting to 4-bytes UTF-8 (utf8mb4), then these unique keys are now 1020 bytes long (255*4), which is more than the limit and that’s when the shit hits the fan.

I think there should be more tests done before advising to do the conversion, because the solution is not that simple, as explained here: https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes

Changing the max key length only works in specific conditions: the row format must be dynamic or compressed, and the InnoDB file format must be Barracuda.

I would advise to remove the warning from the NC admin panel.

3 Likes
2

I agree, yopu should remove that warning!!
I fuc* up my NC for this warning!!
And there is hard to return it back.

3

I finally made it work without messing up my tables!

I followed the Nextcloud 17 documentation: Enabling MySQL 4-byte support.

It was a bit weird to do though since the documentation is not 100% clear on what to do if you’re coming from MariaDB 10.2 or earlier. It needs to do stuff that is written at the end of the page before doing the main stuff at the top.

Here are the steps I followed:

  1. So the first thing to do is to backup all your databases
  2. Then follow the MariaDB 10.2 or earlier section
    It alters the MariaDB config file and the Nextcloud tables
  3. Then follow the main Enabling MySQL 4-byte support section, starting at step 4
    Just to be sure, before starting the steps, you can type the “show variables like XXXX;” in MariaDB shell, to ensure the new config file is applied
  4. Then, if you’re using mysqldump to backup your database, don’t forget to add the --default-character-set=utf8mb4 argument

Notes:

The changes on the MariaDB configuration file are pretty safe since they’re the default for version 10.3 and above.

  • InnoDB file format ‘Antelope’ is deprecated starting 10.2.2 (doc).
  • InnoDB large prefix is ‘on’ by default starting 10.2.2 (doc).
  • InnoDB file per table is ‘on’ by default since ever (doc).
1 Like