Mattermost-server: v5.8.0 upgrade failed: "Error 1071: Specified key was too long; max key length is 767 bytes"

Created on 25 Feb 2019 · 18Comments · Source: mattermost/mattermost-server

Upgrading by instruction manual.

Log show error:
Error 1071: Specified key was too long; max key length is 767 bytes"

Database is MySQL 5.7.

Connection URL:
"mmuser:thepassword@tcp(theserver:3306)/mattermost?charset=utf8mb4,utf8\u0026readTimeout=30s\u0026writeTimeout=30s"

Source

jprusch

Most helpful comment

ALTER TABLE mattermost.XYZ ROW_FORMAT = DYNAMIC

I have this issue as well, similar setup as everyone else.

To confirm, every table should have its row format set to dynamic?

Thanks.

For anyone else hitting this error:

{"level":"error","ts":1551914700.580665,"caller":"sqlstore/supplier.go:807","msg":"Failed to create index Error 1709: Index column size too large. The maximum column size is 767 bytes."}

I ran this to fix things (EL7, MySQL 5.6, Mattermost Team 5.8.0)

First I modified /etc/my.cnf with the following from suggestions here under the [mysqld] section:

innodb_file_format=barracuda
innodb_file_per_table=true
innodb_large_prefix=true

Then I bounced mysqld via systemctl restart mysqld and modified the mattermost DB table row format via the following:

use mattermost;
alter table Audits ROW_FORMAT=dynamic;
alter table ChannelMemberHistory ROW_FORMAT=dynamic;
alter table ChannelMembers ROW_FORMAT=dynamic;
alter table Channels ROW_FORMAT=dynamic;
alter table ClusterDiscovery ROW_FORMAT=dynamic;
alter table CommandWebhooks ROW_FORMAT=dynamic;
alter table Commands ROW_FORMAT=dynamic;
alter table Compliances ROW_FORMAT=dynamic;
alter table Emoji ROW_FORMAT=dynamic;
alter table FileInfo ROW_FORMAT=dynamic;
alter table GroupChannels ROW_FORMAT=dynamic;
alter table GroupMembers ROW_FORMAT=dynamic;
alter table GroupTeams ROW_FORMAT=dynamic;
alter table IncomingWebhooks ROW_FORMAT=dynamic;
alter table Jobs ROW_FORMAT=dynamic;
alter table Licenses ROW_FORMAT=dynamic;
alter table LinkMetadata ROW_FORMAT=dynamic;
alter table OAuthAccessData ROW_FORMAT=dynamic;
alter table OAuthApps ROW_FORMAT=dynamic;
alter table OAuthAuthData ROW_FORMAT=dynamic;
alter table OutgoingWebhooks ROW_FORMAT=dynamic;
alter table PluginKeyValueStore ROW_FORMAT=dynamic;
alter table Posts ROW_FORMAT=dynamic;
alter table Preferences ROW_FORMAT=dynamic;
alter table PublicChannels ROW_FORMAT=dynamic;
alter table Reactions ROW_FORMAT=dynamic;
alter table Roles ROW_FORMAT=dynamic;
alter table Schemes ROW_FORMAT=dynamic;
alter table Sessions ROW_FORMAT=dynamic;
alter table Status ROW_FORMAT=dynamic;
alter table Systems ROW_FORMAT=dynamic;
alter table TeamMembers ROW_FORMAT=dynamic;
alter table Teams ROW_FORMAT=dynamic;
alter table TermsOfService ROW_FORMAT=dynamic;
alter table Tokens ROW_FORMAT=dynamic;
alter table UserAccessTokens ROW_FORMAT=dynamic;
alter table UserGroups ROW_FORMAT=dynamic;
alter table UserTermsOfService ROW_FORMAT=dynamic;
alter table Users ROW_FORMAT=dynamic;

sadsfae on 7 Mar 2019

👍4

All 18 comments

Confirming that you followed all the steps in the upgrade notes: https://docs.mattermost.com/administration/upgrade.html?

Also, confirming that you have taken a look at the Important upgrade notes for scenarios that may apply to you: https://docs.mattermost.com/administration/important-upgrade-notes.html?

If yes, can you help post here the Mattermost logs?

Also, this thread may help: https://forum.mattermost.org/t/update-from-5-3-0-to-5-7-0-doesnt-work/6617.

amyblais on 25 Feb 2019

Hm,
just did it the second time... Just to be sure.

Logs show:
2019-02-25T18:25:20.546+0100 info sqlstore/supplier.go:215 Pinging SQL master database 2019-02-25T18:25:21.739+0100 warn sqlstore/upgrade.go:131 The database schema version of 5.6.0 appears to be out of date 2019-02-25T18:25:21.739+0100 warn sqlstore/upgrade.go:132 Attempting to upgrade the database schema version to 5.7.0 2019-02-25T18:25:21.740+0100 warn sqlstore/upgrade.go:126 The database schema has been upgraded to version 5.7.0 2019-02-25T18:25:21.740+0100 warn sqlstore/upgrade.go:131 The database schema version of 5.7.0 appears to be out of date 2019-02-25T18:25:21.740+0100 warn sqlstore/upgrade.go:132 Attempting to upgrade the database schema version to 5.8.0 2019-02-25T18:25:21.874+0100 warn sqlstore/upgrade.go:126 The database schema has been upgraded to version 5.8.0 2019-02-25T18:25:22.064+0100 error sqlstore/supplier.go:807 Failed to create index Error 1071: Specified key was too long; max key length is 767 bytes 2019-02-25T18:25:33.510+0100 info utils/i18n.go:79 Loaded system translations for 'en' from '/opt/mattermost/i18n/en.json' 2019-02-25T18:25:33.511+0100 info app/server_app_adapters.go:58 Server is initializing... 2019-02-25T18:25:33.513+0100 info sqlstore/supplier.go:215 Pinging SQL master database 2019-02-25T18:25:33.946+0100 error sqlstore/supplier.go:807 Failed to create index Error 1071: Specified key was too long; max key length is 767 bytes
To me it seems that upgrade 5.6.0 -> 5.70 is OK, but 5.7.0 -> 5.8.0 seems to be buggy.

Database is 5.6.40. Got that wrong but shouldn't be a problem.
There is another guy posting the same problem on an old closed ticket:

https://github.com/mattermost/mattermost-server/issues/5079

jprusch on 25 Feb 2019

@cpanato Can you take a look at the log errors above and see if you know what the issue could be?

amyblais on 25 Feb 2019

Hey! I'm the another guy @jprusch referred to. I wasn't sure if this deserves a new issue at first, so here's a copy of my post in the closed issue here, just for reference.

Environment

CentOS 7.6
MySQL 5.6.43
Mattermost v5.8.0
New installation

Error

Running sudo -u mattermost ./bin/mattermost produces the following:

{"level":"error","ts":1551108438.7856157,"caller":"sqlstore/supplier.go:807","msg":"Failed to create index Error 1709: Index column size too large. The maximum column size is 767 bytes."}

Database

Database uses utf8mb4 as charset, although I tried using utf8 as well, but that did not resolve the issue.

| database           | charset | collation          |
+--------------------+---------+--------------------+
| mmatter         | utf8mb4 | utf8mb4_unicode_ci |
+--------------------+---------+--------------------+

Here is the config.json connection string:

"DataSource": "<user>:<password>@tcp(localhost:3306)/mmatter?charset=utf8mb4\u0026readTimeout=30s\u0026writeTimeout=30s",

petarov on 25 Feb 2019

Just a follow up. I have installed v5.6.5 and this works without problems.

There is an interesting blog post about MySQL: Specified key was too long; max key length is 767 bytes, but I don't have any hands on XP with Mattermost's source code to try to make any changes in the tables create process and see if MyISAM as an engine or setting another table charset will fix the issue.

petarov on 27 Feb 2019

The problem is the upgrade of the DB scheme to 5.8.0
The type of the column 'description' is changed from VARCHAR(255) to TEXT on the outgoingwebhooks & incomingwebhooks table. With MySQL 5.6 this leads to some indexes using a key which is too long. The restriction is lifted with MySQL 5.7 but an upgrade now is no option for us.

jprusch on 27 Feb 2019

Similary issuse occured while upgrade mannual. But the logs show {"level":"error","ts":1551329484.870871,"caller":"sqlstore/supplier.go:807","msg":"Failed to create index Error 1709: Index column size too large. The maximum column size is 767 bytes."}

cedarice on 28 Feb 2019

I found a solution:
For mariadb, update your *my.cnf file with following configuration,

innodb_default_row_format=dynamic
innodb_file_format=barracuda
innodb_file_per_table=true
innodb_large_prefix=true

Then, you have to restart mariadb service for updated configuration to take effect.

reference: https://stackoverflow.com/a/52778785

cedarice on 28 Feb 2019

👍2

@cpanato Have you had a chance to take a look if there is an issue here on our side?

amyblais on 1 Mar 2019

I was not aware of this @amyblais
Will look

cpanato on 1 Mar 2019

Hi,
using MySQL 5.6.40 one must make sure that:

innodb_file_format=barracuda
innodb_file_per_table=true
innodb_large_prefix=true

are set (Restart MySQL after change)

AND the row format of each table is DYNAMIC. COMPACT does NOT work.

ALTER TABLE mattermost.XYZ ROW_FORMAT = DYNAMIC

After that, 5.8 starts without problems.

jprusch on 3 Mar 2019

I did a upgrade from 5.6.0 to 5.7.0 to 5.8.0 using an unbuntu 16.04 and mysql mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using EditLine wrapper and i did not find any issues

and following the instructions here: https://docs.mattermost.com/install/install-ubuntu-1604.html

cpanato on 5 Mar 2019

ALTER TABLE mattermost.XYZ ROW_FORMAT = DYNAMIC

I have this issue as well, similar setup as everyone else.

To confirm, every table should have its row format set to dynamic?

Thanks.

sadsfae on 7 Mar 2019

ALTER TABLE mattermost.XYZ ROW_FORMAT = DYNAMIC

I have this issue as well, similar setup as everyone else.

To confirm, every table should have its row format set to dynamic?

Thanks.

For anyone else hitting this error:

{"level":"error","ts":1551914700.580665,"caller":"sqlstore/supplier.go:807","msg":"Failed to create index Error 1709: Index column size too large. The maximum column size is 767 bytes."}

I ran this to fix things (EL7, MySQL 5.6, Mattermost Team 5.8.0)

First I modified /etc/my.cnf with the following from suggestions here under the [mysqld] section:

innodb_file_format=barracuda
innodb_file_per_table=true
innodb_large_prefix=true

Then I bounced mysqld via systemctl restart mysqld and modified the mattermost DB table row format via the following:

use mattermost;
alter table Audits ROW_FORMAT=dynamic;
alter table ChannelMemberHistory ROW_FORMAT=dynamic;
alter table ChannelMembers ROW_FORMAT=dynamic;
alter table Channels ROW_FORMAT=dynamic;
alter table ClusterDiscovery ROW_FORMAT=dynamic;
alter table CommandWebhooks ROW_FORMAT=dynamic;
alter table Commands ROW_FORMAT=dynamic;
alter table Compliances ROW_FORMAT=dynamic;
alter table Emoji ROW_FORMAT=dynamic;
alter table FileInfo ROW_FORMAT=dynamic;
alter table GroupChannels ROW_FORMAT=dynamic;
alter table GroupMembers ROW_FORMAT=dynamic;
alter table GroupTeams ROW_FORMAT=dynamic;
alter table IncomingWebhooks ROW_FORMAT=dynamic;
alter table Jobs ROW_FORMAT=dynamic;
alter table Licenses ROW_FORMAT=dynamic;
alter table LinkMetadata ROW_FORMAT=dynamic;
alter table OAuthAccessData ROW_FORMAT=dynamic;
alter table OAuthApps ROW_FORMAT=dynamic;
alter table OAuthAuthData ROW_FORMAT=dynamic;
alter table OutgoingWebhooks ROW_FORMAT=dynamic;
alter table PluginKeyValueStore ROW_FORMAT=dynamic;
alter table Posts ROW_FORMAT=dynamic;
alter table Preferences ROW_FORMAT=dynamic;
alter table PublicChannels ROW_FORMAT=dynamic;
alter table Reactions ROW_FORMAT=dynamic;
alter table Roles ROW_FORMAT=dynamic;
alter table Schemes ROW_FORMAT=dynamic;
alter table Sessions ROW_FORMAT=dynamic;
alter table Status ROW_FORMAT=dynamic;
alter table Systems ROW_FORMAT=dynamic;
alter table TeamMembers ROW_FORMAT=dynamic;
alter table Teams ROW_FORMAT=dynamic;
alter table TermsOfService ROW_FORMAT=dynamic;
alter table Tokens ROW_FORMAT=dynamic;
alter table UserAccessTokens ROW_FORMAT=dynamic;
alter table UserGroups ROW_FORMAT=dynamic;
alter table UserTermsOfService ROW_FORMAT=dynamic;
alter table Users ROW_FORMAT=dynamic;

sadsfae on 7 Mar 2019

👍4

Thanks for the details @sadsfae. I ~~haven't tested~~ applied the innodb_large_prefix as others have pointed. Reading the MySQL documentation it seems it is enabled by default in MySQL 5.7 and disabled by default in MySQL 5.6, so that explains where this comes from (to me at least).

petarov on 7 Mar 2019

Thanks for the details @sadsfae. I haven't tested applied the innodb_large_prefix as others have pointed. Reading the MySQL documentation it seems it is enabled by default in MySQL 5.7 and disabled by default in MySQL 5.6, so that explains where this comes from (to me at least).

Hey, good to know. For me I also needed to convert the row format to dynamic, even with the mysqld my.cnf changes on 5.6 it still yielded the same error.

sadsfae on 8 Mar 2019

I found a solution:
For mariadb, update your *my.cnf file with following configuration,
innodb_default_row_format=dynamic
innodb_file_format=barracuda
innodb_file_per_table=true
innodb_large_prefix=true
Then, you have to restart mariadb service for updated configuration to take effect.

reference: https://stackoverflow.com/a/52778785

That fixed it for me (Ubuntu 18.04, mariadb 10.1.38-MariaDB-0ubuntu0.18.04.1)

Make sure you put this into the [mariadb] section, otherwise the mysql client will complain about unknown options.

kampfflunder on 12 Mar 2019

I found a solution:
For mariadb, update your *my.cnf file with following configuration,
innodb_default_row_format=dynamic
innodb_file_format=barracuda
innodb_file_per_table=true
innodb_large_prefix=true
Then, you have to restart mariadb service for updated configuration to take effect.

reference: https://stackoverflow.com/a/52778785

this didn't work for me.
MariaDB 10.4
Mattermost 5.22.1
Centos 7.5

update:I also altered all the tables to have dynamic row format. this also didn't fix it for me :(

2nd update: I checked what engine my Mattermost tables were using. MyISAM!! I guess it was a while ago that I set that up. So I converted all my tables to InnoDB and now everything works!