Problems with utf8mb4 encoding

ℹ️ Support
1

I have enabled utf8mb4 in MariaDB, I had to manually fix some table due to fields too wide to be indexed. It seems to work well but I got problems with nextcloud and app upgrade.

The plugins attempt to create tables with index on varchar(255) field and it give this error:
An exception occurred while executing ‘CREATE TABLE oc_group_folders_trash (trash_id BIGINT AUTO_INCREMENT NOT NULL, name VARCHAR(250) NOT NULL, original_location VARCHAR(4000) NOT NULL, deleted_time BIGINT NOT NULL, folder_id BIGINT NOT NULL, INDEX groups_folder_trash_folder (folder_id), INDEX groups_folder_name (folder_id, name), UNIQUE INDEX groups_folder_trash_unique (folder_id, name, deleted_time), PRIMARY KEY(trash_id)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ENGINE = InnoDB ROW_FORMAT = compressed’: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

So I must shorten the 255 to 190 in source code before installing.

Am I wrong to fix things this way ? Is there a general parameter that force nextcloud to limit indices to 190 char ?

2

Hi Sebastien and welcome to the forum.

Judging by the error message:

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

This is most likely due to the database not being setup correctly for Nextcloud. Please check the following documentation of Nextcloud to setup the database server correctly.

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

The most common problem is, that the innodb_file_format of the database is set to Antelope instead of Barracuda

If you do have SSH access, I would suggest adding the following to your MySQL config at /etc/mysql/my.cnf and restarting your MySQL afterwards.

[mysqld]
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=1

Now you can try your operation again.

1 Like
3

@CFelix :
That was it ! thank you verymuch.

Sorry I was sure I did folow the tutorial already. I don’t know how the value could have be reverted back.

4

I am glad to hear that this helped!

I had the same issue with it reverting back after a server restart, so I executed the statements again but after another restart it reverted back again, so I got fed up with it and just added this to the mentioned config file.

The problem with this is though, that you need to have access to that config, to make these changes. People who don’t have access to the config, might have to ask their hoster.

If my solution helped you, it would be awesome, of you could mark it as solution.

1 Like
5

@CFelix
Yes I did almost what the tutorial said, but by using the “set config” command, this does not save upon restart so it was not working.

Thank you again, hope we put enough keywords in the thread so it will help other people.

Cheers

6

seems this worked form me but only after a server reboot. NC 16.0.7 debian9
thanks.