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

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

24 Likes

Hi, I found this video, it just help me https://www.youtube.com/watch?v=vPNU3fONY7c

1 Like

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

Thanks a lot for the fix and for keeping it brief and easy to follow! :smile:

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