Unable to upgrade past 6.3 (pq: multiple primary keys for table \"reactions\" are not allowed)

asdf · July 27, 2022, 7:24pm

Docker mattermost on EC2 + postgres RDS instances here…

When trying to upgrade to 6.4, 6.7, 7.0 or 7.1, in all cases I get the error:

docker-compose -f docker-compose.yml -f docker-compose.without-nginx.yml up
Building with native build. Learn about native build in Compose here: https://docs.docker.com/go/compose-native-build/
Creating network "mattermost_default" with the default driver
Pulling mattermost (mattermost/mattermost-team-edition:7.0)...
7.0: Pulling from mattermost/mattermost-team-edition




Digest: sha256:e8b7c441dafa159ff75ad40ef43e5377f20d0e3d0063b18d624568f3dbb1a1ac
Status: Downloaded newer image for mattermost/mattermost-team-edition:7.0
Creating mattermost_mattermost_1 ... 
Attaching to mattermost_mattermost_1
mattermost_1  | {"timestamp":"2022-07-27 16:44:27.109 Z","level":"info","msg":"Server is initializing...","caller":"app/server.go:258","go_version":"go1.18.1"}
mattermost_1  | {"timestamp":"2022-07-27 16:44:27.109 Z","level":"info","msg":"Pinging SQL","caller":"sqlstore/store.go:225","database":"master"}
mattermost_1  | {"timestamp":"2022-07-27 16:44:27.136 Z","level":"fatal","msg":"Failed to apply database migrations.","caller":"sqlstore/store.go:162","error":"driver: postgres, message: failed to execute migration, command: executing_query, originalError: pq: multiple primary keys for table \"reactions\" are not allowed, query: \n\nCREATE TABLE IF NOT EXISTS reactions(\n    userid VARCHAR(26) NOT NULL,\n    postid VARCHAR(26) NOT NULL,\n    emojiname VARCHAR(64) NOT NULL,\n    createat bigint\n);\n\nALTER TABLE reactions ADD COLUMN IF NOT EXISTS updateat bigint;\nALTER TABLE reactions ADD COLUMN IF NOT EXISTS deleteat bigint;\n\nDO $$\n<<alter_pk>>\nDECLARE\n    existing_index text;\nBEGIN\n    SELECT string_agg(a.attname, ',') INTO existing_index\n    FROM pg_constraint AS c\n    CROSS JOIN\n        (SELECT unnest(conkey) FROM pg_constraint WHERE conrelid = 'reactions'::regclass AND contype='p') AS cols(colnum)\n    INNER JOIN pg_attribute AS a ON a.attrelid = c.conrelid AND cols.colnum = a.attnum\n    WHERE c.contype = 'p'\n    AND c.conrelid = 'reactions'::regclass;\n\n    IF COALESCE (existing_index, '') <> text('postid,userid,emojiname') THEN\n        ALTER TABLE reactions\n            DROP CONSTRAINT IF EXISTS reactions_pkey,\n            ADD PRIMARY KEY (postid, userid, emojiname);\n    END IF;\nEND alter_pk $$;\n\nALTER TABLE reactions ADD COLUMN IF NOT EXISTS remoteid VARCHAR(26);\n\n"}
mattermost_1  | {"timestamp":"2022-07-27 16:44:28.594 Z","level":"info","msg":"Server is initializing...","caller":"app/server.go:258","go_version":"go1.18.1"}
mattermost_1  | {"timestamp":"2022-07-27 16:44:28.594 Z","level":"info","msg":"Pinging SQL","caller":"sqlstore/store.go:225","database":"master"}
mattermost_1  | {"timestamp":"2022-07-27 16:44:28.623 Z","level":"fatal","msg":"Failed to apply database migrations.","caller":"sqlstore/store.go:162","error":"driver: postgres, message: failed to execute migration, command: executing_query, originalError: pq: multiple primary keys for table \"reactions\" are not allowed, query: \n\nCREATE TABLE IF NOT EXISTS reactions(\n    userid VARCHAR(26) NOT NULL,\n    postid VARCHAR(26) NOT NULL,\n    emojiname VARCHAR(64) NOT NULL,\n    createat bigint\n);\n\nALTER TABLE reactions ADD COLUMN IF NOT EXISTS updateat bigint;\nALTER TABLE reactions ADD COLUMN IF NOT EXISTS deleteat bigint;\n\nDO $$\n<<alter_pk>>\nDECLARE\n    existing_index text;\nBEGIN\n    SELECT string_agg(a.attname, ',') INTO existing_index\n    FROM pg_constraint AS c\n    CROSS JOIN\n        (SELECT unnest(conkey) FROM pg_constraint WHERE conrelid = 'reactions'::regclass AND contype='p') AS cols(colnum)\n    INNER JOIN pg_attribute AS a ON a.attrelid = c.conrelid AND cols.colnum = a.attnum\n    WHERE c.contype = 'p'\n    AND c.conrelid = 'reactions'::regclass;\n\n    IF COALESCE (existing_index, '') <> text('postid,userid,emojiname') THEN\n        ALTER TABLE reactions\n            DROP CONSTRAINT IF EXISTS reactions_pkey,\n            ADD PRIMARY KEY (postid, userid, emojiname);\n    END IF;\nEND alter_pk $$;\n\nALTER TABLE reactions ADD COLUMN IF NOT EXISTS remoteid VARCHAR(26);\n\n"}
mattermost_mattermost_1 exited with code 1

asdf · July 27, 2022, 7:25pm

failed to execute migration, command: executing_query, originalError: pq: multiple primary keys for table \"reactions\" are not allowed,

Any ideas?

asdf · July 27, 2022, 8:53pm

More info,

\d reactions
                             Table "public.reactions"
  Column   |         Type          | Collation | Nullable |        Default        
-----------+-----------------------+-----------+----------+-----------------------
 userid    | character varying(26) |           | not null | 
 postid    | character varying(26) |           | not null | 
 emojiname | character varying(64) |           | not null | 
 createat  | bigint                |           |          | 
 updateat  | bigint                |           |          | 
 deleteat  | bigint                |           |          | 
 remoteid  | character varying(26) |           |          | 
 channelid | character varying(26) |           | not null | ''::character varying
Indexes:
    "idx_34040_primary" PRIMARY KEY, btree (postid, userid, emojiname)
    "idx_reactions_channel_id" btree (channelid)

asdf · July 27, 2022, 8:56pm

I originally thought it might be related to this issue, Updating from 6.3.3 to 6.4.0 : "Failed to apply database migrations." · Issue #19602 · mattermost/mattermost-server · GitHub where collation issues to mysql users and utf8mb4 seemed to be a problem, but this is postgres, and that isn’t an issue in postgres is it? And our emojis “work”

agriesser · July 28, 2022, 4:17am

Hey asdf,

thanks for the additional info, the link you found is not related.
DB Migration ID 16 is failing here:

CREATE TABLE IF NOT EXISTS reactions(
    userid VARCHAR(26) NOT NULL,
    postid VARCHAR(26) NOT NULL,
    emojiname VARCHAR(64) NOT NULL,
    createat bigint
);

ALTER TABLE reactions ADD COLUMN IF NOT EXISTS updateat bigint;
ALTER TABLE reactions ADD COLUMN IF NOT EXISTS deleteat bigint;

DO $$
<<alter_pk>>
DECLARE
    existing_index text;
BEGIN
    SELECT string_agg(a.attname, ',') INTO existing_index
    FROM pg_constraint AS c
    CROSS JOIN
        (SELECT unnest(conkey) FROM pg_constraint WHERE conrelid = 'reactions'::regclass AND contype='p') AS cols(colnum)
    INNER JOIN pg_attribute AS a ON a.attrelid = c.conrelid AND cols.colnum = a.attnum
    WHERE c.contype = 'p'
    AND c.conrelid = 'reactions'::regclass;

    IF COALESCE (existing_index, '') <> text('postid,userid,emojiname') THEN
        ALTER TABLE reactions
            DROP CONSTRAINT IF EXISTS reactions_pkey,
            ADD PRIMARY KEY (postid, userid, emojiname);
    END IF;
END alter_pk $$;

ALTER TABLE reactions ADD COLUMN IF NOT EXISTS remoteid VARCHAR(26);

And I compared your output with mine (newer version, already migrated) and after the migration, it should look like this:

mattermost=# \d reactions
                             Table "public.reactions"
  Column   |         Type          | Collation | Nullable |        Default
-----------+-----------------------+-----------+----------+-----------------------
 userid    | character varying(26) |           | not null |
 postid    | character varying(26) |           | not null |
 emojiname | character varying(64) |           | not null |
 createat  | bigint                |           |          |
 updateat  | bigint                |           |          |
 deleteat  | bigint                |           |          |
 remoteid  | character varying(26) |           |          |
 channelid | character varying(26) |           | not null | ''::character varying
Indexes:
    "reactions_pkey" PRIMARY KEY, btree (postid, userid, emojiname)
    "idx_reactions_channel_id" btree (channelid)

So what is happening here is that the DB migration is not able to drop the current primary key and there can only be one and this is resulting in an error then. Also I just installed a fresh version of 6.3 on my system and my reactions table looks different to yours:

mattermost63=# select * from schema_migrations;
 version | dirty
---------+-------
       3 | f
(1 row)

mattermost63=# \d reactions
                      Table "public.reactions"
  Column   |         Type          | Collation | Nullable | Default
-----------+-----------------------+-----------+----------+---------
 userid    | character varying(26) |           | not null |
 postid    | character varying(26) |           | not null |
 emojiname | character varying(64) |           | not null |
 createat  | bigint                |           |          |
 updateat  | bigint                |           |          |
 deleteat  | bigint                |           |          |
 remoteid  | character varying(26) |           |          |
Indexes:
    "reactions_pkey" PRIMARY KEY, btree (postid, userid, emojiname)

So you do have an index here, which is not available on my installation and the migration tool does not expect that and is therefore failing.
Can you please run the SELECT on the schema_migrations and let me know what version you’re on? I assume you`re on 15 now, since 16 failed.

Do you have any records in the reactions table?

SELECT COUNT(userid) FROM reactions;

If the table is empty, we can choose a different way of proceeding by just dropping it:

DROP table reactions;

You should then be able to start the server again and the migration step with ID 16 should work.
If the table contains entries, we can try to rename the index. In the below code, I first check for the indices on the table (this should be your output as well, I prepared my setup to look like that) and using the ALTER INDEX command I then renamed it to what the migration is expecting:

mattermost63=# select indexname,indexdef from pg_indexes where tablename='reactions';
     indexname     |                                             indexdef
-------------------+---------------------------------------------------------------------------------------------------
 idx_34040_primary | CREATE UNIQUE INDEX idx_34040_primary ON public.reactions USING btree (postid, userid, emojiname)
(1 row)

mattermost63=# alter index idx_34040_primary RENAME TO pkey_reactions;
ALTER INDEX
mattermost63=# select indexname,indexdef from pg_indexes where tablename='reactions';
   indexname    |                                            indexdef
----------------+------------------------------------------------------------------------------------------------
 pkey_reactions | CREATE UNIQUE INDEX pkey_reactions ON public.reactions USING btree (postid, userid, emojiname)
(1 row)

Let me know if that helps.

asdf · July 28, 2022, 5:01pm

Hey @agriesser ,

Here’s the output from SELECT * FROM schema_migrations;

 version | dirty 
---------+-------
       3 | f
(1 row)

Is that what you wanted to see?

I wonder if this issue may be related to our custom emojis?

SELECT COUNT(userid) FROM reactions; outputs:

 count 
-------
  7666
(1 row)

I can make backups before running any actions - what do you recommend we do to potentially resolve the issue and get our version up to 7.0 and then 7.1?

asdf · July 28, 2022, 5:58pm

We will take the latter approach of renaming the index and report back how it goes

asdf · July 29, 2022, 2:27am

@agriesser I tried the ALTER as follows and unfortunately, it still did not resolve it. I noticed we had an extra row that you didn’t have in the test, might be related?

psql (13.7, server 13.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

internal-services-mm-prod=> select indexname,indexdef from pg_indexes where tablename='reactions';
        indexname         |                                             indexdef                                              
--------------------------+---------------------------------------------------------------------------------------------------
 idx_34040_primary        | CREATE UNIQUE INDEX idx_34040_primary ON public.reactions USING btree (postid, userid, emojiname)
 idx_reactions_channel_id | CREATE INDEX idx_reactions_channel_id ON public.reactions USING btree (channelid)
(2 rows)

internal-services-mm-prod=> alter index idx_34040_primary RENAME TO pkey_reactions;
ALTER INDEX
internal-services-mm-prod=> select indexname,indexdef from pg_indexes where tablename='reactions';
        indexname         |                                            indexdef                                            
--------------------------+------------------------------------------------------------------------------------------------
 pkey_reactions           | CREATE UNIQUE INDEX pkey_reactions ON public.reactions USING btree (postid, userid, emojiname)
 idx_reactions_channel_id | CREATE INDEX idx_reactions_channel_id ON public.reactions USING btree (channelid)
(2 rows)

internal-services-mm-prod=> \q
[ec2-user@ mattermost]$ docker-compose -f docker-compose.yml -f docker-compose.without-nginx.yml up
Building with native build. Learn about native build in Compose here: https://docs.docker.com/go/compose-native-build/
Creating network "mattermost_default" with the default driver
Creating mattermost_mattermost_1 ... 
Attaching to mattermost_mattermost_1
mattermost_1  | {"timestamp":"2022-07-29 02:24:44.284 Z","level":"info","msg":"Server is initializing...","caller":"app/server.go:264","go_version":"go1.18.1"}
mattermost_1  | {"timestamp":"2022-07-29 02:24:44.320 Z","level":"info","msg":"Pinging SQL","caller":"sqlstore/store.go:228","database":"master"}
mattermost_1  | {"timestamp":"2022-07-29 02:24:44.352 Z","level":"fatal","msg":"Failed to apply database migrations.","caller":"sqlstore/store.go:165","error":"driver: postgres, message: failed to execute migration, command: executing_query, originalError: pq: multiple primary keys for table \"reactions\" are not allowed, query: \n\nCREATE TABLE IF NOT EXISTS reactions(\n    userid VARCHAR(26) NOT NULL,\n    postid VARCHAR(26) NOT NULL,\n    emojiname VARCHAR(64) NOT NULL,\n    createat bigint\n);\n\nALTER TABLE reactions ADD COLUMN IF NOT EXISTS updateat bigint;\nALTER TABLE reactions ADD COLUMN IF NOT EXISTS deleteat bigint;\n\nDO $$\n<<alter_pk>>\nDECLARE\n    existing_index text;\nBEGIN\n    SELECT string_agg(a.attname, ',') INTO existing_index\n    FROM pg_constraint AS c\n    CROSS JOIN\n        (SELECT unnest(conkey) FROM pg_constraint WHERE conrelid = 'reactions'::regclass AND contype='p') AS cols(colnum)\n    INNER JOIN pg_attribute AS a ON a.attrelid = c.conrelid AND cols.colnum = a.attnum\n    WHERE c.contype = 'p'\n    AND c.conrelid = 'reactions'::regclass;\n\n    IF COALESCE (existing_index, '') <> text('postid,userid,emojiname') THEN\n        ALTER TABLE reactions\n            DROP CONSTRAINT IF EXISTS reactions_pkey,\n            ADD PRIMARY KEY (postid, userid, emojiname);\n    END IF;\nEND alter_pk $$;\n\nALTER TABLE reactions ADD COLUMN IF NOT EXISTS remoteid VARCHAR(26);\n\n"}
mattermost_1  | {"timestamp":"2022-07-29 02:24:45.696 Z","level":"info","msg":"Server is initializing...","caller":"app/server.go:264","go_version":"go1.18.1"}
mattermost_1  | {"timestamp":"2022-07-29 02:24:45.696 Z","level":"info","msg":"Pinging SQL","caller":"sqlstore/store.go:228","database":"master"}
mattermost_1  | {"timestamp":"2022-07-29 02:24:45.715 Z","level":"fatal","msg":"Failed to apply database migrations.","caller":"sqlstore/store.go:165","error":"driver: postgres, message: failed to execute migration, command: executing_query, originalError: pq: multiple primary keys for table \"reactions\" are not allowed, query: \n\nCREATE TABLE IF NOT EXISTS reactions(\n    userid VARCHAR(26) NOT NULL,\n    postid VARCHAR(26) NOT NULL,\n    emojiname VARCHAR(64) NOT NULL,\n    createat bigint\n);\n\nALTER TABLE reactions ADD COLUMN IF NOT EXISTS updateat bigint;\nALTER TABLE reactions ADD COLUMN IF NOT EXISTS deleteat bigint;\n\nDO $$\n<<alter_pk>>\nDECLARE\n    existing_index text;\nBEGIN\n    SELECT string_agg(a.attname, ',') INTO existing_index\n    FROM pg_constraint AS c\n    CROSS JOIN\n        (SELECT unnest(conkey) FROM pg_constraint WHERE conrelid = 'reactions'::regclass AND contype='p') AS cols(colnum)\n    INNER JOIN pg_attribute AS a ON a.attrelid = c.conrelid AND cols.colnum = a.attnum\n    WHERE c.contype = 'p'\n    AND c.conrelid = 'reactions'::regclass;\n\n    IF COALESCE (existing_index, '') <> text('postid,userid,emojiname') THEN\n        ALTER TABLE reactions\n            DROP CONSTRAINT IF EXISTS reactions_pkey,\n            ADD PRIMARY KEY (postid, userid, emojiname);\n    END IF;\nEND alter_pk $$;\n\nALTER TABLE reactions ADD COLUMN IF NOT EXISTS remoteid VARCHAR(26);\n\n"}
mattermost_mattermost_1 exited with code 1
[ec2-user@ mattermost]$ docker-compose -f docker-compose.yml -f docker-compose.without-nginx.yml down

agriesser · July 29, 2022, 2:50am

You’re right, I overlooked the channelid column. It shouldn’t be there, it has been added in 7.something but your database should be on 6.3 and your schema migration is on version 3 and the channelid attribute on the reactions table has been added with migration ID 89…

I’d like to look at your whole databaseschema and compare it to a vanilla 6.3 schema to identify differences.
Please run the following command and send me the resulting file 63.schema:

pg_dump -s -d mattermost > 63.schema

asdf · August 2, 2022, 4:59pm

Hello @agriesser I have posted our schema to http://ix.io/46sD

Thanks again for your help, let me know what you find.

agriesser · August 2, 2022, 7:49pm

Interesting - could it be that you used a tool like pg_loader to convert a previous MySQL installation to the current PostgreSQL database?

I compared your schema to my vanilla 6.3 PostgreSQL schema and found differences like these in some tables (data types not matching, green is mine, red is yours):

@@ -192,43 +192,19 @@
     createat bigint,
     userid character varying(26),
     status character varying(64),
-    count bigint,
-    "desc" text,
+    count integer,
+    "desc" character varying(512),
     type character varying(64),
     startat bigint,
     endat bigint,
-    keywords text,
-    emails text
+    keywords character varying(512),
+    emails character varying(1024)
 );

And a lot of additional culprits like the one we already found:

--- Name: idx_34132_idx_user_terms_of_service_user_id; Type: INDEX; Schema: public; Owner: mattermost63
+-- Name: usertermsofservice usertermsofservice_pkey; Type: CONSTRAINT; Schema: public; Owner: mattermost63
 --

-CREATE INDEX idx_34132_idx_user_terms_of_service_user_id ON public.usertermsofservice USING btree (userid);
+ALTER TABLE ONLY public.usertermsofservice
+    ADD CONSTRAINT usertermsofservice_pkey PRIMARY KEY (userid);

To be honest, I would suggest to run pg_dump with the --data-only parameter and create a fresh database with a vanilla 6.3 schema in order to fix all these problems at once.

I’ve uploaded the default schema also to ix.io: http://ix.io/46ti

Try to create a new database in parallel, load my schema there and import your data. If that works without issues, try to point your mattermost installation to the new database to see if it works.

OlivierV · September 30, 2022, 10:08am

Hi,

I run into a similar problem, due to pg_loader, however i am stuck upgrading from 6.5. Could you please share a vanilla 6.5 PostgreSQL schema?

This is the dump of my schema
http://ix.io/4bTm

Thank you

agriesser · October 3, 2022, 2:02pm

Hi Olivier and welcome to the Mattermost forums!

I’ve uploaded a vanilla 6.5 scheme to this URL.
There are several things in your dump that are off standard, so not sure where they all are coming from to be honest… Will be quite some work to get that aligned.

OlivierV · October 3, 2022, 7:43pm

Thank you agriesser,

I fixed the problem by pulling the v6.5 from github to generate an only-schema export. Then applying this schema to my DB. Finally importing the only-data export. The process was quite long as I had to remove some deprecated table data from the only-data export.

The off standard stuff came from an initial intallation with Mysql at least 4 years ago and yes it was long.

Btw i am not sure how it’s done in the go code but an additional function to have a safe indexing would help migrating data for fileinfo.

--
-- Name: idx_fileinfo_content_txt; Type: INDEX; Schema: public; Owner: mmuser
--

CREATE FUNCTION public.safe_tsv_fn(content text) RETURNS tsvector AS $$
begin
    return to_tsvector('english'::regconfig, left(content, 256*1024));
end
$$ LANGUAGE plpgsql
IMMUTABLE;

CREATE INDEX idx_fileinfo_content_txt ON public.fileinfo USING gin (public.safe_tsv_fn(content));

agriesser · October 4, 2022, 5:34am

I’m not sure I can follow you on the safe indexing topic - did you experience any troubles when importing the data to the new empty scheme?

Topic		Replies	Views
Upgrade from 9.4.5 to 9.5.x or 9. 7.3	7	170	May 28, 2024
Mattermost is unable to connect to Postgres database when using Docker Troubleshooting	1	1356	May 22, 2022
Upgrading Mattermost 7.1 Docker version Troubleshooting	1	788	September 27, 2023
Migrate from mattermost/mattermost-prod-app to mattermost/mattermost-team-edition Troubleshooting	8	610	June 8, 2023
Mattermost needs to be upgraded from docker mattermost/mattermost-prod-app:v5.31.7 to mattermost/mattermost-team-edition:6.0 Troubleshooting	0	489	January 7, 2022

Unable to upgrade past 6.3 (pq: multiple primary keys for table \"reactions\" are not allowed)

Related topics