Mattermost-server: Unable to re-open an old group message

[parnic-sks]

Summary

Unable to re-open an old group message after upgrading to Mattermost 5.22.0

Steps to reproduce

This is the first time we've noticed this problem, so unsure if these steps are exactly required.

• Mattermost self-hosted on Ubuntu, connected to a PostgreSQL 12 server.
• Create a group message in Mattermost 5.21.0 with several participants.
• Send some messages.
• Upgrade to Mattermost 5.22.0.
• Close the group DM.
• Attempt to re-open the group DM.

Expected behavior

Group DM opens.

Observed behavior (that appears unintentional)

We are directed to this page:

and the following appears in the log:

{"level":"error","ts":1587065794.3749323,"caller":"mlog/log.go:175","msg":"Unable to save the channel.","path":"/api/v4/channels/group","request_id":"wsyh6egnsf8tmyxecsm54f4a1w","ip_addr":"my_ip","user_id":"u63ytfmwmjfxjnubsthj5pxpnh","method":"POST","err_where":"SqlChannelStore.Save","http_code":500,"err_details":"id=d3srhpxjn3bntqwq7bg51gqjqr, pq: duplicate key value violates unique constraint \"idx_19074_name\""}

Possible fixes

Unknown

[amyblais]

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

[lieut-data]

@parnic-sks: do you recall the first Mattermost version you installed against this database? The index name in question isn't a match for what we currently have in code, but I know early versions were a little sloppier in their schema definitions. The code is supposed to handle this by detecting the existing group channel and just returning it, but it doesn't recognize idx_19074_name.

Though you mentioned as well that this occurred after upgrading to v5.22.0. Can you confirm that re-opening a closed group message in this particular way is something you (or someone else) definitely recall having done successfully in the past? If it's definitely a regression, there's more details here than just what my theory proposes.

[parnic-sks]

@lieut-data: Our original install was a Docker installation with a self-contained MySQL host on v5.20.0, I believe. We then used a script to convert the MySQL data to a separate PostgreSQL and switched to a regular Mattermost build hosted on Ubuntu. It's possible the conversion script from one db to another got something(s) wrong.

I can't say for certain that we've ever successfully reopened an old group DM. We're a pretty small team who just started using Mattermost within the last month or so.

I'm very comfortable poking around with the database itself. If there's a db-side workaround available, I'd be happy to give it a shot.

[lieut-data]

We then used a script to convert the MySQL data to a separate PostgreSQL and switched to a regular Mattermost build hosted on Ubuntu. It's possible the conversion script from one db to another got something(s) wrong.

Ah, yes, this is almost certainly the cause. Our application makes some assumptions about the schema at play, but if you convert between databases, the resulting object names might be different than how we created them. Really, we shouldn't actually have to care about the names at all, but indexes can be a bit tricky in some cases.

If you're able to run a pg_dump --schema-only against your database and share that here, I can provide steps to restore it to a "good" state so that it behaves well with the server. I'll also file a separate ticket improvement to perhaps support this scenario better.

[parnic-sks]

@lieut-data Much appreciated! Here you go: https://pastebin.com/ZUgkzjfH

[parnic-sks]

I installed a fresh copy of Mattermost, compared the two schemas, and was mildly horrified at the results.

Here's the query I ran to fix up the constraints and indexes, which fixed my immediate issue:

