Error on upgrade - failed to create index

(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

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

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

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

@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.

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!

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

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