MySqlMigration is incompatible with MariaDB

LXGaming commented 1 week ago

Steps To Reproduce

Create a new unified instance (https://bitwarden.com/help/install-and-deploy-unified-beta/#using-docker-compose)
Wait about 1 minute

Expected Result

The admin process shouldn't be constantly crashing. A separate issue is that it should stop attempting to restart it after several failed attempts.

Actual Result

The admin process in the Bitwarden container repeatedly crashes, I've attached the error from the admin.log in the Additional Context.

2024-10-11 22:22:16,198 WARN exited: admin (terminated by SIGABRT (core dumped); not expected)
2024-10-11 22:22:17,200 INFO spawned: 'admin' with pid 359
2024-10-11 22:22:32,219 INFO success: admin entered RUNNING state, process has stayed up for > than 15 seconds (startsecs)

Screenshots or Videos

No response

Additional Context

Doing a quick search it would appear that this is the problematic migration 2024-09-05_00_SyncDuoVersionFourMetadataToVersionTwo.sql

admin.log

2024-10-11 22:21:59.723 +00:00 [INF] Migrating database.
2024-10-11 22:22:12.015 +00:00 [ERR] Failed executing DbCommand (3ms) [Parameters=[], CommandType='"Text"', CommandTimeout='30']
/* Update User Table */
UPDATE
    `User` U
SET
    U.TwoFactorProviders = JSON_SET(
        JSON_SET(
            U.TwoFactorProviders, '$."2".MetaData.ClientSecret',
    JSON_UNQUOTE(U.TwoFactorProviders ->'$."2".MetaData.SKey')),
    '$."2".MetaData.ClientId',
    JSON_UNQUOTE(U.TwoFactorProviders -> '$."2".MetaData.IKey'))
WHERE
    JSON_CONTAINS(TwoFactorProviders,
    '{"2":{}}')
    AND JSON_VALID(TwoFactorProviders);

/* Update Organization Table */
UPDATE
    Organization o
SET
    o.TwoFactorProviders = JSON_SET(
        JSON_SET(
            o.TwoFactorProviders, '$."6".MetaData.ClientSecret',
    JSON_UNQUOTE(o.TwoFactorProviders ->'$."6".MetaData.SKey')),
    '$."6".MetaData.ClientId',
    JSON_UNQUOTE(o.TwoFactorProviders -> '$."6".MetaData.IKey'))
WHERE
    JSON_CONTAINS(o.TwoFactorProviders,
    '{"6":{}}')
    AND JSON_VALID(o.TwoFactorProviders);
2024-10-11 22:22:12.035 +00:00 [ERR] Hosting failed to start
MySqlConnector.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$."2".MetaData.SKey')),
    '$."2".MetaData.ClientId',
    JSON_UNQUOTE(U.TwoFact...' at line 8
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/ServerSession.cs:line 894
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 37
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 130
   at MySqlConnector.MySqlDataReader.InitAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 483
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
   at MySqlConnector.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 309
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
   at Bit.MySqlMigrations.MySqlDbMigrator.MigrateDatabase(Boolean enableLogging, CancellationToken cancellationToken) in /source/util/MySqlMigrations/MySqlDbMigrator.cs:line 30
   at Bit.Admin.HostedServices.DatabaseMigrationHostedService.StartAsync(CancellationToken cancellationToken) in /source/src/Admin/HostedServices/DatabaseMigrationHostedService.cs:line 29
   at Microsoft.Extensions.Hosting.Internal.Host.<StartAsync>b__15_1(IHostedService service, CancellationToken token)
   at Microsoft.Extensions.Hosting.Internal.Host.ForeachService[T](IEnumerable`1 services, CancellationToken token, Boolean concurrent, Boolean abortOnFirstException, List`1 exceptions, Func`3 operation)

Githash Version

ad8aec97-dirty

Environment Details

Desktop:

Operating system: Windows 11
Environment: Docker Desktop v27.2.0
Hardware: AMD Ryzen 9 5950X, 32GB RAM

Server:

Operating system: Ubuntu 24.04.1
Environment: Docker v27.3.1
Hardware: AMD Ryzen 5 3600, 64GB RAM

Database Image

mariadb:10

Issue-Link

https://github.com/bitwarden/server/issues/2480

Issue Tracking Info

[X] I understand that work is tracked outside of Github. A PR will be linked to this issue should one be opened to address it, but Bitwarden doesn't use fields like "assigned", "milestone", or "project" to track progress.

fer commented 6 days ago

Hi there,

Thank you for your report!

I was able to reproduce this issue, and I have flagged this to our engineering team.

If you wish to add any further information/screenshots/recordings etc., please feel free to do so at any time - our engineering team will be happy to review these.

Thanks once again!

MokkaSchnalle commented 5 days ago

same here. Does this fail only if there is no row found containing JSON data with "2" (User) or "6" (Org)?

Tried Using JSON_EXTRACT instead of -> changing query to JSON_UNQUOTE(JSON_EXTRACT(U.TwoFactorProviders... worked but I cannot reliably test the Duo function.

MrCoala commented 19 hours ago

I have the same issue

2024-10-21 16:35:36.943 +00:00 [INF] Migrating database. 2024-10-21 16:35:38.553 +00:00 [ERR] Failed executing DbCommand (2ms) [Parameters=[], CommandType='"Text"', CommandTimeout='30'] / Update User Table / UPDATE User U SET U.TwoFactorProviders = JSON_SET( JSON_SET( U.TwoFactorProviders, '$."2".MetaData.ClientSecret', JSON_UNQUOTE(U.TwoFactorProviders ->'$."2".MetaData.SKey')), '$."2".MetaData.ClientId', JSON_UNQUOTE(U.TwoFactorProviders -> '$."2".MetaData.IKey')) WHERE JSON_CONTAINS(TwoFactorProviders, '{"2":{}}') AND JSON_VALID(TwoFactorProviders);

/ Update Organization Table / UPDATE Organization o SET o.TwoFactorProviders = JSON_SET( JSON_SET( o.TwoFactorProviders, '$."6".MetaData.ClientSecret', JSON_UNQUOTE(o.TwoFactorProviders ->'$."6".MetaData.SKey')), '$."6".MetaData.ClientId', JSON_UNQUOTE(o.TwoFactorProviders -> '$."6".MetaData.IKey')) WHERE JSON_CONTAINS(o.TwoFactorProviders, '{"6":{}}') AND JSON_VALID(o.TwoFactorProviders); 2024-10-21 16:35:38.572 +00:00 [ERR] Hosting failed to start MySqlConnector.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$."2".MetaData.SKey')), '$."2".MetaData.ClientId', JSONUNQUOTE(U.TwoFact...' at line 8 at MySqlConnector.Core.ServerSession.ReceiveReplyAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in //src/MySqlConnector/Core/ServerSession.cs:line 894 at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in //src/MySqlConnector/Core/ResultSet.cs:line 37 at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in //src/MySqlConnector/MySqlDataReader.cs:line 130 at MySqlConnector.MySqlDataReader.InitAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 483 at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56 at MySqlConnector.MySqlCommand.ExecuteNonQueryAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 309 at MySqlConnector.MySqlCommand.ExecuteNonQuery() in /_/src/MySqlConnector/MySqlCommand.cs:line 108 at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable1 migrationCommands, IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration) at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade) at Bit.MySqlMigrations.MySqlDbMigrator.MigrateDatabase(Boolean enableLogging, CancellationToken cancellationToken) in /source/util/MySqlMigrations/MySqlDbMigrator.cs:line 30 at Bit.Admin.HostedServices.DatabaseMigrationHostedService.StartAsync(CancellationToken cancellationToken) in /source/src/Admin/HostedServices/DatabaseMigrationHostedService.cs:line 29 at Microsoft.Extensions.Hosting.Internal.Host.<StartAsync>b__15_1(IHostedService service, CancellationToken token) at Microsoft.Extensions.Hosting.Internal.Host.ForeachService[T](IEnumerable1 services, CancellationToken token, Boolean concurrent, Boolean abortOnFirstException, List1 exceptions, Func3 operation) 2024-10-21 16:36:02.743 +00:00 [INF] Migrating database.

i switched to the dev branch of self hosted docker container but the problem still persists. Is there any fix right now?

RamonAbudAlcala commented 6 hours ago

This problem appeared for me today. I am (was) running the latest tag on the self-hosted unified docker container (updated 6 hours ago on dockerhub at the time of writing), with mariadb. Reverting to 2024.10.1-beta made my container work again.

bitwarden / server