I managed to finally solve it.
The error message provided by the Mattermost Server was not only insufficient, but also false.
There was no error inserting anything into the database. The SidebarCategories could not be fetched and sorted. That was the issue.
Whenever you reload the page or try to create a new category in the sidebar, the SELECT statement, which tries to gather all existing categories with their channels or channel IDs, will fail, because the semantics changed between Mattermost Server versions.
To fix it for your Mattermost Server, this is what you have to do:
(Kept the SQL statements very simple, so every user can understand, even without any database knowledge.)
SELECT * FROM SidebarCategories WHERE (SidebarCategories.UserId = 'myuserid' AND SidebarCategories.TeamId = 'myteamid');
You get a result like the following:
id | userid | teamid | sortorder | sorting | type | displayname | muted | collapsed
----------------------------+----------------------------+----------------------------+-----------+---------+-----------------+-----------------+-------+-----------
id........................ | userid.................... | teamid.................... | 0 | | favorites | Favorites | |
id........................ | userid.................... | teamid.................... | 10 | | channels | Channels | |
id........................ | userid.................... | teamid.................... | 20 | recent | direct_messages | Direct Messages | |
First, you need to makre sure the IDs of the official Mattermost categories are upgraded. In earlier versions, the IDs were custom strings, which were randomly generated, just like with custom categories.
I.e., they look like this: sfkjhdgsfkljhdgfkjhdfg
However, newer Mattermost versions generate the ID by the following pattern:
<channel type>_userid_teamid
So, if you are looking at the previous table result:
id | userid | teamid | sortorder | sorting | type | displayname | muted | collapsed
----------------------------+----------------------------+----------------------------+-----------+---------+-----------------+-----------------+-------+-----------
<Favorites category>........ | userid.................... | teamid.................... | 0 | | favorites | Favorites | |
<Channels category>......... | userid.................... | teamid.................... | 10 | | channels | Channels | |
<Direct Messages category>.. | userid.................... | teamid.................... | 20 | recent | direct_messages | Direct Messages | |
You need to update the IDs of each category.
UPDATE SidebarCategories
SET id = '<Favorites category>........'
WHERE id = 'favorites_myuserid_myteamid';
UPDATE SidebarCategories
SET id = '<Channels category>.........'
WHERE id = 'channels_myuserid_myteamid';
UPDATE SidebarCategories
SET id = '<Direct Messages category>..'
WHERE id = 'direct_messages_myuserid_myteamid';
Now, the table should look like this:
id | userid | teamid | sortorder | sorting | type | displayname | muted | collapsed
----------------------------+----------------------------+----------------------------+-----------+---------+-----------------+-----------------+-------+-----------
favorites_myuserid_myteamid | userid.................... | teamid.................... | 0 | | favorites | Favorites | |
channels_myuserid_myteamid | userid.................... | teamid.................... | 10 | | channels | Channels | |
direct_messages_myuserid_myteamid | userid.................... | teamid.................... | 20 | recent | direct_messages | Direct Messages | |
It may also be the case, that the sortorder column has invalid values.
In that case you need (this is mandatory!) make sure, that each row has a unique sortorder value. There must not be any duplicates.
So, let’s say you have sortorder column, where the Favorites category and the Channels category have the same value in the sortorder column, you need to update those values to be distinct.
UPDATE SidebarCategories
SET sortorder = 0
WHERE id = 'favorites_myuserid_myteamid';
UPDATE SidebarCategories
SET sortorder = 10
WHERE id = 'channels_myuserid_myteamid';
Correct:
id | userid | teamid | sortorder | sorting | type | displayname | muted | collapsed
----------------------------+----------------------------+----------------------------+-----------+---------+-----------------+-----------------+-------+-----------
favorites_myuserid_myteamid | userid.................... | teamid.................... | 0 (ok) | | favorites | Favorites | |
channels_myuserid_myteamid | userid.................... | teamid.................... | 10 (ok) | | channels | Channels | |
direct_messages_myuserid_myteamid | userid.................... | teamid.................... | 20 (ok) | recent | direct_messages | Direct Messages | |
Wrong:
id | userid | teamid | sortorder | sorting | type | displayname | muted | collapsed
----------------------------+----------------------------+----------------------------+-----------+---------+-----------------+-----------------+-------+-----------
favorites_myuserid_myteamid | userid.................... | teamid.................... | 0 (ok) | | favorites | Favorites | |
channels_myuserid_myteamid | userid.................... | teamid.................... | 0 (duplicate!) | | channels | Channels | |
direct_messages_myuserid_myteamid | userid.................... | teamid.................... | 20 (ok) | recent | direct_messages | Direct Messages | |
Now, to the last column of this table.
I’m not sure, if adding a sorting value is mandatory, but just let’s add it, to be sure:
UPDATE SidebarCategories
SET sorting = 'manual'
WHERE id = 'channels_myuserid_myteamid';
Set this for every category, except direct_messages, which stays as recent and favorites, which does not need one.
So far, we are done with the SidebarCategories table.
Next is SidebarChannels.
Let’s have a look:
SELECT * FROM SidebarChannels;
Might look something like this:
channelid | userid | categoryid | sortorder
----------------------------+----------------------------+----------------------------------------------------------------+-----------
(0 rows)
It’s empty. We need to add the channels to the channels category. We will add all channels to that category, because once everything works, you can re-order and re-assign in the Web UI, anyway.
Let’s see what channels are available, in the first place:
SELECT * FROM Channels WHERE teamid = 'myteamid';
Could look like this:
id | createat | updateat | deleteat | teamid | type | displayname | name | header | purpose | lastpostat | totalmsgcount | extraupdateat | creatorid | sche
meid | groupconstrained | shared | totalmsgcountroot | lastrootpostat
----------------------------+---------------+---------------+----------+----------------------------+------+-------------+-------------+--------+---------+---------------+---------------+---------------+----------------------------+-----
-----+------------------+--------+-------------------+----------------
myrandomchannelidabc| 123 | 123 | 0 | teamid | O | Town Square | town-square | | | 321 | 0 | 0 | |
| | | 0 | 321
myrandomchannelidefg | 123 | 123 | 0 | teamid | O | Off-Topic | off-topic | | | 321 | 0 | 0 | |
| | | 0 | 321
myrandomchannelidhij | 123 | 123 | 0 | teamid | O | TEST_2 | test_2 | | | 321 | 0 | 0 | userid |
| f | | 0 | 321
(3 rows)
We are only interested in the ChannelId (id) values available. They are enough to identify which channels are available and should be added to the channels category:
SELECT id FROM Channels WHERE teamid = 'myteamid';
May result in:
id
----------------------------
myrandomchannelidabc
myrandomchannelidefg
myrandomchannelidhij
(3 rows)
For each of these id values, you need to add a row into the SidebarChannels table, to assign them to a category:
INSERT INTO SidebarChannels(channelid, userid, categoryid, sortorder)
VALUES ('myrandomchannelidabc', 'userid', 'channels_userid_teamid', 10);
INSERT INTO SidebarChannels(channelid, userid, categoryid, sortorder)
VALUES ('myrandomchannelidefg', 'userid', 'channels_userid_teamid', 20);
INSERT INTO SidebarChannels(channelid, userid, categoryid, sortorder)
VALUES ('myrandomchannelidhij', 'userid', 'channels_userid_teamid', 30);
Not how the sortorder is correct, as explained previously.
You have to apply this process to all members in the Team, because we just used a single userid here. If you have several Teams, you need to apply that to the different Teams, as well.
All this can be probably solved by creating some huge SQL functions. I described the step by step process, the way I got to the point of fixing the issue. So, I did not start with a huge function to begin with, as I did not know what’s wrong, in the first place.
If I did not forget a step, this should be enough to fix the tables. If you reload your Mattermost Web UI (reload the page in the browser), then the Sidebar should properly work, now.
To fix this from a Mattermost product quality perspective, this is what you have to do (in no particular order):
- Improve error messages. Having the error message
Failed to insert record to database for this kind of scenario is not only insufficient, but also false!
- Do not swallow the actual error messages. As seen in the code examples above, there are so many different error messages, while still too short and unhelpful, they would’ve definitely still helped more than the generic
Failed to insert record to database which seemingly is used for any database related error. This is a debugging nightmare.
- Raise all error messages to the
error level. I shouldn’t have to enable the DEBUG log to see these messages.
- Improve error handling. Just raising the error through 10 procedures in the call stack does not solve any issue. This is hard to debug (see my wasted time above, in previous posts) and does not help anyone, really.
- Fix migration. If the migration would’ve worked in the first place, I probably wouldn’t have wasted so many hours in the first place debugging this, if the migration would’ve worked to begin with.
- STOP DEPENDING ON
sortorder!!! Why is sortorder absolutely mandatory? Do not make it mandatory. If some sortorder values are duplicates, then fix them and the user can re-order the channels in the UI. Boom, done. Everything works. Stop making an elephant out of a warning type of situation. Just fix the sortorder for all entries and we are done. No need to break an entire feature just because the sortorder values are not entirely correct.
- Improve documentation. Oh, how it would’ve helped if the code were properly documented. In hundreds of lines of code I have read, I have seen like 3 or 2 short sentences as comments. That’s it.
You need to explain the code in such a project. Especially important in open source projects. How would a majority of people start contributing, if there are no "why"s explained. By having so little documentation, already the vast majority of FOSS contributers are excluded from supporting and improving.