Summary
ERROR: relation “idx_*” does not exist
Expected behavior
No errors in my logs
Observed behavior
I ran a (flawless) update from v5.36 to v5.38 today and had a quick look at the logs afterwards when I noticed a couple of SQL errors which make me a bit nervous. The errors don’t seem to be related to the upgrade though, I can see from the logs that they were showing already before the upgrade.
mattermost-db | 2021-08-03 10:59:02.258 CEST [1] LOG: database system is ready to accept connections
mattermost-db | 2021-08-03 10:59:03.126 CEST [28] ERROR: relation "idx_posts_channel_id" does not exist
mattermost-db | 2021-08-03 10:59:03.126 CEST [28] STATEMENT: SELECT $1::regclass
mattermost-db | 2021-08-03 10:59:03.127 CEST [28] ERROR: relation "idx_channels_name" does not exist
mattermost-db | 2021-08-03 10:59:03.127 CEST [28] STATEMENT: SELECT $1::regclass
mattermost-db | 2021-08-03 10:59:03.127 CEST [28] ERROR: relation "idx_publicchannels_name" does not exist
mattermost-db | 2021-08-03 10:59:03.127 CEST [28] STATEMENT: SELECT $1::regclass
mattermost-db | 2021-08-03 10:59:03.128 CEST [28] ERROR: relation "idx_channelmembers_channel_id" does not exist
mattermost-db | 2021-08-03 10:59:03.128 CEST [28] STATEMENT: SELECT $1::regclass
mattermost-db | 2021-08-03 10:59:03.128 CEST [28] ERROR: relation "idx_emoji_name" does not exist
mattermost-db | 2021-08-03 10:59:03.128 CEST [28] STATEMENT: SELECT $1::regclass
mattermost-db | 2021-08-03 10:59:03.129 CEST [28] ERROR: relation "idx_oauthaccessdata_client_id" does not exist
mattermost-db | 2021-08-03 10:59:03.129 CEST [28] STATEMENT: SELECT $1::regclass
mattermost-db | 2021-08-03 10:59:03.129 CEST [28] ERROR: relation "idx_oauthauthdata_client_id" does not exist
mattermost-db | 2021-08-03 10:59:03.129 CEST [28] STATEMENT: SELECT $1::regclass
mattermost-db | 2021-08-03 10:59:03.130 CEST [28] ERROR: relation "idx_preferences_user_id" does not exist
mattermost-db | 2021-08-03 10:59:03.130 CEST [28] STATEMENT: SELECT $1::regclass
mattermost-db | 2021-08-03 10:59:03.130 CEST [28] ERROR: relation "idx_notice_views_user_id" does not exist
mattermost-db | 2021-08-03 10:59:03.130 CEST [28] STATEMENT: SELECT $1::regclass
mattermost-db | 2021-08-03 10:59:03.130 CEST [28] ERROR: relation "idx_notice_views_user_notice" does not exist
mattermost-db | 2021-08-03 10:59:03.130 CEST [28] STATEMENT: SELECT $1::regclass
mattermost-db | 2021-08-03 10:59:03.131 CEST [28] ERROR: relation "idx_status_user_id" does not exist
mattermost-db | 2021-08-03 10:59:03.131 CEST [28] STATEMENT: SELECT $1::regclass
mattermost-db | 2021-08-03 10:59:03.131 CEST [28] ERROR: relation "idx_teammembers_team_id" does not exist
mattermost-db | 2021-08-03 10:59:03.131 CEST [28] STATEMENT: SELECT $1::regclass
mattermost-db | 2021-08-03 10:59:03.131 CEST [28] ERROR: relation "idx_teams_name" does not exist
mattermost-db | 2021-08-03 10:59:03.131 CEST [28] STATEMENT: SELECT $1::regclass
mattermost-db | 2021-08-03 10:59:03.132 CEST [28] ERROR: relation "idx_user_access_tokens_token" does not exist
mattermost-db | 2021-08-03 10:59:03.132 CEST [28] STATEMENT: SELECT $1::regclass
mattermost-db | 2021-08-03 10:59:03.132 CEST [28] ERROR: relation "idx_user_terms_of_service_user_id" does not exist
mattermost-db | 2021-08-03 10:59:03.132 CEST [28] STATEMENT: SELECT $1::regclass
mattermost-db | 2021-08-03 10:59:03.133 CEST [28] ERROR: relation "idx_users_email" does not exist
mattermost-db | 2021-08-03 10:59:03.133 CEST [28] STATEMENT: SELECT $1::regclass
mattermost-db | 2021-08-03 10:59:03.133 CEST [28] ERROR: relation "idx_sharedchannelusers_user_id" does not exist
mattermost-db | 2021-08-03 10:59:03.133 CEST [28] STATEMENT: SELECT $1::regclass
mattermost-db | 2021-08-03 10:59:03.133 CEST [28] ERROR: relation "idx_retentionpolicies_displayname_id" does not exist
mattermost-db | 2021-08-03 10:59:03.133 CEST [28] STATEMENT: SELECT $1::regclass
mattermost-db | 2021-08-03 10:59:03.142 CEST [28] ERROR: constraint "fk_retentionpolicieschannels_retentionpolicies" for relation "retentionpolicieschannels" already exists
mattermost-db | 2021-08-03 10:59:03.142 CEST [28] STATEMENT:
mattermost-db | ALTER TABLE RetentionPoliciesChannels
mattermost-db | ADD CONSTRAINT FK_RetentionPoliciesChannels_RetentionPolicies
mattermost-db | FOREIGN KEY (PolicyId) REFERENCES RetentionPolicies (Id)
mattermost-db | ON DELETE CASCADE;
mattermost-db | 2021-08-03 10:59:03.142 CEST [28] ERROR: constraint "fk_retentionpoliciesteams_retentionpolicies" for relation "retentionpoliciesteams" already exists
mattermost-db | 2021-08-03 10:59:03.142 CEST [28] STATEMENT:
mattermost-db | ALTER TABLE RetentionPoliciesTeams
mattermost-db | ADD CONSTRAINT FK_RetentionPoliciesTeams_RetentionPolicies
mattermost-db | FOREIGN KEY (PolicyId) REFERENCES RetentionPolicies (Id)
mattermost-db | ON DELETE CASCADE;
mattermost-app | {"level":"info","ts":1627981145.7062602,"caller":"mlog/sugar.go:21","msg":"Ensuring Surveybot exists","plugin_id":"com.mattermost.nps"}
mattermost-db | 2021-08-03 10:59:05.710 CEST [31] ERROR: duplicate key value violates unique constraint "users_username_key"
mattermost-db | 2021-08-03 10:59:05.710 CEST [31] DETAIL: Key (username)=(surveybot) already exists.
mattermost-db | 2021-08-03 10:59:05.710 CEST [31] STATEMENT: insert into "users" ("id","createat","updateat","deleteat","username","password","authdata","authservice","email","emailverified","nickname","firstname","lastname","position","roles","allowmarketing","props","notifyprops","lastpasswordupdate","lastpictureupdate","failedattempts","locale","timezone","mfaactive","mfasecret","remoteid") values ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26);
mattermost-app | {"level":"info","ts":1627981145.7717543,"caller":"app/server.go:1179","msg":"Starting Server..."}
mattermost-app | {"level":"info","ts":1627981145.7721815,"caller":"app/server.go:1255","msg":"Server is listening on [::]:8065","address":"[::]:8065"}
mattermost-app | {"level":"error","ts":1627981149.3193753,"caller":"log/log.go:181","msg":"[ERR] plugin: plugin acceptAndServe error: timeout waiting for accept","source":"stdlog"}
mattermost-app | {"level":"error","ts":1627981150.6999316,"caller":"log/log.go:181","msg":"[ERR] plugin: plugin acceptAndServe error: timeout waiting for accept","source":"stdlog"}
I then checked another instance I am looking after and see similar idx_* missing errors?
I can find some posts here related to missing idx relations, but they look outdated to me. My two instances are 3 and less than one year old.
Anyone got an idea what might be going on? Is there an easy way to recreate those missing relations?
Can I ignore the constraint exists and adding surveybot errors? Maybe Mattermost is trying to recreate those items whenever the server restarts, no matter if they are there already?
Both Environments
- Mattermost Version: 5.38.0-rc1
- Database Schema Version: 5.38.0
- Database: postgres
- Docker on Ubuntu
