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

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]

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

[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.

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)

You mean I shouldn't be paying for a beta of a software? Absolutely agree. Backups? I have them. Reverted immediately and cancelled my subscription. Thanks for the valuable input.

[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)

You mean I shouldn't be paying for a beta of a software? Absolutely agree. Backups? I have them. Reverted immediately and cancelled my subscription. Thanks for the valuable input.

Last time I checked you didn't have to pay for bitwarden unified. And if you did, $10 a year is well worth the value for the features it provides. Bitwarden is by far the best password manager on the market for cost and use ability. What other companies allow you to self host their software in a container??? It's the best even with the headaches of bugs here and there. Try signing up for LastPass... they have a stellar security record......

[shofutex]

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.

2024.10.1 is functional for me, so I left it there.

[iLeonidze]

Same here, after upgrade to latest version my installation is completely broken

[dmalanij]

+1 for me. Today after updating to the latest bitwarden/selfhost:beta (the one published last week, digest: sha256:4e7dff1d79db5d273b39dc8dc3d0a3eff86a706142538f84342c2b15690e8164) it didn't come back up.

After fiddling a bit was able to go back to the previous version (that is 2024.9.2-beta matching the digest sha256:8d90075358899c5d35e6210ef41a18428533f81d44edb17e860b04d4294f3d37). I had also some mariadb-upgrade pending, but I'm not sure how did that affect (if it did at all).

In the logs found the same migration crash described by @LXGaming, and that's how I ended up in this issue.

[turnah]

+1 for me. Today after updating to the latest bitwarden/selfhost:beta (the one published last week, digest: sha256:4e7dff1d79db5d273b39dc8dc3d0a3eff86a706142538f84342c2b15690e8164) it didn't come back up.

After fiddling a bit was able to go back to the previous version (that is 2024.9.2-beta matching the digest sha256:8d90075358899c5d35e6210ef41a18428533f81d44edb17e860b04d4294f3d37). I had also some mariadb-upgrade pending, but I'm not sure how did that affect (if it did at all).

In the logs found the same migration crash described by @LXGaming, and that's how I ended up in this issue.

Confirmed, i went back through the versions - all had the error until i got to 2024.9.2-beta

[danepowell]

I'm also seeing the crashes in the Docker container logs on every version after 2024.9.2. But it was only after updating from 2024.10.1 to 2024.10.2 that I was unable to log into Bitwarden any more, and rolling back fixed that.

Did updating to 2024.10.2 trigger some irreparable bug / db corruption? I'm trying to understand how these issues could be related.

[RamonAbudAlcala]

Thanks, just tried updating to the rolling beta tag, can confirm this works.

[dmalanij]

Same here, just tried with the latest beta tag (that's 2024.11.0-beta) and worked fine.

Thanks everyone!