ERROR 1071 (42000): Specified key was too long

Help / Discuss
1

Hi! In my way to a brand new and fully updated XWiki instance, I found the following error while manually creating indexes as per https://www.xwiki.org/xwiki/bin/view/Documentation/AdminGuide/Installation/InstallationWAR/InstallationMySQL/#HIndexes:

mysql> create index solr_iterate_all_documents on xwikidoc (XWD_WEB, XWD_NAME, XWD_LANGUAGE, XWD_VERSION);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
mysql>

My configuration:

XWiki 13.8
Apache Tomcat/9.0.45
MySQL Server version: 8.0.26 Source distribution
mysql-connector-java-8.0.26.jar
CentOS Linux release 8.4.2105
openjdk version "1.8.0_302"
OpenJDK Runtime Environment (build 1.8.0_302-b08)
OpenJDK 64-Bit Server VM (build 25.302-b08, mixed mode)

I founded only one entry in Jira related with ERROR 1071, but not sure if I can extract some useful information that apply to the issue that arises in my case:

https://jira.xwiki.org/browse/XWIKI-18869?jql=text%20~%20"ERROR%201071%20(42000)%3A%20Specified%20key%20was%20too%20long"

Please, any idea? Thanks!

Ricardo

2

Maybe @Enygma has an idea since he added that line in the doc: https://www.xwiki.org/xwiki/bin/view/Documentation/AdminGuide/Installation/InstallationWAR/InstallationMySQL/?viewer=changes&rev1=68.1&rev2=68.2

1 Like
3

Awesome! Please, @vmassol, how did you find the exact version where the line was introduced by @Enygma? Thanks!

4

I’ve used the source blame feature: https://www.xwiki.org/xwiki/bin/view/Documentation/AdminGuide/Installation/InstallationWAR/InstallationMySQL/?viewer=code&showLastAuthors=true

1 Like
5

Impressive! I’m glad to be back here, learning again!

6

This cannot work in recent versions of XWiki (>= 13.2) where the column XWD_WEB and XWD_NAME size have been increased, since it cause the total size of all those combined columns to be too big for MySQL max index size.

In short, the total size of all the combined column must not be bigger than 768 characters (assuming you use “utf8mb4” encoding, can be a bit bigger with “utf8”).

Fortunately MySQL allow you to reduce the part of the column you want to put in the index (it does not reduce the max size of the column but if the value is bigger it will be slightly slower since several entries will share the same index value). So something like the following should work:

create index solr_iterate_all_documents on xwikidoc (XWD_WEB(500), XWD_NAME(253), XWD_LANGUAGE(5), XWD_VERSION(10));

You should probably change XWD_WEB and XWD_NAME depending if you tend to have big hiearchy with a lot pages inside a page inside a page, etc. (so bigger XWD_WEB) or big page name (XWD_NAME).

1 Like
7

Shouldn’t this be mentioned at https://www.xwiki.org/xwiki/bin/view/Documentation/AdminGuide/Installation/InstallationWAR/InstallationMySQL/#HIndexes: under the index command list?

1 Like
8

It worked! Thanks! I guess the index could be later rebuilt by using different lengths for each column without affecting XWiki functioning, couldn’t it?

9

@tmortagne, you removed some lines creating indexes on activitystream_events. Should we remove those indexes from the database? Thanks!

10

You could if you could. I think that Thomas removed them because we have deprecated and removed the old ActivityStream (replaced by Notifications which is using Solr for the store).

11

Oops! It seems like a Galician answer to me! Many people say that they cannot know when we are going up or going down! :rofl:

I’m installing a brand new 13.9 XWiki instance. Thus, I interpret that I should remove them!

Should those lines about manually creating indexes have some reference to every XWiki release? Perhaps a comment pointing out that they relate to the last stable release?

Thanks!

12

They were not related to the last stable release. This table still exist and can be used for retro compatibility reasons but is deprecated since longer than the current LTS, and we tend to avoid complexify the documentation by adding information related to versions older than the current LTS.

1 Like
13

FTR, I’ve added the missing Solr indexes because of Loading...

Indeed, after the recent column size changes, the index entry was not updated, but Thomas cleared that out now.

Maybe this is yet another reason to handle the indexes our selves (automatically) and not defer them to some easily desynchronized documentation that the admin has to find out, read and apply; i.e. we should maybe prioritize Loading...

1 Like