latest beta mariadb error

[adamaen87]

Steps To Reproduce

I'am using bitwarden unified beta for month without problem. Since last beta there is a problem with mariadb connection.

Expected Result

everything fine :)

Actual Result

mariadb logs:
06/01/2024
17:47:24
2024-01-06 16:47:24 5 [Warning] Aborted connection 5 to db: 'bitwarden_vault' user: 'bitwarden' host: '172.29.0.4' (Got an error reading communication packets)
06/01/2024
17:47:24
2024-01-06 16:47:24 3 [Warning] Aborted connection 3 to db: 'unconnected' user: 'bitwarden' host: '172.29.0.4' (Got an error reading communication packets)
06/01/2024
17:47:47
and repeating every few seconds.
Same in bitwarden logs:
admin entered RUNNING state, process has stayed up for > than 15 seconds (startsecs)
exited: admin (terminated by SIGABRT (core dumped); not expected)

Screenshots or Videos

No response

Additional Context

I revert back to the 2023.12.0-beta image and no more error in both bitwarden and mariadb

Githash Version

b44cdc7-dirty

Environment Details

Debian 11 (OMV6 latest). Bitwarden and mariad in docker.

Database Image

mariadb:lts

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.

[JoeGTN1]

I'm getting the same issue. Here is the error in admin.log:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CALL POMELO_BEFORE_DROP_PRIMARY_KEY(NULL, 'Grant');
      ALTER TABLE `Grant` DROP PRIMARY KEY;
Unhandled exception. MySqlConnector.MySqlException (0x80004005): Illegal mix of collations (utf8mb3_general_ci,IMPLICIT) and (utf8mb3_unicode_ci,IMPLICIT) for operation '='
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 43
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 130
   at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, Activity activity, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 468
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, 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 296
   at MySqlConnector.MySqlCommand.ExecuteNonQuery() in /_/src/MySqlConnector/MySqlCommand.cs:line 107
   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(CancellationToken cancellationToken)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.RunAsync(IHost host, CancellationToken token)
   at Microsoft.Extensions.Hosting.HostingAbstractionsHostExtensions.Run(IHost host)
   at Bit.Admin.Program.Main(String[] args) in /source/src/Admin/Program.cs:line 9

[DuckThom]

I've also been running into this issue on an upgrade from 2023.12.0 to 2024.1.0.

I just posted a comment in bitwarden/self-host aswell with some info: bitwarden/self-host#79

However, it looks like there are multiple problems occurring in the Grant table migrations. I've tried to undo some of the changes the migrations were making and it would seem like the original state of the table had the primary key on another column (I'm assuming Key, but i could be wrong here).

The first migration that is failing for me is running this query:

ALTER TABLE `Grant` ADD CONSTRAINT `PK_Grant` PRIMARY KEY (`Id`);

Error: Duplicate entry '0' for key 'Grant.PRIMARY'

This is because my Grant table is filled with rows and the previous migration runs this query:

ALTER TABLE `Grant` ADD `Id` int NOT NULL DEFAULT 0;

This sets all the values for the existing rows to 0, making it impossible to be a primary key as they have to be unique.

Because of the failing migration, the admin process crashes and supervisor restarts it. However, this will not undo the migrations that were already executed. So now when it tries to re-execute them, the primary key is already gone

ALTER TABLE `Grant` DROP PRIMARY KEY;

Error: Can't DROP 'PRIMARY'; check that column/key exists

I'm still debugging some more, but this is what I have found so far. To me the issue appears to be that since there are already items in the Grant table, the migrations are not able to be executed properly since the Id is not auto-incremented when it's added to the table.

[withinfocus]

@adamaen87 can you pull the latest from main and give this a try? We made a modification to the database migration.

[adamaen87]

Hello,

So, i just give a try and still have the same problem, but this time the container take much longer to start.

Bitwarden logs:

