[SOLVED] After upgrading to 5.29 messages fail to mark as read

Troubleshooting
1

Summary

After viewing messages in the sidebar, the messages fail to remain marked as read.

Steps to reproduce

Using version 5.29 on any platform (iOS, Android, Electron Apps, Chrome, Safari, Brave etc) view unread messages.

Expected behavior

After viewing messages in the sidebar, the messages should be marked as viewed and no longer show up as Unread.

Observed behavior

After viewing messages in the sidebar, the messages fail to remain marked as read.

POST Response: “Unable to update the last viewed at time.”

From the admin console logs:

{
"level":"error",
"ts":1606594028.5723724,
"caller":"mlog/log.go:229",
"msg":"Unable to update the last viewed at time.",
"path":"/api/v4/channels/members/me/view",
"request_id":"[REMOVED]",
"ip_addr":"[REMOVED]",
"user_id":"[REMOVED]",
"method":"POST",
"err_where":"MarkChannelsAsViewed",
"http_code":500,
"err_details":"failed to fetch threads: Error 1267: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='"
}
2
Update (did not resolve issue)

I found this forum post: Cant see channels which seems to deal with a similar MySQL issue. One user, Ilya, shared the SQL below, which I executed on my mattermost database:

Login to MySQL
mysql -u mmuser -p
MySQL commands
ALTER DATABASE mattermost CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Audits CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Bots CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table ChannelMemberHistory CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table ChannelMembers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Channels CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table ClusterDiscovery CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table CommandWebhooks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Commands CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Compliances CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Emoji CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table FileInfo CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table GroupChannels CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table GroupMembers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table GroupTeams CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table IncomingWebhooks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Jobs CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Licenses CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table LinkMetadata CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table OAuthAccessData CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table OAuthApps CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table OAuthAuthData CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table OutgoingWebhooks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table PluginKeyValueStore CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Preferences CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table PublicChannels CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Reactions CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Roles CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Schemes CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Sessions CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table SidebarCategories CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table SidebarChannels CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Status CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Systems CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table TeamMembers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Teams CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table TermsOfService CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Tokens CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table UserAccessTokens CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table UserGroups CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table UserTermsOfService CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This didn’t resolve the issue.

3
Update (DID resolve issue)

This forum post: Cant see channels does deal with a similar MySQL issue. I used show tables and found that the mattermost database contains 46 tables, while Ilya’s script only had 42 alter table lines. To save anyone else my frustration, the (4) new tables are:

ProductNoticeViewState
ThreadMemberships
Threads
UploadSessions
Login to MySQL
mysql -u mmuser -p
MySQL commands (including 4 new lines for the new tables)
ALTER DATABASE mattermost CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Audits CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Bots CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table ChannelMemberHistory CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table ChannelMembers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Channels CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table ClusterDiscovery CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table CommandWebhooks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Commands CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Compliances CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Emoji CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table FileInfo CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table GroupChannels CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table GroupMembers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table GroupTeams CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table IncomingWebhooks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Jobs CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Licenses CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table LinkMetadata CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table OAuthAccessData CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table OAuthApps CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table OAuthAuthData CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table OutgoingWebhooks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table PluginKeyValueStore CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Preferences CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table ProductNoticeViewState CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table PublicChannels CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Reactions CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Roles CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Schemes CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Sessions CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table SidebarCategories CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table SidebarChannels CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Status CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Systems CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table TeamMembers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Teams CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table TermsOfService CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table ThreadMemberships CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Threads CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Tokens CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table UploadSessions CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table UserAccessTokens CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table UserGroups CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table UserTermsOfService CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
alter table Users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This DID resolve the issue.

3 Likes
4

Just mentioning that I had a similar experience when moving from 5.16 (auto-deployed by Digital Ocean) to 5.36. There are now around 60 tables. Note that for me, it was helpful to issue

use mattermost;

as the first MySQL command; otherwise, all the alter table commands failed.

My symptoms were the sidebar not working right, and errors in the Mattermost log mentioning new channel IR_Incident after Bleve indexing seemed to hang at 95%. I’m not convinced that the latter is fully fixed, but I believe it’s unrelated to the table collation problem now, at least.

5

And one more tip: I found that I was still getting collation errors like this one:

ddr":"xx.xx.xx.xx","user_id":"XYZ","method":"GET","err_where":"GetAllTeamsPage","http_code":500,"err_details":"failed to find Teams: Error 1267: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='"}

I couldn’t tell what table this was associated with. BUT since I was the one who had changed to the utf8mb4_unicode_ci following the steps above, I thought, hey, maybe I should change everything to match that versioned accent-insensitive collation utf8mb4_0900_ai_ci instead of changing everything away from it. So I did that. The commands started with

ALTER DATABASE mattermost CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

use mattermost;

alter table Audits CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

(and then one more alter table for every table that I had seen from show tables; after the use command. So far, I haven’t seen any more of the collation problems. Fingers crossed.