Asking to confirm 'useraccesstokens' table contents

Troubleshooting Unanswered
1

I detected an error reported by postgresql.log:

 mmuser@mattermost ERROR:  relation "useraccesstokens" does not exist at character 139

I didn’t find the error in Mattermost logs maybe because we are not using this table!?

The problem is there since the migration from mysql to postgresql.

I renamed the table from user_access_tokens to useraccesstokens, then create 2 missing columns and recreate the indexes accordingly the information that I got…but I would like if someone can confirm that the following set for this table is correct:

mattermost=> \d+ useraccesstokens
                                                Table "public.useraccesstokens"
     Column     |         Type          | Collation | Nullable | Default | Storage  |
----------------+-----------------------+-----------+----------+---------+----------+ ...
 id             | character varying(26) |           | not null |         | extended |             
 token          | character varying(26) |           |          |         | extended |             
 userid         | character varying(26) |           |          |         | extended |             
 description    | text                  |           |          |         | extended |             
 isactive       | boolean               |           |          |         | plain    |             
 expiresat      | bigint                |           |          | 0       | plain    |             
 lastnotifiedat | bigint                |           |          | 0       | plain    |             
Indexes:
    "useraccesstokens_pkey" PRIMARY KEY, btree (id)
    "idx_user_access_tokens_user_id" btree (userid)
    "idx_useraccesstokens_expiresat" btree (expiresat) WHERE expiresat <> 0
    "useraccesstokens_token_key" UNIQUE, btree (token)
Access method: heap

Thank you.

2

Hey there, not clear to me what version you’re running from, I may have missed it. This is what I have on my system running 11.7.10

REATE TABLE IF NOT EXISTS public.useraccesstokens
(
    id character varying(26) COLLATE pg_catalog."default" NOT NULL,
    token character varying(26) COLLATE pg_catalog."default",
    userid character varying(26) COLLATE pg_catalog."default",
    description character varying(512) COLLATE pg_catalog."default",
    isactive boolean,
    CONSTRAINT useraccesstokens_pkey PRIMARY KEY (id),
    CONSTRAINT useraccesstokens_token_key UNIQUE (token)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.useraccesstokens
    OWNER to !!YOUCANTSEETHIS!!;
-- Index: idx_user_access_tokens_user_id

-- DROP INDEX IF EXISTS public.idx_user_access_tokens_user_id;

CREATE INDEX IF NOT EXISTS idx_user_access_tokens_user_id
    ON public.useraccesstokens USING btree
    (userid COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;
3

Thanks for the details, CJF, and the comparison, @gdelia-pm; could you confirm your Mattermost version and migration method, since the v11 changelog confirms expiresat was added in v11.9 and lastnotifiedat in v11.10, so we’ll need to check your exact release before confirming the table definition?

5

Thanks for the response John and sorry for the late reply.
The migration to postgresql was made when we were at version 10.11.19 (Now we are at 11.7.10).
I used Limetric pgloader. I tried to use the script provided by mattermost github but never worked, so I did many corrections in database following as much possible the instructions as preparation of the database, simulating manually what the script should do, and fixing posterior required changes to the postgresql database. The above is the last change that I did(Already on 11.7.10 mattermost version).
The logs seem to be good now.

6

Thank you gdelia-pm. I have the same version as you, 11.7.10 .