Sync fails on self-hosted vaultwarden #1881

New Issue

MrUnknownDE · 2026-04-06T02:33:48+02:00

MrUnknownDE commented

2026-04-06 02:33:48 +02:00

Originally created by @Nos78 on 12/5/2022

I have been running a self-hosted vaultwarden for several weeks now without issue (having been a previous user of a self-hosted bitwarden installation a couple of years ago, I found my way back to vaultwarden back in September and have been running since then without issue).

A couple of days ago, I found I was no longer able to log into my vault - I was getting the error (I paraphrase) 2fa_incomplete_record, and a quick google search revealed the problem to be incompatible characters in the database, with a solution to be running a bunch of queries to set the charset to be utf8mb4 (or something, I have the actual SQL saved - THANKFULLY - in case you need to see it to undo whatever I have now broken.,..)

After running the queries, it appeared to fix my database with respect to the users with 2FA being unable to log in.

However, it appears to have broken the sync! Which, if you discount being unable to login, is the worst thing that could have happened to a password manager! 😄

On the positive side, I finally got around to fixing why my log files were zero length - permissions in my .service file being incorrect - I use ProtectSystem=Strict but had not specified /var/log as a writable directory, only allowing /var/lib/vaultwarden in the list of ReadWritePaths

Having fixed my log files, I see that "Handler Sync" thread is panicking, giving a GET /api/sync/ => 500 Internal Server Error

