V5.39.0 upgrade to v6.3.1

Troubleshooting
1

For feature requests, please see: Contributing Feature Proposals - Mattermost.

For troubleshooting questions, please post in the following format:

Summary
Today i tried to upgrade Mattermost 5.39 to 6.3.1 by first upgrading to Debian 11 to obtain MariaDB 10.5 (which seems to fix database migrations issues) but after the upgrade Channels seem to be lost

Steps to reproduce
Running Mattermost version 5.39 on Debian 9 (MariaDB 10.1)
Dump database to backup
Upgrade to Debian 11 (MariaDB 10.5)
Start Mattermost 6.3.1 to start the database migration

Expected behavior
Working upgrade with no dataloss after the upgrade

Observed behavior
What did you see happen? Please include relevant error messages and/or screenshots.

2

Do you see errors in the Mattermost logs?

3

Yes, i found out these issues and it may be related to MariaDB 1:10.5.12-0+deb11u1 but i already migrated the setup to Debian 11 and MariaDB 10.5.12 but without success. I update to 5.39.3 before upgrading to 6.3.3 but this gives me the following error. After the upgrade the channels and direct messages are lost from the interface

{"timestamp":"2022-02-14 21:03:56.366 +01:00","level":"info","msg":"Pinging SQL","caller":"sqlstore/store.go:262","database":"migrations"}
{"timestamp":"2022-02-14 21:09:12.230 +01:00","level":"warn","msg":"Attempting to upgrade the database schema version","caller":"sqlstore/upgrade.go:251","stored_version":"5.39.0","current_version":"5.39.0","new_version":"6.0.0"}
{"timestamp":"2022-02-14 21:09:12.231 +01:00","level":"warn","msg":"The database schema version has been upgraded","caller":"sqlstore/upgrade.go:232","version":"6.0.0"}
{"timestamp":"2022-02-14 21:09:12.630 +01:00","level":"warn","msg":"Attempting to upgrade the database schema version","caller":"sqlstore/upgrade.go:251","stored_version":"6.0.0","current_version":"6.0.0","new_version":"6.1.0"}
{"timestamp":"2022-02-14 21:09:12.632 +01:00","level":"warn","msg":"The database schema version has been upgraded","caller":"sqlstore/upgrade.go:232","version":"6.1.0"}
{"timestamp":"2022-02-14 21:09:12.632 +01:00","level":"warn","msg":"Attempting to upgrade the database schema version","caller":"sqlstore/upgrade.go:251","stored_version":"6.1.0","current_version":"6.1.0","new_version":"6.2.0"}
{"timestamp":"2022-02-14 21:09:12.633 +01:00","level":"warn","msg":"The database schema version has been upgraded","caller":"sqlstore/upgrade.go:232","version":"6.2.0"}
{"timestamp":"2022-02-14 21:09:12.634 +01:00","level":"warn","msg":"Attempting to upgrade the database schema version","caller":"sqlstore/upgrade.go:251","stored_version":"6.2.0","current_version":"6.2.0","new_version":"6.3.0"}
{"timestamp":"2022-02-14 21:09:12.704 +01:00","level":"warn","msg":"The database schema version has been upgraded","caller":"sqlstore/upgrade.go:232","version":"6.3.0"}
{"timestamp":"2022-02-14 21:09:12.726 +01:00","level":"warn","msg":"Could not create foreign key: Error 1005: Can't create table `mattermost`.`RetentionPoliciesChannels` (errno: 121 \"Duplicate key on write or update\")","caller":"sqlstore/store.go:1153"}
{"timestamp":"2022-02-14 21:09:12.743 +01:00","level":"warn","msg":"Could not create foreign key: Error 1005: Can't create table `mattermost`.`RetentionPoliciesTeams` (errno: 121 \"Duplicate key on write or update\")","caller":"sqlstore/store.go:1153"}
{"timestamp":"2022-02-14 21:09:12.755 +01:00","level":"debug","msg":"Deleting any unused pre-release features","caller":"sqlstore/preference_store.go:39"}

{"timestamp":"2022-02-14 21:56:17.626 +01:00","level":"debug","msg":"Channel listed in the request doesn't belong to the user","caller":"web/context.go:101","path":"/api/v4/users/hc6o45gfxig79enrt6ximmeggh/teams/nwsobwhb9tn79b9rmxrq8a1ddh/channels/categories/favorites_hc6o45gfxig79enrt6ximmeggh_nwsobwhb9tn79b9rmxrq8a1ddh","request_id":"nfqxyfhtwjyd9q8bh11g5o4iea","ip_addr":"xx.xx.xx.x","user_id":"hc6o45gfxig79enrt6ximmeggh","method":"PUT","err_where":"validateSidebarCategory","http_code":400,"err_details":"GetChannelsForUser: Unable to get the channels., failed to get channels with TeamId=nwsobwhb9tn79b9rmxrq8a1ddh and UserId=hc6o45gfxig79enrt6ximmeggh: sql: Scan error on column index 18, name \"LastRootPostAt\": converting NULL to int64 is unsupported"}
4

Today i tried another upgrade, but with the same outcome

 Scan error on column index 18, name \"LastRootPostAt\": converting NULL to int64 is unsupported

And i found this in the changelog

Bug Fixes
Fixed a scan error on column name “LastRootPostAt”: converting NULL to int64.

https://docs.mattermost.com/install/cloud-changelog.html

But not a fix for the selfhosted version

5

To help others, i have just found a ticket where this issues was discussed

MM-39351: fixes LastRootPostAt being null by koox00 · Pull Request #19574 · mattermost/mattermost-server · GitHub and the following fixed my channels

UPDATE Channels SET LastRootPostAt=0 WHERE LastRootPostAt is NULL;