We're Hiring!

Mattermost, Inc.

Update from 5.28 to 5.35.2: SQL-ERROR

Hi Mattermost-Team.

I wanted to update from version 5.28 to version 5.35.2.
I used these instructions:
https://docs.mattermost.com/administration/upgrade.html?src=dl

After the upgrade, the server does not start.

Error:
mattermost: {“level”:“error”,“ts”:1623009059.2082427,“caller”:“sqlstore/store.go:179”,“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”)”}

On the second attempt to start it shows this error:
mattermost: {“level”:“error”,“ts”:1623009164.7265103,“caller”:“sqlstore/store.go:179”,“msg”:“Failed to apply database migrations.”,“error”:“Dirty database version 1. Fix and force version.”}

OS:
CentOS Linux release 7.8.2003 (Core)

DB:
Postgres 9.5

Does somebody has any idea?

Thanks for help!

Greetings
Andreas

Hello, @Santec

May I know if you have checked on the Database migration topic and verify if dropping the schema_migrations table helps you to push forward with the upgrade?

We also don’t support Postgres 9.4 anymore, and you will need to be on Postgres v10+.

1 Like

@streamer45 Anything else that should be noted here besides what Ahmad already suggested for the reporter?

Not really. As noted this error is due to an unsupported PostgreSQL version (< 10).
They should upgrade, run the query:

DELETE FROM schema_migrations;

And restarting the server should then work correctly.

Dear @streamer45, @ahmaddanial and @amy.blais .

Thank you for your help.
I will try the proposed solution in the next few days, maybe not until the weekend.

Big Thanks!

Greetings
Andreas.

1 Like

Hello,

I try to upgrade 5.29 to 5.35.2 and get the same error

{“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”)”}

My postgres version psql (PostgreSQL) 9.5.24
Is needed upgrading postgree to v10 version?