Cannot properly set collations

[tareko]

Hello all,

I just upgraded our server to v6.0.2 via Helm. I am using MySQL 8 via Digitalocean. Playbooks does not appear, which seems to have to do with a collation problem. When I look in the logs, I have the following:

{"timestamp":"2021-11-21 14:26:12.049 Z","level":"error","msg":"Unable to activate plugin","caller":"app/plugin.go:146","plugin_id":"playbooks","error":"failed to run migrations: failed to complete migrations: error executing migration from version 0.10.0 to version 0.11.0: failed updating table IR_Incident with Channels' CreateAt and DeleteAt values: Error 1267: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='"}

Collations are set correctly both for the database and for the tables:

| IR_Incident                  | InnoDB |      10 | Dynamic    |      0 |              0 |       16384 |               0 |        65536 |         0 |           NULL | 2021-11-21 15:06:01 | NULL                | NULL       | utf8mb4_0900_ai_ci |     NULL |

mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_0900_ai_ci   |
+--------------------------+----------------------+

After lots of review, I've tried both of these as connection strings: mysql://user:pass@tcp(db.ondigitalocean.com:25060)/database?charset=utf8mb4,utf8&collation=utf8mb4_0900_ai_ci

and (based on this example output: mysql://user:pass@tcp(db.ondigitalocean.com:25060)/database?charset=utf8mb4,utf8\u0026collation=utf8mb4_0900_ai_ci

Despite this, I still get the error above.

Any hints as to what this could be or how to figure out what collation mattermost is using?

Thank you

tarek : )

[tareko]

In the end, I had to run the following command on each table:

alter table Audits CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

For ease for others, here is the complete list:

alter table Audits CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table Bots CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table ChannelMemberHistory CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
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;
alter table ClusterDiscovery CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table CommandWebhooks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table Commands CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table Compliances CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table ConfigurationFiles CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table Configurations CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table Emoji CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table FileInfo CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table GroupChannels CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table GroupMembers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table GroupTeams CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table IR_Incident CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table IR_Playbook CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table IR_PlaybookMember CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table IR_StatusPosts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table IR_System CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table IR_TimelineEvent CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table IncomingWebhooks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table Jobs CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table Licenses CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table LinkMetadata CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table OAuthAccessData CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table OAuthApps CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table OAuthAuthData CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table OutgoingWebhooks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table PluginKeyValueStore CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table Posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table Preferences CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table ProductNoticeViewState CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table PublicChannels CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table Reactions CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table RemoteClusters CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table RetentionPolicies CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table RetentionPoliciesChannels CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table RetentionPoliciesTeams CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table Roles CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table Schemes CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table Sessions CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table SharedChannelAttachments CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table SharedChannelRemotes CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table SharedChannelUsers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table SharedChannels CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table SidebarCategories CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table SidebarChannels CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table Status CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table Systems CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table TeamMembers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table Teams CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table TermsOfService CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table ThreadMemberships CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table Threads CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table Tokens CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table UploadSessions CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table UserAccessTokens CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table UserGroups CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table UserTermsOfService CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table Users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table focalboard_blocks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table focalboard_blocks_history CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table focalboard_schema_migrations CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table focalboard_sessions CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table focalboard_sharing CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table focalboard_system_settings CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table focalboard_users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table focalboard_workspaces CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
alter table schema_migrations CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;