Error in Mattermost Boards after Upgrade to 7.5.1

Troubleshooting
7

OK, sorry - the focalboard schema migrations are not that intelligent and do not wrap around “if exists” clauses, my fault :frowning:

Please run the following query to work around my stupidness:

INSERT INTO `focalboard_schema_migrations` VALUES (1,'init'),(2,'system_settings_table'),(3,'blocks_rootid'),(4,'auth_table'),(5,'blocks_modifiedby'),(6,'sharing_table'),(7,'workspaces_table'),(8,'teams'),(9,'blocks_history'),(10,'blocks_created_by'),(11,'match_collation'),(12,'match_column_collation'),(13,'millisecond_timestamps'),(14,'add_not_null_constraint'),(15,'blocks_history_no_nulls'),(16,'subscriptions_table'),(17,'add_file_info');

this should restore the previous setting and we then need to manually apply the commands from this file:

Looking at your database schema, you do already have the table focalboard_teams and no focalboard_workspaces table anymore, so line #2 can be skipped.

Please try to run the following commands one after the other and let me know if you encounter a problem with one of them:

show create table focalboard_blocks;

If the column called workspace_id is not of type VARCHAR(36):

ALTER TABLE focalboard_blocks CHANGE workspace_id channel_id VARCHAR(36);

If there is noj column called board_id:

ALTER TABLE focalboard_blocks ADD COLUMN board_id VARCHAR(36);

show create table focalboard_blocks_history;

If the column called workspace_id is not of type VARCHAR(36):

ALTER TABLE focalboard_blocks_history CHANGE workspace_id channel_id VARCHAR(36);

If there is no column called board_id:

ALTER TABLE focalboard_blocks_history ADD COLUMN board_id VARCHAR(36);

Run this command:

UPDATE focalboard_blocks SET fields = JSON_SET(fields, '$.columnCalculations', cast('{}' as json)) WHERE fields->'$.columnCalculations' = cast('[]' as json);

UPDATE focalboard_blocks b
  JOIN (
    SELECT id, fields->'$.columnCalculations' as board_calculations from focalboard_blocks
    WHERE fields -> '$.columnCalculations' <> cast('{}' as json)
  ) AS s on s.id = b.root_id
  SET fields = JSON_SET(fields, '$.columnCalculations', cast(s.board_calculations as json))
  WHERE b.fields->'$.viewType' = 'table'
  AND b.type = 'view';

show create table focalboard_boards;

If there are no indices named idx_board_team_id or idx_board_channel_id, create the missing ones:

CREATE INDEX idx_board_team_id ON {{.prefix}}boards(team_id, is_template);
CREATE INDEX idx_board_channel_id ON {{.prefix}}boards(channel_id);

select count(*) from focalboard_boards;

If the table has NO entries (if it is empty), run the following commands:

  INSERT INTO focalboard_boards (
      SELECT B.id, B.insert_at, C.TeamId, B.channel_id, B.created_by, B.modified_by, C.Type,
                 COALESCE(B.title, ''),
                 COALESCE(JSON_UNQUOTE(JSON_EXTRACT(B.fields,'$.description')), ''),
                 JSON_UNQUOTE(JSON_EXTRACT(B.fields,'$.icon')),
                 COALESCE(B.fields->'$.showDescription', 'false') = 'true',
                 COALESCE(JSON_EXTRACT(B.fields, '$.isTemplate'), 'false') = 'true',
                 COALESCE(B.fields->'$.templateVer', 0),
                 '{}', B.fields->'$.cardProperties', B.create_at,
                 B.update_at, B.delete_at
          FROM focalboard_blocks AS B
          INNER JOIN Channels AS C ON C.Id=B.channel_id
          WHERE B.type='board'
  );

UPDATE focalboard_blocks SET board_id=root_id;
DELETE FROM focalboard_blocks WHERE type = 'board';

select count(*) from focalboard_boards_history;

If the table has NO entries (if it is empty), run the following commands:

  INSERT INTO focalboard_boards_history (
      SELECT B.id, B.insert_at, C.TeamId, B.channel_id, B.created_by, B.modified_by, C.Type,
                 COALESCE(B.title, ''),
                 COALESCE(JSON_UNQUOTE(JSON_EXTRACT(B.fields,'$.description')), ''),
                 JSON_UNQUOTE(JSON_EXTRACT(B.fields,'$.icon')),
                 COALESCE(B.fields->'$.showDescription', 'false') = 'true',
                 COALESCE(JSON_EXTRACT(B.fields, '$.isTemplate'), 'false') = 'true',
                 COALESCE(B.fields->'$.templateVer', 0),
                 '{}', B.fields->'$.cardProperties', B.create_at,
                 B.update_at, B.delete_at
          FROM focalboard_blocks_history AS B
          INNER JOIN Channels AS C ON C.Id=B.channel_id
          WHERE B.type='board'
  );

UPDATE focalboard_blocks_history SET board_id=root_id;
DELETE FROM focalboard_blocks_history WHERE type = 'board';

Now up to the next table:

show create table focalboard_board_members;

if it does NOT have an index called idx_boardmembers_user_id, create it:

CREATE INDEX idx_boardmembers_user_id ON focalboard_board_members(user_id);

select count(*) from focalboard_board_members;

If the table has NO entries (if it is empty), run the following commands:

INSERT INTO focalboard_board_members (
    SELECT B.Id, CM.UserId, CM.Roles, TRUE, TRUE, FALSE, FALSE
    FROM focalboard_boards AS B
    INNER JOIN ChannelMembers as CM ON CM.ChannelId=B.channel_id
    WHERE CM.SchemeAdmin=True OR (CM.UserId=B.created_by)
);

And if this all worked out, tell the system that we managed to jump over migration ID 18:

INSERT INTO focalboard_schema_migrations VALUES (18, 'add_teams_and_boards');

And restart your Mattermost server or try to enable the plugin again.