Enabling MySQL 4-byte support caused exceptions

ℹ️ Support
1

I’m running NC 18.0.3 on fully updated Centos7 with mariadb 5.5.64

Being a bit bored I thought I would get rid of some of the warnings that I have on
…/nextcloud/index.php/settings/admin/overview

The one about “Enabling MySQL 4-byte support” caused me problems. I followed this page https://docs.nextcloud.com/server/18/admin_manual/configuration_database/mysql_4byte_support.html

and everything went fine until the step 7 php occ maintenance:repair. It caused the following output

Nextcloud is in maintenance mode - no apps have been loaded

  • Repair MySQL collation
    • Change row format for oc_accounts …
    • Change collation for oc_accounts …
    • Change row format for oc_activity …
    • Change collation for oc_activity …
    • Change row format for oc_activity_mq …
    • Change collation for oc_activity_mq …
    • Change row format for oc_addressbookchanges …
    • Change collation for oc_addressbookchanges …
    • Change row format for oc_addressbooks …
    • Change collation for oc_addressbooks …

In AbstractMySQLDriver.php line 106:

An exception occurred while executing ‘ALTER TABLE oc_addressbooks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;’:

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

In PDOStatement.php line 119:

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

In PDOStatement.php line 117:

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

maintenance:repair [–include-expensive]

But when I ended maintenance mode and went back to the page
…/nextcloud/index.php/settings/admin/overview
the warning was gone and NC seems to behave normally.

So I don’t know what to think about this, any advice?

2

Can you try first:

php occ db:add-missing-indices

php occ db:convert-filecache-bigint 

php occ db:convert-mysql-charset

And then see if

php occ maintenance:repair

Is oke again.

3

I also suggest to upgrade youre mariadb from 5.5 to 10:

4

Of the two suggestions, in which order should I do them?

5 
 1.  php occ db:add-missing-indices

 2.  php occ db:convert-filecache-bigint 

 3.  php occ db:convert-mysql-charset
6

The two first executed fine, but on the third I got the response
“The database is not properly setup to use the charset utf8mb4.”

7

You change youre database with:

ALTER DATABASE nextcloud CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

8

I did a “ALTER DATABASE nextcloud CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;”
and it returned “Query OK, 1 row affected (0.00 sec)”

After that I did “occ db:convert-mysql-charset” but it returned
“The database is not properly setup to use the charset utf8mb4.”

After that I tried “occ maintenance:repair” and it returned

  • Repair MySQL collation
    • Change row format for oc_addressbooks …
    • Change collation for oc_addressbooks …

In AbstractMySQLDriver.php line 106:

An exception occurred while executing ‘ALTER TABLE oc_addressbooks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;’:

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

In PDOStatement.php line 119:

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

In PDOStatement.php line 117:

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

9

Hi Robin,

Have a look at this issue on GitHub, there are a few detailed steps on how to enable utf8mb4 with MariaDB.

Link: https://github.com/nextcloud/server/issues/15648

10

Thanks.

I got rid of the errors