Error on upgrade - failed to create index

Troubleshooting
1

(edited for formatting)

Hello-

I am upgrading from 5.19.1 to 5.25.5. This is an existing mattermost server that’s been upgraded several times before without issue.

After performing the upgrade procedure as per documentation, when I attempt to start mattermost, it fails with this error-

Oct 19 17:03:55 mattermost mattermost[5981]: {"level":"info","ts":1603145035.0427167,"caller":"utils/i18n.go:83","msg":"Loaded system translations","for locale":"en","from locale":"/opt/mattermost/i18n/en.json"}
Oct 19 17:03:55 mattermost mattermost[5981]: {"level":"info","ts":1603145035.0428789,"caller":"app/server_app_adapters.go:58","msg":"Server is initializing..."}
Oct 19 17:03:55 mattermost mattermost[5981]: {"level":"info","ts":1603145035.0453696,"caller":"sqlstore/supplier.go:227","msg":"Pinging SQL","database":"master"}
Oct 19 17:03:55 mattermost mattermost[5981]: {"level":"warn","ts":1603145035.0580902,"caller":"sqlstore/upgrade.go:200","msg":"Attempting to upgrade the database schema version","current_version":"5.21.0","new_version":"5.22.0"}
Oct 19 17:03:55 mattermost mattermost[5981]: {"level":"error","ts":1603145035.0597358,"caller":"sqlstore/supplier.go:915","msg":"Failed to create index","error":"Error 1170: BLOB/TEXT column 'DefaultChannelGuestRole' used in key specification without a key length"}

I have googled the error and haven’t found anything, please let me know if you have any suggestions

Thank you
David

2

This may be an issue related to the character type that is set for the column DefaultChannelGuestRole. I get this from the text that says "DefaultChannelGuestRole used in key specification without a length," which makes me think that the character set is either not set correctly, or not valid. Would you be able to provide a sample for that portion of your database schema to look over?

1 Like
3

Hi, thanks for your help, please see below

mysql> DESCRIBE Schemes;
+-------------------------+--------------+------+-----+---------+-------+
| Field                   | Type         | Null | Key | Default | Extra |
+-------------------------+--------------+------+-----+---------+-------+
| Id                      | varchar(26)  | NO   | PRI | NULL    |       |
| Name                    | varchar(64)  | YES  | UNI | NULL    |       |
| DisplayName             | varchar(128) | YES  |     | NULL    |       |
| Description             | text         | YES  |     | NULL    |       |
| CreateAt                | bigint(20)   | YES  |     | NULL    |       |
| UpdateAt                | bigint(20)   | YES  |     | NULL    |       |
| DeleteAt                | bigint(20)   | YES  |     | NULL    |       |
| Scope                   | varchar(32)  | YES  |     | NULL    |       |
| DefaultTeamAdminRole    | varchar(64)  | YES  |     | NULL    |       |
| DefaultTeamUserRole     | varchar(64)  | YES  |     | NULL    |       |
| DefaultChannelAdminRole | varchar(64)  | YES  |     | NULL    |       |
| DefaultChannelUserRole  | varchar(64)  | YES  |     | NULL    |       |
| DefaultTeamGuestRole    | text         | YES  |     | NULL    |       |
| DefaultChannelGuestRole | text         | YES  |     | NULL    |       |
+-------------------------+--------------+------+-----+---------+-------+
14 rows in set (0.00 sec)


CREATE TABLE `Schemes` (
  `Id` varchar(26) NOT NULL,
  `Name` varchar(64) DEFAULT NULL,
  `DisplayName` varchar(128) DEFAULT NULL,
  `Description` text,
  `CreateAt` bigint(20) DEFAULT NULL,
  `UpdateAt` bigint(20) DEFAULT NULL,
  `DeleteAt` bigint(20) DEFAULT NULL,
  `Scope` varchar(32) DEFAULT NULL,
  `DefaultTeamAdminRole` varchar(64) DEFAULT NULL,
  `DefaultTeamUserRole` varchar(64) DEFAULT NULL,
  `DefaultChannelAdminRole` varchar(64) DEFAULT NULL,
  `DefaultChannelUserRole` varchar(64) DEFAULT NULL,
  `DefaultTeamGuestRole` text,
  `DefaultChannelGuestRole` text,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Name` (`Name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
4

I’ve checked my dev server, and it has DefaultTeamGuestRole and DefaultChannelGuestRole as varchar(64)

I will be performing a backup after hours and changing these two columns from text to varchar(64), then I will re-attempt the upgrade

I will report back, thank you for pointing me in (seemingly) the right direction

5

@dmdmdm

For comparison purposes, this is the output that I am getting on my end:

mysql> DESCRIBE Schemes;
+-------------------------+--------------+------+-----+---------+-------+
| Field                   | Type         | Null | Key | Default | Extra |
+-------------------------+--------------+------+-----+---------+-------+
| Id                      | varchar(26)  | NO   | PRI | NULL    |       |
| Name                    | varchar(64)  | YES  | UNI | NULL    |       |
| DisplayName             | varchar(128) | YES  |     | NULL    |       |
| Description             | text         | YES  |     | NULL    |       |
| CreateAt                | bigint       | YES  |     | NULL    |       |
| UpdateAt                | bigint       | YES  |     | NULL    |       |
| DeleteAt                | bigint       | YES  |     | NULL    |       |
| Scope                   | varchar(32)  | YES  |     | NULL    |       |
| DefaultTeamAdminRole    | varchar(64)  | YES  |     | NULL    |       |
| DefaultTeamUserRole     | varchar(64)  | YES  |     | NULL    |       |
| DefaultChannelAdminRole | varchar(64)  | YES  | MUL | NULL    |       |
| DefaultChannelUserRole  | varchar(64)  | YES  | MUL | NULL    |       |
| DefaultTeamGuestRole    | varchar(64)  | YES  |     | NULL    |       |
| DefaultChannelGuestRole | varchar(64)  | YES  | MUL | NULL    |       |
+-------------------------+--------------+------+-----+---------+-------+
14 rows in set (0.01 sec)

indicating that DefaultTeamGuestRole and DefaultChannelGuestRole are supposed to be created as varchar(64) instead of `text.

Let us know how it goes on your end.

6

I apologize for the delay in response, I’ve been quite busy of late. I do agree, it looks like the columns are incorrectly syntax, I do believe that changing to varchar(64) should work, I’ll be interested to see the result!

7

Hello all

I am writing to confirm that converting DefaultTeamGuestRole and DefaultChannelGuestRole to varchar(64) fixed the issue and let me upgrade without any problems.

Thank you for the assistance

David

2 Likes
8

Glad to be able to help, and I’m glad to hear your instance is up and running again!