ALTER TABLE ONLY mattermost.audits RENAME CONSTRAINT idx_19053_primary to audits_pkey;
ALTER TABLE ONLY mattermost.bots RENAME CONSTRAINT idx_19059_primary to bots_pkey;
ALTER TABLE ONLY mattermost.channelmemberhistory RENAME CONSTRAINT idx_19065_primary to channelmemberhistory_pkey;
ALTER TABLE ONLY mattermost.channelmembers RENAME CONSTRAINT idx_19068_primary to channelmembers_pkey;
ALTER TABLE ONLY mattermost.channels ADD CONSTRAINT channels_name_teamid_key UNIQUE (name, teamid);
ALTER TABLE ONLY mattermost.channels RENAME CONSTRAINT idx_19074_primary to channels_pkey;
ALTER TABLE ONLY mattermost.clusterdiscovery RENAME CONSTRAINT idx_19080_primary to clusterdiscovery_pkey;
ALTER TABLE ONLY mattermost.commands RENAME CONSTRAINT idx_19086_primary to commands_pkey;
ALTER TABLE ONLY mattermost.commandwebhooks RENAME CONSTRAINT idx_19092_primary to commandwebhooks_pkey;
ALTER TABLE ONLY mattermost.compliances RENAME CONSTRAINT idx_19095_primary to compliances_pkey;
ALTER TABLE ONLY mattermost.emoji ADD CONSTRAINT emoji_name_deleteat_key UNIQUE (name, deleteat);
ALTER TABLE ONLY mattermost.emoji RENAME CONSTRAINT idx_19101_primary to emoji_pkey;
ALTER TABLE ONLY mattermost.fileinfo RENAME CONSTRAINT idx_19104_primary to fileinfo_pkey;
ALTER TABLE ONLY mattermost.groupchannels RENAME CONSTRAINT idx_19110_primary to groupchannels_pkey;
ALTER TABLE ONLY mattermost.groupmembers RENAME CONSTRAINT idx_19113_primary to groupmembers_pkey;
ALTER TABLE ONLY mattermost.groupteams RENAME CONSTRAINT idx_19116_primary to groupteams_pkey;
ALTER TABLE ONLY mattermost.incomingwebhooks RENAME CONSTRAINT idx_19119_primary to incomingwebhooks_pkey;
ALTER TABLE ONLY mattermost.jobs RENAME CONSTRAINT idx_19125_primary to jobs_pkey;
ALTER TABLE ONLY mattermost.licenses RENAME CONSTRAINT idx_19131_primary to licenses_pkey;
ALTER TABLE ONLY mattermost.linkmetadata RENAME CONSTRAINT idx_19137_primary to linkmetadata_pkey;
ALTER TABLE ONLY mattermost.oauthaccessdata ADD CONSTRAINT oauthaccessdata_clientid_userid_key UNIQUE (clientid, userid);
ALTER TABLE ONLY mattermost.oauthaccessdata RENAME CONSTRAINT idx_19143_primary to oauthaccessdata_pkey;
ALTER TABLE ONLY mattermost.oauthapps RENAME CONSTRAINT idx_19149_primary to oauthapps_pkey;
ALTER TABLE ONLY mattermost.oauthauthdata RENAME CONSTRAINT idx_19155_primary to oauthauthdata_pkey;
ALTER TABLE ONLY mattermost.outgoingwebhooks RENAME CONSTRAINT idx_19161_primary to outgoingwebhooks_pkey;
ALTER TABLE ONLY mattermost.pluginkeyvaluestore RENAME CONSTRAINT idx_19167_primary to pluginkeyvaluestore_pkey;
ALTER TABLE ONLY mattermost.posts RENAME CONSTRAINT idx_19173_primary to posts_pkey;
ALTER TABLE ONLY mattermost.preferences RENAME CONSTRAINT idx_19179_primary to preferences_pkey;
ALTER TABLE ONLY mattermost.publicchannels ADD CONSTRAINT publicchannels_name_teamid_key UNIQUE (name, teamid);
ALTER TABLE ONLY mattermost.publicchannels RENAME CONSTRAINT idx_19185_primary to publicchannels_pkey;
ALTER TABLE ONLY mattermost.reactions RENAME CONSTRAINT idx_19191_primary to reactions_pkey;
ALTER TABLE ONLY mattermost.roles ADD CONSTRAINT roles_name_key UNIQUE (name);
ALTER TABLE ONLY mattermost.roles RENAME CONSTRAINT idx_19194_primary to roles_pkey;
ALTER TABLE ONLY mattermost.schemes ADD CONSTRAINT schemes_name_key UNIQUE (name);
ALTER TABLE ONLY mattermost.schemes RENAME CONSTRAINT idx_19200_primary to schemes_pkey;
ALTER TABLE ONLY mattermost.sessions RENAME CONSTRAINT idx_19206_primary to sessions_pkey;
ALTER TABLE ONLY mattermost.status RENAME CONSTRAINT idx_19212_primary to status_pkey;
ALTER TABLE ONLY mattermost.systems RENAME CONSTRAINT idx_19215_primary to systems_pkey;
ALTER TABLE ONLY mattermost.teammembers RENAME CONSTRAINT idx_19221_primary to teammembers_pkey;
ALTER TABLE ONLY mattermost.teams ADD CONSTRAINT teams_name_key UNIQUE (name);
ALTER TABLE ONLY mattermost.teams RENAME CONSTRAINT idx_19224_primary to teams_pkey;
ALTER TABLE ONLY mattermost.termsofservice RENAME CONSTRAINT idx_19230_primary to termsofservice_pkey;
ALTER TABLE ONLY mattermost.tokens RENAME CONSTRAINT idx_19236_primary to tokens_pkey;
ALTER TABLE ONLY mattermost.useraccesstokens RENAME CONSTRAINT idx_19242_primary to useraccesstokens_pkey;
ALTER TABLE ONLY mattermost.useraccesstokens ADD CONSTRAINT useraccesstokens_token_key UNIQUE (token);
ALTER TABLE ONLY mattermost.usergroups ADD CONSTRAINT usergroups_name_key UNIQUE (name);
ALTER TABLE ONLY mattermost.usergroups RENAME CONSTRAINT idx_19248_primary to usergroups_pkey;
ALTER TABLE ONLY mattermost.usergroups ADD CONSTRAINT usergroups_source_remoteid_key UNIQUE (source, remoteid);
ALTER TABLE ONLY mattermost.users ADD CONSTRAINT users_authdata_key UNIQUE (authdata);
ALTER TABLE ONLY mattermost.users ADD CONSTRAINT users_email_key UNIQUE (email);
ALTER TABLE ONLY mattermost.users RENAME CONSTRAINT idx_19254_primary to users_pkey;
ALTER TABLE ONLY mattermost.users ADD CONSTRAINT users_username_key UNIQUE (username);
ALTER TABLE ONLY mattermost.usertermsofservice RENAME CONSTRAINT idx_19260_primary to usertermsofservice_pkey;
DROP INDEX mattermost.idx_19053_idx_audits_user_id;
DROP INDEX mattermost.idx_19068_idx_channelmembers_channel_id;
DROP INDEX mattermost.idx_19068_idx_channelmembers_user_id;
DROP INDEX mattermost.idx_19074_idx_channel_search_txt;
DROP INDEX mattermost.idx_19074_idx_channels_create_at;
DROP INDEX mattermost.idx_19074_idx_channels_delete_at;
DROP INDEX mattermost.idx_19074_idx_channels_name;
DROP INDEX mattermost.idx_19074_idx_channels_team_id;
DROP INDEX mattermost.idx_19074_idx_channels_update_at;
DROP INDEX mattermost.idx_19074_name;
DROP INDEX mattermost.idx_19086_idx_command_create_at;
DROP INDEX mattermost.idx_19086_idx_command_delete_at;
DROP INDEX mattermost.idx_19086_idx_command_team_id;
DROP INDEX mattermost.idx_19086_idx_command_update_at;
DROP INDEX mattermost.idx_19092_idx_command_webhook_create_at;
DROP INDEX mattermost.idx_19101_idx_emoji_create_at;
DROP INDEX mattermost.idx_19101_idx_emoji_delete_at;
DROP INDEX mattermost.idx_19101_idx_emoji_name;
DROP INDEX mattermost.idx_19101_idx_emoji_update_at;
DROP INDEX mattermost.idx_19101_name;
DROP INDEX mattermost.idx_19104_idx_fileinfo_create_at;
DROP INDEX mattermost.idx_19104_idx_fileinfo_delete_at;
DROP INDEX mattermost.idx_19104_idx_fileinfo_postid_at;
DROP INDEX mattermost.idx_19104_idx_fileinfo_update_at;
DROP INDEX mattermost.idx_19110_idx_groupchannels_channelid;
DROP INDEX mattermost.idx_19113_idx_groupmembers_create_at;
DROP INDEX mattermost.idx_19116_idx_groupteams_teamid;
DROP INDEX mattermost.idx_19119_idx_incoming_webhook_create_at;
DROP INDEX mattermost.idx_19119_idx_incoming_webhook_delete_at;
DROP INDEX mattermost.idx_19119_idx_incoming_webhook_team_id;
DROP INDEX mattermost.idx_19119_idx_incoming_webhook_update_at;
DROP INDEX mattermost.idx_19119_idx_incoming_webhook_user_id;
DROP INDEX mattermost.idx_19125_idx_jobs_type;
DROP INDEX mattermost.idx_19137_idx_link_metadata_url_timestamp;
DROP INDEX mattermost.idx_19143_clientid;
DROP INDEX mattermost.idx_19143_idx_oauthaccessdata_client_id;
DROP INDEX mattermost.idx_19143_idx_oauthaccessdata_refresh_token;
DROP INDEX mattermost.idx_19143_idx_oauthaccessdata_user_id;
DROP INDEX mattermost.idx_19149_idx_oauthapps_creator_id;
DROP INDEX mattermost.idx_19155_idx_oauthauthdata_client_id;
DROP INDEX mattermost.idx_19161_idx_outgoing_webhook_create_at;
DROP INDEX mattermost.idx_19161_idx_outgoing_webhook_delete_at;
DROP INDEX mattermost.idx_19161_idx_outgoing_webhook_team_id;
DROP INDEX mattermost.idx_19161_idx_outgoing_webhook_update_at;
DROP INDEX mattermost.idx_19173_idx_posts_channel_id;
DROP INDEX mattermost.idx_19173_idx_posts_channel_id_delete_at_create_at;
DROP INDEX mattermost.idx_19173_idx_posts_channel_id_update_at;
DROP INDEX mattermost.idx_19173_idx_posts_create_at;
DROP INDEX mattermost.idx_19173_idx_posts_delete_at;
DROP INDEX mattermost.idx_19173_idx_posts_hashtags_txt;
DROP INDEX mattermost.idx_19173_idx_posts_is_pinned;
DROP INDEX mattermost.idx_19173_idx_posts_message_txt;
DROP INDEX mattermost.idx_19173_idx_posts_root_id;
DROP INDEX mattermost.idx_19173_idx_posts_update_at;
DROP INDEX mattermost.idx_19173_idx_posts_user_id;
DROP INDEX mattermost.idx_19179_idx_preferences_category;
DROP INDEX mattermost.idx_19179_idx_preferences_name;
DROP INDEX mattermost.idx_19179_idx_preferences_user_id;
DROP INDEX mattermost.idx_19185_idx_publicchannels_delete_at;
DROP INDEX mattermost.idx_19185_idx_publicchannels_name;
DROP INDEX mattermost.idx_19185_idx_publicchannels_search_txt;
DROP INDEX mattermost.idx_19185_idx_publicchannels_team_id;
DROP INDEX mattermost.idx_19185_name;
DROP INDEX mattermost.idx_19194_name;
DROP INDEX mattermost.idx_19200_name;
DROP INDEX mattermost.idx_19206_idx_sessions_create_at;
DROP INDEX mattermost.idx_19206_idx_sessions_expires_at;
DROP INDEX mattermost.idx_19206_idx_sessions_last_activity_at;
DROP INDEX mattermost.idx_19206_idx_sessions_token;
DROP INDEX mattermost.idx_19206_idx_sessions_user_id;
DROP INDEX mattermost.idx_19212_idx_status_status;
DROP INDEX mattermost.idx_19212_idx_status_user_id;
DROP INDEX mattermost.idx_19221_idx_teammembers_delete_at;
DROP INDEX mattermost.idx_19221_idx_teammembers_team_id;
DROP INDEX mattermost.idx_19221_idx_teammembers_user_id;
DROP INDEX mattermost.idx_19224_idx_teams_create_at;
DROP INDEX mattermost.idx_19224_idx_teams_delete_at;
DROP INDEX mattermost.idx_19224_idx_teams_invite_id;
DROP INDEX mattermost.idx_19224_idx_teams_name;
DROP INDEX mattermost.idx_19224_idx_teams_update_at;
DROP INDEX mattermost.idx_19224_name;
DROP INDEX mattermost.idx_19242_idx_user_access_tokens_token;
DROP INDEX mattermost.idx_19242_idx_user_access_tokens_user_id;
DROP INDEX mattermost.idx_19242_token;
DROP INDEX mattermost.idx_19248_idx_usergroups_delete_at;
DROP INDEX mattermost.idx_19248_idx_usergroups_remote_id;
DROP INDEX mattermost.idx_19248_name;
DROP INDEX mattermost.idx_19248_source;
DROP INDEX mattermost.idx_19254_authdata;
DROP INDEX mattermost.idx_19254_email;
DROP INDEX mattermost.idx_19254_idx_users_all_no_full_name_txt;
DROP INDEX mattermost.idx_19254_idx_users_all_txt;
DROP INDEX mattermost.idx_19254_idx_users_create_at;
DROP INDEX mattermost.idx_19254_idx_users_delete_at;
DROP INDEX mattermost.idx_19254_idx_users_email;
DROP INDEX mattermost.idx_19254_idx_users_names_no_full_name_txt;
DROP INDEX mattermost.idx_19254_idx_users_names_txt;
DROP INDEX mattermost.idx_19254_idx_users_update_at;
DROP INDEX mattermost.idx_19254_username;
DROP INDEX mattermost.idx_19260_idx_user_terms_of_service_user_id;

Unsure if it's worth trying to change all the text fields into varchar(len) or not to fully match the intended setup.

Thanks for pointing me in the right direction!

[lieut-data]

@parnic-sks, glad you were able to get the immediate issue sorted! I was literally going to do the same diff :)

Generally speaking, the varchar(len) is just to impose a database-level constraint on the allowed values. The application /should/ already enforce this. But it would be hard to fully anticipate what could go wrong in the future.

If you're up to making the column type changes, I'd recommend you go for it -- let me know if you need any specific help.

[amyblais]

Closing as the immediate issue is resolved. Please re-open this issue if I've closed this too soon,