dB migration failed for a new installation (team édition 7.5.2)

Troubleshooting
1

Summary
Can’t deploy mattermost 7.5.2 team édition using helm

Steps to reproduce
Try to deploy mattermost by launching the container

Expected behavior
Mattermost is appliying dB migration and start

Observed behavior
Mattermost failed to do a migration. There is the log error :

{“timestamp”:“2022-12-28 09:32:42.402 Z”,“level”:“debug”,“msg”:“morph.go:175: == create_users: migrating =======================================================================\n”,“caller”:“sqlstore/utils.go:167”}

{“timestamp”:“2022-12-28 09:32:42.453 Z”,“level”:“fatal”,“msg”:“Failed to apply database migrations.”,“caller”:“sqlstore/store.go:166”,“error”:"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 ‘{"automaticTimezone":"","manualTimezone":"","useAutomaticTimezone":"true"}\’;‘\n)’ at line 9, query: \n\nCREATE TABLE IF NOT EXISTS Users (\n Id varchar(26) NOT NULL,\n CreateAt bigint(20) DEFAULT NULL,\n UpdateAt bigint(20) DEFAULT NULL,\n DeleteAt bigint(20) DEFAULT NULL,\n Username varchar(64) DEFAULT NULL,\n Password varchar(128) DEFAULT NULL,\n AuthData varchar(128) DEFAULT NULL,\n AuthService varchar(32) DEFAULT NULL,\n Email varchar(128) DEFAULT NULL,\n EmailVerified tinyint(1),\n Nickname varchar(64) DEFAULT NULL,\n FirstName varchar(64) DEFAULT NULL,\n LastName varc…

The db is a MySQL dB in version 8.0.31 that should be supported by mattermost.

The user used by mattermost to log into the db is owner.

Can someone help me please ?

2

Hi @Pierre67

Can you confirm that you don’t have ANSI_QUOTES enabled in your SQL_MODE?

3

Hello @agnivade

Thanks you for your answer.

It looks like ANSI_QUOTES is disabled.

SELECT @@GLOBAL.sql_mode;

±-------------------------------------------------------------------------------------------------------------------------------------------+

| @@GLOBAL.sql_mode |

±-------------------------------------------------------------------------------------------------------------------------------------------+

| ONLY_FULL_GROUP_BY,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |

±-------------------------------------------------------------------------------------------------------------------------------------------+

4

Thanks, I spun up a MySQL 8.0.31 locally but could not reproduce your issue.

Could you try these steps manually in a separate test DB and see what results you get?

CREATE TABLE IF NOT EXISTS Users (
    Id varchar(26) NOT NULL,
    CreateAt bigint(20) DEFAULT NULL,
    UpdateAt bigint(20) DEFAULT NULL,
    DeleteAt bigint(20) DEFAULT NULL,
    Username varchar(64) DEFAULT NULL,
    Password varchar(128) DEFAULT NULL,
    AuthData varchar(128) DEFAULT NULL,
    AuthService varchar(32) DEFAULT NULL,
    Email varchar(128) DEFAULT NULL,
    EmailVerified tinyint(1),
    Nickname varchar(64) DEFAULT NULL,
    FirstName varchar(64) DEFAULT NULL,
    LastName varchar(64) DEFAULT NULL,
    Roles varchar(256) DEFAULT NULL,
    AllowMarketing tinyint(1),
    Props text,
    NotifyProps text,
    LastPasswordUpdate bigint(20) DEFAULT NULL,
    LastPictureUpdate bigint(20) DEFAULT NULL,
    FailedAttempts integer,
    Locale varchar(5) DEFAULT NULL,
    MfaActive tinyint(1),
    MfaSecret varchar(128) DEFAULT NULL,
    PRIMARY KEY (Id),
    UNIQUE KEY Username (Username),
    UNIQUE KEY AuthData (AuthData),
    UNIQUE KEY Email (Email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


SET @preparedStatement = (SELECT IF(
    (
        SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = 'Users'
        AND table_schema = DATABASE()
        AND column_name = 'Timezone'
    ) > 0,
    'SELECT 1',
    'ALTER TABLE Users ADD Timezone varchar(256) DEFAULT \'{"automaticTimezone":"","manualTimezone":"","useAutomaticTimezone":"true"}\';'
));

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

Hello @agnivade and happy new year !

The first request ended without errors but with 8 warnings

 CREATE TABLE IF NOT EXISTS Users (
    ->  Id varchar(26) NOT NULL,
    ->  CreateAt bigint(20) DEFAULT NULL,
    ->  UpdateAt bigint(20) DEFAULT NULL,
    ->  DeleteAt bigint(20) DEFAULT NULL,
    ->  Username varchar(64) DEFAULT NULL,
    ->  Password varchar(128) DEFAULT NULL,
    ->  AuthData varchar(128) DEFAULT NULL,
    ->  AuthService varchar(32) DEFAULT NULL,
    ->  Email varchar(128) DEFAULT NULL,
    ->  EmailVerified tinyint(1),
    ->  Nickname varchar(64) DEFAULT NULL,
    ->  FirstName varchar(64) DEFAULT NULL,
    ->  LastName varchar(64) DEFAULT NULL,
    ->  Roles varchar(256) DEFAULT NULL,
    ->  AllowMarketing tinyint(1),
    ->  Props text,
    ->  NotifyProps text,
    ->  LastPasswordUpdate bigint(20) DEFAULT NULL,
    ->  LastPictureUpdate bigint(20) DEFAULT NULL,
    ->  FailedAttempts integer,
    ->  Locale varchar(5) DEFAULT NULL,
    ->  MfaActive tinyint(1),
    ->  MfaSecret varchar(128) DEFAULT NULL,
    ->  PRIMARY KEY (Id),
    ->  UNIQUE KEY Username (Username),
    ->  UNIQUE KEY AuthData (AuthData),
    ->  UNIQUE KEY Email (Email)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected, 8 warnings (0.03 sec)

The second one generated an error that looks to be the one that made the migration failed.

SET @preparedStatement = (SELECT IF(
    ->  (
    ->  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    ->  WHERE table_name = 'Users'
    ->  AND table_schema = DATABASE()
    ->  AND column_name = 'Timezone'
    ->  ) > 0,
    ->  'SELECT 1',
    ->  'ALTER TABLE Users ADD Timezone varchar(256) DEFAULT \'{"automaticTimezone":"","manualTimezone":"","useAutomaticTimezone":"true"}\';'
ERROR:
Unknown command '\''.
ERROR 1064 (42000): 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 '{"automaticTimezone":"","manualTimezone":"","useAutomaticTimezone":"true"}\'' at line 9
6

Hello @Pierre67 and happy new year to you as well!

Ok I can see that MySQL is throwing Unknown command '\''. I have the exact same version 8.0.31 locally and the query works for me. Searching for this on the internet is showing some solutions around setting charset to utf8 and corrupted databases. I am not really sure what to make of this.

What does SELECT 'hello \' world'; show for you?

7

Aha I figured it out!

It was NO_BACKSLASH_ESCAPES in your SQL_MODE. Please remove that and things should start working again :slight_smile:

8

IMHO the server should check for these two SQL modes and alert when they’re set (or overwrite them for the session, if possible).

9

Agreed. There’s just too many things to check :slight_smile: We added a check for collation in the past.

10

It worked ! Thank you so much.