MySQL feature issues #312

Closed
opened 2026-02-04 19:28:15 +03:00 by OVERLORD · 20 comments
Owner

Originally created by @tycho on GitHub (Jun 2, 2019).

I've got two issues with the MySQL feature.

First, bitwarden_rs apparently logs the DATABASE_URL for on startup, which is a Bad Idea(tm) because with the MySQL variant it includes the password (!!), e.g:

bitwarden_rs[13455]: /--------------------------------------------------------------------\
bitwarden_rs[13455]: |                       Starting Bitwarden_RS                        |
bitwarden_rs[13455]: |                       Version 1.9.1-9add8e19                       |
bitwarden_rs[13455]: |--------------------------------------------------------------------|
bitwarden_rs[13455]: | This is an *unofficial* Bitwarden implementation, DO NOT use the   |
bitwarden_rs[13455]: | official channels to report bugs/features, regardless of client.   |
bitwarden_rs[13455]: | Report URL: https://github.com/dani-garcia/bitwarden_rs/issues/new |
bitwarden_rs[13455]: \--------------------------------------------------------------------/
bitwarden_rs[13455]: mysql://bitwarden_rs:SUPERSECRETPASSWORDHERE@localhost/bitwarden_rs
bitwarden_rs[13455]: mysql://bitwarden_rs:SUPERSECRETPASSWORDHERE@localhost/bitwarden_rs
bitwarden_rs[13455]: [2019-06-01 22:46:36][launch][INFO] Configured for production.
bitwarden_rs[13455]: [2019-06-01 22:46:36][launch_][INFO] address: 0.0.0.0
bitwarden_rs[13455]: [2019-06-01 22:46:36][launch_][INFO] port: 8000
bitwarden_rs[13455]: [2019-06-01 22:46:36][launch_][INFO] log: critical
bitwarden_rs[13455]: [2019-06-01 22:46:36][launch_][INFO] workers: 64
bitwarden_rs[13455]: [2019-06-01 22:46:36][launch_][INFO] secret key: private-cookies disabled
bitwarden_rs[13455]: [2019-06-01 22:46:36][launch_][INFO] limits: forms = 32KiB
bitwarden_rs[13455]: [2019-06-01 22:46:36][launch_][INFO] keep-alive: 5s
bitwarden_rs[13455]: [2019-06-01 22:46:36][launch_][INFO] tls: disabled
bitwarden_rs[13455]: [2019-06-01 22:46:36][rocket::fairing::fairings][INFO] Fairings:
bitwarden_rs[13455]: [2019-06-01 22:46:36][_][INFO] 1 response: Application Headers
bitwarden_rs[13455]: [2019-06-01 22:46:36][launch][INFO] Rocket has launched from http://0.0.0.0:8000

Please do this before you do a release with the MySQL feature:

diff --git a/src/db/mod.rs b/src/db/mod.rs
index 2751aea..5c9f6ee 100644
--- a/src/db/mod.rs
+++ b/src/db/mod.rs
@@ -45,7 +45,6 @@ pub fn init_pool() -> Pool {

 pub fn get_connection() -> Result<Connection, ConnectionError> {
     let url = CONFIG.database_url();
-    println!("{}", url.to_string());
     Connection::establish(&url)
 }

diff --git a/src/main.rs b/src/main.rs
index 4ff6c62..c820d60 100644
--- a/src/main.rs
+++ b/src/main.rs
@@ -149,7 +149,6 @@ fn check_db() {
                 .expect("Failed to turn on WAL");
         }
     }
-    println!("{}", url.to_string());
     db::get_connection().expect("Can't connect to DB");
 }

Alternatively, if there's some other way to specify the password than in the connection string, that'd be nice. But I couldn't find such an option.


And second issue, I couldn't find any documentation in bitwarden_rs.env for setting up the DATABASE_URL for the MySQL build variant. I found this, which got me what I needed. It'd probably be a good idea to add an example to the bitwarden_rs.env template file.

