Mm upgrade to 7.3.0 failing with SQL error

Troubleshooting
1

Summary
mattermost upgrade 6.4.2 to 7.3.0 fails with Error 1064 on the k8 cluster using external HA MySQL 8 cluster

Steps to reproduce
upgrade MM 6.4.2 to 7.3.0 on k8 cluster using external MysqlV8 DB

Expected behavior
successful upgrade

Observed behavior

2022/10/11 11:54:18 ==  update_configurations_sha: migrating  ==========================================================
Error: failed to load configuration: failed to initialize: driver: mysql, message: failed when applying migration, command: apply_migration, originalError: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '""' at line 1, query: 

SET @preparedStatement = (SELECT IF(
    (
        SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = 'Configurations'
        AND table_schema = DATABASE()
        AND column_name = 'SHA'
    ) > 0,
    'SELECT 1',
    'ALTER TABLE Configurations ADD COLUMN SHA char(64) DEFAULT "";'
));

PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;
2

Hi mscs2946 and welcome to the Mattermost forums,

can you please post the output of the following SQL query?

show create table Configurations;
3

Thanks, @agriesser! Running the query show create table Configurations; on MySQL Workbench.

chinyasuhail-2022-10-11-14.40.04@2x
chinyasuhail-2022-10-11-14.40.04@2x1998×1414 152 KB

4

Looks like you have ANSI_QUOTES enabled, which is not supported by the DB migration script.
Is there any specific reason why you have that enabled?

There are several ways to work around this issue now:

  • Disable ANSI_QUOTES globally
  • Manually run the migration with a modified query
ALTER TABLE Configurations ADD COLUMN SHA char(64) DEFAULT '';
  • Find out how to disable ANSI_QUOTES only for the mattermost connections to this DB server (if it hosts other applications too)
5

can you help me with how I can disable ANSI_QUOTES globally? I use digitalocean managed SQL cluster.

6

Your sql_mode has ANSI_QUOTES enabled, you should see it by running:

show variables like 'sql_mode';

I don’t know how a managed digitalocean SQL cluster works, but if it is managed, ask them to turn it off :slight_smile: There also might be a webinterface where you can configure the sql_mode or some other settings. I can send you the command to fix it temporarily (until the next restart of the service), but that will not help you in the long run, so it’s better to look for a permanent solution.

I found this documentation, though, maybe that helps.

7

Wow! This worked! thanks @agriesser