[Solved] Upgrading to version 9.10.1-0 fails on database migration

Summary
After upgrading mattermost (self hosted), the service start fails on database migration with an error message.

Steps to reproduce
Using apt-get (using mattermost server repo) to upgrade to version 9.10.1-0 from the next to last version.
Mattermost was installed following “Install Mattermost Server on Ubuntu - Mattermost documentation

MariaDB is on version: 10.3.39
System is Ubuntu 20.04 LTS.

Expected behavior
Expected restart after upgrading mattermost via apt-get.

Observed behavior
After upgrading mattermost (self hosted) to version 9.10.1-0 from the next to last version, mattermost service fails on database migration with an error message (taken from mattermost.log):

error [2024-08-01 16:17:00.164 +02:00] failed to initialize platform: cannot create store: failed to apply database migrations: driver: mysql, message: failed when applying migration, command: apply_migration, originalError: Error 1044 (42000): Access denied for user ‘mmuser’@‘localhost’ to database ‘mattermost’, query:

CREATE PROCEDURE RemoveUuploadFilePermission()
BEGIN
    updateRoles: LOOP
        -- update affected rows
        UPDATE Roles
            SET Permissions = REGEXP_REPLACE(Permissions, 'upload_file([[:space:]]|$)', '')
            WHERE Permissions like '%upload_file%' and Permissions not REGEXP 'create_post([[:space:]]|$)'
            LIMIT 100;

          -- check if the loop has completed
          IF  ROW_COUNT() < 100 THEN
              LEAVE updateRoles;
          END IF;
    END LOOP updateRoles;
END;

CALL RemoveUuploadFilePermission();
DROP PROCEDURE IF EXISTS RemoveUuploadFilePermission;

 caller="commands/server.go:76"

[End of log excerpt]

The mattermost instance was running stable before. User ‘mmuser’ does have privileges on database ‘mattermost’ - which I had re-checked on the mysql prompt. The error message may be misleading then. Does anyone have an idea what has happened and what may fix it?

Hi dfn! Thank you for reaching out and providing detailed information! It sounds like you may be encountering an issue with database permissions during the migration. You might want to double-check the mmuser permissions on the Mattermost database. Additionally, reviewing the Mattermost upgrade guide and the database setup documentation could provide more insights. Please let us know if the issue persists, and we’ll assist further!

For anyone stumbling upon, the issue has been solved by migrating the database system to PostgreSQL and re-installing Mattermost.

Woot! Thank you for following up with the solution, dfn!