Originally created by @tycho on GitHub (Jun 2, 2019). I've got two issues with the MySQL feature. First, bitwarden_rs apparently logs the `DATABASE_URL` for on startup, which is a Bad Idea(tm) because with the MySQL variant it includes the password (!!), e.g: ``` bitwarden_rs[13455]: /--------------------------------------------------------------------\ bitwarden_rs[13455]: | Starting Bitwarden_RS | bitwarden_rs[13455]: | Version 1.9.1-9add8e19 | bitwarden_rs[13455]: |--------------------------------------------------------------------| bitwarden_rs[13455]: | This is an *unofficial* Bitwarden implementation, DO NOT use the | bitwarden_rs[13455]: | official channels to report bugs/features, regardless of client. | bitwarden_rs[13455]: | Report URL: https://github.com/dani-garcia/bitwarden_rs/issues/new | bitwarden_rs[13455]: \--------------------------------------------------------------------/ bitwarden_rs[13455]: mysql://bitwarden_rs:SUPERSECRETPASSWORDHERE@localhost/bitwarden_rs bitwarden_rs[13455]: mysql://bitwarden_rs:SUPERSECRETPASSWORDHERE@localhost/bitwarden_rs bitwarden_rs[13455]: [2019-06-01 22:46:36][launch][INFO] Configured for production. bitwarden_rs[13455]: [2019-06-01 22:46:36][launch_][INFO] address: 0.0.0.0 bitwarden_rs[13455]: [2019-06-01 22:46:36][launch_][INFO] port: 8000 bitwarden_rs[13455]: [2019-06-01 22:46:36][launch_][INFO] log: critical bitwarden_rs[13455]: [2019-06-01 22:46:36][launch_][INFO] workers: 64 bitwarden_rs[13455]: [2019-06-01 22:46:36][launch_][INFO] secret key: private-cookies disabled bitwarden_rs[13455]: [2019-06-01 22:46:36][launch_][INFO] limits: forms = 32KiB bitwarden_rs[13455]: [2019-06-01 22:46:36][launch_][INFO] keep-alive: 5s bitwarden_rs[13455]: [2019-06-01 22:46:36][launch_][INFO] tls: disabled bitwarden_rs[13455]: [2019-06-01 22:46:36][rocket::fairing::fairings][INFO] Fairings: bitwarden_rs[13455]: [2019-06-01 22:46:36][_][INFO] 1 response: Application Headers bitwarden_rs[13455]: [2019-06-01 22:46:36][launch][INFO] Rocket has launched from http://0.0.0.0:8000 ``` Please do this before you do a release with the MySQL feature: ```diff diff --git a/src/db/mod.rs b/src/db/mod.rs index 2751aea..5c9f6ee 100644 --- a/src/db/mod.rs +++ b/src/db/mod.rs @@ -45,7 +45,6 @@ pub fn init_pool() -> Pool { pub fn get_connection() -> Result<Connection, ConnectionError> { let url = CONFIG.database_url(); - println!("{}", url.to_string()); Connection::establish(&url) } diff --git a/src/main.rs b/src/main.rs index 4ff6c62..c820d60 100644 --- a/src/main.rs +++ b/src/main.rs @@ -149,7 +149,6 @@ fn check_db() { .expect("Failed to turn on WAL"); } } - println!("{}", url.to_string()); db::get_connection().expect("Can't connect to DB"); } ``` Alternatively, if there's some other way to specify the password than in the connection string, that'd be nice. But I couldn't find such an option. --- And second issue, I couldn't find any documentation in `bitwarden_rs.env` for setting up the `DATABASE_URL` for the MySQL build variant. I found [this](https://docs.diesel.rs/diesel/mysql/index.html), which got me what I needed. It'd probably be a good idea to add an example to the `bitwarden_rs.env` template file.
OVERLORD added the good first issuedocumentation labels 2026-02-04 19:28:15 +03:00
Author
Owner

@Skeen commented on GitHub (Jun 2, 2019):

I'm pretty sure that printout is a debug statement I made, that was accidentally left in, it really should be removed, or as a minimum the password should be redacted.

@Skeen commented on GitHub (Jun 2, 2019): I'm pretty sure that printout is a debug statement I made, that was accidentally left in, it really should be removed, or as a minimum the password should be redacted.
Author
Owner

@tycho commented on GitHub (Jun 2, 2019):

On the plus side, I moved my bitwarden_rs SQLite database into MySQL and things are running smoothly so far!

@tycho commented on GitHub (Jun 2, 2019): On the plus side, I moved my bitwarden_rs SQLite database into MySQL and things are running smoothly so far!
Author
Owner

@dani-garcia commented on GitHub (Jun 2, 2019):

The URL is not printed anymore in the latest commit (12af32b9ea), and I added a short explanation in the .env.template file in (fff72889f6).

The Wiki might still need updating, I'd appreciate if someone could check it out, maybe even create a SQLite to MySQL migration guide?

@dani-garcia commented on GitHub (Jun 2, 2019): The URL is not printed anymore in the latest commit (12af32b9eab9f1498abda0535cd51fa924d075f1), and I added a short explanation in the .env.template file in (fff72889f6604c72f5743cae3406c548c05a043b). The Wiki might still need updating, I'd appreciate if someone could check it out, maybe even create a SQLite to MySQL migration guide?
Author
Owner

@tycho commented on GitHub (Jun 2, 2019):

Well, the migration from SQLite->MySQL was kind of frustrating because there are some differences between SQLite and MySQL syntax. I ended up using my Navicat Premium license for the job, which I wouldn't really recommend everyone else do, since it's expensive ($600 for a single-user non-commercial license). But I already had the license anyway since I use Navicat daily. I'm sure someone else has a free/open source tool for the job.

With Navicat doing the SQL dialect translation, there were only two hitches with the process:

The first issue was that the table structure needed to be in place in MySQL before I migrated data. So I started the MySQL-enabled version of bitwarden_rs for a second and stopped it again, enough time to create all the tables and apply all the schema migrations.

There was one row in the SQLite database that was rejected by MySQL. It was an entry in the devices table -- one row had a UUID starting with web- which made the value too long to fit the target DB's varchar width constraints. I dropped that row from the input database since it didn't matter anyway.

Except for that, transferring is pretty easy, at least with Navicat:

  • Start the MySQL build of bitwarden_rs once to create the table structures.
  • Add the SQLite source and MySQL target connections to Navicat.
  • Open up Tools->Data Transfer, select the appropriate source and destination databases.
  • Click the Options tab at the top and uncheck this option:

2019-06-02 07_30_57-_ Data Transfer

  • Click Next, and uncheck this table, since it's already got the right data in it:

2019-06-02 07_31_45-_ Data Transfer

  • Now just click Start.

But again, there's probably a nice open source way to do this.

@tycho commented on GitHub (Jun 2, 2019): Well, the migration from SQLite->MySQL was kind of frustrating because there are some differences between SQLite and MySQL syntax. I ended up using my [Navicat Premium](https://www.navicat.com/en/products/navicat-premium) license for the job, which I wouldn't really recommend everyone else do, since it's expensive ($600 for a single-user non-commercial license). But I already had the license anyway since I use Navicat daily. I'm sure someone else has a free/open source tool for the job. With Navicat doing the SQL dialect translation, there were only two hitches with the process: The first issue was that the table structure needed to be in place in MySQL before I migrated data. So I started the MySQL-enabled version of bitwarden_rs for a second and stopped it again, enough time to create all the tables and apply all the schema migrations. There was one row in the SQLite database that was rejected by MySQL. It was an entry in the `devices` table -- one row had a UUID starting with `web-` which made the value too long to fit the target DB's `varchar` width constraints. I dropped that row from the input database since it didn't matter anyway. Except for that, transferring is pretty easy, at least with Navicat: * Start the MySQL build of bitwarden_rs once to create the table structures. * Add the SQLite source and MySQL target connections to Navicat. * Open up `Tools`->`Data Transfer`, select the appropriate source and destination databases. * Click the `Options` tab at the top and uncheck this option: ![2019-06-02 07_30_57-_ Data Transfer](https://user-images.githubusercontent.com/29616/58762818-8e96f080-8508-11e9-9a57-a69faf47ffdf.png) * Click `Next`, and uncheck this table, since it's already got the right data in it: ![2019-06-02 07_31_45-_ Data Transfer](https://user-images.githubusercontent.com/29616/58762823-95bdfe80-8508-11e9-9cf3-07f905f7e942.png) * Now just click `Start`. But again, there's probably a nice open source way to do this.
Author
Owner

@mprasil commented on GitHub (Jun 6, 2019):

I think MySQL Workbench should be able to migrate data from SQLite. If there's someone willing to try and document that, it would be greatly appreciated.

@mprasil commented on GitHub (Jun 6, 2019): I think MySQL Workbench [should be able to migrate data from SQLite](https://dev.mysql.com/doc/workbench/en/wb-migration-overview-supported.html). If there's someone willing to try and document that, it would be greatly appreciated.
Author
Owner

@Skeen commented on GitHub (Jun 6, 2019):

Another idea would be a tiny rust script / command, which utilizes the diesel orm to load all objects from one database and saves them into another. - That should handle all conversions, etc.?

@Skeen commented on GitHub (Jun 6, 2019): Another idea would be a tiny rust script / command, which utilizes the diesel orm to load all objects from one database and saves them into another. - That should handle all conversions, etc.?
Author
Owner

@tycho commented on GitHub (Jun 6, 2019):

Another idea would be a tiny rust script / command, which utilizes the diesel orm to load all objects from one database and saves them into another. - That should handle all conversions, etc.?

I don't think that is possible with Diesel. From what I understand, Diesel's DB backend is selected at compile-time and the backends are mutually exclusive. I think you can't have both MySQL and SQLite backends in the same binary. I could be wrong, but that's what I got from this.

@tycho commented on GitHub (Jun 6, 2019): > Another idea would be a tiny rust script / command, which utilizes the diesel orm to load all objects from one database and saves them into another. - That should handle all conversions, etc.? I don't think that is possible with Diesel. From what I understand, Diesel's DB backend is selected at compile-time and the backends are mutually exclusive. I think you can't have both MySQL and SQLite backends in the same binary. I could be wrong, but that's what I got from [this](https://github.com/dani-garcia/bitwarden_rs/pull/493#discussion_r287900539).
Author
Owner

@Skeen commented on GitHub (Jun 6, 2019):

@tycho you're totally right. My bad, I was considering the diesel orm somewhat equivalent with the Django orm, but one could still attempt something similar by serializing the data using the orm to json or similar.

@Skeen commented on GitHub (Jun 6, 2019): @tycho you're totally right. My bad, I was considering the diesel orm somewhat equivalent with the Django orm, but one could still attempt something similar by serializing the data using the orm to json or similar.
Author
Owner

@janost commented on GitHub (Jul 16, 2019):

I've found a pretty easy migration solution from sqlite to mysql:

  1. Start bitwarden_rs with and empty mysql database, so diesel can run migrations and set up the schema properly. Do not do anything else.
  2. Stop bitwarden_rs.
  3. Dump your existing sqlite database: sqlite3 db.sqlite3 .dump > sqlitedump.sql
  4. Drop schema creation and diesel metadata from your dump, leaving only your actual data: grep "INSERT INTO" sqlitedump.sql | grep -v "__diesel_schema_migrations" > mysqldump.sql
  5. Load your MySQL dump: mysql -u bitwarden -p bitwarden < mysqldump.sql
  6. Start bitwarden_rs.
@janost commented on GitHub (Jul 16, 2019): I've found a pretty easy migration solution from sqlite to mysql: 1. Start bitwarden_rs with and **empty mysql database**, so diesel can run migrations and set up the schema properly. **Do not do anything else.** 2. Stop bitwarden_rs. 3. Dump your existing sqlite database: `sqlite3 db.sqlite3 .dump > sqlitedump.sql` 4. Drop schema creation and diesel metadata from your dump, leaving only your actual data: `grep "INSERT INTO" sqlitedump.sql | grep -v "__diesel_schema_migrations" > mysqldump.sql` 5. Load your MySQL dump: `mysql -u bitwarden -p bitwarden < mysqldump.sql` 6. Start bitwarden_rs.
Author
Owner

@tycho commented on GitHub (Jul 16, 2019):

Very nice! I'm pleasantly surprised that works considering SQL dialect differences, but maybe SQLite's ".dump" command is special in that regard, or maybe my mistake was trying to export and import with structure or something?

We probably should have a tool for doing the migration from $dbengine1 to $dbengine2 anyway, though. The process for migrating Nextcloud was amazingly pain-free for example, and it'd be nice to support such a process.

@tycho commented on GitHub (Jul 16, 2019): Very nice! I'm pleasantly surprised that works considering SQL dialect differences, but maybe SQLite's ".dump" command is special in that regard, or maybe my mistake was trying to export and import with structure or something? We probably should have a tool for doing the migration from $dbengine1 to $dbengine2 anyway, though. The process for migrating Nextcloud was [amazingly pain-free](https://docs.nextcloud.com/server/12/admin_manual/configuration_database/db_conversion.html) for example, and it'd be nice to support such a process.
Author
Owner

@janost commented on GitHub (Jul 16, 2019):

I was surprised as well. Yes, schema definition is totally incompatible between the two databases, that's why I was only migrating the data and letting diesel do the schema creation.

@janost commented on GitHub (Jul 16, 2019): I was surprised as well. Yes, schema definition is totally incompatible between the two databases, that's why I was only migrating the data and letting diesel do the schema creation.
Author
Owner

@Brawl345 commented on GitHub (Aug 27, 2019):

Thanks @janost, the migration worked! I had to replace the apstrophes (") with backtickets (`) in the INSERT INTO "table" statements iin the resulting sql file though or else MariaDB threw an error. Don't know why it's so picky.

@Brawl345 commented on GitHub (Aug 27, 2019): Thanks @janost, the migration worked! I had to replace the apstrophes (`"`) with backtickets (\`) in the `INSERT INTO "table"` statements iin the resulting sql file though or else MariaDB threw an error. Don't know why it's so picky.
Author
Owner

@vialou commented on GitHub (Aug 28, 2019):

@janost thank you !
Would you have a migration process using docker images ?

@vialou commented on GitHub (Aug 28, 2019): @janost thank you ! Would you have a migration process using docker images ?
Author
Owner

@janost commented on GitHub (Aug 28, 2019):

@vialou I'm not exactly sure what you're referring to. Even when using bitwarden_rs in a container, you have your sqlite database in a file which is mounted into the container.

@janost commented on GitHub (Aug 28, 2019): @vialou I'm not exactly sure what you're referring to. Even when using bitwarden_rs in a container, you have your sqlite database in a file which is mounted into the container.
Author
Owner

@BobWs commented on GitHub (Sep 2, 2019):

I've found a pretty easy migration solution from sqlite to mysql:

  1. Start bitwarden_rs with and empty mysql database, so diesel can run migrations and set up the schema properly. Do not do anything else.
  2. Stop bitwarden_rs.
  3. Dump your existing sqlite database: sqlite3 db.sqlite3 .dump > sqlitedump.sql
  4. Drop schema creation and diesel metadata from your dump, leaving only your actual data: grep "INSERT INTO" sqlitedump.sql | grep -v "__diesel_schema_migrations" > mysqldump.sql
  5. Load your MySQL dump: mysql -u bitwarden -p bitwarden < mysqldump.sql
  6. Start bitwarden_rs.

So I've tried your suggestion and it work partly. All info was imported in de mysql database but all the URL in my password account didn't come along and also all of the Authenticator Key (TOTP) are also empty.

Any idea how to fix this?

@BobWs commented on GitHub (Sep 2, 2019): > I've found a pretty easy migration solution from sqlite to mysql: > > 1. Start bitwarden_rs with and **empty mysql database**, so diesel can run migrations and set up the schema properly. **Do not do anything else.** > 2. Stop bitwarden_rs. > 3. Dump your existing sqlite database: `sqlite3 db.sqlite3 .dump > sqlitedump.sql` > 4. Drop schema creation and diesel metadata from your dump, leaving only your actual data: `grep "INSERT INTO" sqlitedump.sql | grep -v "__diesel_schema_migrations" > mysqldump.sql` > 5. Load your MySQL dump: `mysql -u bitwarden -p bitwarden < mysqldump.sql` > 6. Start bitwarden_rs. So I've tried your suggestion and it work partly. All info was imported in de mysql database but all the URL in my password account didn't come along and also all of the Authenticator Key (TOTP) are also empty. Any idea how to fix this?
Author
Owner

@BobWs commented on GitHub (Sep 2, 2019):

So I've check a view of the imported accounts and there are a lot of info missing! The dump import wasn't completely. Anyone else noticed this?

@BobWs commented on GitHub (Sep 2, 2019): So I've check a view of the imported accounts and there are a lot of info missing! The dump import wasn't completely. Anyone else noticed this?
Author
Owner

@janost commented on GitHub (Sep 2, 2019):

@BobWs I'm not sure what went wrong with your import. I converted a fairly large database with that procedure (60 users, hundreds of saved secrets) and haven't noticed any issue. Did you get any errors while loading the converted dump into MySQL?

@janost commented on GitHub (Sep 2, 2019): @BobWs I'm not sure what went wrong with your import. I converted a fairly large database with that procedure (60 users, hundreds of saved secrets) and haven't noticed any issue. Did you get any errors while loading the converted dump into MySQL?
Author
Owner

@BobWs commented on GitHub (Sep 2, 2019):

@BobWs I'm not sure what went wrong with your import. I converted a fairly large database with that procedure (60 users, hundreds of saved secrets) and haven't noticed any issue. Did you get any errors while loading the converted dump into MySQL?

First error was same as:

Thanks @janost, the migration worked! I had to replace the apstrophes (") with backtickets (`) in the INSERT INTO "table" statements iin the resulting sql file though or else MariaDB threw an error. Don't know why it's so picky.

But after I changing that import was no problem, didn't receive any errors. Only when I inspected the account I noticed the missing info.

btw, I imported the dump via phpmyadmin

@BobWs commented on GitHub (Sep 2, 2019): > @BobWs I'm not sure what went wrong with your import. I converted a fairly large database with that procedure (60 users, hundreds of saved secrets) and haven't noticed any issue. Did you get any errors while loading the converted dump into MySQL? First error was same as: > Thanks @janost, the migration worked! I had to replace the apstrophes (") with backtickets (`) in the INSERT INTO "table" statements iin the resulting sql file though or else MariaDB threw an error. Don't know why it's so picky. > But after I changing that import was no problem, didn't receive any errors. Only when I inspected the account I noticed the missing info. btw, I imported the dump via phpmyadmin
Author
Owner

@mprasil commented on GitHub (Nov 5, 2019):

I think both of the problems mentioned in the issue have been resolved. So I'm going to close this.

@mprasil commented on GitHub (Nov 5, 2019): I think both of the problems mentioned in the issue have been resolved. So I'm going to close this.
Author
Owner

@AquaRelliux commented on GitHub (Feb 28, 2021):

I still have issues with this following the official guide I got this error:

ERROR 1064 (42000) at line 1: 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 '-ne SET FOREIGN_KEY_CHECKS=0' at line 1

@AquaRelliux commented on GitHub (Feb 28, 2021): I still have issues with this following the official guide I got this error: ERROR 1064 (42000) at line 1: 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 '-ne SET FOREIGN_KEY_CHECKS=0' at line 1
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/vaultwarden#312