MySqlMigration is incompatible with MariaDB

[LXGaming]

Steps To Reproduce

1. Create a new unified instance (https://bitwarden.com/help/install-and-deploy-unified-beta/#using-docker-compose)
2. 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

ad8aec9-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

#2480

Issue Tracking Info

• 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]

Hi there,

Thank you for your report!

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]

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]

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',
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, 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]

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.

[7djs7]

Having the same issue. Appeared today. I'm on latest tag (beta) which i think is 2024.10.2. Going to revert back to 2024.10.1-beta until this is fixed.

[kociubin]

Same issue. Everything was working fine until today when my container was automatically updated. I reverted to 2024.10.1 and everything is working again.

[rlandolt]

Same for me

[imightbelosthere]

Amazing stuff! Thank you @RamonAbudAlcala for pointing out the revert back to a previous version.
This issue hit me today as well and boy am I glad I have a copy of my password DB on another platform.

[shofutex]

In my case, after reverting, I continue to receive the error in the logs. My admin.log is growing extremely fast.

[imightbelosthere]

In my case, after reverting, I continue to receive the error in the logs. My admin.log is growing extremely fast.

Yep. I can confirm it's still doing the same error for me too... I think I spoke too fast.

Second time I get barred from accessing my passwords... Last time for me. Pulling out from the subscription and going back to my previous password manager solution. I couldn't use it with my mobile browser app, but at least I never got barred from accessing my passwords.

[LXGaming]

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

@MokkaSchnalle I'm assuming it doesn't matter as it's an SQL syntax error, MariaDB cannot understand the SQL provided so it can't lookup the data to begin with.

---

In my case, after reverting, I continue to receive the error in the logs. My admin.log is growing extremely fast.

@shofutex Try 2024.9.2-beta, that's the version I reverted to. The problematic migration with introduced in v2024.10.0 so I don't understand how others are reporting that 2024.10.x versions are resolving this issue.

[7djs7]

In my case, after reverting, I continue to receive the error in the logs. My admin.log is growing extremely fast.

Yep. I can confirm it's still doing the same error for me too... I think I spoke too fast.

Second time I get barred from accessing my passwords... Last time for me. Pulling out from the subscription and going back to my previous password manager solution. I couldn't use it with my mobile browser app, but at least I never got barred from accessing my passwords.

You should have a backup solution. The unified solution is only beta. This is not the first time my deployment has had bugs that leave it in operable. I use keepassxc for backup solution because the instability. Or you can use the public hosting server (vault.bitwarden.com)

[7djs7]

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

@MokkaSchnalle I'm assuming it doesn't matter as it's an SQL syntax error, MariaDB cannot understand the SQL provided so it can't lookup the data to begin with.

In my case, after reverting, I continue to receive the error in the logs. My admin.log is growing extremely fast.

@shofutex Try 2024.9.2-beta, that's the version I reverted to. The problematic migration with introduced in v2024.10.0 so I don't understand how others are reporting that 2024.10.x versions are resolving this issue.

My deployment is working just fine on 2024.10.1-beta