Mattermost-server: Stuck notifications when using multiple teams

[agusl88]

Summary

When using multiple teams, we are getting "stuck notifications" that can't be cleared. Notifications seems to be stuck server-side because it happens with all clients, and the situation is present for many users, not just one.

There seems to be always 1 stuck notification per team, because after reading the regular notifications, the unread count is always 1.

Currently my server is on v5.25.5, but this was happening with the previous LTS version (
5.19).

Steps to reproduce

I'm not 100% sure these steps will reproduce the error, but this is what i've done in each case where a new team ended up with a stuck notification"

1 - Create a new team (it only started to happend after i had more than one team in the server)
2 - Add people to the team
3 - Rename the default "Town square" channel

Expected behavior

No stuck notificaitons

Observed behavior (that appears unintentional)

https://imgur.com/hu9c9O1.

Possible fixes

Haven't found a workaround yet.

[amyblais]

Opened a ticket here: https://mattermost.atlassian.net/browse/MM-30269

[isacikgoz]

Hey @agusl88 thanks for reporting this! However, I am having issues while trying to reproduce this bug. Can you please confirm the reproduction steps? Maybe a specific order triggered this in your case? Also the online/offline member statuses and their notifications settings would help a lot.

[agusl88]

Hi, let me try to reproduce it again so i can provide a more accurate steps. For what is worth, it never happended instantly after creating the team and renaming the channel, notifcations always endedup getting stuck after a few hours/days when people started to use the new team, so is very likely that there is something more involved besides just renaming the default channel.

[agusl88]

I manage to reproduce the problem again.

1 - Create a secondary team
2 - Added one other person to it
3 - At this point we started sending messages to the channel, doing @ mentions, sending direct messages, switching back and forth in the UI beetwen teams, and renaming the channel several times.

After a few tries, the other user i added in step 2 got a stuck notification, i didn't. Sorry i can't be more specific but the problem seems to be related to notifications about @mentions and renaming the default channel.

Screenshoots: https://imgur.com/4NmSouq

[isacikgoz]

Thanks for helping with this @agusl88! It is much appreciated!! However, I'm still unable to reproduce this on v.5.25.5 and also on v.19.3. I will ask for another colleague of mine to see if they can observe this.

[thystra]

I have Mattermost MYSQL Version 5.29 and now have stuck notifications as well. I have several channels in a team (on a server with multiple teams) that continuously appear as unread on the android client and the windows app (v4.2.1). Reading the channels clears the indication, but after a bit it comes back and shows as unread (bold) in the client. No new messages were posted to the channels in the intervening time.

Closing the app completely and reopening it causes the session to be expired and the channels remain unread.

[discostur]

having the same issue like @thystra - just upgraded mattermost from 5.27.0 to 5.29.1. The issue did not appear in the old version;

client: 4.6.1mac osx

[discostur]

@isacikgoz @amyblais in the mattermost log i can see the following error:

{"level":"error","ts":1607113861.4809077,"caller":"mlog/log.go:229","msg":"Unable to update the last viewed at time.","path":"/api/v4/channels/members/me/view","request_id":"qgfyuk6k778ytd3xknz3ehde1y","ip_addr":"192.168.xxx","user_id":"1aukw8gw3fbzinbz88erkio7ey","method":"POST","err_where":"MarkChannelsAsViewed","http_code":500,"err_details":"failed to fetch threads: Error 1267: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='"}

[MrOffline77]

same problem here.

[discostur]

@isacikgoz @amyblais i also the the following error:

{"level":"warn","ts":1607110575.0874128,"caller":"app/notification.go:272","msg":"Failed to update mention count","post_id":"u8a4b4xfbfyitm1ge6fecf1dmw","channel_id":"f9yqirfsa3ba9b8treqzp5dtga","error":"SendNotifications: Unable to increment the mention count., failed to fetch threads: Error 1267: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='"}

MySQL is 8.0.16

[amyblais]

Thanks, updated the ticket with the errors.

[discostur]

@amyblais @isacikgoz it seems to be a problem with the database collations; i just check my default database collation:

mysql> show variables like "collation_database";
+--------------------+--------------------+
| Variable_name      | Value              |
+--------------------+--------------------+
| collation_database | utf8mb4_0900_ai_ci |
+--------------------+--------------------+

and i checked my mattermost tables:

