I’ve been hosting a Mattermost instance for a few years. It’s updated to version 5.22.1.
When I try to create a new channel, I get this message in red: Unable to upsert materialized public channel.
In the log I see this:
{"level":"error","ts":1587712158.347758,"caller":"mlog/log.go:175","msg":"Unable to upsert materialized public channel.","path":"/api/v4/channels","request_id":"~~~~~~~~~~~~~~~~~","ip_addr":"82.~~.~~.~~","user_id":"~~~~~~~~","method":"POST","err_where":"SqlChannelStore.Save","http_code":500,"err_details":"failed to insert public channel: pq: duplicate key value violates unique constraint \"publicchannels_name_teamid_key\""}
The error message is not very clear. How can I fix this?
@hamoid, did you recently upgrade to v5.22.1? If so, what version were you running prior to encountering this issue? Also, are you using MySQL or Postgres?
The error relates to an insertion attempt into a PublicChannels table – a subset of the Channels table that only contains public channels and which the server uses to optimize various queries. Generally speaking, the upsert should never fail, since it either inserts a new row, or tries to update an existing one. However, we have noticed this error on MySQL when the server is under heavy write conditions, i.e. lots of public channels being created. Does this perchance describe your situation?
The server runs on Postgresql. There’s no MySQL installed.
It’s not an instance with high demand: 188 Active Users, 5 Teams, 27 Channels, 8094 Posts, 7 Daily Active Users, 16 Monthly Active Users.
I updated to v5.22.1 today, but I don’t know since when this is an issue, as we haven’t created new channels in a long time.
I was updating from the immediate previous version. I update for every 1 or 2 releases.
Since you explained what should be in that table, I logged in and saw that the channel we’re trying to create already exists in PublicChannels but not in Channels.
I wonder if the channel existed in the past, was deleted from Channels during some clean up I did last year, and forgotten in PublicChannels.
Can I remove the entry from PublicChannels? Will it be recreated if needed?
The missing indexes /should/ be normal – I believe that code path is triggered during upgrade when they are created if they don’t already exist. I admin I’m not sure where fce_fileid_idx comes from. Is this database used exclusively by Mattermost?
I remain mystified by the original error, however. The upsert code is designed to try to update if the row already exists, and only then will it insert. Does this happen for every public channel you attempt to create? Do you have a master/replica database setup, or just a single database node?
You can rebuild the PublicChannels table from scratch. I always recommend making a backup first!
TRUNCATE PublicChannels;
INSERT INTO PublicChannels
(Id, DeleteAt, TeamId, DisplayName, Name, Header, Purpose)
SELECT
c.Id, c.DeleteAt, c.TeamId, c.DisplayName, c.Name, c.Header, c.Purpose
FROM
Channels c
LEFT JOIN
PublicChannels pc ON (pc.Id = c.Id)
WHERE
c.Type = 'O'
AND pc.Id IS NULL
The database “server” is used for some other stuff, but they have different Postgresql usernames and passwords. Only Mattermost should have access to this specific database and it’s not a replica. Single database.
I just tried and it does not happen for every attempt at creation of channels, only with this specific name which already existed in the PublicChannels.
Thanks for the tip on recreating the database. Since I’m having an orphan entry in PublicChannels that does not exist in Channels I feel more confident deleting that entry as that’s what would happen anyway whene truncating and recreating.
I could also query for any entries in PublicChannels that are not backed by an entry in Channels, which should not happen I guess.