User delete no longer working in 5.29.0

Troubleshooting
1

Summary

user delete does no longer work in 5.29.0

Steps to reproduce

Due to the EU GDPR I am always deleting users from our instance when they left us.
This did always work quite well in the last couple of years.
Now with version 5.29.0 it is no longer working. I do get the information that the messages to delete could not be selected. The error appears with the old CLI and also with the new mmctl.
It did still work in 5.27.x and as far as I remember also in 5.28.x with the old CLI (there I did not yet use mmctl)

With the old CLI:

root@xyz:/var/xyz/mattermost# docker-compose exec app mattermost user delete 88m5gojcmbdcb8wf14dzhrmwae --confirm
Error: PermanentDeleteUser: Konnte die zu löschenden Nachrichten für den Benutzer nicht auswählen., failed to get a thread: resource: Thread id: 557ee7cknb8nichugob13x1oqo

With the new mmctl the additional information that the it failed to get a thread is not shown in the console.

root@xyz:/var/xyz/mattermost# docker-compose exec app mmctl user delete 88m5gojcmbdcb8wf14dzhrmwae --confirm --local
Unable to delete user 'abc.xyz' error: : Konnte die zu löschenden Nachrichten für den Benutzer nicht auswählen.,

Expected behavior

user should be deleted like it worked in the older versions

(the Bleve search engine is active in our instance since 5.27.x, so for a deletion with the old CLI I always had to first temporarily deactivate Bleve, do the deletion and then reactivate Bleve)

Observed behavior

see above

1 Like
2

Can you help confirm you’re using the mmctl user delete [userid] command?

2 Likes
3

well as you can see above I also had tried mmctl user delete [userid] :wink:
Since it is running in a docker container of course it had been
docker-compose exec app mmctl user delete [userid] --confirm --local

4

Hi Guido! It looks like the error is happening at the database level, could you please check the server logs to see if there is anything related to a delete error? A possible string to look for would be “PermanentDeleteUser”

5

I now set the log level to debug. Here the data from the mattermost.log:

{"level":"debug","ts":1606932038.5730777,"caller":"web/handlers.go:100","msg":"Received HTTP request","method":"GET","url":"/api/v4/users/email/abc@xyz.de","request_id":"gabxqdj46tnqfnt1moie5fq1cc","status_code":"200"}
{"level":"warn","ts":1606932038.5735576,"caller":"app/user.go:1479","msg":"Attempting to permanently delete account","user_id":"88m5gojcmbdcb8wf14dzhrmwae","user_email":"abc@xyz.de"}
{"level":"error","ts":1606932038.6132126,"caller":"mlog/log.go:229","msg":"Unable to select the posts to delete for the user.","path":"/api/v4/users/88m5gojcmbdcb8wf14dzhrmwae","request_id":"ayz9c6a4pfnpjda695phaypnyc","ip_addr":"","user_id":"","method":"DELETE","err_where":"PermanentDeleteUser","http_code":500,"err_details":"failed to get a thread: resource: Thread id: 557ee7cknb8nichugob13x1oqo"}
{"level":"debug","ts":1606932038.6132631,"caller":"web/handlers.go:100","msg":"Received HTTP request","method":"DELETE","url":"/api/v4/users/88m5gojcmbdcb8wf14dzhrmwae","request_id":"ayz9c6a4pfnpjda695phaypnyc","status_code":"500"}
{"level":"debug","ts":1606932038.6866002,"caller":"searchlayer/layer.go:95","msg":"Indexed user in search engine","search_engine":"bleve","user_id":"88m5gojcmbdcb8wf14dzhrmwae"}
{"level":"debug","ts":1606932038.7480607,"caller":"searchlayer/layer.go:95","msg":"Indexed user in search engine","search_engine":"bleve","user_id":"88m5gojcmbdcb8wf14dzhrmwae"}

and no errors in mattermost_db log besides the usual “idx_teams_description” error:

db_1   | PostgreSQL Database directory appears to contain a database; Skipping initialization
db_1   |
db_1   | LOG:  database system was interrupted; last known up at 2020-12-02 17:48:09 UTC
db_1   | LOG:  database system was not properly shut down; automatic recovery in progress
db_1   | LOG:  redo starts at 5DC/F7E77538
db_1   | LOG:  record with zero length at 5DC/F7E97F48
db_1   | LOG:  redo done at 5DC/F7E97F18
db_1   | LOG:  last completed transaction was at log time 2020-12-02 17:49:39.860369+00
db_1   | LOG:  MultiXact member wraparound protections are now enabled
db_1   | LOG:  database system is ready to accept connections
db_1   | LOG:  autovacuum launcher started
db_1   | ERROR:  relation "idx_teams_description" does not exist
6

Hello, @GuidoD

Tested this on a fresh Mattermost Server 5.29.0 and the mmctl user delete command is working properly, at least on MySQL.

Will further test this on Docker deployment to see what I am getting.

7

I see an error in the Mattermost Debug logs that raised my attention, specifically the section below:

This quote leads me to think that your database may be running with the max amount of threads connected already, and is being prevented from deleting the user as desired due to its inability to create a new worker thread. Could you provide the number of threads that you are using on your database? You can find this information in your database’s configuration files.

1 Like
8

Hello everyone.

Quick update. Verified that the command also works on PostgreSQL. No issues there.

Caught up with a couple of meetings and tasks on my end but will update my finding with Docker.

1 Like
9

well it is as far as I know unchanged from the default values of the mattermost docker image, so 100 connections.
I just changed it to 200 connections and restarted the docker container.
Same result, user deletion not working.

Therefore I just tested it with the old versions and max 100 connections:

I went back to version 5.27.1 => user deletion worked fine with the old CLI (mmctl user does not yet have the command delete)
Upgraded from 5.27.1 to version 5.28.1 => user deletion still worked fine, now even with mmctl

root@xyz:/var/xyz/mattermost# docker-compose exec app mmctl user delete <user_id> --confirm --local
Deleted user '<user_id>'

Upgraded again from 5.28.1 to 5.29.0 => user deletion is no longer working

root@xyz:/var/xyz/mattermost# docker-compose exec app mmctl user delete <user_id> --confirm --local
Unable to delete user '<user_id>' error: : Konnte die zu löschenden Nachrichten für den Benutzer nicht auswählen.,

So it really is a new problem that only does occur in 5.29.0 :frowning:

10

Could you verify your database schema is correct by providing the output here of the following MySQL command?
use mattermost;
show columns from Users;
I’m wondering if your database schematics might have gotten changed, or needed to be upgraded and not upgraded, and so is not being read properly.

11

well it is a postgres database :wink:

docker-compose exec db bash
bash-5.0# psql --dbname=mattermost --username=mmuser --password

mattermost=# \d users
                                                                     Table "public.users"
          Column          |          Type           |                                                Modifiers
--------------------------+-------------------------+---------------------------------------------------------------------------------------------------------
 id                       | character varying(26)   | not null
 createat                 | bigint                  |
 updateat                 | bigint                  |
 deleteat                 | bigint                  |
 username                 | character varying(64)   |
 password                 | character varying(128)  |
 authdata                 | character varying(128)  |
 authservice              | character varying(32)   |
 email                    | character varying(128)  |
 emailverified            | boolean                 |
 nickname                 | character varying(64)   |
 firstname                | character varying(64)   |
 lastname                 | character varying(64)   |
 position                 | character varying(128)  |
 roles                    | character varying(256)  |
 allowmarketing           | boolean                 |
 props                    | character varying(4000) |
 notifyprops              | character varying(2000) |
 lastpasswordupdate       | bigint                  |
 lastpictureupdate        | bigint                  |
 failedattempts           | integer                 |
 locale                   | character varying(5)    |
 mfaactive                | boolean                 |
 mfasecret                | character varying(128)  |
 timezone                 | character varying(256)  | default '{"automaticTimezone":"","manualTimezone":"","useAutomaticTimezone":"true"}'::character varying
 acceptedtermsofserviceid | character varying(64)   | default ''::character varying
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_authdata_key" UNIQUE CONSTRAINT, btree (authdata)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)
    "users_username_key" UNIQUE CONSTRAINT, btree (username)
    "idx_users_all_no_full_name_txt" gin (to_tsvector('english'::regconfig, (((username::text || ' '::text) || nickname::text) || ' '::text) || email::text))
    "idx_users_all_txt" gin (to_tsvector('english'::regconfig, (((((((username::text || ' '::text) || firstname::text) || ' '::text) || lastname::text) || ' '::text) || nickname::text) || ' '::text) || email::text))
    "idx_users_create_at" btree (createat)
    "idx_users_delete_at" btree (deleteat)
    "idx_users_email" btree (email)
    "idx_users_email_lower_textpattern" btree (lower(email::text) text_pattern_ops)
    "idx_users_firstname_lower_textpattern" btree (lower(firstname::text) text_pattern_ops)
    "idx_users_lastname_lower_textpattern" btree (lower(lastname::text) text_pattern_ops)
    "idx_users_names_no_full_name_txt" gin (to_tsvector('english'::regconfig, (username::text || ' '::text) || nickname::text))
    "idx_users_names_txt" gin (to_tsvector('english'::regconfig, (((((username::text || ' '::text) || firstname::text) || ' '::text) || lastname::text) || ' '::text) || nickname::text))
    "idx_users_nickname_lower_textpattern" btree (lower(nickname::text) text_pattern_ops)
    "idx_users_update_at" btree (updateat)
    "idx_users_username_lower_textpattern" btree (lower(username::text) text_pattern_ops)
12

Hello, @GuidoD

This is how my Mattermost 5.29.0 PostgreSQL database schema looks like:

                List of relations
 Schema |          Name          | Type  | Owner  
--------+------------------------+-------+--------
 public | audits                 | table | mmuser
 public | bots                   | table | mmuser
 public | channelmemberhistory   | table | mmuser
 public | channelmembers         | table | mmuser
 public | channels               | table | mmuser
 public | clusterdiscovery       | table | mmuser
 public | commands               | table | mmuser
 public | commandwebhooks        | table | mmuser
 public | compliances            | table | mmuser
 public | emoji                  | table | mmuser
 public | fileinfo               | table | mmuser
 public | groupchannels          | table | mmuser
 public | groupmembers           | table | mmuser
 public | groupteams             | table | mmuser
 public | incomingwebhooks       | table | mmuser
 public | jobs                   | table | mmuser
 public | licenses               | table | mmuser
 public | linkmetadata           | table | mmuser
 public | oauthaccessdata        | table | mmuser
 public | oauthapps              | table | mmuser
 public | oauthauthdata          | table | mmuser
 public | outgoingwebhooks       | table | mmuser
 public | pluginkeyvaluestore    | table | mmuser
 public | posts                  | table | mmuser
 public | preferences            | table | mmuser
 public | productnoticeviewstate | table | mmuser
 public | publicchannels         | table | mmuser
 public | reactions              | table | mmuser
 public | roles                  | table | mmuser
 public | schemes                | table | mmuser
 public | sessions               | table | mmuser
 public | sidebarcategories      | table | mmuser
 public | sidebarchannels        | table | mmuser
 public | status                 | table | mmuser
 public | systems                | table | mmuser
 public | teammembers            | table | mmuser
 public | teams                  | table | mmuser
 public | termsofservice         | table | mmuser
 public | tokens                 | table | mmuser
 public | uploadsessions         | table | mmuser
 public | useraccesstokens       | table | mmuser
 public | usergroups             | table | mmuser
 public | users                  | table | mmuser
 public | usertermsofservice     | table | mmuser
(44 rows)

       Column       |          Type           | Collation | Nullable | Default 
--------------------+-------------------------+-----------+----------+---------
 id                 | character varying(26)   |           | not null | 
 createat           | bigint                  |           |          | 
 updateat           | bigint                  |           |          | 
 deleteat           | bigint                  |           |          | 
 username           | character varying(64)   |           |          | 
 password           | character varying(128)  |           |          | 
 authdata           | character varying(128)  |           |          | 
 authservice        | character varying(32)   |           |          | 
 email              | character varying(128)  |           |          | 
 emailverified      | boolean                 |           |          | 
 nickname           | character varying(64)   |           |          | 
 firstname          | character varying(64)   |           |          | 
 lastname           | character varying(64)   |           |          | 
 position           | character varying(128)  |           |          | 
 roles              | character varying(256)  |           |          | 
 allowmarketing     | boolean                 |           |          | 
 props              | character varying(4000) |           |          | 
 notifyprops        | character varying(2000) |           |          | 
 lastpasswordupdate | bigint                  |           |          | 
 lastpictureupdate  | bigint                  |           |          | 
 failedattempts     | integer                 |           |          | 
 locale             | character varying(5)    |           |          | 
 timezone           | character varying(256)  |           |          | 
 mfaactive          | boolean                 |           |          | 
 mfasecret          | character varying(128)  |           |          | 

Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_authdata_key" UNIQUE CONSTRAINT, btree (authdata)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)
    "users_username_key" UNIQUE CONSTRAINT, btree (username)
    "idx_users_all_no_full_name_txt" gin (to_tsvector('english'::regconfig, (((username::text || ' '::text) || nickname::text) || ' '::text) || email::text))
    "idx_users_all_txt" gin (to_tsvector('english'::regconfig, (((((((username::text || ' '::text) || firstname::text) || ' '::text) || lastname::text) || ' '::text) || nickname::text) || ' '::text) || email::text))
    "idx_users_create_at" btree (createat)
    "idx_users_delete_at" btree (deleteat)
    "idx_users_email" btree (email)
    "idx_users_email_lower_textpattern" btree (lower(email::text) text_pattern_ops)
    "idx_users_firstname_lower_textpattern" btree (lower(firstname::text) text_pattern_ops)
    "idx_users_lastname_lower_textpattern" btree (lower(lastname::text) text_pattern_ops)
    "idx_users_names_no_full_name_txt" gin (to_tsvector('english'::regconfig, (username::text || ' '::text) || nickname::text))
    "idx_users_names_txt" gin (to_tsvector('english'::regconfig, (((((username::text || ' '::text) || firstname::text) || ' '::text) || lastname::text) || ' '::text) || nickname::text))
    "idx_users_nickname_lower_textpattern" btree (lower(nickname::text) text_pattern_ops)
    "idx_users_update_at" btree (updateat)
    "idx_users_username_lower_textpattern" btree (lower(username::text) text_pattern_ops)

Seems to be specific to Docker + PostgreSQL probably.

1 Like
13

It looks like the error is related to the threads table not existing in the database. I’ve created a ticket here so the team can take a look at the issue:

2 Likes
14

I just upgraded Mattermost to version 5.30.1 and afterwards did upgrade the database from PostgreSQL 9.4 to 13.1 with all additional major versions inbetween, so from 9.4 to 9.5, 9.6, 10, 11, 12 up to 13 (version 13.1).
Between every version I did a pg_dumpall, shut down the database, move away the database data directory, start up the database which initialized a new database and did import the dumped data, i.e.:

dump:

docker-compose exec db pg_dumpall -U mmuser > pgdump12

re-import:

docker-compose exec -T db psql mattermost -U mmuser < pgdump12

Now the threads table does exist and I could sucessfully delete the users with mmctl.

Looks like somewhere in between when I did the mattermost upgrades over the last three years an update of the database schema which would have created the threads table was not done.

2 Likes