# \[SOLVED\] Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes **URL:** \ **Category:** ℹ️ Support\ **Tags:** update\_problems, database, nc16\ **Created:** [August 4, 2018, 3:59pm UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740 "2018-08-04T15:59:30Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![snake29](https://help.nextcloud.com/letter_avatar/snake29/32/6_22734026db12803ebb3e059622ac3534.png) [@snake29](https://help.nextcloud.com/u/snake29)\ **Post date:** [August 4, 2018, 3:59pm UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740/1 "2018-08-04T15:59:31Z")
**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 ---
**Author:** ![Carlo](https://help.nextcloud.com/letter_avatar/carlo/32/6_22734026db12803ebb3e059622ac3534.png) [@Carlo](https://help.nextcloud.com/u/Carlo)\ **Post date:** [October 24, 2018, 11:32pm UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740/2 "2018-10-24T23:32:37Z")
Hi, I found this video, it just help me [https://www.youtube.com/watch?v=vPNU3fONY7c](https://www.youtube.com/watch?v=vPNU3fONY7c) ---
**Author:** ![avg](https://help.nextcloud.com/user_avatar/help.nextcloud.com/avg/32/41381_2.png) [@avg](https://help.nextcloud.com/u/avg)\ **Post date:** [April 14, 2019, 8:02am UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740/3 "2019-04-14T08:02:18Z")
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] ``` ---
**Author:** ![GSmitt](https://help.nextcloud.com/letter_avatar/gsmitt/32/6_22734026db12803ebb3e059622ac3534.png) [@GSmitt](https://help.nextcloud.com/u/GSmitt)\ **Post date:** [April 19, 2019, 2:01am UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740/4 "2019-04-19T02:01:28Z")
@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 ---
**Author:** ![lamixer](https://help.nextcloud.com/letter_avatar/lamixer/32/6_22734026db12803ebb3e059622ac3534.png) [@lamixer](https://help.nextcloud.com/u/lamixer)\ **Post date:** [May 10, 2019, 3:51pm UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740/5 "2019-05-10T15:51:50Z")
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](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. ---
**Author:** ![jepainc](https://help.nextcloud.com/letter_avatar/jepainc/32/6_22734026db12803ebb3e059622ac3534.png) [@jepainc](https://help.nextcloud.com/u/jepainc)\ **Post date:** [May 13, 2019, 12:51am UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740/6 "2019-05-13T00:51:44Z")
> [@snake29](#): > > sudo -u www-data php occ maintenance:repair Thanks!! this worked for me… ---
**Author:** ![shyaminayesh](https://help.nextcloud.com/letter_avatar/shyaminayesh/32/6_22734026db12803ebb3e059622ac3534.png) [@shyaminayesh](https://help.nextcloud.com/u/shyaminayesh)\ **Post date:** [May 19, 2019, 11:15pm UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740/7 "2019-05-19T23:15:18Z")
thanks a lot … you save my day 😊 ---
**Author:** ![TroyDowling](https://help.nextcloud.com/user_avatar/help.nextcloud.com/troydowling/32/13792_2.png) [@TroyDowling](https://help.nextcloud.com/u/TroyDowling)\ **Post date:** [May 26, 2019, 11:32pm UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740/8 "2019-05-26T23:32:30Z")
Thanks a lot for the fix and for keeping it brief and easy to follow! 😄 ---
**Author:** ![kibade](https://help.nextcloud.com/letter_avatar/kibade/32/6_22734026db12803ebb3e059622ac3534.png) [@kibade](https://help.nextcloud.com/u/kibade)\ **Post date:** [July 14, 2019, 6:48pm UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740/9 "2019-07-14T18:48:15Z")
> [@snake29](#): > > sudo -u www-data php occ maintenance:repair This also solved my update to 15.0.10 identical issue. Thanks ---
**Author:** ![jbyvosges](https://help.nextcloud.com/user_avatar/help.nextcloud.com/jbyvosges/32/2908_2.png) [@jbyvosges](https://help.nextcloud.com/u/jbyvosges)\ **Post date:** [September 28, 2019, 4:03pm UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740/11 "2019-09-28T16:03:05Z")
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! ---
**Author:** ![kesselb](https://help.nextcloud.com/user_avatar/help.nextcloud.com/kesselb/32/74519_2.png) [@kesselb](https://help.nextcloud.com/u/kesselb)\ **Post date:** [September 28, 2019, 5:39pm UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740/12 "2019-09-28T17:39:57Z")
If there is no way to change the mysql configuration set `'mysql.utf8mb4' => false,` in your config.php ---
**Author:** ![jbyvosges](https://help.nextcloud.com/user_avatar/help.nextcloud.com/jbyvosges/32/2908_2.png) [@jbyvosges](https://help.nextcloud.com/u/jbyvosges)\ **Post date:** [September 28, 2019, 5:57pm UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740/13 "2019-09-28T17:57:05Z")
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 ---
**Author:** ![lfod14](https://help.nextcloud.com/letter_avatar/lfod14/32/6_22734026db12803ebb3e059622ac3534.png) [@lfod14](https://help.nextcloud.com/u/lfod14)\ **Post date:** [November 16, 2019, 6:21pm UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740/14 "2019-11-16T18:21:25Z")
THANK YOU, this just fixed my botched upgrade to 17.0.1! ---
**Author:** ![ikkeT](https://help.nextcloud.com/user_avatar/help.nextcloud.com/ikket/32/5378_2.png) [@ikkeT](https://help.nextcloud.com/u/ikkeT)\ **Post date:** [November 16, 2019, 8:43pm UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740/15 "2019-11-16T20:43:17Z")
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) ```auto 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: ```auto 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: ```auto 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: ```auto 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? ---
**Author:** ![ikkeT](https://help.nextcloud.com/user_avatar/help.nextcloud.com/ikket/32/5378_2.png) [@ikkeT](https://help.nextcloud.com/u/ikkeT)\ **Post date:** [November 17, 2019, 10:32am UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740/16 "2019-11-17T10:32:24Z")
I created a ticket in github, this is deeper than just regular user problem. Some extra steps required in code I believe. > > > Steps to reproduce > I got notified to enable emoji support following this guide: https://docs.nextcloud.com/server/17/admin\_manual/configuration\_database/mysql\_4byte\_support.html > I did the mariadb parts, but failed at the... ---
**Author:** ![eredwood007](https://help.nextcloud.com/user_avatar/help.nextcloud.com/eredwood007/32/46799_2.png) [@eredwood007](https://help.nextcloud.com/u/eredwood007)\ **Post date:** [December 24, 2019, 5:36pm UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740/17 "2019-12-24T17:36:02Z")
Just wanted to say Thank you. ---
**Author:** ![nso](https://help.nextcloud.com/user_avatar/help.nextcloud.com/nso/32/25333_2.png) [@nso](https://help.nextcloud.com/u/nso)\ **Post date:** [December 27, 2019, 1:06am UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740/18 "2019-12-27T01:06:08Z")
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 ---
**Author:** ![nso](https://help.nextcloud.com/user_avatar/help.nextcloud.com/nso/32/25333_2.png) [@nso](https://help.nextcloud.com/u/nso)\ **Post date:** [December 27, 2019, 1:12am UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740/19 "2019-12-27T01:12:55Z")
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](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 ---
**Author:** ![XenonOrion](https://help.nextcloud.com/user_avatar/help.nextcloud.com/xenonorion/32/25002_2.png) [@XenonOrion](https://help.nextcloud.com/u/XenonOrion)\ **Post date:** [April 1, 2020, 3:05pm UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740/20 "2020-04-01T15:05:29Z")
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! ---
**Author:** ![dominikhaczek](https://help.nextcloud.com/letter_avatar/dominikhaczek/32/6_22734026db12803ebb3e059622ac3534.png) [@dominikhaczek](https://help.nextcloud.com/u/dominikhaczek)\ **Post date:** [April 18, 2020, 10:54pm UTC](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740/21 "2020-04-18T22:54:06Z")
Thanks! Very helpful [Next page](https://help.nextcloud.com/t/solved-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-767-bytes/34740.md?page=2)