Upgrade problem from 5.26.1 to 5.34.2

Troubleshooting
1

Summary
I’m trying to upgrade our self hosted server from v5.26.1 to 5.34.2 and the server is reporting errors when starting:

{"level":"error","ts":1620821782.9717069,"caller":"sqlstore/store.go:170","msg":"Failed to apply database migrations.","error":"migration failed: syntax error at or near \"NOT\" (column 33) in line 25: CREATE TABLE IF NOT EXISTS teams (\n    id VARCHAR(26) PRIMARY KEY,\n    createat bigint,\n    updateat bigint,\n    deleteat bigint,\n    displayname VARCHAR(64),\n    name VARCHAR(64),\n    description VARCHAR(255),\n    email VARCHAR(128),\n    type VARCHAR(255),\n    companyname VARCHAR(64),\n    alloweddomains VARCHAR(1000),\n    inviteid VARCHAR(32),\n    schemeid VARCHAR(26),\n    UNIQUE(name)\n);\n\nCREATE INDEX IF NOT EXISTS idx_teams_name ON teams (name) ;\nCREATE INDEX IF NOT EXISTS idx_teams_invite_id ON teams (inviteid);\nCREATE INDEX IF NOT EXISTS idx_teams_update_at ON teams (updateat);\nCREATE INDEX IF NOT EXISTS idx_teams_create_at ON teams (createat);\nCREATE INDEX IF NOT EXISTS idx_teams_delete_at ON teams (deleteat);\nCREATE INDEX IF NOT EXISTS idx_teams_scheme_id ON teams (schemeid);\n\nALTER TABLE teams ADD COLUMN IF NOT EXISTS allowopeninvite boolean;\nALTER TABLE teams ADD COLUMN IF NOT EXISTS lastteamiconupdate bigint;\nALTER TABLE teams ADD COLUMN IF NOT EXISTS description VARCHAR(255);\nALTER TABLE teams ADD COLUMN IF NOT EXISTS groupconstrained boolean;\n (details: pq: syntax error at or near \"NOT\")"}

From what I can tell, migrations is trying to update our database and the command “NOT” is not available at PostgreSQL v9.6.

Question is, should I upgrade our postgres instance before I try to update our mattermost server? Or is it a issue with the script? (I highly doubt the second option but we never know).

By the way: I checked the script and the database, it all seems in order.

2

You should upgrade to at least PostgreSQL version 10.x (older versions are no longer supported Important Upgrade Notes — Mattermost 5.34 documentation) and then you can follow the suggestions below to fix the error.

Connect to your postgresql instance and delete the data in the migrations table like: DELETE FROM schema_migrations;

Then the server will be able to boot normally.

We have a ticket to address it further: [MM-35030] Move s.sqlStore.GetDbVersion(true) before creating store - Mattermost

2 Likes
3

Worked like a charm. Thanks a lot!