Upgrading from 5.25.0 Mattermost SQL Error Creating Database tables

k0ushik · March 1, 2021, 8:07pm

For feature requests, please see:

For troubleshooting questions, please post in the following format:

Summary

I am trying to restore a backup from Bitnami Team Edition AWS Marketplace version EC2 to another instance. This does not seem to be working. I already have backup taken and uploaded to new instance. I am following Part B of this: Create and restore application backups
This works and I am able to get the new instance working. But after this I want to upgrade from 5.25 like this: 5.25.0 → 5.25.7->v5.26.2 → v5.27.2 ->v5.28.2 ->v5.29.2 ->v5.30.3 → v5.31.1 *Extended Support Release (ESR)->v5.32.1 .

Firstly when I restore backup onto the new instance, I go back to version 5.25.0. After that I am upgrading using this guide: Upgrade Mattermost Team Edition

After upgrading to 5.26 mattermost is not working. I see this error: {“level”:“info”,“ts”:1614628262.9054418,“caller”:“sqlstore/supplier.go:227”,“msg”:“Pinging SQL”,“database”:“master”}
{“level”:“error”,“ts”:1614628262.9204803,“caller”:“sqlstore/supplier.go:174”,“msg”:“Error creating database tables.”,“error”:“Error 1146: Table ‘bitnami_mattermost.sidebarcategories’ doesn’t exist”}

Steps to reproduce

How can we reproduce the issue (what version are you using?) Version 5.25.0 → 5.25.7->5.26.2

Expected behavior

Upgrade should work properly according to guide

Observed behavior

What did you see happen? Please include relevant error messages and/or screenshots.
Mattermost starts when doing ctlscript.sh restart
But then I see the error given above and I cannot open the url. It says 502 nginx error.

Please advise.

ahmaddanial · March 2, 2021, 2:12am

Hello, @k0ushik

The error below indicates that there is an issue with the creating the SidebarCategories table on the database:

{“level”:“error”,“ts”:1614628262.9204803,“caller”:“sqlstore/supplier.go:174”,“msg”:“Error creating database tables.”,“error”:“Error 1146: Table ‘bitnami_mattermost.sidebarcategories’ doesn’t exist”}`

mysql> DESCRIBE SidebarCategories;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| Id          | varchar(26) | NO   | PRI | NULL    |       |
| UserId      | varchar(26) | YES  |     | NULL    |       |
| TeamId      | varchar(26) | YES  |     | NULL    |       |
| SortOrder   | bigint(20)  | YES  |     | NULL    |       |
| Sorting     | varchar(64) | YES  |     | NULL    |       |
| Type        | varchar(64) | YES  |     | NULL    |       |
| DisplayName | varchar(64) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

It could either mean that there is a permissions and ownership issue of MySQL data directory or corrupted / improper table name.

Can you please share the following output and remove the sensitive information such as password and IP address?

cat /opt/bitnami/apps/mattermost/server/config/config.json | grep -A12 "SqlSettings"

I would also like to check the ownership and permission of the database in use. Switch to root access and run ls -lAh /var/lib/mysql | grep "<database_name>". In your case, I believe it should be bitnami_mattermost based on the error we are seeing.

For example, this is how it looks like on my side based on my database table mattermost:

root@mattermost:~# ls -lAh /var/lib/mysql | grep "mattermost"
drwxr-x--- 2 mysql mysql  12K Mar  2 01:51 mattermost

Lastly, run the mysqlcheck to verify the status of the tables. For example:

ahmaddanial@mattermost:~$ mysqlcheck -u <username> -p <database_name>
Enter password:
mattermost.Audits                                  OK
mattermost.Bots                                    OK
mattermost.ChannelMemberHistory                    OK
mattermost.ChannelMembers                          OK
mattermost.Channels                                OK
mattermost.ClusterDiscovery                        OK
mattermost.CommandWebhooks                         OK
mattermost.Commands                                OK
mattermost.Compliances                             OK
mattermost.Emoji                                   OK
mattermost.FileInfo                                OK
mattermost.GroupChannels                           OK
mattermost.GroupMembers                            OK
mattermost.GroupTeams                              OK
mattermost.IncomingWebhooks                        OK
mattermost.Jobs                                    OK
mattermost.Licenses                                OK
mattermost.LinkMetadata                            OK
mattermost.OAuthAccessData                         OK
mattermost.OAuthApps                               OK
mattermost.OAuthAuthData                           OK
mattermost.OutgoingWebhooks                        OK
mattermost.PluginKeyValueStore                     OK
mattermost.Posts                                   OK
mattermost.Preferences                             OK
mattermost.PublicChannels                          OK
mattermost.Reactions                               OK
mattermost.Roles                                   OK
mattermost.Schemes                                 OK
mattermost.Sessions                                OK
mattermost.SidebarCategories                       OK
mattermost.SidebarChannels                         OK
mattermost.Status                                  OK
mattermost.Systems                                 OK
mattermost.TeamMembers                             OK
mattermost.Teams                                   OK
mattermost.TermsOfService                          OK
mattermost.Tokens                                  OK
mattermost.UserAccessTokens                        OK
mattermost.UserGroups                              OK
mattermost.UserTermsOfService                      OK
mattermost.Users                                   OK

Keep me posted. Thanks.

k0ushik · March 2, 2021, 2:40am

k0ushik · March 2, 2021, 2:49am

Just to be clear: These outputs are from the second Mattermost instance. I had to restore the first instance back to working 5.25.0.
After taking a backup and restoring onto second instance (Another Bitnami image which was launched at 5.32.1 on Debian 10) After restoring using the documentation, the second instance also worked fine. I am getting this issue when I upgrade the second instance.

k0ushik · March 2, 2021, 2:53am

Also what do I enter as password for the 3rd check you mentioned?
I tried the bitnami credentials I got when I launched the instance but it doesnt seem to work

ahmaddanial · March 2, 2021, 3:14am

Hi, @k0ushik

The ls -lAh /var/lib/mysql | grep "<database_name>" is to be ran on the server hosting your Mattermost database. I noticed from your configuration that it is on localhost. Can I please confirm where is your MySQL database being stored in that machine? By default, it is stored in /var/lib/mysql which is why I shared to you the command earlier.

For the third check, you should be using the password you used to access your database. If I look at your example, it is e47cb77d6.

k0ushik · March 2, 2021, 4:16am

I dont understand. This is just one instance. So that mattermost database is also on the same server. Do you mean my original instance who’s backup I restored here?

The password you gave doesnt work either:

k0ushik · March 2, 2021, 4:20am

Ok I have located mysql but theres no output for grep bitnami_mattermost or mattermost

ahmaddanial · March 2, 2021, 4:55am

Hi, @k0ushik

That shows that there isn’t any database at all in the directory. How is the database actually being configured in the new instance?

How did you access the MySQL backend in the original instance? What username and password did you use?
What is the configuration like on this new instance? Is it self hosted on a Linux machine or it is still running on another Bitnami deployment as well?
If it is an a Linux machine, is the MySQL database that you are using to restore the information back hosted on the same machine? How do you access it?

When you mentioned this, what I understand here is that you are currently have a working Mattermost Server 5.25.7.

The specific issue that you are facing here is the database issue when you upgraded to 5.26.2. Just to double confirm that you did not miss any steps in the Upgrade Mattermost Team Edition guide.

I went through the upgrade notes and the changelog of Mattermost, did not see any breaking changes that would affect the database when we move from the said versions.

k0ushik · March 2, 2021, 5:34am

Hi, @ahmaddanial

I did not access the MySQL while backing up. I just got application-backup.tar.gz which I then unzipped on new instance. Then I restarted all services using the ctlscript.sh restart command.

I followed this: Upgrade Mattermost Team Edition
to take a backup from my Instance A which is Bitnami Mattermost Team Edition running on Ubuntu 14.04.
Instance B is also a Bitnamo Mattermost Team Edition but this runs on Debian 10. As I didnt see the latest Ubuntu version support while configuring the new instance from AWS Marketplace.

I’m not sure how to access this mysql database. I have never done this before.

ahmaddanial · March 2, 2021, 7:26am

Hello, @k0ushik

I might be repeating myself but you did go through the rest of the steps prior to restarting the server, yes?

sudo cp /opt/bitnami/apps/mattermost/backup/config.json /opt/bitnami/apps/mattermost/server/config/config.json

sudo chown -R root:root /opt/bitnami/apps/mattermost/server
sudo touch /opt/bitnami/apps/mattermost/server/logs/mattermost.log
sudo chown -R bitnami:mattermost /opt/bitnami/apps/mattermost/server/logs
sudo chmod g+w /opt/bitnami/apps/mattermost/server/logs/mattermost.log
sudo chown -R bitnami:mattermost /opt/bitnami/apps/mattermost/server/client/
sudo chmod -R g+w /opt/bitnami/apps/mattermost/server/client/

Just want to make sure that there are no loose ends.

So, it’s on a different distro. I don’t see it as a problem since the error that we are seeing is specific to the database table.

Do you have any database administrator who is helping you with the original Mattermost setup, migration. and upgrade that we can get involve in this discussion?

k0ushik · March 2, 2021, 8:07am

Yes I have followed all steps in the upgrade procedure. All the steps.
I have managed this instance since a long time(no separate database admin). I have never had the need to access the database so I’m not sure if I have any such credentials. I can look in my previous notes if I have saved any such configurations.

ahmaddanial · March 2, 2021, 8:12am

Hi, @k0ushik

Got it. I would like to check the database side of it based on the error you shared. Hence, the database access that I asked. All the steps were followed correctly based on the documentation provided by Bitnami themselves, so there should not be any issues there.

Side note: Can you attempt to upgrade the working 5.25.7 to the latest version and confirm if you are still hitting into the same problem? Just trying to further isolate if this is a Mattermost Server specific or rather something else (like database in discussion now).

k0ushik · March 2, 2021, 4:21pm

Sure @ahmaddanial I will try this again. Full you mean try upgrading directly from 5.25 to 5.32? Or just finish the whole upgrade path on existing instance which is on 5.26??

k0ushik · March 2, 2021, 7:40pm

I am trying to restore backup onto the second instance again. Will update how it went

k0ushik · March 2, 2021, 9:43pm

Hey @ahmaddanial I restored the backup successfully again and got a working 5.25.0
This time I followed the instructions here: Upgrade Mattermost Team Edition
But this time as mentioned in the article, I directly tried mattermost 5.32.1. This also breaks mattermost and I get mattermost could not be started. Also, there is nothing in mattermost.log file. I was getting some issues in nginx(related to new domain resolution) which I think I have resolved. So nginx starts, but mattermost still does not start. Please advise.

ahmaddanial · March 2, 2021, 10:25pm

Hello, @k0ushik

Clearly something is missing in the upgrade documentation because it continues to fail. If there is nothing in the mattermost.log file, we do not have any information to start troubleshooting the issue from Mattermost Server side.

Can you please confirm if your logging in Mattermost is set to the following, specifically the FileLevel set to DEBUG so more information is logged to the mattermost.log?

    "LogSettings": {
        "EnableConsole": true,
        "ConsoleLevel": "DEBUG",
        "ConsoleJson": false,
        "EnableFile": true,
        "FileLevel": "DEBUG",
        "FileJson": true,
        "FileLocation": "",
        "EnableWebhookDebugging": true,
        "EnableDiagnostics": false,
        "EnableSentry": true,
        "AdvancedLoggingConfig": ""
    },

k0ushik · March 2, 2021, 10:32pm

@ahmaddanial These are my settings:

Even after I change FileLevel to DEBUG, the mattermost log file is empty. I just get a 502 from NGINX when I visit the site

ahmaddanial · March 3, 2021, 1:35am

Hello, @k0ushik

Got it. Since there is nothing much from Mattermost, please keep me posted on the database access so we can check on things there.

At the same time, I have reached out to the dev / engineering team as well to gather their thoughts on this issue.

k0ushik · March 3, 2021, 6:37pm

Hi @ahmaddanial I am not seeing any logs on mattermost as the log file is empty. So I dont know if the database issue is resolved. Can the dev /engineering team advise on how to debug this? Thanks!

Topic		Replies	Views
Error on upgrade - failed to create index Troubleshooting	7	832	November 7, 2020
Update from 5.28 to 5.35.2: SQL-ERROR Troubleshooting	14	1917	January 12, 2022
Minimum MySQL version requirements not met Troubleshooting	2	441	January 23, 2023
Upgrade from v7.2.1 to 7.5.1 Fails Troubleshooting	24	738	March 14, 2023
Migrating to Omnibus Installation on Ubuntu 20 from Bitnami AWS EC2 image running on Ubuntu 14.04 Troubleshooting	1	556	March 8, 2021

Upgrading from 5.25.0 Mattermost SQL Error Creating Database tables

Summary

Steps to reproduce

Expected behavior

Observed behavior

Related Topics