[Solved] Update vom 7.10.4. to 8 fails - pq: there is no unique or exclusion constraint matching the ON CONF LICT specification

Troubleshooting
1

We cannot update from 7.10.4 to 8.0 (8.1.1) (Postgres 13-alpine) It seems that the database is missing constraint. Has anyone an idea what happens?

mattermost-postgres-1    | 2023-09-01 21:02:20.112 CEST [568] ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification
mattermost-postgres-1    | 2023-09-01 21:02:20.112 CEST [568] STATEMENT:  INSERT INTO Systems (Name,Value) VALUES ($1,$2) ON CONFLICT (name) DO UPDATE SET Value = $3
mattermost-mattermost-1  | {"timestamp":"2023-09-01 21:02:20.112 +02:00","level":"fatal","msg":"(app.App).DoPermissionsMigrations failed","caller":"app/migrations.go:588","error":
"doPermissionsMigration: We encountered an error saving the system property., failed to upsert system property: pq: there is no unique or exclusion constraint matching the ON CONF
LICT specification"}
mattermost-mattermost-1 exited with code 1
2

This solved the issue:

delete from systems where name = 'migration_advanced_permissions_phase_2';
3

I have this Error message. It seems almost same. But your solution didnt fix it. Can you help?

“msg”:“(app.App).DoPermissionsMigrations failed”,“caller”:“app/migrations.go:588”,“error”:“doPermissionsMigration: We encountered an error saving the system property., failed to upsert system property: pq: there is no unique or exclusion constraint matching the ON CONFLICT specification”

4

@kingfisher77 - Thank you so much for following up with your solution!

@emree - I’m sorry to hear that you’re still having trouble. To assist you better, please helps provide some additional details:

  1. Mattermost version: Please provide the exact version of Mattermost you are currently using.
  2. Steps to reproduce: It would be helpful to know the steps that led to this error message.
  3. Any recent changes: Did you make any changes to the Mattermost configuration or database setup before encountering this error?

With this information, I’ll be able to provide you with a more accurate and tailored solution.

5

Hi,

after i manually set the name column as primary key in systems table, mattermost started to work.

Thank you.

6

Woo hoo! So glad to hear, Emre. Happy Mattermosting! :heart: