# Error in Mattermost Boards after Upgrade to 7.5.1

**URL:** <https://forum.mattermost.com/t/error-in-mattermost-boards-after-upgrade-to-7-5-1/14614>\
**Category:** Troubleshooting\
**Created:** [December 9, 2022, 8:45am UTC](https://forum.mattermost.com/t/error-in-mattermost-boards-after-upgrade-to-7-5-1/14614 "2022-12-09T08:45:31Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![WayneZ](https://avatars.discourse-cdn.com/v4/letter/w/3bc359/32.png) [@WayneZ](https://forum.mattermost.com/u/WayneZ)\
**Post date:** [December 9, 2022, 8:45am UTC](https://forum.mattermost.com/t/error-in-mattermost-boards-after-upgrade-to-7-5-1/14614/1 "2022-12-09T08:45:31Z")

</div>

**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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![b3lt3r](https://avatars.discourse-cdn.com/v4/letter/b/aeb1de/32.png) [@b3lt3r](https://forum.mattermost.com/u/b3lt3r)\
**Post date:** [December 31, 2022, 9:40am UTC](https://forum.mattermost.com/t/error-in-mattermost-boards-after-upgrade-to-7-5-1/14614/2 "2022-12-31T09:40:22Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![agriesser](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/agriesser/32/5644_2.png) [@agriesser](https://forum.mattermost.com/u/agriesser)\
**Post date:** [December 31, 2022, 3:30pm UTC](https://forum.mattermost.com/t/error-in-mattermost-boards-after-upgrade-to-7-5-1/14614/3 "2022-12-31T15:30:25Z")

</div>

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:

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

```

> [@b3lt3r](#):
>
> I _think_ boards now require postgresql as the db back-end.

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

---

<div class="post-metadata">

**Author:** ![WayneZ](https://avatars.discourse-cdn.com/v4/letter/w/3bc359/32.png) [@WayneZ](https://forum.mattermost.com/u/WayneZ)\
**Post date:** [January 9, 2023, 10:49am UTC](https://forum.mattermost.com/t/error-in-mattermost-boards-after-upgrade-to-7-5-1/14614/4 "2023-01-09T10:49:24Z")

</div>

> [@agriesser](#):
>
> uninstall

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

```JSON
{"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%”;**

```auto
+------------------------------------+
| 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;**

```auto
+---------+-------------------------+
| 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 |
+---------+-------------------------+

```

---

<div class="post-metadata">

**Author:** ![agriesser](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/agriesser/32/5644_2.png) [@agriesser](https://forum.mattermost.com/u/agriesser)\
**Post date:** [January 23, 2023, 4:55am UTC](https://forum.mattermost.com/t/error-in-mattermost-boards-after-upgrade-to-7-5-1/14614/5 "2023-01-23T04:55:11Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![WayneZ](https://avatars.discourse-cdn.com/v4/letter/w/3bc359/32.png) [@WayneZ](https://forum.mattermost.com/u/WayneZ)\
**Post date:** [January 23, 2023, 4:03pm UTC](https://forum.mattermost.com/t/error-in-mattermost-boards-after-upgrade-to-7-5-1/14614/6 "2023-01-23T16:03:38Z")

</div>

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"}`

---

<div class="post-metadata">

**Author:** ![agriesser](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/agriesser/32/5644_2.png) [@agriesser](https://forum.mattermost.com/u/agriesser)\
**Post date:** [January 23, 2023, 4:27pm UTC](https://forum.mattermost.com/t/error-in-mattermost-boards-after-upgrade-to-7-5-1/14614/7 "2023-01-23T16:27:06Z")

</div>

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

Please run the following query to work around my stupidness:

```sql
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:

> <https://github.com/mattermost/focalboard/blob/release-7.2/server/services/store/sqlstore/migrations/000018_add_teams_and_boards.up.sql>

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:

```sql
show create table focalboard_blocks;

```

If the column called workspace\_id is not of type VARCHAR(36):

```sql
ALTER TABLE focalboard_blocks CHANGE workspace_id channel_id VARCHAR(36);

```

If there is noj column called `board_id`:

```sql
ALTER TABLE focalboard_blocks ADD COLUMN board_id VARCHAR(36);

```

```sql
show create table focalboard_blocks_history;

```

If the column called workspace\_id is not of type VARCHAR(36):

```sql
ALTER TABLE focalboard_blocks_history CHANGE workspace_id channel_id VARCHAR(36);

```

If there is no column called `board_id`:

```sql
ALTER TABLE focalboard_blocks_history ADD COLUMN board_id VARCHAR(36);

```

Run this command:

```sql
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';

```

```sql
show create table focalboard_boards;

```

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

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

```

```sql
select count(*) from focalboard_boards;

```

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

```sql
  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';

```

```sql
select count(*) from focalboard_boards_history;

```

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

```sql
  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:

```sql
show create table focalboard_board_members;

```

if it does _NOT_ have an index called `idx_boardmembers_user_id`, create it:

```sql
CREATE INDEX idx_boardmembers_user_id ON focalboard_board_members(user_id);

```

```sql
select count(*) from focalboard_board_members;

```

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

```sql
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:

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

```

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

---

<div class="post-metadata">

**Author:** ![WayneZ](https://avatars.discourse-cdn.com/v4/letter/w/3bc359/32.png) [@WayneZ](https://forum.mattermost.com/u/WayneZ)\
**Post date:** [February 1, 2023, 3:47pm UTC](https://forum.mattermost.com/t/error-in-mattermost-boards-after-upgrade-to-7-5-1/14614/8 "2023-02-01T15:47:07Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![agriesser](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.mattermost.com/agriesser/32/5644_2.png) [@agriesser](https://forum.mattermost.com/u/agriesser)\
**Post date:** [February 1, 2023, 4:00pm UTC](https://forum.mattermost.com/t/error-in-mattermost-boards-after-upgrade-to-7-5-1/14614/9 "2023-02-01T16:00:18Z")

</div>

Awesome, thanks for your confirmation!