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

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

爱旅游的盒饭 · 北京大学研究生招生网· 2 月前 ·

忐忑的铁板烧 · 被岳飞吊打的金兀术真的是弱鸡吗？他是怎样看待 ...· 4 月前 ·

痴情的啄木鸟 · The Only ...· 4 月前 ·

聪明伶俐的电影票 · 张扣扣案出庭检察员意见书_中华人民共和国最高 ...· 7 月前 ·

小眼睛的西装 · 新冠肺炎4大疫苗種類比較！成分、保護力一次了 ...· 11 月前 ·

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 ?

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 |

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

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'
    'SELECT 1',
    'ALTER TABLE Users ADD Timezone varchar(256) DEFAULT \'{"automaticTimezone":"","manualTimezone":"","useAutomaticTimezone":"true"}\';'
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;
              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
              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?