github-starred/vaultwarden
2
0
Fork 0
mirror of https://github.com/dani-garcia/vaultwarden.git synced 2026-05-22 16:14:20 -05:00
Code Issues 307 Packages Projects Releases 83 Wiki Activity

[GH-ISSUE #2958] Sync fails on self-hosted vaultwarden #10238

New Issue
Closed
opened 2026-04-20 13:39:24 -05:00 by GiteaMirror · 5 comments
GiteaMirror commented 2026-04-20 13:39:24 -05:00
Owner
Copy Link

Originally created by @Nos78 on GitHub (Dec 5, 2022).
Original GitHub issue: https://github.com/dani-garcia/vaultwarden/issues/2958

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 GitHub (Dec 5, 2022). Original GitHub issue: https://github.com/dani-garcia/vaultwarden/issues/2958 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. ``` <!-- How the server was installed: Docker image, OS package, built from source, etc. --> * Install method: Built from source * Clients used: <!-- web vault, desktop, Android, iOS, etc. (if applicable) --> Android app, browser login, chrome extension, linux desktop GUI app * Reverse proxy and version: <!-- if applicable --> Apache2 * MySQL/MariaDB or PostgreSQL version: <!-- if applicable --> 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 <!-- Tell us how to reproduce this issue. What parameters did you set (differently from the defaults) and how did you start vaultwarden? --> 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 ```
GiteaMirror commented 2026-04-20 13:39:27 -05:00
Author
Owner
Copy Link

@Nos78 commented on GitHub (Dec 5, 2022):

Okay, sorry for the original post, as I've now fixed my sync problem - but this might be useful for anyone else who has done the same thing that I have done today...

Whatever broke my 2FA may well have broken other peoples self-hosted solutions as well, so this is worth posting - they might have also google searched the issue and came across the same page that I did, then followed those instructions to change all their DB tables charset to be utf8mb4_unicode_ci - and subsequently break their sync in the process!

It seems the solution was rather simple in the end... I googled for utf8mb4_0900_ai_ci and what was the first thing I see? A post about MySQL changing its default unicode character set from utf8mb4_unicode_ci to utf8mb4_0900_ai_ci 🤣 Applying some logic to this, I thought - hang about, I can just use the same SQL I executed earlier, but this time use the correct charset that my log file is telling me it wants! My logic went thusly: 1) My database tables are ALL now using the charset utf8mb4_unicode_ci - but 2) the log file highlights that there are an illegal mix of collations... hmm, well then logically this MUST be rocket throwing utf8mb4_0900_ai_ci into the mix, because we know for sure that the DB is all consistently utf8mb4_unicode_ci! So, give rocket what it wants...

The new SQL I executed goes like this (and I cut out the middle bit, since you MUST paste in the correct generated queries yourself - mysql will output all the correct queries, as a table of SQL statements in the form | ALTER TABLE x ... | So remove the | table bits (just search and replace the pipe | character) and paste the resulting SQL queries into the following script at the indicated position and executing it) :

    -- utf8mb4_0900_ai_ci - NEW
    -- utf8mb4_unicode_ci - OLD

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

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

    -- Disable foreign key checking temporarily, and run the output of the above (after removing the |+| table characters)
    SET foreign_key_checks = 0;
    --
    -- CUT & PASTE the OUTPUT from the SELECT CONCAT... command you just executed --
    --
    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, and utf8mb4_0900_ai_ci