12/01/2024
07:43:00
addgroup: The GID 100' is already in use. 12/01/2024 07:43:00 Adding user bitwarden' ...
12/01/2024
07:43:00
Adding new user bitwarden' (1002) with group users' ...
12/01/2024
07:43:00
Not creating home directory `/home/bitwarden'.
12/01/2024
07:45:10
2024-01-12 06:45:10,997 INFO Included extra file "/etc/supervisor.d/admin.ini" during parsing
12/01/2024
07:45:10
2024-01-12 06:45:10,997 INFO Included extra file "/etc/supervisor.d/api.ini" during parsing
12/01/2024
07:45:10
2024-01-12 06:45:10,997 INFO Included extra file "/etc/supervisor.d/events.ini" during parsing
12/01/2024
07:45:10
2024-01-12 06:45:10,997 INFO Included extra file "/etc/supervisor.d/icons.ini" during parsing
12/01/2024
07:45:10
2024-01-12 06:45:10,997 INFO Included extra file "/etc/supervisor.d/identity.ini" during parsing
12/01/2024
07:45:10
2024-01-12 06:45:10,997 INFO Included extra file "/etc/supervisor.d/nginx.ini" during parsing
12/01/2024
07:45:10
2024-01-12 06:45:10,997 INFO Included extra file "/etc/supervisor.d/notifications.ini" during parsing
12/01/2024
07:45:10
2024-01-12 06:45:10,997 INFO Included extra file "/etc/supervisor.d/scim.ini" during parsing
12/01/2024
07:45:10
2024-01-12 06:45:10,997 INFO Included extra file "/etc/supervisor.d/sso.ini" during parsing
12/01/2024
07:45:10
2024-01-12 06:45:10,999 INFO RPC interface 'supervisor' initialized
12/01/2024
07:45:10
2024-01-12 06:45:10,999 CRIT Server 'unix_http_server' running without any HTTP authentication checking
12/01/2024
07:45:10
2024-01-12 06:45:10,999 INFO supervisord started with pid 1
12/01/2024
07:45:12
2024-01-12 06:45:12,000 INFO spawned: 'identity' with pid 63
12/01/2024
07:45:12
2024-01-12 06:45:12,001 INFO spawned: 'admin' with pid 64
12/01/2024
07:45:12
2024-01-12 06:45:12,002 INFO spawned: 'api' with pid 65
12/01/2024
07:45:12
2024-01-12 06:45:12,003 INFO spawned: 'icons' with pid 66
12/01/2024
07:45:12
2024-01-12 06:45:12,004 INFO spawned: 'nginx' with pid 67
12/01/2024
07:45:12
2024-01-12 06:45:12,005 INFO spawned: 'notifications' with pid 68
12/01/2024
07:45:27
2024-01-12 06:45:27,988 INFO success: identity entered RUNNING state, process has stayed up for > than 15 seconds (startsecs)
12/01/2024
07:45:27
2024-01-12 06:45:27,989 INFO success: admin entered RUNNING state, process has stayed up for > than 15 seconds (startsecs)
12/01/2024
07:45:27
2024-01-12 06:45:27,989 INFO success: api entered RUNNING state, process has stayed up for > than 15 seconds (startsecs)
12/01/2024
07:45:27
2024-01-12 06:45:27,989 INFO success: icons entered RUNNING state, process has stayed up for > than 15 seconds (startsecs)
12/01/2024
07:45:27
2024-01-12 06:45:27,989 INFO success: nginx entered RUNNING state, process has stayed up for > than 15 seconds (startsecs)
12/01/2024
07:45:27
2024-01-12 06:45:27,989 INFO success: notifications entered RUNNING state, process has stayed up for > than 15 seconds (startsecs)
12/01/2024
07:45:35
2024-01-12 06:45:35,149 INFO exited: admin (terminated by SIGABRT (core dumped); not expected)
12/01/2024
07:45:36
2024-01-12 06:45:36,153 INFO spawned: 'admin' with pid 225

mariadb logs:

12/01/2024
07:45:34
2024-01-12 6:45:34 1980 [Warning] Aborted connection 1980 to db: 'bitwarden_vault' user: 'bitwarden' host: '172.29.0.4' (Got an error reading communication packets)
12/01/2024
07:45:34
2024-01-12 6:45:34 1978 [Warning] Aborted connection 1978 to db: 'unconnected' user: 'bitwarden' host: '172.29.0.4' (Got an error reading communication packets)
12/01/2024
07:45:57
2024-01-12 6:45:57 1984 [Warning] Aborted connection 1984 to db: 'bitwarden_vault' user: 'bitwarden' host: '172.29.0.4' (Got an error reading communication packets)
12/01/2024
07:45:57
2024-01-12 6:45:57 1982 [Warning] Aborted connection 1982 to db: 'unconnected' user: 'bitwarden' host: '172.29.0.4' (Got an error reading communication packets)
12/01/2024
07:46:21
2024-01-12 6:46:21 1988 [Warning] Aborted connection 1988 to db: 'bitwarden_vault' user: 'bitwarden' host: '172.29.0.4' (Got an error reading communication packets)
12/01/2024
07:46:21
2024-01-12 6:46:21 1986 [Warning] Aborted connection 1986 to db: 'unconnected' user: 'bitwarden' host: '172.29.0.4' (Got an error reading communication packets)

Here is my compose file:

version: "3.8"
networks:
bitwarden:
name: bitwarden
mariadb:
external: true
services:
bitwarden:
container_name: bitwarden
env_file:
- settings.env
image: bitwarden/self-host:beta
networks:
- bitwarden
- mariadb
ports:
- "8043:8080"
restart: unless-stopped
volumes:
- CHANGE_TO_COMPOSE_DATA_PATH/bitwarden/config:/etc/bitwarden

I have also provided my settings file.
settings.txt

[DuckThom]

@adamaen87 I think the problem here is that your table is already been partially mutated. The migration executed the DROP PRIMARY KEY statement already but since it failed halfway through the migration, upon restart, Bitwarden is retrying the same migration steps even though some steps have executed already.

@withinfocus I think to fix the already mutated tables, they either have to be reverted to their pre-migration state (Id column removed and the PK re-added to where it used to be), or (some of) the migration steps need to be conditional, so they don't re-execute when the table has already been modified partially.

[withinfocus]

The Entity Framework migration process shouldn't have applied a partial migration however and it'd roll back changes transactionally. Was something modified manually?

[DuckThom]

I did not make manual modifications prior to the crashing admin panel other than updating the tag in my docker compose yaml from 2023.12.0 to 2024.1.0 and then doing: docker compose pull && docker compose up -d

I'm also curious how exactly it would roll back as MySQL (and perhaps therefor also MariaDB) can't do a rollback on its own for DDL statements.

https://dev.mysql.com/doc/refman/8.0/en/cannot-roll-back.html

[withinfocus]

You're correct, and the ID application is what went awry here. Pomelo cannot apply auto-increment immediately. Dropping the Id column and then recreating it like what's seen at

server/util/MySqlMigrations/Migrations/20231214162533_GrantIdWithIndexes.cs

Lines 75 to 80 in db4d7aa

	migrationBuilder.Sql("ALTER TABLE `Grant` ADD COLUMN `Id` INT AUTO_INCREMENT UNIQUE;");
	migrationBuilder.AddPrimaryKey(
	name: "PK_Grant",
	table: "Grant",
	column: "Id");

would be a likely patch.

[adamaen87]

Nothing manually modified either.

[DuckThom]

@withinfocus I think so too. But what about the currently broken tables?
In the end I managed to fix mine by manually doing some queries to dump all Grant table data, re-add a primary key, remove the Id column and then import the data dump again to make the migration work from top-to-bottom again. If my originally broken table state is anything to go by, I'm assuming most people encountering this issue right now have a Grant table that has no PK and `Id` int NOT NULL DEFAULT '0'

CREATE TABLE `Grant` (
  `Key` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `SubjectId` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `SessionId` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `ClientId` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Description` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `CreationDate` datetime(6) NOT NULL,
  `ExpirationDate` datetime(6) DEFAULT NULL,
  `ConsumedDate` datetime(6) DEFAULT NULL,
  `Data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Id` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci