Failed to insert record to database

{
   "caller": "web/context.go:101",
   "err_details": "resource: SidebarCategories id: userId=a...",
   "err_where": "GetSidebarCategories",
   "http_code": 404,
   "ip_addr": "1...",
   "level": "debug",
   "method": "GET",
   "msg": "Failed to insert record to database.",
   "path": "/api/v4/users/a.../teams/a.../channels/categories",
   "request_id": "a...",
   "timestamp": "2022-03-12 20:15:32.417 Z",
   "user_id": "a..."
}

I experience pretty much the same symptoms as described here:

https://docs.mattermost.com/upgrade/important-upgrade-notes.html

However, I am using PostgreSQL and the collations have to be correct, because when migrating the database from 9.4 to 14.2, the pg_upgrade check did not succeed, until the locale and the encode settings were correct.

The frustrating part about this situation is that the error is shown in the logs at debug level (first mistake) and even when you turn up the level to DEBUG in the Mattermost system console, then all you get is “Failed to insert record to database.” as the error message (second mistake). I have no idea, what the cause is. It just says, it’s failing. But why? What’s the problem? I need to fix it.

I would appreciate any assistance!

The offending procedure in the source code:

Line 92 & 101 are the culprit. Insufficient error message + posting it at debug level.

The procedure calling the problem inducing procedure:

Line 26.

The procedure releasing the problem:

The procedure called by its wrapper:

Going down to lower levels:

Current state of the database:

SELECT * FROM SidebarCategories;
             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 |       |
SELECT * FROM SidebarChannels;
 channelid | userid | categoryid | sortorder
-----------+--------+------------+-----------
(0 rows)

I tried out every non-modifying SQL statement from all the procedures manually and they all worked, as they should. I did not see any indication, why anything would fail.

I’m very sure, this is where the actual error originates from:

At least, that must be the case, because only this fits the error message from the log. If it were in a different place, the error message from the log would be slightly different.

@hmhealy Could you please assist?

I have set up a new Team on the same server. Everything works.

I have set up a Mattermost Preview server. Everything works.

I have duplicated the table rows to fit into the original Team. The way the rows’ values are filled are precisely the same, as with the working Team and with the working server.

Still, even when everything is exactly the same, it still won’t work for the original Team.

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

  1. 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!
  2. 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.
  3. Raise all error messages to the error level. I shouldn’t have to enable the DEBUG log to see these messages.
  4. 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.
  5. 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.
  6. 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.
  7. 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.

@agnivade @streamer45 @hmhealey @isacikgoz Pinging you for visibility if there are any items here we could open tickets for.

1 Like

@Akito - Thank you for the great feedback and sorry for all the debugging work you had to do! I completely agree about the error swallowing/improve error messages part. I’ll see if I can create some issues for that.

But I am curious to know why did this issue happen in the first place. From what I understood, you upgraded your Postgres server from 9.4 to 14.2 and upgraded the Mattermost version as well? And somehow the migration failed? Do you have any error message from the migration failure?

If there is a bug in the migration somewhere, we definitely need to fix that.

1 Like

I am currently in the process of migrating my server apps from a standard installation to Kubernetes by Helm Chart.

One of those apps was Mattermost Team Edition. Before doing the actual upgrade/migration, I noticed that the database version on the Mattermost instance is 9.4, which is way too old. I would like it to be at the very least on 11, to stay at least a bit up to date.

I looked at the default version in the Helm Chart and saw, that the newest PostgreSQL version was the default, so I decided to migrate the 9.4 initialised database to 14.2. I have experience with that and it usually works well, because PostgreSQL database upgrades go pretty well, once you get the gist of what must be taken care of.

I am still under the impression, that Mattermost would’ve just kept running under PostgreSQL 9.4, if I wouldn’t have upgraded myself. Please, correct me, if I’m wrong.

I assumed, that Mattermost would detect all legacy database structures and automatically migrate them, as this is usually the case with such apps, according to my experience.

From what I know, this should be the case with Mattermost. It has a db_migrations table and it lists changes made, by version number.

I did not see any noticable log messages during the first run, so I dismissed them. I also did not notice anything going wrong, until I realised that the missing channels in the sidebar are actually a bug, rather than me misunderstanding the new version with its new features.

Indeed, it seems like that. The way I fixed it, it was only related to the database tables SidebarCategories and SidebarChannels. If I understood the upgrade notes correctly, then these were added completely anew in 6.x. I currently cannot find the specific line/place where the additiona was mentioned. I read it somewhere in the documentation or some notes from Mattermost, though.

https://docs.mattermost.com/upgrade/important-upgrade-notes.html


If you need any database tables or whatever debug information, you can tell me which to extract. The problem with logs is though, that they are ephemeral by nature. I cannot restore the logs from days ago. However, I watched them closely after finding out, that the sidebar is not working correctly and the only error (at debug level) I ever saw was this Failed to insert record to database error, which I previously mentioned. Anything else were just debug messages, that looked very boring and very 200 OK like, so it seemed like there was nothing else wrong, judging from the logs.

Would be curious to see, what the logs did say during the migration by the server itself, but these logs do not exist, anymore. (Except the server has an extra log, I don’t know of.)

If you really cannot reproduce the same issue, I still have the old 9.4 database backed up.