For completeness, here is the full set of commands that where generated for me (other installations may be different, the first SELECT CONCAT(...) statement generates these queries:

    SET foreign_key_checks = 0;
    ALTER TABLE `__diesel_schema_migrations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `attachments` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `ciphers` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `ciphers_collections` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `collections` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `collections_groups` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `devices` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `emergency_access` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `favorites` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `folders` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `folders_ciphers` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `groups` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `groups_users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `invitations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `org_policies` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `organizations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `sends` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `twofactor` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `twofactor_incomplete` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `users_collections` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    ALTER TABLE `users_organizations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
    SET foreign_key_checks = 1;

And for background purposes - what I discovered from the above mentioned google search - it appears that the default utf8 charset type has changed from (the now old default) utf8mb4_unicode_ci to a new default of utf8mb4_0900_ai_ci,
And for why? So that it can cope with all these extra emojis that keep appearing... and the SQL queries that I ran to fix the initial 2FA_incomplete_record problem that appeared over the last several days has changed all my tables to be unicode, using the old (now wrong) default. The page I used was dated sometime in 2021.

I was unable to find out when this default charset changed from _unicode_ci to _0900_ai_ci - but I used the same SQL to fix it, so now it all works perfectly.

<!-- gh-comment-id:1336718098 --> @Nos78 commented on GitHub (Dec 5, 2022): Okay, sorry for the original post, as I've now fixed my sync problem - but this might be useful for anyone else who has done the same thing that I have done today... Whatever broke my 2FA may well have broken other peoples self-hosted solutions as well, so this is worth posting - they might have also google searched the issue and came across the same page that I did, then followed those instructions to change all their DB tables charset to be utf8mb4_unicode_ci - and subsequently break their sync in the process! It seems the solution was rather simple in the end... I googled for `utf8mb4_0900_ai_ci` and what was the first thing I see? A post about MySQL changing its default unicode character set from `utf8mb4_unicode_ci` to `utf8mb4_0900_ai_ci` :rofl: Applying some logic to this, I thought - hang about, I can just use the same SQL I executed earlier, but this time use the correct charset that my log file is telling me it wants! My logic went thusly: 1) My database tables are ALL now using the charset `utf8mb4_unicode_ci` - but 2) the log file highlights that there are an illegal mix of collations... hmm, well then logically this MUST be rocket throwing `utf8mb4_0900_ai_ci` into the mix, because we know for sure that the DB is all consistently `utf8mb4_unicode_ci`! So, give rocket what it wants... The new SQL I executed goes like this (and I cut out the middle bit, since you MUST paste in the correct generated queries yourself - mysql will output all the correct queries, as a table of SQL statements in the form | ALTER TABLE x ... | So remove the | table bits (just search and replace the pipe | character) and paste the resulting SQL queries into the following script at the indicated position and executing it) : ```sql -- utf8mb4_0900_ai_ci - NEW -- utf8mb4_unicode_ci - OLD -- Change collation and charset of the database 'bitwarden' ALTER DATABASE `bitwarden` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; -- Convert all tables (inc. text fields) - COPY THE OUTPUT and PASTE it as indicated below! SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;') AS CharSetConvert FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="bitwarden" AND TABLE_TYPE="BASE TABLE"; -- Disable foreign key checking temporarily, and run the output of the above (after removing the |+| table characters) SET foreign_key_checks = 0; -- -- CUT & PASTE the OUTPUT from the SELECT CONCAT... command you just executed -- -- 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, and utf8mb4_0900_ai_ci ``` For completeness, here is the full set of commands that where generated for me (other installations may be different, the first SELECT CONCAT(...) statement generates these queries: ```sql SET foreign_key_checks = 0; ALTER TABLE `__diesel_schema_migrations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `attachments` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `ciphers` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `ciphers_collections` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `collections` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `collections_groups` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `devices` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `emergency_access` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `favorites` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `folders` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `folders_ciphers` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `groups` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `groups_users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `invitations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `org_policies` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `organizations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `sends` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `twofactor` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `twofactor_incomplete` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `users_collections` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE `users_organizations` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; SET foreign_key_checks = 1; ``` And for background purposes - what I discovered from the above mentioned google search - it appears that the default utf8 charset type has changed from (the now old default) `utf8mb4_unicode_ci` to a new default of `utf8mb4_0900_ai_ci`, And for why? So that it can cope with all these extra emojis that keep appearing... and the SQL queries that I ran to fix the initial 2FA_incomplete_record problem that appeared over the last several days has changed all my tables to be unicode, using the old (now wrong) default. The page I used was dated sometime in 2021. I was unable to find out when this default charset changed from _unicode_ci to _0900_ai_ci - but I used the same SQL to fix it, so now it all works perfectly.
GiteaMirror commented 2026-04-20 13:39:27 -05:00
Author
Owner
Copy Link

@Nos78 commented on GitHub (Dec 5, 2022):

Not sure if I'll have permission to do this, but attempting to close this issue & mark it as completed.

<!-- gh-comment-id:1336719294 --> @Nos78 commented on GitHub (Dec 5, 2022): Not sure if I'll have permission to do this, but attempting to close this issue & mark it as completed.
GiteaMirror commented 2026-04-20 13:39:29 -05:00
Author
Owner
Copy Link

@BlackDex commented on GitHub (Dec 5, 2022):

@Nos78 (And anyone else who reads this).
The utf8mb4_unicode_ci works just fine for MySQL v8. I have tested this a while back and works just fine.
What you need to remember is that you need to stop Vaultwarden first. Change the database encoding and collation for both the database, and all the tables. If you do not close the connection, it will keep the current setting in the session, as that is what is defined during the connection.

I do not know what the impact of using utf8mb4_0900_ai_ci will be, it could be the storage is going to take up more space, or maybe other strange items. We suggest to use utf8mb4_unicode_ci for either MySQL v8 or v5.7 or MariaDB.

<!-- gh-comment-id:1337524604 --> @BlackDex commented on GitHub (Dec 5, 2022): @Nos78 (And anyone else who reads this). The `utf8mb4_unicode_ci` works just fine for MySQL v8. I have tested this a while back and works just fine. What you need to remember is that you need to stop Vaultwarden first. Change the database encoding and collation for both the database, and all the tables. If you do not close the connection, it will keep the current setting in the session, as that is what is defined during the connection. I do not know what the impact of using `utf8mb4_0900_ai_ci` will be, it could be the storage is going to take up more space, or maybe other strange items. We suggest to use `utf8mb4_unicode_ci` for either MySQL v8 or v5.7 or MariaDB.
GiteaMirror commented 2026-04-20 13:39:30 -05:00
Author
Owner
Copy Link

@Nos78 commented on GitHub (Dec 5, 2022):

@BlackDex thanks for that pointer! You're right, I hadn't stopped vaultwarden before chomping on the database - AFAIR my default was utf8mb4_unicode_ci before I encountered the 2FA_incomplete_record issue (and I double checked this by opening phpmyadmin that I had forgotten I'd installed) and sure enough, thats my the default for all my new dbs.

I don't know where the different collation came from - the reason I subsequently changed from unicode_ci to 0900_ai_ci was because the error log suggested that rocket was looking for this? I'd prefer to put it back to utf8mb4_unicode_ci if it's going to sync and 2FA works. Would you know where this other collation came from - like it appeared out of nowhere 🤣 - I can't figure out why my session wanted this, although I cannot be 100% certain that it was using unicode_ci, I'm fairly certain I've never encountered or specified 0900_ai_ci before this issue (the one thing I can be 100% certain of is that I was using utf8something, its my go-to charset). And thanks for your input in any event.

<!-- gh-comment-id:1338304370 --> @Nos78 commented on GitHub (Dec 5, 2022): @BlackDex thanks for that pointer! You're right, I hadn't stopped vaultwarden before chomping on the database - AFAIR my default was utf8mb4_unicode_ci before I encountered the 2FA_incomplete_record issue (and I double checked this by opening phpmyadmin that I had forgotten I'd installed) and sure enough, thats my the default for all my new dbs. I don't know where the different collation came from - the reason I subsequently changed from unicode_ci to 0900_ai_ci was because the error log suggested that rocket was looking for this? I'd prefer to put it back to `utf8mb4_unicode_ci` if it's going to sync and 2FA works. Would you know where this other collation came from - like it appeared out of nowhere :rofl: - I can't figure out why my session wanted this, although I cannot be 100% certain that it was using unicode_ci, I'm fairly certain I've never encountered or specified 0900_ai_ci before this issue (the one thing I can be 100% certain of is that I was using utf8*something*, its my go-to charset). And thanks for your input in any event.
GiteaMirror commented 2026-04-20 13:39:31 -05:00
Author
Owner
Copy Link

@BlackDex commented on GitHub (Dec 5, 2022):

Well, if one tables still had that collation, or maybe the database it self, because some MySQL upgrade changed it, that could have caused this. And then a new Vaultwarden migration created or updated some tables which then got the 'new' defaults.

Reverting all tables back to Unicode will not be an issue i think, since 0900 is just an extension on top of Unicode as far as i understand, save there should be no characters containing those extended range in the database at all.

<!-- gh-comment-id:1338342069 --> @BlackDex commented on GitHub (Dec 5, 2022): Well, if one tables still had that collation, or maybe the database it self, because some MySQL upgrade changed it, that could have caused this. And then a new Vaultwarden migration created or updated some tables which then got the 'new' defaults. Reverting all tables back to Unicode will not be an issue i think, since 0900 is just an extension on top of Unicode as far as i understand, save there should be no characters containing those extended range in the database at all.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
better for forum
bug
documentation
duplicate
enhancement
future Vault
good first issue
help wanted
low priority
notes
pull-request
Mirrored from GitHub Pull Request
 question
SSO
Third party
troubleshooting
wontfix
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
GiteaMirror ninjasurge
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: github-starred/vaultwarden#10238
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?