# NC19 SQLSTATE\[42000\]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes while executing 'CREATE INDEX properties\_path\_index ON oc\_properties (userid, propertypath)' **URL:** \ **Category:** ℹ️ Support\ **Tags:** database, nc19\ **Created:** [August 18, 2020, 10:12am UTC](https://help.nextcloud.com/t/nc19-sqlstate-42000-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-1000-bytes-while-executing-create-index-properties-path-index-on-oc-properties-userid-propertypath/89943 "2020-08-18T10:12:07Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![enrico.tagliavini](https://help.nextcloud.com/letter_avatar/enrico.tagliavini/32/5_5575768a8748004e209b776fc1b2916d.png) [@enrico.tagliavini](https://help.nextcloud.com/u/enrico.tagliavini)\ **Post date:** [August 18, 2020, 10:12am UTC](https://help.nextcloud.com/t/nc19-sqlstate-42000-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-1000-bytes-while-executing-create-index-properties-path-index-on-oc-properties-userid-propertypath/89943/1 "2020-08-18T10:12:07Z")
Nextcloud version: 19.0.1 Operating system and version: CentOS 7 Apache version: httpd 2.4.34-18 PHP version: 7.3 MariaDB version: 5.5.65 This morning I updated from NC 18.0.7 (everything working, no warnings reported) to NC 19.0.1. Once I upgraded I had to add some missing indexes, but this fails with an error: ```auto # sudo -u apache -- scl enable rh-php73 -- php /var/www/nextcloud/occ db:add-missing-indices Check indices of the share table. Check indices of the filecache table. Check indices of the twofactor_providers table. Check indices of the login_flow_v2 table. Check indices of the whats_new table. Check indices of the cards table. Check indices of the cards_properties table. Check indices of the calendarobjects_props table. Check indices of the schedulingobjects table. Check indices of the oc_properties table. Adding properties_path_index index to the oc_properties table, this can take some time... In AbstractMySQLDriver.php line 106: An exception occurred while executing 'CREATE INDEX properties_path_index ON oc_properties (userid, propertypath)': SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes In PDOConnection.php line 90: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes In PDOConnection.php line 88: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes db:add-missing-indices ``` I have no idea how to solve this. I googled a bit and most hits are referring to the utf8mb4 conversion which I did correctly to my best understanding. NC 18 didn’t complain about it and I set the mysql.utf8mb4 to true. File format is barracuda. I tried a repair and it runs successfully ```auto # sudo -u apache -- scl enable rh-php73 -- php /var/www/nextcloud/occ maintenance:repair - Repair MySQL collation - All tables already have the correct collation -> nothing to do - Repair mime types - Clean tags and favorites - 0 tags of deleted users have been removed. - 0 tags for delete files have been removed. - 0 tag entries for deleted tags have been removed. - 0 tags with no entries have been removed. - Repair invalid shares - Move .step file of updater to backup location - Fix potential broken mount points - No mounts updated - Add log rotate job - Clear frontend caches - Image cache cleared - SCSS cache cleared - JS cache cleared - Clear every generated avatar on major updates - Add preview background cleanup job - Queue a one-time job to cleanup old backups of the updater - Cleanup invalid photocache files for carddav - Add background job to cleanup login flow v2 tokens - Remove potentially over exposing share links - No need to remove link shares. - Clear access cache of projects - Reset generated avatar flag - Fix component of birthday calendars - 6 birthday calendars updated. - Regenerating birthday calendars to use new icons and fix old birthday events without year - Repair step already executed - Fix broken values of calendar objects 0 [->--------------------------] - Registering building of calendar search index as background job - Repair step already executed - Registering background jobs to update cache for webcal calendars - Added 0 background jobs to update webcal calendars - Registering building of calendar reminder index as background job - Repair step already executed - Clean up orphan event and contact data - 0 events without a calendar have been cleaned up - 0 properties without an events have been cleaned up - 0 changes without a calendar have been cleaned up - 0 cached events without a calendar subscription have been cleaned up - 0 changes without a calendar subscription have been cleaned up - 0 contacts without an addressbook have been cleaned up - 0 properties without a contact have been cleaned up - 0 changes without an addressbook have been cleaned up - Remove activity entries of private events - Removed 0 activity entries - Fix the share type of guest shares when migrating from ownCloud - Copy the share password into the dedicated column - Set existing shares as accepted - Clean up meta table - Update OAuth token expiration times - Create help command - Invalidate access cache for projects conversation provider - Invalidation not required - Switches from default updater server to the customer one if a valid subscription is available - Repair step already executed - Add background job to check for backup codes - Insert UUIDFix background job for user and group in batches - Populating added database structures for workflows # echo $? 0 ``` config.php ```auto '', 'passwordsalt' => '', 'datadirectory' => '', 'dbtype' => 'mysql', 'version' => '19.0.1.1', 'dbname' => 'owncloud', 'dbhost' => 'localhost', 'dbtableprefix' => 'oc_', 'dbuser' => 'owncloud', 'dbpassword' => '', 'installed' => true, 'ldapIgnoreNamingRules' => false, 'overwriteprotocol' => 'https', 'maintenance' => false, 'theme' => '', 'log_type' => 'owncloud', 'logfile' => '/logs/nextcloud.log', 'loglevel' => 1, 'trusted_domains' => array ( 0 => '', ), 'trusted_proxies' => array ( 0 => '', 1 => '', ), 'share_folder' => '/Shared', 'overwritewebroot' => '/nextcloud', 'secret' => '', 'memcache.local' => '\\OC\\Memcache\\APCu', 'overwritehost' => '', 'overwrite.cli.url' => 'https:///nextcloud', 'trashbin_retention_obligation' => 'auto', 'ldapProviderFactory' => '\\OCA\\User_LDAP\\LDAPProviderFactory', 'updater.release.channel' => 'stable', 'mysql.utf8mb4' => true, 'mail_from_address' => 'nextcloud', 'mail_smtpmode' => 'smtp', 'mail_sendmailmode' => 'smtp', 'mail_domain' => '', 'mail_smtphost' => 'localhost', 'mail_smtpport' => '25', ); ``` The table also looks file to my (poor) understanding ```auto MariaDB [owncloud]> SHOW FULL COLUMNS FROM oc_properties; +---------------+--------------+-------------+------+-----+---------+----------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +---------------+--------------+-------------+------+-----+---------+----------------+---------------------------------+---------+ | userid | varchar(64) | utf8mb4_bin | NO | MUL | | | select,insert,update,references | | | propertypath | varchar(255) | utf8mb4_bin | NO | | | | select,insert,update,references | | | propertyname | varchar(255) | utf8mb4_bin | NO | | | | select,insert,update,references | | | propertyvalue | longtext | utf8mb4_bin | NO | | NULL | | select,insert,update,references | | | id | bigint(20) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | +---------------+--------------+-------------+------+-----+---------+----------------+---------------------------------+---------+ ``` Based on other topics I can’t solve this issue, any help is appreciated. Thank you. Enrico ---
**Author:** ![tuxedo-rb](https://help.nextcloud.com/user_avatar/help.nextcloud.com/tuxedo-rb/32/15189_2.png) [@tuxedo-rb](https://help.nextcloud.com/u/tuxedo-rb)\ **Post date:** [August 18, 2020, 12:25pm UTC](https://help.nextcloud.com/t/nc19-sqlstate-42000-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-1000-bytes-while-executing-create-index-properties-path-index-on-oc-properties-userid-propertypath/89943/2 "2020-08-18T12:25:20Z")
Hello. Contains your my.cnf file the `innodb_large_prefix=true` entry under `[mysqld]`-section? This increases the byte limit up to 3072 bytes for index prefixes. ---
**Author:** ![enrico.tagliavini](https://help.nextcloud.com/letter_avatar/enrico.tagliavini/32/5_5575768a8748004e209b776fc1b2916d.png) [@enrico.tagliavini](https://help.nextcloud.com/u/enrico.tagliavini)\ **Post date:** [August 18, 2020, 12:42pm UTC](https://help.nextcloud.com/t/nc19-sqlstate-42000-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-1000-bytes-while-executing-create-index-properties-path-index-on-oc-properties-userid-propertypath/89943/3 "2020-08-18T12:42:17Z")
Yes I do have it, full content: ```auto [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd # custom settings for nextcloud innodb_large_prefix=true innodb_file_format=barracuda innodb_file_per_table=1 [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid # # include all files from the config directory # !includedir /etc/my.cnf.d ``` ---
**Author:** ![tuxedo-rb](https://help.nextcloud.com/user_avatar/help.nextcloud.com/tuxedo-rb/32/15189_2.png) [@tuxedo-rb](https://help.nextcloud.com/u/tuxedo-rb)\ **Post date:** [August 18, 2020, 12:51pm UTC](https://help.nextcloud.com/t/nc19-sqlstate-42000-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-1000-bytes-while-executing-create-index-properties-path-index-on-oc-properties-userid-propertypath/89943/4 "2020-08-18T12:51:29Z")
Hm, ok. Somewhat seems to ~~decrease~~ restrict the limit to 1000 bytes on your server. ---
**Author:** ![enrico.tagliavini](https://help.nextcloud.com/letter_avatar/enrico.tagliavini/32/5_5575768a8748004e209b776fc1b2916d.png) [@enrico.tagliavini](https://help.nextcloud.com/u/enrico.tagliavini)\ **Post date:** [August 18, 2020, 1:07pm UTC](https://help.nextcloud.com/t/nc19-sqlstate-42000-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-1000-bytes-while-executing-create-index-properties-path-index-on-oc-properties-userid-propertypath/89943/5 "2020-08-18T13:07:46Z")
Looks like you need MySQL (or MariaDB equivalent) version 5.7 or newer to get more than 1000 bytes. Is there a way to solve this without having to migrate the DB? Thank you. Kind regards. Enrico ---
**Author:** ![enrico.tagliavini](https://help.nextcloud.com/letter_avatar/enrico.tagliavini/32/5_5575768a8748004e209b776fc1b2916d.png) [@enrico.tagliavini](https://help.nextcloud.com/u/enrico.tagliavini)\ **Post date:** [August 18, 2020, 1:25pm UTC](https://help.nextcloud.com/t/nc19-sqlstate-42000-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-1000-bytes-while-executing-create-index-properties-path-index-on-oc-properties-userid-propertypath/89943/6 "2020-08-18T13:25:11Z")
Did a quick test with MariaDB 10.3, nothing changed, still the same error. So it’s not the DB version. Any more clue what it could be? Thank you. Kind regards. ---
**Author:** ![tuxedo-rb](https://help.nextcloud.com/user_avatar/help.nextcloud.com/tuxedo-rb/32/15189_2.png) [@tuxedo-rb](https://help.nextcloud.com/u/tuxedo-rb)\ **Post date:** [August 18, 2020, 1:35pm UTC](https://help.nextcloud.com/t/nc19-sqlstate-42000-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-1000-bytes-while-executing-create-index-properties-path-index-on-oc-properties-userid-propertypath/89943/7 "2020-08-18T13:35:05Z")
This option got removed as of MariaDB v10.3.1 ([https://mariadb.com/kb/en/innodb-system-variables/#innodb\_large\_prefix](https://mariadb.com/kb/en/innodb-system-variables/#innodb_large_prefix)). So remove the entries `innodb_large_prefix=true` and `innodb_file_format=barracuda` and restart your sql-server (like described here: [https://docs.nextcloud.com/server/19/admin\_manual/configuration\_database/mysql\_4byte\_support.html#mariadb-support](https://docs.nextcloud.com/server/19/admin_manual/configuration_database/mysql_4byte_support.html#mariadb-support)). If this doesn’t help, then i’m out of ideas for now. ---
**Author:** ![enrico.tagliavini](https://help.nextcloud.com/letter_avatar/enrico.tagliavini/32/5_5575768a8748004e209b776fc1b2916d.png) [@enrico.tagliavini](https://help.nextcloud.com/u/enrico.tagliavini)\ **Post date:** [August 18, 2020, 1:57pm UTC](https://help.nextcloud.com/t/nc19-sqlstate-42000-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-1000-bytes-while-executing-create-index-properties-path-index-on-oc-properties-userid-propertypath/89943/8 "2020-08-18T13:57:15Z")
Yeah I knew it and changed the my.cnf already to ```auto # cat /etc/opt/rh/rh-mariadb103/my.cnf # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # This group is read by the server # [mysqld] # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # custom setting for Nextcloud innodb_file_per_table=1 # # include all files from the config directory # !includedir /etc/opt/rh/rh-mariadb103/my.cnf.d ``` Also tried to re-run the repair but nothing. If I disable mysql.utf8mb4 in the config everything works fine, I can add the indexes (had to run the repair again to convert the row format back). But if I enable mysql.utf8mb4 then the repair fails to migrate the table again ```auto # sudo -u apache -- scl enable rh-php73 -- php /var/www/nextcloud/occ maintenance:repair --include-expensive Nextcloud is in maintenance mode - no apps have been loaded - Repair MySQL collation - Change row format for oc_properties ... - Change collation for oc_properties ... In AbstractMySQLDriver.php line 106: An exception occurred while executing 'ALTER TABLE `oc_properties` 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 super odd. Thank you for looking into this. I’ll open an issue on Github eventually if I cannot get it fixed from here. Kind regards. Enrico ---
**Author:** ![tuxedo-rb](https://help.nextcloud.com/user_avatar/help.nextcloud.com/tuxedo-rb/32/15189_2.png) [@tuxedo-rb](https://help.nextcloud.com/u/tuxedo-rb)\ **Post date:** [August 18, 2020, 4:27pm UTC](https://help.nextcloud.com/t/nc19-sqlstate-42000-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-1000-bytes-while-executing-create-index-properties-path-index-on-oc-properties-userid-propertypath/89943/9 "2020-08-18T16:27:51Z")
Any chance that your table `oc_properties` got created using MyISAM as storage engine instead of InnoDB? MyISAM seems to have a index length limit of 1000 bytes. ---
**Author:** ![enrico.tagliavini](https://help.nextcloud.com/letter_avatar/enrico.tagliavini/32/5_5575768a8748004e209b776fc1b2916d.png) [@enrico.tagliavini](https://help.nextcloud.com/u/enrico.tagliavini)\ **Post date:** [August 18, 2020, 6:25pm UTC](https://help.nextcloud.com/t/nc19-sqlstate-42000-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-1000-bytes-while-executing-create-index-properties-path-index-on-oc-properties-userid-propertypath/89943/10 "2020-08-18T18:25:37Z")
This is possible, I installed a long time ago, around Owncloud version 4 or 5, maybe even version 3. I upgraded ever since and moved to Nextcloud fairly early on. However I believe I migrated the table to InnoDB. How can I double check? ---
**Author:** ![tuxedo-rb](https://help.nextcloud.com/user_avatar/help.nextcloud.com/tuxedo-rb/32/15189_2.png) [@tuxedo-rb](https://help.nextcloud.com/u/tuxedo-rb)\ **Post date:** [August 18, 2020, 6:50pm UTC](https://help.nextcloud.com/t/nc19-sqlstate-42000-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-1000-bytes-while-executing-create-index-properties-path-index-on-oc-properties-userid-propertypath/89943/11 "2020-08-18T18:50:09Z")
Execute following SQL-query: ```auto SHOW CREATE TABLE oc_properties; ``` ---
**Author:** ![enrico.tagliavini](https://help.nextcloud.com/letter_avatar/enrico.tagliavini/32/5_5575768a8748004e209b776fc1b2916d.png) [@enrico.tagliavini](https://help.nextcloud.com/u/enrico.tagliavini)\ **Post date:** [August 18, 2020, 7:12pm UTC](https://help.nextcloud.com/t/nc19-sqlstate-42000-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-1000-bytes-while-executing-create-index-properties-path-index-on-oc-properties-userid-propertypath/89943/12 "2020-08-18T19:12:45Z")
> [@tuxedo-rb](#): > > ```auto > SHOW CREATE TABLE oc_properties; > > ``` ```auto MariaDB [owncloud]> SHOW CREATE TABLE oc_properties; +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | oc_properties | CREATE TABLE `oc_properties` ( `userid` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '', `propertypath` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `propertyname` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `propertyvalue` longtext COLLATE utf8_bin NOT NULL, `id` bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`), KEY `property_index` (`userid`), KEY `properties_path_index` (`userid`,`propertypath`) ) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPRESSED | +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` it was MyISAM indeed. Darn it, I thought they were all moved to InnoDB… something must have gone wrong. There are a few more tables in MyISAM. I converted them all to InnoDB now. Repair is working now. Thank you very very much, I couldn’t figure it out. ---
**Author:** ![tuxedo-rb](https://help.nextcloud.com/user_avatar/help.nextcloud.com/tuxedo-rb/32/15189_2.png) [@tuxedo-rb](https://help.nextcloud.com/u/tuxedo-rb)\ **Post date:** [August 18, 2020, 7:15pm UTC](https://help.nextcloud.com/t/nc19-sqlstate-42000-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-1000-bytes-while-executing-create-index-properties-path-index-on-oc-properties-userid-propertypath/89943/13 "2020-08-18T19:15:01Z")
Great 🙂 ---
**Author:** ![Mark0Carlson](https://help.nextcloud.com/user_avatar/help.nextcloud.com/mark0carlson/32/64319_2.png) [@Mark0Carlson](https://help.nextcloud.com/u/Mark0Carlson)\ **Post date:** [August 25, 2024, 4:30pm UTC](https://help.nextcloud.com/t/nc19-sqlstate-42000-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-1000-bytes-while-executing-create-index-properties-path-index-on-oc-properties-userid-propertypath/89943/14 "2024-08-25T16:30:07Z")
Yeah it can be a real headache, I tried LongPath Tool Program which helped a lot. ---
**Author:** ![wwe](https://help.nextcloud.com/user_avatar/help.nextcloud.com/wwe/32/72963_2.png) [@wwe](https://help.nextcloud.com/u/wwe)\ **Post date:** [January 16, 2025, 9:20am UTC](https://help.nextcloud.com/t/nc19-sqlstate-42000-syntax-error-or-access-violation-1071-specified-key-was-too-long-max-key-length-is-1000-bytes-while-executing-create-index-properties-path-index-on-oc-properties-userid-propertypath/89943/15 "2025-01-16T09:20:19Z")