mysql> SELECT table_name, table_collation FROM information_schema.tables WHERE table_schema = 'mattermost';
+------------------------+--------------------+
| TABLE_NAME             | TABLE_COLLATION    |
+------------------------+--------------------+
| Audits                 | utf8mb4_general_ci |
| Bots                   | utf8mb4_general_ci |
| ChannelMemberHistory   | utf8mb4_general_ci |
| ChannelMembers         | utf8mb4_general_ci |
| Channels               | utf8mb4_general_ci |
| ClusterDiscovery       | utf8mb4_general_ci |
| CommandWebhooks        | utf8mb4_general_ci |
| Commands               | utf8mb4_general_ci |
| Compliances            | utf8mb4_general_ci |
| Emoji                  | utf8mb4_general_ci |
| FileInfo               | utf8mb4_general_ci |
| GroupChannels          | utf8mb4_general_ci |
| GroupMembers           | utf8mb4_general_ci |
| GroupTeams             | utf8mb4_general_ci |
| IncomingWebhooks       | utf8mb4_general_ci |
| Jobs                   | utf8mb4_general_ci |
| Licenses               | utf8mb4_general_ci |
| LinkMetadata           | utf8mb4_general_ci |
| OAuthAccessData        | utf8mb4_general_ci |
| OAuthApps              | utf8mb4_general_ci |
| OAuthAuthData          | utf8mb4_general_ci |
| OutgoingWebhooks       | utf8mb4_general_ci |
| PluginKeyValueStore    | utf8mb4_general_ci |
| Posts                  | utf8mb4_general_ci |
| Preferences            | utf8mb4_general_ci |
| ProductNoticeViewState | utf8mb4_0900_ai_ci |
| PublicChannels         | utf8mb4_general_ci |
| Reactions              | utf8mb4_general_ci |
| Roles                  | utf8mb4_general_ci |
| Schemes                | utf8mb4_general_ci |
| Sessions               | utf8mb4_general_ci |
| SidebarCategories      | utf8mb4_0900_ai_ci |
| SidebarChannels        | utf8mb4_0900_ai_ci |
| Status                 | utf8mb4_general_ci |
| Systems                | utf8mb4_general_ci |
| TeamMembers            | utf8mb4_general_ci |
| Teams                  | utf8mb4_general_ci |
| TermsOfService         | utf8mb4_general_ci |
| ThreadMemberships      | utf8mb4_0900_ai_ci |
| Threads                | utf8mb4_0900_ai_ci |
| Tokens                 | utf8mb4_general_ci |
| UploadSessions         | utf8mb4_0900_ai_ci |
| UserAccessTokens       | utf8mb4_general_ci |
| UserGroups             | utf8mb4_general_ci |
| UserTermsOfService     | utf8mb4_general_ci |
| Users                  | utf8mb4_general_ci |
+------------------------+--------------------+

Can you tell me which one of the collations is the correlt one? Is it either utf8mb4_general_ci or utf8mb4_0900_ai_ci?
With that information i would be able to apply the changes to the DB and test if it is fixed afterwards ...

Is this maybe a DB migration error? Where did the changes come from?

[isacikgoz]

Thanks for the great insight @discostur! Probably the reason that I couldn't reproduce this was that I was using postgres. After MySQL 8.0.1 the default collation is set to utf8mb4_0900_ai_ci so, I'd suggest to update every table to use default collation depending on your mysql version.

[discostur]

@isacikgoz thanks for your reply. In the meantime i found this thread in your forums:

https://forum.mattermost.org/t/solved-after-upgrading-to-5-29-messages-fail-to-mark-as-read/10816

I altered all the wrong tables from utf8mb4_general_ci to utf8mb4_0900_ai_ci which is the default in mysql 8:

alter table ChannelMembers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table Channels CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
...

Everything is working fine now for me ;)

[isacikgoz]

Thanks a lot @discostur! We should update the developer documentation regarding to this issue. @agusl88 can you also confirm that this fix solves your problems as well?

[thystra]

Updating the database solved my issue as well. Thanks @discostur !

[agusl88]

Hi @isacikgoz

I changed the collation of all tables to utf8mb4_0900_ai_ci as suggested (my database was in the same state as @discostur, database collation set to utf8mb4_0900_ai_ci and tables set to utf8mb4_general_ci) , but the notifications i had stuck are still there. Maybe i need to manually delete/update them in the database? They are several months old, my guess is that there is not way to trigger the unread update from the clients.