mirror of
https://github.com/dani-garcia/vaultwarden.git
synced 2026-03-01 11:19:52 +03:00
MySQL feature issues #312
Reference in New Issue
Block a user
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?
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_URLfor on startup, which is a Bad Idea(tm) because with the MySQL variant it includes the password (!!), e.g:Please do this before you do a release with the MySQL feature:
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.envfor setting up theDATABASE_URLfor 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 thebitwarden_rs.envtemplate file.@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.
@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!
@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?
@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
devicestable -- one row had a UUID starting withweb-which made the value too long to fit the target DB'svarcharwidth 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:
Tools->Data Transfer, select the appropriate source and destination databases.Optionstab at the top and uncheck this option:Next, and uncheck this table, since it's already got the right data in it:Start.But again, there's probably a nice open source way to do this.
@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.
@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.?
@tycho commented on GitHub (Jun 6, 2019):
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 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.
@janost commented on GitHub (Jul 16, 2019):
I've found a pretty easy migration solution from sqlite to mysql:
sqlite3 db.sqlite3 .dump > sqlitedump.sqlgrep "INSERT INTO" sqlitedump.sql | grep -v "__diesel_schema_migrations" > mysqldump.sqlmysql -u bitwarden -p bitwarden < mysqldump.sql@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.
@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.
@Brawl345 commented on GitHub (Aug 27, 2019):
Thanks @janost, the migration worked! I had to replace the apstrophes (
") with backtickets (`) in theINSERT INTO "table"statements iin the resulting sql file though or else MariaDB threw an error. Don't know why it's so picky.@vialou commented on GitHub (Aug 28, 2019):
@janost thank you !
Would you have a migration process using docker images ?
@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.
@BobWs commented on GitHub (Sep 2, 2019):
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):
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?
@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?
@BobWs commented on GitHub (Sep 2, 2019):
First error was same as:
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
@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.
@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