Server: "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 TABLEoc_addressbooksCONVERT 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!

[danxensen]

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?

[gytisrepecka]

Makeshift:

Set the following.

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

Thanks, this helped!

[iakiakiak]

I ran into that issue migration Nextcloud 16.0.1 to 16.0.4 on CentOS 7.7 and MariaDB 5.5.64

I fixed with adding these lines to /etc/my.cnf :
innodb_large_prefix=1
innodb_large_prefix=true
innodb_file_per_table=true
innodb_file_format=barracuda

Especially the last line did the trick.

[Sx3]

I ran into that issue migration Nextcloud 16.0.1 to 16.0.4 on CentOS 7.7 and MariaDB 5.5.64

I fixed with adding these lines to /etc/my.cnf :
innodb_large_prefix=1
innodb_large_prefix=true
innodb_file_per_table=true
innodb_file_format=barracuda

Especially the last line did the trick.

Oh Man You Just Saved me....That worked..Thank you.

[47780]

I ran into that issue migration Nextcloud 16.0.1 to 16.0.4 on CentOS 7.7 and MariaDB 5.5.64

I fixed with adding these lines to /etc/my.cnf :
innodb_large_prefix=1
innodb_large_prefix=true
innodb_file_per_table=true
innodb_file_format=barracuda

Especially the last line did the trick.

This worked for me! Thanks!