Mattermost Boards Plugin Failed to start after update

Summary
Hello, while the title is self-explanatory we would also like to elaborate further. We have been self-hosting Mattermost since the Early 5.x release, with the addition of Mattermost Board we were thrilled as we can now have a more centralized way of managing our Kanbans, all integrated in Mattermost. However, due to a wild-ride of mismanagement we have decided to switch back to other platforms for Kanban and Board was shelved for awhile.

At some point during this duration, one of our technician updated Mattermost from 5.x to 7.x completely skipping important updates. As a result of incomplete or faulty SQL table migration, Boards no longer works. As off today, we have decided to revive Boards and to salvage some of the old data left on there.

Steps to reproduce

Database: MySQL

Timeline of our updates:

2022-08-23: First update attempt: 6.4.2 → 7.2 (skipping important updates)

2022-11-16: Update attempt 6.4.2 → 6.7 → 7.0 ->7.2 Boards was still not working

2022-12-06: No update was attempted however, MM logs showed this after some back tracking.

{“timestamp”:“2022-12-01 06:04:28.982 +07:00”,“level”:“error”,“msg”:“Table creation / migration failed”,“caller”:“app/plugin_api.go:974”,“plugin_id”:“focalboard”,“error”:“"driver: mysql, message: failed when applying migration, command: apply_migration, originalError: Error 1050: Table ‘focalboard_teams’ already exists, query: \n\n\nRENAME TABLE focalboard_workspaces TO focalboard_teams;\nALTER TABLE focalboard_blocks CHANGE workspace_id channel_id VARCHAR(36);\nALTER TABLE focalboard_blocks_history CHANGE workspace_id channel_id VARCHAR(36);\n\nALTER TABLE focalboard_blocks ADD COLUMN board_id VARCHAR(36);\nALTER TABLE focalboard_blocks_history ADD COLUMN board_id VARCHAR(36);\nUPDATE focalboard_blocks SET fields = JSON_SET(fields, ‘$.columnCalculations’, cast(‘{}’ as json)) WHERE fields->‘$.columnCalculations’ = cast(‘’ as json);\n\nUPDATE focalboard_blocks b\n JOIN (\n SELECT id, fields->‘$.columnCalculations’ as board_calculations from focalboard_blocks\n WHERE fields → ‘$.columnCalculations’ <> cast(‘{}’ as json)\n ) AS s on s.id = b.root_id\n SET fields = JSON_SET(fields, ‘$.columnCalculations’, cast(s.board_calculations as json))\n WHERE b.fields->‘$.viewType’ = ‘table’\n AND b.type = ‘view’;\n\n\n\n\n/* TODO: Migrate the columnCalculations at app level and remove it from the boards and boards_history tables */CREATE TABLE IF NOT EXISTS focalboard_boards (\n id VARCHAR(36) NOT NULL PRIMARY KEY,\n\n \n\t\n\tinsert_at DATETIME(6) NOT NULL DEFAULT NOW(6),\n\n team_id VARCHAR(36) NOT NULL,\n channel_id VARCHAR(36),\n created_by VARCHAR(36),\n modified_by VARCHAR(36),\n type VARCHAR(1) NOT NULL,\n title TEXT NOT NULL,\n description TEXT,\n icon VARCHAR(256),\n show_description BOOLEAN,\n is_template BOOLEAN,\n template_version INT DEFAULT 0,\n \n properties JSON,\n card_properties JSON,\n \n \n \n create_at BIGINT,\n update_at BIGINT,\n delete_at BIGINT\n) DEFAULT CHARACTER SET utf8mb4;\n\nCREATE INDEX idx_board_team_id ON focalboard_boards(team_id, is_template);\nCREATE INDEX idx_board_channel_id ON focalboard_boards(channel_id);\n\nCREATE TABLE IF NOT EXISTS focalboard_boards_history (\n id VARCHAR(36) NOT NULL,\n\n \n\t\n\tinsert_at DATETIME(6) NOT NULL DEFAULT NOW(6),\n\n team_id VARCHAR(36) NOT NULL,\n channel_id VARCHAR(36),\n created_by VARCHAR(36),\n modified_by VARCHAR(36),\n type VARCHAR(1) NOT NULL,\n title TEXT NOT NULL,\n description TEXT,\n icon VARCHAR(256),\n show_description BOOLEAN,\n is_template BOOLEAN,\n template_version INT DEFAULT 0,\n \n properties JSON,\n card_properties JSON,\n \n \n \n create_at BIGINT,\n update_at BIGINT,\n delete_at BIGINT,\n\n PRIMARY KEY (id, insert_at)\n) DEFAULT CHARACTER SET utf8mb4;\n \n \n INSERT INTO focalboard_boards (\n SELECT B.id, B.insert_at, C.TeamId, B.channel_id, B.created_by, B.modified_by, C.Type,\n COALESCE(B.title, ‘’),\n COALESCE(JSON_UNQUOTE(JSON_EXTRACT(B.fields,‘$.description’)), ‘’),\n JSON_UNQUOTE(JSON_EXTRACT(B.fields,‘$.icon’)),\n COALESCE(B.fields->‘$.showDescription’, ‘false’) = ‘true’,\n COALESCE(JSON_EXTRACT(B.fields, ‘$.isTemplate’), ‘false’) = ‘true’,\n COALESCE(B.fields->‘$.templateVer’, 0),\n ‘{}’, B.fields->‘$.cardProperties’, B.create_at,\n B.update_at, B.delete_at\n FROM focalboard_blocks AS B\n INNER JOIN Channels AS C ON C.Id=B.channel_id\n WHERE B.type=‘board’\n );\n INSERT INTO focalboard_boards_history (\n SELECT B.id, B.insert_at, C.TeamId, B.channel_id, B.created_by, B.modified_by, C.Type,\n COALESCE(B.title, ‘’),\n COALESCE(JSON_UNQUOTE(JSON_EXTRACT(B.fields,‘$.description’)), ‘’),\n JSON_UNQUOTE(JSON_EXTRACT(B.fields,‘$.icon’)),\n COALESCE(B.fields->‘$.showDescription’, ‘false’) = ‘true’,\n COALESCE(JSON_EXTRACT(B.fields, ‘$.isTemplate’), ‘false’) = ‘true’,\n COALESCE(B.fields->‘$.templateVer’, 0),\n ‘{}’, B.fields->‘$.cardProperties’, B.create_at,\n B.update_at, B.delete_at\n FROM focalboard_blocks_history AS B\n INNER JOIN Channels AS C ON C.Id=B.channel_id\n WHERE B.type=‘board’\n );\n \nUPDATE focalboard_blocks SET board_id=root_id;\nUPDATE focalboard_blocks_history SET board_id=root_id;DELETE FROM focalboard_blocks WHERE type = ‘board’;\nDELETE FROM focalboard_blocks_history WHERE type = ‘board’;CREATE TABLE IF NOT EXISTS focalboard_board_members (\n board_id VARCHAR(36) NOT NULL,\n user_id VARCHAR(36) NOT NULL,\n roles VARCHAR(64),\n scheme_admin BOOLEAN,\n scheme_editor BOOLEAN,\n scheme_commenter BOOLEAN,\n scheme_viewer BOOLEAN,\n PRIMARY KEY (board_id, user_id)\n) DEFAULT CHARACTER SET utf8mb4;\n\nCREATE INDEX idx_boardmembers_user_id ON focalboard_board_members(user_id);\nINSERT INTO focalboard_board_members (\n SELECT B.Id, CM.UserId, CM.Roles, TRUE, TRUE, FALSE, FALSE\n FROM focalboard_boards AS B\n INNER JOIN ChannelMembers as CM ON CM.ChannelId=B.channel_id\n WHERE CM.SchemeAdmin=True OR (CM.UserId=B.created_by)\n);\n\n\n\n\n"”}

2023-05-01: We tried to take a peak at the database following this guide: Error in Mattermost Boards after Upgrade to 7.5.1 (shout out to agriesser) and found out that 2 tables are missing focalboard_boards", “focalboard_boards_member” and “focalboard_boards_history”.

±-----------------------------------+
| Tables_in_mattermost (focalboard%) |
±-----------------------------------+
| focalboard_blocks |
| focalboard_blocks_history |
| focalboard_file_info |
| focalboard_notification_hints |
| focalboard_schema_migrations |
| focalboard_sessions |
| focalboard_sharing |
| focalboard_subscriptions |
| focalboard_system_settings |
| focalboard_teams |
| focalboard_users |
±-----------------------------------+

Expected behavior
Mattermost Boards working again.

Observed behavior
Mattermost Boards failed to start and only return errors.

I can also provide more details if required. Thanks in advance.

Hi @vdlam-interlink and welcome to the Mattermost forums!

I’m afraid you will have to go through the troubleshooting steps in the topic you linked yourself, since the error looks related. What’s the current max value in your focalboard_schema_migrations table?

Thanks for the reply agriesser!

After some careful digging, combined with Mattermost well documented user guides, github repo and support forum. I was finally been able to get Boards working again!

Thanks for the help!.