I Can’t use Boards and lost DMs when upgrade v6.6.0 to v7.7.0

Troubleshooting
1

Summary
I Can’t use Boards and lost DMs when upgrade v6.6.0 to v7.7.0

Here is my environment.

OS:Linux Ubuntu 20.04
DB: mysql Ver15.1 Distrib 10.3.37-MariaDB,for debian-linux-gnu(x86_64) using readline 5.2
Mattermost Version:6.6.0

Steps to reproduce
First off,I upgrade Mattermost v6.6.0 to v7.7.0 like this.

1:shut down every apps and backup my mattermost data

sudo systemctl stop nginx mysqld mattermost

cp -r /opt/mattermost /backupmattermost

2:Get v7.7.0

wget https://releases.mattermost.com/7.7.0/mattermost-7.7.0-linux-amd64.tar.gz

3:Copy v7.7.0 folder to my mattermost folder

cp -r /tmp/mattermost /opt

4:Put in the backup data to upgraded folder

cp -r /backupmattermost/mattermost/data /opt/mattermost
cp -r /backupmattermost/mattermost/config /opt/mattermost
cp -r /backupmattermost/mattermost/logs /opt/mattermost

5:Authorize mattermost

sudo chown -R mattermost:mattermost /opt/mattermost
sudo chmod -R g+w /opt/mattermost

6:Start apps

sudo systemctl start nginx
sudo systemctl start mysqld
sudo systemctl start mattermost

Expected behavior
I was able to launch Mattermost without any problems.
Board functionality was lost and when I checked the plugin admin screen, I found that
When I checked the plugin admin screen, I got an error message saying that the plugin could not access the database.

I thought that perhaps my use of MariaDB was the cause of this problem. I had heard in a thread that MariaDB is not officially supported.

So this issue is not that important.
I don’t know what is causing the direct messages to disappear.
After upgrading to v7.7.0, the conversations on each channel are still there, but the
Only the direct messages have all disappeared.
Luckily, I restored the backup and went back to v6.6.0, but
Where did I go wrong?

Observed behavior
I know that error messages are essential to solving this problem,
but I forgot to note the contents of the log this time.
And since I am running Mattermost in my office,
I can only verify the logs at certain times of the day,
so please give me a little more time to submit the logs.

And is there any cause that can be predicted from my testimony at this time?

Thank you.

2

Hi Onidaruma and welcome to the Mattermost forums!

The boards problem is related to the use of MariaDB, since the boards version that comes bundled with Mattermost 7.7.0 includes a database schema migration that is not supported on MariaDB, so during plugin start, the plugin will not be able to modify the database accordingly and therefore the database will be left in a dirty state.

Also with regards to your commands, please use the commands described in the official upgrade documentation, since it will also take care of eliminating superfluous files in the application directory that could interfere with newer versions:

https://docs.mattermost.com/upgrade/upgrading-mattermost-server.html

In order to get back to a supported configuration, you would have to install a MySQL server and dump the mattermost database from your current MariaDB setup and import it into a new MySQL database. This will make sure that the database schema migrations work and it could also fix the problem with your direct messages, although I cannot see how this could be related right now, but without the error logs it’s hard to tell.

Do you have the option to clone your Mattermost server and try to run the upgrade on the clone again to check for the error messages before trying it again on the production system?

1 Like
3

Hello agriesser! and Big thanks for the reply.

I see…OK! :smiley:
I’ll try to clone my Mattermost to a debug server.
then I upgrade and obey the official upgrade documentation.
(I was referring to a website written in my own language)

If problems arise or are resolved at that time I will report back to you :)))

4

Hi! I tried to upgrade mattermost on Debug server.
Migrated database from MariaDB to MySQL and confirmed that direct messages are displayed correctly!

but as for the Board function, The following error message is displayed and the function has stopped.

here is the error that I saw.

cannot activate plugin: error initializing the DB: error running categoryID migration: Error 1267: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
5

Hi again,

good to hear that one of the issues has been fixed.
With regards to the error you’re seeing here, it seems as if your current table collation is not compatible to the one the one focalboard expects. I’ve never seen this error, so we need to do some additional test here.

Please send the output of the following command on your existing MariaDB server (the one, that is not yet converted) and the clone server:

show create table focalboard_category_boards;

The resulting scheme after a successful migration should look like this:

CREATE TABLE `focalboard_category_boards` (
  `id` varchar(36) NOT NULL,
  `user_id` varchar(36) NOT NULL,
  `category_id` varchar(36) NOT NULL,
  `board_id` varchar(36) NOT NULL,
  `create_at` bigint DEFAULT NULL,
  `update_at` bigint DEFAULT NULL,
  `delete_at` bigint DEFAULT NULL,
  `sort_order` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_categoryboards_category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

It’s very likely, that your collation is different currently and that might cause the issue and I’d like to find out if this happened during the migration from MariaDB to MySQL, so we also need to see the output of this command on the source server.
How did you migrate the database? Using mysqldump and a restore of this dump afterwards?
Did you create the target MySQL database for mattermost on your own or did you let mattermost create the database for you?

6

OMG… I entered the command that you gave me on MariaDB and got the following error message.

ERROR 1146 (42S02): Table 'mattermost.focalboard_category_boards' doesn't exist

I tried on the server after cloning the data and got the same error.

Curiously, it is a mystery that the board function is working on the source server even though the table data does not exist as indicated by this error.

And I have restored data from MariaDB to MySQL using the following method.

Step1 Dump the data on MariaDB

mysqldump --opt -Q -u my_database_user_id -p my_database_name > /dump_name.sql

Step2 Create a Database on MySQL

create database my_database_name;
create user 'my_database_user_id'@'localhost' identified by '<password>';
grant all privileges on my_database_name.* to 'my_database_user_id'@'localhost';
flush privileges;
quit;

Step3 restore the data on MySQL

mysql -u my_database_user_id -p my_database_name < /dump_name.sql

this is how I restore the database.

7

Hi again,

can you please provide the output of the following queries on the source and destination systems?

show tables like "%board%";
select * from focalboard_schema_migrations ORDER BY Version DESC LIMIT 1;

This is the default list of tables you should see in your installation:

mysql> show tables like "%board%";
+----------------------------------+
| Tables_in_mm770 (%board%)        |
+----------------------------------+
| focalboard_blocks                |
| focalboard_blocks_history        |
| focalboard_board_members         |
| focalboard_board_members_history |
| focalboard_boards                |
| focalboard_boards_history        |
| focalboard_categories            |
| focalboard_category_boards       |
| focalboard_file_info             |
| focalboard_notification_hints    |
| focalboard_preferences           |
| focalboard_schema_migrations     |
| focalboard_sessions              |
| focalboard_sharing               |
| focalboard_subscriptions         |
| focalboard_system_settings       |
| focalboard_teams                 |
| focalboard_users                 |
+----------------------------------+
18 rows in set (0.00 sec)

If you do not get any table as a result of this query, there might have been some local modification to the tables names, in this case I would need to see a list of all the tables ( show tables; ) in your Mattermost database.

Please do also provide the output of show create database <databasename>; on the source and the clone system. Your dump&restore steps indicate that you manually created the database and there could be some discrepancy with the default collation used in the database.

P.S.: the --opt -Q parameters for mysqldump are set by default, so you can safely skip them.

8

I Really Thank you for your help.

here is result! :smiley:

Source Server:

MariaDB [mattermost]> show tables like "%board%";
+--------------------------------+
| Tables_in_mattermost (%board%) |
+--------------------------------+
| focalboard_blocks              |
| focalboard_blocks_history      |
| 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               |
| focalboard_workspaces          |
+--------------------------------+
14 rows in set (0.000 sec)

MariaDB [mattermost]> select * from focalboard_schema_migrations ORDER BY Version DESC LIMIT 1;
+---------+-------+
| version | dirty |
+---------+-------+
|      16 |     0 |
+---------+-------+
1 row in set (0.001 sec)

MariaDB [mattermost]> show create database mattermost;
+------------+---------------------------------------------------------------------------------------------------+
| Database   | Create Database                                                                                   |
+------------+---------------------------------------------------------------------------------------------------+
| mattermost | CREATE DATABASE `mattermost` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ |
+------------+---------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

destination server:

mysql> show tables like "%board%";
+--------------------------------+
| Tables_in_mattermost (%board%) |
+--------------------------------+
| focalboard_blocks              |
| focalboard_blocks_history      |
| focalboard_board_members       |
| focalboard_boards              |
| focalboard_boards_history      |
| focalboard_categories          |
| focalboard_category_boards     |
| focalboard_file_info           |
| focalboard_notification_hints  |
| focalboard_schema_migrations   |
| focalboard_sessions            |
| focalboard_sharing             |
| focalboard_subscriptions       |
| focalboard_system_settings     |
| focalboard_teams               |
| focalboard_users               |
| focalboard_workspaces          |
+--------------------------------+
17 rows in set (0.00 sec)

mysql> select * from focalboard_schema_migrations ORDER BY Version DESC LIMIT 1;
+---------+--------------------------+
| Version | Name                     |
+---------+--------------------------+
|      20 | populate_category_blocks |
+---------+--------------------------+
1 row in set (0.01 sec)

mysql> show create database mattermost;
+------------+--------------------------------------------------------------------------------------------------------------------------------------+
| Database   | Create Database                                                                                                                      |
+------------+--------------------------------------------------------------------------------------------------------------------------------------+
| mattermost | CREATE DATABASE `mattermost` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+------------+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

I thought that restoring a database required a newly created database with the same name, but apparently not.
I am a newbie so I am sorry to give you a hard time.

9

OK, thanks - it seems the last migration that has been applied and where we’re stuck now is the one with ID 20:

As you can see, the table focalboard_category_boards has been created on the destination server so I’m wondering why the query show create table focalboard_category_boards; does not return any result on the destination server. Can you please try that again or verify that you did not put a typo in the query or something like that?

Looking at the migration, I’m almost confident that the index could not be created, but we’ll see after you post the output of show create table focalboard_category_boards;.

10

I apologize…I found the output from destination server.
:smiling_face_with_tear:

mysql> show create table focalboard_category_boards;
+----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| focalboard_category_boards | CREATE TABLE `focalboard_category_boards` (
  `id` varchar(36) NOT NULL,
  `user_id` varchar(36) NOT NULL,
  `category_id` varchar(36) NOT NULL,
  `board_id` varchar(36) NOT NULL,
  `create_at` bigint DEFAULT NULL,
  `update_at` bigint DEFAULT NULL,
  `delete_at` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_category_boards_category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
11

Yep, that’s the problem here. The database has a different collation than the table and I’m not sure where this is coming from.

Can you please drop the database on the destination server again and create a new one with the other collation and run the import again?
These are the commands that should work on your destination server in order to start over with a proper converted MySQL database:

DROP DATABASE my_database_name;
CREATE DATABASE `my_database_name` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE my_database_name
SOURCE /path/to/your/dump.sql

Then try to run the upgrade again.

12

I tried to create a new one and import again with your advice,
and re-upgrade mattermost but this error is still displaying D:

cannot activate plugin: error initializing the DB: error running categoryID migration: Error 1267: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

and I tried this again on destination server

show create table focalboard_category_boards;

then

mysql> show create table focalboard_category_boards;
+----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| focalboard_category_boards | CREATE TABLE `focalboard_category_boards` (
  `id` varchar(36) NOT NULL,
  `user_id` varchar(36) NOT NULL,
  `category_id` varchar(36) NOT NULL,
  `board_id` varchar(36) NOT NULL,
  `create_at` bigint DEFAULT NULL,
  `update_at` bigint DEFAULT NULL,
  `delete_at` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_category_boards_category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

same result… :cold_sweat:

13

Hi again,

can you confirm that your database has the right collation set now?

show create database `my_database`?

It should also have utf8mb4_0900_ai_ci.
One additional thing I can think of maybe is the session collation, so please post the output of the followin query (see example output from my system):

mysql> show variables like "%coll%";
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_0900_ai_ci |
| collation_database            | utf8mb4_0900_ai_ci |
| collation_server              | utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.01 sec)

Also in your config.json, is there anything specific with regards to the collation set in your DataSource string? The default MySQL DataSource string looks like this, does your differ from that (except for the credentials and db names, of course):

"DataSource": "mmuser:mmpass@tcp(localhost:3306)/mattermost?charset=utf8mb4,utf8\u0026writeTimeout=30s",
14

I understand.
Just in case, I list the results of my output on both the source and destination servers.

Source Server:

MariaDB [mattermost]> show create database mattermost;
+------------+---------------------------------------------------------------------------------------------------+
| Database   | Create Database                                                                                   |
+------------+---------------------------------------------------------------------------------------------------+
| mattermost | CREATE DATABASE `mattermost` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ |
+------------+---------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [mattermost]> show variables like "%coll%";
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

hmm…? Apparently, as far as the source server logs are concerned.
default_collation_for_utf8mb4 does not exist in Variable_name and
Value is also listed as utf8mb4_general_ci, not utf8mb4_0900_ai_ci.

Destination Server:

mysql> show create database mattermost;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------+
| Database    | Create Database                                                                                                                       |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------+
| mattermost  | CREATE DATABASE `mattermost` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */  |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> show variables like "%coll%";
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_0900_ai_ci |
| collation_database            | utf8mb4_0900_ai_ci |
| collation_server              | utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)

The destination server is correctly displaying the values you indicated.

Then I checked config.json and found that
There was a slight discrepancy with the default values.
So I fixed that.

Default config.json

"DataSource": "mmuser:mmpass@tcp(localhost:3306)/mattermost?charset=utf8mb4,utf8\u0026writeTimeout=30s",

My config.json(Source Server):

"DataSource": "mmuser:mmpass@tcp(localhost:3306)/mattermost?charset=utfmb4,utf8\u0026readTimeout=30s\u0026writeTimeout=30s",

“charset=utfmb4” I don’t see the number 8 in there :thinking:
and maybe I have to delete “\u0026readTimeout=30s”

So I fixed it like this:

"DataSource": "mmuser:mmpass@tcp(localhost:3306)/mattermost?charset=utf8mb4,utf8\u0026writeTimeout=30s",
15

Hey,

you can leave the timeout values as they were, they don’t make a difference, but what does make a difference is your database collation:

mysql> show create database mattermost;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------+
| Database    | Create Database                                                                                                                       |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------+
| mattermost  | CREATE DATABASE `mattermost` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */  |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

As you can see it has been created with collation utf8mb4_general_ci and the command I sent you to create the database should have worked around that.

CREATE DATABASE `my_database_name` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

This command definitely defines the collation to be used as utf8mb4_0900_ai_ci and not as utf8mb4_general_ai_ci.
Sorry to bother you, but can you verify again that afer you create a new database (choose a random name instead of my_database_name to be sure we start from scratch), this database’s collation which you can then check with show create database yourname; does still show the wrong collation?

We need to have the right collation in the database before we can start with the the import again.
One other thing I could think of is that your import contains the database creation statement with the drop collation and therefore drops our predefined database first and creates a new one with the wrong collation.

To verify that, please post the output of the following commands:

head -30 /path/to/your/dump_name.sql
grep ai_ci /path/to/your/dump_name.sql
16

I am very sorry. I was a bit premature.

CREATE DATABASE `my_database_name` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

Because right after executing the above command, I restored the DUMP data
because it was sending log data to you.

So I send you the log data executed in the following order.
(Hope this order is correct)

1:

mysql> CREATE DATABASE `newmattermost` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

2:

mysql> show create database newmattermost;
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Database      | Create Database                                                                                                                         |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| newmattermost | CREATE DATABASE `newmattermost` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3:

head -30 /path/to/your/todaydump.sql

-- MySQL dump 10.19  Distrib 10.3.37-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: mattermost
-- ------------------------------------------------------
-- Server version 10.3.37-MariaDB-0ubuntu0.20.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `Audits`
--

DROP TABLE IF EXISTS `Audits`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Audits` (
  `Id` varchar(26) NOT NULL,
  `CreateAt` bigint(20) DEFAULT NULL,
  `UserId` varchar(26) DEFAULT NULL,
  `Action` text DEFAULT NULL,
  `ExtraInfo` text DEFAULT NULL,

4:

grep ai_ci /path/to/your/todaydump.sql

The last command did not output any results.

17

OK, good - the database newmattermost has been created with the correct collation.
Please make sure that the database which is being used by the updated mattermost installation on the new server uses a database, which collation is set to utf8mb4_0900_ai_ci - you know how to check that now :slight_smile:

Not sure if you imported it to newmattermost now or to any other database name, but whatever database it is, it should have the correct collation before you try to run the upgrade again.

1 Like
18

Thanks soooo much agriesser! :smiling_face_with_three_hearts:
the utf8mb4_0900_ai_ci problem is finally fixed!

but sadly, I’m stuck at new error.
(maybe this will be a last tribulation)

I got this error.

cannot activate plugin: error initializing the DB: driver: mysql, message: failed to fetch applied migrations, command: select_applied_migrations, originalError: Error 1054: Unknown column 'name' in 'field list', query: SELECT version, name FROM focalboard_schema_migrations

So I remember this thread↓

The problem this person is having is similar to my phenomenon, but
What do you think? :sneezing_face:

and, Thanks again for your kindness and massive help :slight_smile: