SQL error when upgrading from 6.0.4 to 6.7.2

Troubleshooting
1

For feature requests, please see: https://portal.productboard.com/mattermost/33-what-matters-to-you.

For troubleshooting questions, please post in the following format:

Summary
Mattermost service fails to start after upgrade with a sql error

Steps to reproduce
upgrading from 6.0.4 to 6.7.2 in order to keep upgrading to meet compliance

Expected behavior
ran the upgrade steps and mattermost service won’t start

– Unit mattermost.service has begun starting up.
Jan 26 04:29:26 ip-172-31-39-85 mattermost[24349]: {“timestamp”:“2023-01-26 04:29:26.122 Z”,“level”:“info”,“msg”:“Server is initializing…”,“caller”:“app/server.go:262”,“go_version”:“go1.18.1”}
Jan 26 04:29:26 ip-172-31-39-85 mattermost[24349]: {“timestamp”:“2023-01-26 04:29:26.125 Z”,“level”:“info”,“msg”:“Pinging SQL”,“caller”:“sqlstore/store.go:226”,“database”:“master”}
Jan 26 04:29:26 ip-172-31-39-85 mattermost[24349]: {“timestamp”:“2023-01-26 04:29:26.150 Z”,“level”:“info”,“msg”:“Pinging SQL”,“caller”:“sqlstore/store.go:226”,“database”:“master”}
Jan 26 04:29:26 ip-172-31-39-85 mattermost[24349]: {“timestamp”:“2023-01-26 04:29:26.175 Z”,“level”:“fatal”,“msg”:“Failed to apply database migrations.”,“caller”:“sqlstore/store.go:163”,“error”:"driver: mysql, mess
Jan 26 04:29:26 ip-172-31-39-85 systemd[1]: mattermost.service: Main process exited, code=exited, status=1/FAILURE
Jan 26 04:29:26 ip-172-31-39-85 systemd[1]: mattermost.service: Failed with result ‘exit-code’.
Jan 26 04:29:26 ip-172-31-39-85 systemd[1]: Failed to start Mattermost.
– Subject: Unit mattermost.service has failed
– Defined-By: systemd
– Support: Enterprise open source support | Ubuntu

– Unit mattermost.service has failed.

– The result is RESULT.

Trying not to restore as it’s on AWS

2

Hi @will.graham and welcome to the Mattermost forums!

Can you please have a look at your Mattermost logfile? The default location is /opt/mattermost/logs/mattermost.log. It might contain some additional hints that would help us to idenfity the reason for the issue you’re seeing here.

3

{“timestamp”:“2023-01-26 04:41:34.802 Z”,“level”:“info”,“msg”:“Server is initializing…”,“caller”:“app/server.go:262”,“go_version”:“go1.18.1”}
{“timestamp”:“2023-01-26 04:41:34.803 Z”,“level”:“info”,“msg”:“Pinging SQL”,“caller”:“sqlstore/store.go:226”,“database”:“master”}
{“timestamp”:“2023-01-26 04:41:34.829 Z”,“level”:“info”,“msg”:“Pinging SQL”,“caller”:“sqlstore/store.go:226”,“database”:“master”}
{“timestamp”:“2023-01-26 04:41:34.866 Z”,“level”:“fatal”,“msg”:“Failed to apply database migrations.”,“caller”:“sqlstore/store.go:163”,“error”:“driver: mysql, message: failed when applying migration, command: apply_migration, originalError: Error 1304: PROCEDURE Migrate_LastRootPostAt already exists, query: \n\nCREATE PROCEDURE Migrate_LastRootPostAt ()\nBEGIN\nDECLARE\n\tLastRootPostAt_EXIST INT;\n\tSELECT\n\t\tCOUNT(*)\n\tFROM\n\t\tINFORMATION_SCHEMA.COLUMNS\n\tWHERE\n\t\tTABLE_NAME = ‘Channels’\n\t\tAND table_schema = DATABASE()\n\t\tAND COLUMN_NAME = ‘LastRootPostAt’ INTO LastRootPostAt_EXIST;\n\tIF(LastRootPostAt_EXIST = 0) THEN\n ALTER TABLE Channels ADD COLUMN LastRootPostAt bigint DEFAULT 0;\n\t\tUPDATE\n\t\t\tChannels\n\t\t\tINNER JOIN (\n\t\t\t\tSELECT\n\t\t\t\t\tChannels.Id channelid,\n\t\t\t\t\tCOALESCE(MAX(Posts.CreateAt), 0) AS lastrootpost\n\t\t\t\tFROM\n\t\t\t\t\tChannels\n\t\t\t\t\tLEFT JOIN Posts FORCE INDEX (idx_posts_channel_id_update_at) ON Channels.Id = Posts.ChannelId\n\t\t\t\tWHERE\n\t\t\t\t\tPosts.RootId = ‘’\n\t\t\t\tGROUP BY\n\t\t\t\t\tChannels.Id) AS q ON q.channelid = Channels.Id SET LastRootPostAt = lastrootpost;\n\tEND IF;\nEND;\n\tCALL Migrate_LastRootPostAt ();\n\tDROP PROCEDURE IF EXISTS Migrate_LastRootPostAt;\n\n”}

4

thanks for replying so quickly btw Alexander

5

Alright, thanks - I’m not 100% sure I can read that out of the logs, but is that a MySQL or PostgreSQL deployment you’re having here?

Can you please connect to your Mattermost database and run the following command:

DROP PROCEDURE IF EXISTS Migrate_LastRootPostAt;

Then try to start the server again and see if it works now. If not, please look at your mattermost.log file again and share the last few lines again.

6

it’s mysql. FYI this command worked and you saved my sanity, if not my job. So thank you very much for your help!

7

You’re welcome - glad to hear it’s up and running again :slight_smile: