"CONVERT TO CHARACTER SET utf8mb4"

[emresaracoglu]

Hi,

I encountered an error while updating the database:

Doctrine\DBAL\Exception\DriverException: 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

How can I fix this?

PHP 7.2
Nextcloud V: 16 Beta 3 -> 16 RC1

[emresaracoglu]

Makeshift:

Set the following.

INNODB utf8 VARCHAR(255)
INNODB utf8mb4 VARCHAR(191)

[Pasulke]

Hallo, don't know where to insert these commands.
Would you please explain?
Thank You.

[emresaracoglu]

Hallo, don't know where to insert these commands.
Would you please explain?
Thank You.

You can edit tables structure of Nextcloud database with any database manager (Adminer, phpMYadmin or etc) but firstly you should take a backup of the database.

[Pasulke]

Worked, thank you!

[cakexensen]

I don't have any database managers on my server, is there a way to fix this via the MySQL console? I'm not sure what sort of query would be used.

[mr-gosh]

how is this done through the mysql client - so no ssh tunnel is needed to do this on the client

[shyim]

Or upgrade your MySQL Server to a newer version

[mr-gosh]

I am on mysql 5.5.62 and an upgrade is not necessary if you configure the innodb stuff the right way as I found out now:

Most blog entries to this error suggest to configure
innodb_large_prefix=true

But this leads to the mentioned error if you have large datasets as we do.

After setting up the
innodb_large_prefix=1

Everything regarding tha database migration worked like a charm.

[mentalinc]

Can you please provide more step by step on where to change the two settings below?
I cant see it in phpMyAdmin

INNODB utf8 VARCHAR(255)
INNODB utf8mb4 VARCHAR(191)

I get the following when i run sudo -u www-data php occ maintenance:repair

In AbstractMySQLDriver.php line 125:

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 143:

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

In PDOStatement.php line 141:

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

[mentalinc]

Looks like updating to the latest version of 10.2 or 10.3 MariaDB from 10.1 will be easier solution
http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

update:
Can confirm updagint to 10.3 of MariaDB has fixed the issues.

sudo apt-get install software-properties-common
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://mirror.jaleco.com/mariadb/repo/10.3/ubuntu bionic main'
sudo apt-get update
sudo apt-get dist-upgrade

[skjnldsv]

Yep, I can also confirm. Please migrate to mariadb as it's actually vastly more supported and documented than mysql and check your innodb_large_prefix

Closing as it's irrelevant to nextcloud itself.

[mr-gosh]

Closing as it's irrelevant to nextcloud itself.

sorry - but nextcloud suggested to do that migration - i won't give a shit to emoji support BUT as nextcloud pointed me to do so i did and ran into that problem. I would suggest to check the innodb_large_prefix=1 status during the upgrade preparation tests...

[olegchensky]

15.0.7 --> 16.0.1
Stable chanel! The same issue! By production is not normal. What to do?

[MysterHawk]

Makeshift:

Set the following.

INNODB utf8 VARCHAR(255)
INNODB utf8mb4 VARCHAR(191)

Just use
ALTER TABLE oc_addressbooks MODIFY COLUMN principaluri VARCHAR (191);
ALTER TABLE oc_addressbooks MODIFY COLUMN displayname VARCHAR (191);
ALTER TABLE oc_addressbooks MODIFY COLUMN uri VARCHAR (191);
ALTER TABLE oc_addressbooks MODIFY COLUMN description VARCHAR (191);

in the mysql cli.

But probably the best thing would be to change the MYSQL config, with this:

[mysqld]
innodb_file_per_table=1
innodb_large_prefix=ON
innodb_file_format=Barracuda

[pr0krastinator]

Thanks a lot Antonio! It helped me a lot - an additional problem for me is, that I'm working in docker...

For me, the change to the MySQL config had to be:

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

Maybe it depends on the MySQL/MariaDB-version. Mine is:

root@6705990ae06c:/# mariadb -V
mariadb  Ver 15.1 Distrib 10.1.47-MariaDB, for debian-linux-gnueabihf (armv7l) using readline 5.2

If anybody else has this problem with Docker (specifically linuxserver/nextcloud & linuxserver/mariadb):

• docker exec -it mariadb bash
• install nano (optional)
• find right config-file (did it via mariadb --help); in my case it was /etc/mysql/mariadb.cnf
• nano /etc/mysql/mariadb.cnf
• under [client-server] insert:

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

• Restart mysql: service mysql restart
• Exit from container: exit
• Restart docker container: docker restart mariadb nextcloud

Back to nextcloud:

• docker exec --user abc -it nextcloud bash
• cd /config/www/nextcloud/
• Make sure maintainance mode is on: php occ maintenance:mode --on
• Now just repair and disable maintainance mode: php occ repair --include-expensive & php occ maintenance:mode --off

[MysterHawk]

Thanks a lot Antonio! It helped me a lot - an additional problem for me is, that I'm working in docker...

For me, the change to the MySQL config had to be:

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

Maybe it depends on the MySQL/MariaDB-version. Mine is:

root@6705990ae06c:/# mariadb -V
mariadb  Ver 15.1 Distrib 10.1.47-MariaDB, for debian-linux-gnueabihf (armv7l) using readline 5.2

If anybody else has this problem with Docker (specifically linuxserver/nextcloud & linuxserver/mariadb):

* `docker exec -it mariadb bash`

* install nano (optional)

* find right config-file (did it via mariadb --help); in my case it was /etc/mysql/mariadb.cnf

* `nano /etc/mysql/mariadb.cnf`

* under [client-server] insert:

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

* Restart mysql: `service mysql restart`

* Exit from container: `exit`

* Restart docker container: `docker restart mariadb nextcloud`

Back to nextcloud:

* `docker exec --user abc -it nextcloud bash`

* `cd /config/www/nextcloud/`

* Make sure maintainance mode is on: `php occ maintenance:mode --on`

* Now just repair and disable maintainance mode: `php occ repair --include-expensive` & `php occ maintenance:mode --off`

No problem glad that I could help 👍 , yeah probably it depends by the mariadb version used.

If you use the linuxserver/mariadb docker's container, you could just change the config custom.cnf mounted with the mariadb config folder in the disk and then restart it (docker restart mariadb nextcloud);

The mysql service will reload with the container :)

[alabiana]

Makeshift:

Set the following.

INNODB utf8 VARCHAR(255)
INNODB utf8mb4 VARCHAR(191)

How will i do this via mysql console?

[MootoolsSoftware]

Hello,

I have no access to the conf file and no way to to modify it.

I first have the following error
Étape de réparation : Repair MySQL collation
Informations de réparation : Change row format for oc_addressbooks ...
Informations de réparation : Change collation for oc_addressbooks ...
Doctrine\DBAL\Exception\DriverException: 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

So I applied :

_ALTER TABLE oc_addressbooks MODIFY COLUMN principaluri VARCHAR (191);
ALTER TABLE oc_addressbooks MODIFY COLUMN displayname VARCHAR (191);
ALTER TABLE oc_addressbooks MODIFY COLUMN uri VARCHAR (191);
ALTER TABLE oc_addressbooks MODIFY COLUMN description VARCHAR (191);_

Then I launch the update again and now the error is:

  _Informations de réparation : Change row format for oc_authtoken ...
  Informations de réparation : Change collation for oc_authtoken ...
  Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'ALTER TABLE oc_authtoken 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

I am now stuck with this bug and don't know what to do because when I look at the table I don't know which field has to be promoted to VARCHAR(191)

Anyone can help ?
Thanks !