Digging a little deeper, I see:

    [2022-12-05 02:54:09.687][panic][ERROR] thread 'rocket-worker-thread' panicked at 'Error loading ciphers: 
    DatabaseError(Unknown, "Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT)
    for operation '='")': src/db/models/cipher.rs:585

At which point I stopped digging, because that's the exact collation that I used in my previous bunch of SQL statements to fix the 2FA problem.

Can someone please provide assistance - whilst I am a competent software engineer, I'm not familiar enough with vaultwarden, or rust, to investigate this and then generate the SQL I'm going to need to fix my database. Having used google earlier to find the previous SQL statements, I'm loathe to do this again in case I create yet another problem for myself 🤣

Deployment environment

GNU/Linux, google cloud VM, running Ubuntu, Kernel 5.15.0-1021-gcp x86_64

I Built vaultwarden from source, so as to negate the need for docker and its ridiculously sized overheads.

On my admin page, all tabs work except for "Diagnostics", it gives a 404 not found, so unfortunately I cannot provide or generate a support string - the log says

[2022-12-05 03:04:49.960][request][INFO] GET /admin/diagnostics
[2022-12-05 03:04:49.967][_][WARN] Request guard `AdminToken` is forwarding.
[2022-12-05 03:04:49.971][_][WARN] Response was `None`.
[2022-12-05 03:04:49.971][_][WARN] Responding with registered (not_found) 404 catcher.

Install method:
Built from source
Clients used:
Android app, browser login, chrome extension, linux desktop GUI app
Reverse proxy and version:
Apache2
MySQL/MariaDB or PostgreSQL version:
mysql Ver 8.0.31-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
/usr/sbin/mysqld Ver 8.0.31-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

Steps to reproduce

Vaultwarden is ran from a .service file - no issues encountered until the last 48 hours, when 2FA stopped working (without any intervention from my part - when 2FA logins actually stopped working, I couldn't say for sure, since I enabled the "remember" option, and had not needed a hard login until I installed a new browser and tried logging in with it.

I can say for sure that I have not made any updates to the binary or config since October 29th 2022, and I can say for definate that 2FA was working two weeks ago when I logged into my vault on a new laptop. Sometime between mid-November and now, 2FA login stopped working, and I "fixed" it using the following SQL:

    # https://github.com/dani-garcia/vaultwarden/wiki/Using-the-MariaDB-(MySQL)-Backend#foreign-key-errors-collation-and-charset

    # Change collation and charset of the database 'bitwarden'
    ALTER DATABASE `bitwarden` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

    # Convert all tables (inc. text fields) - COPY THE OUTPUT!
    SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS CharSetConvert
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA="bitwarden"
    AND TABLE_TYPE="BASE TABLE";

    # Disable foreign key checking temporarily, before running the above copied output
    SET foreign_key_checks = 0;
    ALTER TABLE `__diesel_schema_migrations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_un
icode_ci;
    ALTER TABLE `attachments` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ALTER TABLE `ciphers` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ALTER TABLE `ciphers_collections` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8b4_unicode_ci
;
    ALTER TABLE `collections` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ALTER TABLE `collections_groups` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
;
    ALTER TABLE `devices` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ALTER TABLE `emergency_access` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ALTER TABLE `favorites` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ALTER TABLE `folders` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ALTER TABLE `folders_ciphers` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ALTER TABLE `groups` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ALTER TABLE `groups_users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ALTER TABLE `invitations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ALTER TABLE `org_policies` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ALTER TABLE `organizations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ALTER TABLE `sends` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ALTER TABLE `twofactor` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ALTER TABLE `twofactor_incomplete` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ALTER TABLE `users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ALTER TABLE `users_collections` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    ALTER TABLE `users_organizations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    SET foreign_key_checks = 1;
    # verify it all worked, by running a query on at least one table:
    SHOW CREATE TABLE `users`;

    # Note, it should say CHARSET=utf8mb4 at the end

*Originally created by @Nos78 on 12/5/2022* I have been running a self-hosted vaultwarden for several weeks now without issue (having been a previous user of a self-hosted bitwarden installation a couple of years ago, I found my way back to vaultwarden back in September and have been running since then without issue). A couple of days ago, I found I was no longer able to log into my vault - I was getting the error (I paraphrase) 2fa_incomplete_record, and a quick google search revealed the problem to be incompatible characters in the database, with a solution to be running a bunch of queries to set the charset to be utf8mb4 (or something, I have the actual SQL saved - THANKFULLY - in case you need to see it to undo whatever I have now broken.,..) After running the queries, it appeared to fix my database with respect to the users with 2FA being unable to log in. However, it appears to have broken the sync! Which, if you discount being unable to login, is the worst thing that could have happened to a password manager! :smile: On the positive side, I finally got around to fixing why my log files were zero length - permissions in my .service file being incorrect - I use ProtectSystem=Strict but had not specified /var/log as a writable directory, only allowing /var/lib/vaultwarden in the list of ReadWritePaths Having fixed my log files, I see that "Handler Sync" thread is panicking, giving a GET /api/sync/ => 500 Internal Server Error Digging a little deeper, I see: ``` [2022-12-05 02:54:09.687][panic][ERROR] thread 'rocket-worker-thread' panicked at 'Error loading ciphers: DatabaseError(Unknown, "Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='")': src/db/models/cipher.rs:585 ``` At which point I stopped digging, because that's the exact collation that I used in my previous bunch of SQL statements to fix the 2FA problem. Can someone please provide assistance - whilst I am a competent software engineer, I'm not familiar enough with vaultwarden, or rust, to investigate this and then generate the SQL I'm going to need to fix my database. Having used google earlier to find the previous SQL statements, I'm loathe to do this again in case I create yet another problem for myself :rofl: ### Deployment environment GNU/Linux, google cloud VM, running Ubuntu, Kernel 5.15.0-1021-gcp x86_64 I Built vaultwarden from source, so as to negate the need for docker and its ridiculously sized overheads. On my admin page, all tabs work except for "Diagnostics", it gives a 404 not found, so unfortunately I cannot provide or generate a support string - the log says ``` [2022-12-05 03:04:49.960][request][INFO] GET /admin/diagnostics [2022-12-05 03:04:49.967][_][WARN] Request guard `AdminToken` is forwarding. [2022-12-05 03:04:49.971][_][WARN] Response was `None`. [2022-12-05 03:04:49.971][_][WARN] Responding with registered (not_found) 404 catcher. ```  * Install method: Built from source * Clients used:  Android app, browser login, chrome extension, linux desktop GUI app * Reverse proxy and version:  Apache2 * MySQL/MariaDB or PostgreSQL version:  mysql Ver 8.0.31-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu)) /usr/sbin/mysqld Ver 8.0.31-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu)) ### Steps to reproduce  Vaultwarden is ran from a .service file - no issues encountered until the last 48 hours, when 2FA stopped working (without any intervention from my part - when 2FA logins actually stopped working, I couldn't say for sure, since I enabled the "remember" option, and had not needed a hard login until I installed a new browser and tried logging in with it. I can say for sure that I have not made any updates to the binary or config since October 29th 2022, and I can say for definate that 2FA was working two weeks ago when I logged into my vault on a new laptop. Sometime between mid-November and now, 2FA login stopped working, and I "fixed" it using the following SQL: ```sql # https://github.com/dani-garcia/vaultwarden/wiki/Using-the-MariaDB-(MySQL)-Backend#foreign-key-errors-collation-and-charset # Change collation and charset of the database 'bitwarden' ALTER DATABASE `bitwarden` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; # Convert all tables (inc. text fields) - COPY THE OUTPUT! SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS CharSetConvert FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="bitwarden" AND TABLE_TYPE="BASE TABLE"; # Disable foreign key checking temporarily, before running the above copied output SET foreign_key_checks = 0; ALTER TABLE `__diesel_schema_migrations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_un icode_ci; ALTER TABLE `attachments` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `ciphers` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `ciphers_collections` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8b4_unicode_ci ; ALTER TABLE `collections` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `collections_groups` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ; ALTER TABLE `devices` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `emergency_access` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `favorites` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `folders` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `folders_ciphers` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `groups` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `groups_users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `invitations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `org_policies` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `organizations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `sends` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `twofactor` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `twofactor_incomplete` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `users_collections` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE `users_organizations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; SET foreign_key_checks = 1; # verify it all worked, by running a query on at least one table: SHOW CREATE TABLE `users`; # Note, it should say CHARSET=utf8mb4 at the end ```

MrUnknownDE closed this issue

2026-04-06 02:33:48 +02:00

Sign in to join this conversation.

No Label

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github/vaultwarden#1881