ERROR MESSAGE:
An exception occurred while executing ‘ALTER TABLE [TABLE_NAME] 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
SOLUTION:
$ sudo mysql -u root -p
MariaDB [(none)]> use nextcloud;
MariaDB [nextcloud]> set global innodb_large_prefix=on;
MariaDB [nextcloud]> set global innodb_file_format=Barracuda;
$ sudo -u www-data php occ maintenance:repair
Hi, I found this video, it just help me https://www.youtube.com/watch?v=vPNU3fONY7c
Hi all,
Getting this after having tried to enable multi-byte support.
NC still works, the only error I see in the web-gui is that when I click Users/Everyone I get the error “An error occured during the request. Unable to proceed.”
Clicking the Admins group, or any other, shows the accounts properly (Except for the Everyone group of course).
Any idea how to proceed?
Thanks.
Edit: Forgot… Using CentOS 6.10 and MariaDB 5.5.63.
root@cyndane2:/var/www/html/nextcloud # sudo -u apache php occ maintenance:repair
- Repair MySQL collation
- Change row format for oc_addressbooks ...
- Change collation for oc_addressbooks ...
In AbstractMySQLDriver.php line 115:
An exception occurred while executing 'ALTER TABLE `oc_addressbooks` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;':
SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes.
In PDOStatement.php line 107:
SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes.
In PDOStatement.php line 105:
SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes.
maintenance:repair [--include-expensive]@snake29
I was looking every where for a solution untill I found this post. This worked very well. No problems and was able to excute occ command again.
Thanks
THANKS for this info. Not sure how else I would have found it.
This is still a problem on Ubuntu 18.04.2 but this fix worked for me.
EDIT: Ok, this was my fault because instructions here https://docs.nextcloud.com/server/16/admin_manual/configuration_database/mysql_4byte_support.html tell to make those changes.
My mistake is I had recently set up a system on FreeBSD which has newer MariaDB so I didn’t realize I was actually using 13.1.x on Ubuntu.
Thanks!! this worked for me…
thanks a lot … you save my day 
Thanks a lot for the fix and for keeping it brief and easy to follow! 
This also solved my update to 15.0.10 identical issue. Thanks
JHello !
This solution does not work for a shared server like OVH:
set global innodb_large_prefix=on;
or set global innodb_file_format=Barracuda; The response is :
Citation Access denied; you need (at least one of) the SUPER privilege(s) for this operation
I do not know what todo: my Cloud is down!
If there is no way to change the mysql configuration set 'mysql.utf8mb4' => false, in your config.php
I did not think about this solution!
In the meantime, I have redone a complete installation instead of an update and my Cloud is working.
I’ll know for next time
Thank you daniel512
THANK YOU, this just fixed my botched upgrade to 17.0.1!
I don’t get this fixed, any idea what am I doing wrong?
My NC is is 17.1, just upgraded from 17.
Mariadb is mariadb103-server-10.3.13 (freebsd iocage)
SHOW VARIABLES like 'innodb_large_prefix';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_large_prefix | true |
+---------------------+-------+
1 row in set (0.001 sec)
MariaDB [nextcloud]> SHOW VARIABLES like 'innodb_file_format';
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| innodb_file_format | barracuda |
+--------------------+-----------+
1 row in set (0.001 sec)
MariaDB [nextcloud]> SHOW VARIABLES like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
MariaDB [nextcloud]> SHOW VARIABLES like 'innodb_default_row_format';
+---------------------------+---------+
| Variable_name | Value |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+
ALTER DATABASE nextcloud CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.000 sec)
Done this on NC:
su www-data -s /bin/sh -c ' php occ config:system:set mysql.utf8mb4 --type boolean --value="true"'
System config value mysql.utf8mb4 set to boolean true
But still, doing this on NC fails:
su www-data -s /bin/sh -c 'php occ maintenance:repair'
Nextcloud is in maintenance mode - no apps have been loaded
- Repair MySQL collation
- Change row format for oc_bookmarks_tags ...
- Change collation for oc_bookmarks_tags ...
In AbstractMySQLDriver.php line 106:
An exception occurred while executing 'ALTER TABLE `oc_bookmarks_tags` 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 1000 bytes
In PDOStatement.php line 119:
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes
In PDOStatement.php line 117:
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes
maintenance:repair [--include-expensive]
This is what faulty table looks like:
MariaDB [nextcloud]> describe oc_bookmarks_tags;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| bookmark_id | bigint(20) | YES | MUL | NULL | |
| tag | varchar(255) | NO | | | |
+-------------+--------------+------+-----+---------+-------+
MariaDB [nextcloud]> select COLUMN_NAME, COLLATION_NAME from information_schema.COLUMNS WHERE table_schema=DATABASE() AND TABLE_NAME='oc_bookmarks_tags';
+-------------+----------------+
| COLUMN_NAME | COLLATION_NAME |
+-------------+----------------+
| bookmark_id | NULL |
| tag | utf8_bin |
+-------------+----------------+
What takes?
I created a ticket in github, this is deeper than just regular user problem. Some extra steps required in code I believe.
Just wanted to say Thank you.
The problem is not OVH, it’s the way you installed mysql or mariadb
if you have a vps you should have created a super user in the sudo group before setting up your db.
if you have an instance just try “sudo mysql -u root -p” with your ubuntu user
Try upgrading mariadb
Or try converting tables using the tips from nextcloud documentation on older mariadb.
https://docs.nextcloud.com/server/15/admin_manual/configuration_database/mysql_4byte_support.html
Follow steps 3 and 4 for mariadb 10.2
Worked fine for me
After following this solution, the I got the same error message as @avg. But after using the server for a bit, and adding some content (e.g. adding users, which poputlates the Addressbook), I tried the solution again, and it did not come back with any issues.
Solved for me! Thanks a lot!
Thanks! Very helpful