Mattermost 10.12.1 was on MySQL everything worked ok. Since Mattermost 11.x is psql only we decided to first migrate 10.12.1 to psql and then when everything is ok upgrade to 11.
We finished the migration to psql but a lot of things are not working ok and I see a lot of errors in the psql log.
First two things that are not working
groups, I cannot collapse/expand groups
message reactions
2025-10-27 15:51:42.887 UTC [121157] STATEMENT: WITH cte AS (SELECT
*
FROM
Posts
WHERE
UpdateAt > $1 AND ChannelId = $2
LIMIT 1000)
(SELECT * FROM cte)
UNION
(SELECT * FROM Posts p1 WHERE id in (SELECT rootid FROM cte))
ORDER BY CreateAt DESC
2025-10-27 15:51:46.255 UTC [121318] ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
2025-10-27 15:51:46.255 UTC [121318] STATEMENT: INSERT INTO Status (UserId,Status,Manual,LastActivityAt,DNDEndTime,PrevStatus) VALUES ($1,$2,$3,$4,$5,$6) ON CONFLICT (userid) DO UPDATE SET Status = EXCLUDED.Status, Manual = EXCLUDED.Manual, LastActivityAt = EXCLUDED.LastActivityAt, DNDEndTime = EXCLUDED.DNDEndTime, PrevStatus = EXCLUDED.PrevStatus
2025-10-27 15:51:59.277 UTC [121318] ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
2025-10-27 15:51:59.277 UTC [121318] STATEMENT: INSERT INTO Preferences (UserId,Category,Name,Value) VALUES ($1,$2,$3,$4) ON CONFLICT (userid, category, name) DO UPDATE SET Value = $5
2025-10-27 15:51:59.933 UTC [121157] ERROR: could not identify an equality operator for type json at character 135
2025-10-27 15:51:59.933 UTC [121157] STATEMENT: WITH cte AS (SELECT
*
FROM
Posts
WHERE
UpdateAt > $1 AND ChannelId = $2
LIMIT 1000)
(SELECT * FROM cte)
UNION
(SELECT * FROM Posts p1 WHERE id in (SELECT rootid FROM cte))
ORDER BY CreateAt DESC
2025-10-27 15:52:01.025 UTC [121157] ERROR: could not identify an equality operator for type json at character 135
2025-10-27 15:52:01.025 UTC [121157] STATEMENT: WITH cte AS (SELECT
*
FROM
Posts
WHERE
UpdateAt > $1 AND ChannelId = $2
LIMIT 1000)
(SELECT * FROM cte)
UNION
(SELECT * FROM Posts p1 WHERE id in (SELECT rootid FROM cte))
ORDER BY CreateAt DESC
2025-10-27 15:52:15.361 UTC [121157] ERROR: could not identify an equality operator for type json at character 135
2025-10-27 15:52:15.361 UTC [121157] STATEMENT: WITH cte AS (SELECT
Is there some upgrade to database structure that is required as this looks like MM is expecting different DB structure than it is. I see in the about “Database Schema Version: 141” but no idea how to check if the DB schema is really at that point as it was MySQL few hours ago (it was MySQL 141 IIRC). Any way to upgrade this automatically?
2025-10-27 16:10:24.260 UTC [121157] ERROR: could not identify an equality operator for type json at character 135
2025-10-27 16:10:24.260 UTC [121157] STATEMENT: WITH cte AS (SELECT
*
FROM
Posts
WHERE
UpdateAt > $1 AND ChannelId = $2
LIMIT 1000)
(SELECT * FROM cte)
UNION
(SELECT * FROM Posts p1 WHERE id in (SELECT rootid FROM cte))
ORDER BY CreateAt DESC
2025-10-27 16:10:24.262 UTC [121319] ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
2025-10-27 16:10:24.262 UTC [121319] STATEMENT: INSERT INTO Preferences (UserId,Category,Name,Value) VALUES ($1,$2,$3,$4) ON CONFLICT (userid, category, name) DO UPDATE SET Value = $5
2025-10-27 16:10:24.404 UTC [121157] ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
2025-10-27 16:10:24.404 UTC [121157] STATEMENT: INSERT INTO SidebarChannels (ChannelId,UserId,CategoryId,SortOrder) VALUES ($1,$2,$3,$4),($5,$6,$7,$8),($9,$10,$11,$12) ON CONFLICT (ChannelId, UserId, CategoryId) DO UPDATE SET SortOrder = excluded.SortOrder
2025-10-27 16:11:40.074 UTC [121318] ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
2025-10-27 16:11:40.074 UTC [121318] STATEMENT: INSERT INTO Preferences (UserId,Category,Name,Value) VALUES ($1,$2,$3,$4) ON CONFLICT (userid, category, name) DO UPDATE SET Value = $5
2025-10-27 16:11:40.088 UTC [121157] ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
2025-10-27 16:11:40.088 UTC [121157] STATEMENT: INSERT INTO
Reactions
(UserId, PostId, EmojiName, CreateAt, UpdateAt, DeleteAt, RemoteId, ChannelId)
VALUES
($1, $2, $3, $4, $5, $6, $7, $8)
ON CONFLICT (UserId, PostId, EmojiName)
DO UPDATE SET UpdateAt = $9, DeleteAt = $10, RemoteId = $11, ChannelId = $12
2025-10-27 16:11:40.786 UTC [121253] ERROR: could not identify an equality operator for type json at character 135
2025-10-27 16:11:40.786 UTC [121253] STATEMENT: WITH cte AS (SELECT
*
FROM
Posts
WHERE
UpdateAt > $1 AND ChannelId = $2
LIMIT 1000)
(SELECT * FROM cte)
UNION
(SELECT * FROM Posts p1 WHERE id in (SELECT rootid FROM cte))
ORDER BY CreateAt DESC
That is the link I sent to devops team that were doing the migration. This was the outcome, they fixed it finally (and documented the changes), now it all works ok except I’m getting non stop the notification bottom right about something only visible to admins that I just snooze but it comes back
Thanks for getting back to me @velikitviteras I see, this is something we didn’t experience before. We might ask the all .load files for pgloader that devops team have used to understand the scope of the migration. Also I’d like to know how the tables were created in the first place. With that information, we can backtrack to this one. Are these files something that you or @ropew1 can provide?
@ropew1 did the migration, I just tried to solve the db issues that prevented it from working by posting here before @ropew1 finally solved (and documented how) the problem. I don’t know of any .load files and afaik the tables were created using pgloader. It is possible some of the steps from link were skipped or did not finish as expected. I know that in first attempt none of the users were transferred to psql so they retried next day again doing something else. I think originally pgloader had issues connecting to mysql 9 or something like that ..
I should note that in some configurations and circumstances, postgresql will silently fail and not display errors, so that may likely be the culprit here. I had to deal with this in migrating my own database from one server to another, though I’d consider it a psql problem more than mattermost specifically.
One of the reasons I prefer mariaDB/mysql over it, but we work with the tools we’ve got.
I don’t mind using either (well after latest changes in Oracle, not any more, but that is another story) but I have to say that logs in psql were pretty useful in figuring out what’s going on. Much more useful than log mattermost created.
Yeah, MariaDB has been the goto rather than mysql for a while for a reason, but Mattermost’s own logs have kind of a common problem where the signal-to-noise ratio is not very favourable, in my experience. It’s very easy to miss things.
I do not like Maria, I worked in MySQL AB when Monty was main character and he is not in favor of QA but expects community to do the testing, very very very very bad releases happened while he was in charge. Also all the FUD he used to spread was not beneficial to anyone, neither what he did after Sun did stuff he did not like, not only for DB bur for whole open source community…. ‘Till “yesterday” most of the development in MySQL was done by Oracle, if you check out the code additions and changes the contribution of Percona and Maria are really minor compared to what Oracle conttributed, so if, and everything looks like it will as they are turning to the cloud/heatwave/ai way, Oracle’s contributions slow down, I doubt there will be any real improvements in that sphere in future, just maintenance and some bugfixes.