MySQL feature issues

[tycho]

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.

[Skeen]

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.

[tycho]

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

[dani-garcia]

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

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

[tycho]

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:

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

• Now just click Start.

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

[mprasil]

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.

[Skeen]

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

[tycho]

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.

[Skeen]

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

[janost]

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.

[tycho]

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.

[janost]

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.

[Brawl345]

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.

[vialou]

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