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.”}
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?
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?
Hello. I am trying to update mattermost version 5.33.5 to version 5.37.6, I do all the steps indicated in the instructions, but I get this error:
Try to follow this forum where they say that you can delete the scheme_migrations table to clean the dirty database but it does not work, the table once given the DROP instruction is deleted but a few seconds later it reappears it is never deleted.
Hi @racso - The solution is not “just” to delete the schema_migrations table. You need to upgrade to Postgres 10+, delete the schema_migrations table, and then restart Mattermost.
The misleading error message is also fixed in a later version.
Hello. The current version of postgresql is 10.19, so try to delete the SCHEMA_MIGRATIONS table as indicated in the forum, however, after deleting it, it appears again, it is never deleted. According to what I could read this table verifies that there are no compatibility errors between databases, but as I mentioned I do not know how to do to eliminate it as you mentioned.
This error is the one that I get when I try to upload the mattermost service
Hi @racso - Can you clarify what do you mean when you say “however, after deleting it, it appears again,”?
Does it appear automatically even when Mattermost is not running? You mentioned that it reappears after a few seconds. But Mattermost was not running during that time, correct?
Hello. What I’m trying to say is that even though I DROP that table, it deletes it, but a few seconds later it comes back and that table appears or is created again automatically. When you say that Mattermost was not running, what do you mean? you mean that the service must be down or the postgres must be down?
By “Mattermost was not running” - I meant that the mattermost.service was down. If Postgres is down, nothing can create a table anyways.
What I’d like to confirm is that when you drop the table, confirm that the Mattermost service is stopped. Then check if it is created again automatically.
