[Solved] Fail to upgrade from 6.3 to 6.4

Troubleshooting
1

Summary
Fail to start mattermost service when upgrade from 6.3 to 6.4

Steps to reproduce
Fatal error occurred when execute systemctl start mattermost.
Log shows some SQL error.

Expected behavior
Should complete schema migration when starting mattermost.

Observed behavior

  1. At first I was trying to leap upgrade from 6.3 to 7.1 but failed in schema migration.
  2. Try to upgrade from 6.3 to 6.4 but still failed in schema migration.
  3. Back to 6.3. It’s working fine. So it’s not the issues with connecting database.(dbname, username, host, password…etc.)
  4. The error reports when upgrade from 6.3 to 6.4 are:
{
 "timestamp":"2023-01-30 22:58:51.538 +08:00",
 "level":"fatal",
 "msg":"Failed to apply database migrations.",
 "caller":"sqlstore/store.go:157",
 "error":"
   driver: mysql, 
   message: failed when applying migration, 
   command: apply_migration, 
   originalError: Error 1304: PROCEDURE RenameSolarizedThemeWithUnderscore already exists, 
   query: 
		//very long SQL command."
}
  1. Try to drop procedure RenameSolarizedThemeWithUnderscore in database but result shows 0 rows affected.
  2. Still get the same error report from upgrading to 6.4
  3. Change database collation to utf8mb4_general_ci and grant all privileges to mmuser as Important Upgrade Notes said on upgrading to 6.4
  4. Still get the same error report from upgrading to 6.4 (now working fine at 6.3)

All suggestions are welcome.

2

Hi @typingmonk ,

6.3 → 7.1 should work without issues, but there might be a problem with your current 6.3 DB scheme already.
When you open the About dialog of your server, what database schema version do you see? You can also check that directly in the database:

select max(Version) from db_migrations;

6.3 should be on DB Schema version 3:

Also please run the following command to verify that there are no hidden procedures in your database anymore:

mysqldump --no-data  --routines <yourdbname> | grep PROCE
3

Hi @agriesser,

I just found out that I was installing Enterprise Edition not Team Edition I want. But still encounter same error message after correcting it.

When you open the About dialog of your server, what database schema version do you see?

Mattermost Version: 6.3.10
Database Schema Version: 6.3.10
Database: mysql

You can also check that directly in the database:

mysql> use mattermost
mysql> select max(Version) from db_migrations;
+--------------+
| max(Version) |
+--------------+
|           25 |
+--------------+
1 row in set (0.00 sec)

Also please run the following command to verify that there are no hidden procedures in your database anymore

mysqldump --no-data  --routines mattermost | grep PROCE

/*!50003 DROP PROCEDURE IF EXISTS `RenameSolarizedThemeWithUnderscore` */;
CREATE DEFINER=`mmuser`@`localhost` PROCEDURE `RenameSolarizedThemeWithUnderscore`()

It seems that there is still hidden procedure RenameSolarizedThemeWithUnderscore in my db.
How to I drop it properly or is there any other thing I should do first?

1 Like
4

Dear @agriesser,

I just resolved the db schema migration issued. Thanks for your rich information which help me to get much more clear view of the issue.

Here are the steps I did that fix the issue:

  1. Back to 6.3 which is working version for me and make sure there are no hidden procedures in my db.
  2. Upgrade to 7.1 to reproduce the very first migration issue.
  3. Checking hidden procedures again with the following command which catch the procedure.
mysqldump --no-data  --routines <yourdbname> | grep PROCE

output:
/*!50003 DROP PROCEDURE IF EXISTS `RenameSolarizedThemeWithUnderscore` */;
CREATE DEFINER=`mmuser`@`localhost` PROCEDURE `RenameSolarizedThemeWithUnderscore`()
  1. Drop the procedure in the database
$ mysql -u root
mysql> use mattermost
mysql> DROP PROCEDURE RenameSolarizedThemeWithUnderscore;
Query OK, 0 rows affected (0.02 sec)
  1. Start the mattermost service again and it’s working in version 7.1 smoothly.
5

Awesome, that’s great to hear! I find it interesting though that you’ve been on database schema version 25 earlier, did you maybe downgrade again without also reverting the database? The blocking procedure in question is part of DB schema version 26 which only comes with 6.4.0:

Waht’s your current DB schema version? With 7.1.0 it should be 89 and if it’s not, we need to fix that.

6

To @agriesser:

After a day of trying. I failed to reproduce the very first error from my mattermost db backup.
It become upgrading with no pains in my test linux machine. :person_facepalming:
It would better if there is another command to DROP the procedure before CREATE and CALL it.
There will be not need to add the command unless this kind of error happened over and over again.

What’s your current DB schema version? With 7.1.0 it should be 89 and if it’s not, we need to fix that.

Mattermost version: 7.1.5
DB schema version: 95
7

Isn’t that always the case with errors you want to reproduce? :slight_smile:

Schema 95 is perfect for 7.1.5 and yes, future db migrations usually use a CREATE PROCEDURE ... IF NOT EXISTS approach, this is an older migration that obviously doesn’t have this.

1 Like