Error in Mattermost Boards after Upgrade to 7.5.1

Troubleshooting
1

Summary
After upgrading to the latest Version 7.5.1 i wasn’t able to open the Mattermost boards.
When i take a look at the “Marcetplace” i see the following error message underneath the Mattermost Boards (7.5.1) entry:

Mattermost Boards(7.5.1)
cannot activate plugin: error initializing the DB: driver: mysql, message: failed when applying migration, command: apply_migration, originalError: Error 1050: Table 'focalboard_teams' already exists, query: RENAME TABLE focalboard_workspaces TO focalboard_teams; ALTER TABLE focalboard_blocks CHANGE workspace_id channel_id VARCHAR(36); ALTER TABLE focalboard_blocks_history CHANGE workspace_id channel_id VARCHAR(36); ALTER TABLE focalboard_blocks ADD COLUMN board_id VARCHAR(36); ALTER TABLE focalboard_blocks_history ADD COLUMN board_id VARCHAR(36); UPDATE focalboard_blocks SET fields = JSON_SET(fields, '$.columnCalculations', JSON_OBJECT()) WHERE JSON_EXTRACT(fields, '$.columnCalculations') = JSON_ARRAY(); UPDATE focalboard_blocks b JOIN ( SELECT id, JSON_EXTRACT(fields, '$.columnCalculations') as board_calculations from focalboard_blocks WHERE JSON_EXTRACT(fields, '$.columnCalculations') <> JSON_OBJECT() ) AS s on s.id = b.root_id SET fields = JSON_SET(fields, '$.columnCalculations', JSON_ARRAY(s.board_calculations)) WHERE JSON_EXTRACT(b.fields, '$.viewType') = 'table' AND b.type = 'view'; /* TODO: Migrate the columnCalculations at app level and remove it from the boards and boards_history tables */CREATE TABLE IF NOT EXISTS focalboard_boards ( id VARCHAR(36) NOT NULL PRIMARY KEY, insert_at DATETIME(6) NOT NULL DEFAULT NOW(6), team_id VARCHAR(36) NOT NULL, channel_id VARCHAR(36), created_by VARCHAR(36), modified_by VARCHAR(36), type VARCHAR(1) NOT NULL, title TEXT NOT NULL, description TEXT, icon VARCHAR(256), show_description BOOLEAN, is_template BOOLEAN, template_version INT DEFAULT 0, properties JSON, card_properties JSON, create_at BIGINT, update_at BIGINT, delete_at BIGINT ) DEFAULT CHARACTER SET utf8mb4; CREATE INDEX idx_board_team_id ON focalboard_boards(team_id, is_template); CREATE INDEX idx_board_channel_id ON focalboard_boards(channel_id); CREATE TABLE IF NOT EXISTS focalboard_boards_history ( id VARCHAR(36) NOT NULL, insert_at DATETIME(6) NOT NULL DEFAULT NOW(6), team_id VARCHAR(36) NOT NULL, channel_id VARCHAR(36), created_by VARCHAR(36), modified_by VARCHAR(36), type VARCHAR(1) NOT NULL, title TEXT NOT NULL, description TEXT, icon VARCHAR(256), show_description BOOLEAN, is_template BOOLEAN, template_version INT DEFAULT 0, properties JSON, card_properties JSON, create_at BIGINT, update_at BIGINT, delete_at BIGINT, PRIMARY KEY (id, insert_at) ) DEFAULT CHARACTER SET utf8mb4; 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(JSON_EXTRACT(B.fields, '$.showDescription'), 'false') = 'true', COALESCE(JSON_EXTRACT(B.fields, '$.isTemplate'), 'false') = 'true', COALESCE(JSON_EXTRACT(B.fields, '$.templateVer'), 0), '{}', JSON_EXTRACT(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' ); 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(JSON_EXTRACT(B.fields, '$.showDescription'), 'false') = 'true', COALESCE(JSON_EXTRACT(B.fields, '$.isTemplate'), 'false') = 'true', COALESCE(JSON_EXTRACT(B.fields, '$.templateVer'), 0), '{}', JSON_EXTRACT(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 SET board_id=root_id; UPDATE focalboard_blocks_history SET board_id=root_id;DELETE FROM focalboard_blocks WHERE type = 'board'; DELETE FROM focalboard_blocks_history WHERE type = 'board';CREATE TABLE IF NOT EXISTS focalboard_board_members ( board_id VARCHAR(36) NOT NULL, user_id VARCHAR(36) NOT NULL, roles VARCHAR(64), scheme_admin BOOLEAN, scheme_editor BOOLEAN, scheme_commenter BOOLEAN, scheme_viewer BOOLEAN, PRIMARY KEY (board_id, user_id) ) DEFAULT CHARACTER SET utf8mb4; CREATE INDEX idx_boardmembers_user_id ON focalboard_board_members(user_id); 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) );

Steps to reproduce

Expected behavior

Observed behavior
I can’t open the boards any more.

2

I think boards now require postgresql as the db back-end. I had to do a migration at my upgrade to 7.4 probably prompted by that.

It was unpleasant but finally managed it with the bulk export/import tool but really annoying that:
a). mariadb was no longer “supported” because I understand that DB - PG is too heavy
b). there is no published and supported DB migration path from mattermost. I found multiple threads and eventually the right one which allowed me to migrate. As I said, it was unpleasant and needlessly difficult.

3

Hi WayneZ,

there’s currently a problem with the upgrade procedure. Can you please try to uninstall and reinstall the plugin? No data will be lost, because a plugin uninstall does not delete the tables in the database (but since you never know, I always recommend running backups before working on your systems). There’s a known issue right now which is fixed by reinstalling the plugin, so let’s hope it also fixes it for you. If it still doesn’t work, then please connect to your database, run the following two queries and post the output then:

show tables like "focalboard%";
select * from focalboard_schema_migrations;

Boards also works with MySQL, but not anymore with MariaDB and to be fair, Mattermost officially never supported MariaDB, it was just luck that it worked up to now. While I do not support that decision, I do understand why it’s been made but the ecosystem around it should be much better (as you said, migration paths, better error messages, compatibility checks before running upgrades, etc.).

1 Like
4

Thanks for your reply.
Sadly, reinstalling the plugin doesn’t work.
Ater the reinstall i get the following error in the server log:

{"timestamp":"2023-01-09 11:44:51.088 +01:00","level":"error","msg":"Unable to activate plugin","caller":"app/plugin.go:183","plugin_id":"focalboard","error":"error initializing the DB: Error 1054: Unknown column 'dirty' in 'field list' in line 0: SELECT version, dirty FROM `focalboard_schema_migrations` LIMIT 1"}

For the 2 SQL-Statements i get the following results:

show tables like “flocalboard%”;

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

select * from focalboard_schema_migrations;

+---------+-------------------------+
| Version | Name                    |
+---------+-------------------------+
|       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           |
+---------+-------------------------+
5

OK, so we’re stuck in some database migration step and I think the only way to fix that is by truncating the focalboard_schema_migrations table now in order for the system to catch up with the migrations again.
There seems to be an issue where the upgraded version sometimes fails to start and then the system silently reverts to the prepackaged version. How did you install the boards plugin after you removed it? Did you upload a new file using the plugin upload method or did you install the new version (which one?) from the Marketplace?

Please let’s try the following steps again:

  • Uninstall the Boards plugin
  • run the query TRUNCATE TABLE focalboard_schema_migrations; in your MySQL database
  • Restart the Mattermost server and make sure it did not try to start, install or enable the boards plugin
  • Install the latest available version of the boards plugin from the Marketplace

Let me know if that helped.

1 Like
6

Hey,
thanks for your reply.

I still can’t start the Plugin.
The following error is shown up @ the server log:
{"timestamp":"2023-01-23 17:01:56.853 +01:00","level":"error","msg":"Unable to activate plugin","caller":"app/plugin.go:183","plugin_id":"focalboard","error":"cannot activate plugin: error initializing the DB: driver: mysql, message: failed when applying migration, command: apply_migration, originalError: Error 1060: Duplicate column name 'root_id', query: \n\nALTER TABLE focalboard_blocks\nADD COLUMN root_id VARCHAR(36);\n\n"}

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.

8

Hey,

thanks for your comprehensive response.

Everything was the mysql - DB. There where only 2 things to do:

  • INSERT INTO focalboard_schema_migrations VALUES (18, 'add_teams_and_boards');
  • Some tables had the wrong collation utf8mb4_0900_ai_ci instead of utf8mb4_unicode_ci
    • I corrected the affected tables by hand.
      After these 2 Steps and a reboot of the Server, the boards work just fine again.

Thank you!

9

Awesome, thanks for your confirmation!

1 Like