Difficulty migrating my sqlite backend to mariadb backend

kevdog · October 16, 2020, 4:56am

Hi - I’m open to any easy way attempting to migrate a bitwarden_rs self-hosted docker server with mysqlite backend to a bitwarden_rs self hosted docker server with maria_db backend.

For reference I was trying to follow these instructions here:

So I went ahead and created a compose file with mariadb and bitwardenrs/server-mysql:latest

I followed the guide and dumped the old db.sqlite3 database using the command:
sqlite3 db.sqlite3 .dump | grep "^INSERT INTO" | grep -v "__diesel_schema_migrations" > sqlitedump.sql ; echo -ne "SET FOREIGN_KEY_CHECKS=0;\n$(cat sqlitedump.sql)" > mysqldump.sql

I copied the mysqldump.sql file to the bin mount location associated with my mariadb location (/var/data/bw-mysql-data).

I then docker exec -it mariadb /bin/bash into the mariadb container and tried to import the data but unfortunately received a ton of errors that are not mentioned in the reference guide. I’m not exactly sure what to do since I haven’t seen these errors reported in the past.

root@mariadb-bitwarden_rs:/config# mysql --force --password --user=bwuser --database=bitwarden < mysqldump.sql
Enter password:
ERROR 1136 (21S01) at line 2: Column count doesn't match value count at row 1
ERROR 1136 (21S01) at line 3: Column count doesn't match value count at row 1
ERROR 1136 (21S01) at line 4: Column count doesn't match value count at row 1
ERROR 1136 (21S01) at line 5: Column count doesn't match value count at row 1
ERROR 1136 (21S01) at line 6: Column count doesn't match value count at row 1
ERROR 1136 (21S01) at line 7: Column count doesn't match value count at row 1
ERROR 1136 (21S01) at line 8: Column count doesn't match value count at row 1
ERROR 1136 (21S01) at line 9: Column count doesn't match value count at row 1
ERROR 1136 (21S01) at line 10: Column count doesn't match value count at row 1
ERROR 1136 (21S01) at line 11: Column count doesn't match value count at row 1
ERROR 1136 (21S01) at line 12: Column count doesn't match value count at row 1
ERROR 1136 (21S01) at line 13: Column count doesn't match value count at row 1
ERROR 1136 (21S01) at line 14: Column count doesn't match value count at row 1
ERROR 1136 (21S01) at line 15: Column count doesn't match value count at row 1
ERROR 1136 (21S01) at line 16: Column count doesn't match value count at row 1
ERROR 1136 (21S01) at line 17: Column count doesn't match value count at row 1
ERROR 1136 (21S01) at line 18: Column count doesn't match value count at row 1
ERROR 1136 (21S01) at line 19: Column count doesn't match value count at row 1
ERROR 1136 (21S01) at line 20: Column count doesn't match value count at row 1
ERROR 1136 (21S01) at line 21: Column count doesn't match value count at row 1
ERROR 1146 (42S02) at line 27: Table 'bitwarden.folders_ciphers' doesn't exist
ERROR 1146 (42S02) at line 28: Table 'bitwarden.folders_ciphers' doesn't exist
ERROR 1146 (42S02) at line 29: Table 'bitwarden.folders_ciphers' doesn't exist
ERROR 1146 (42S02) at line 30: Table 'bitwarden.folders_ciphers' doesn't exist
ERROR 1146 (42S02) at line 31: Table 'bitwarden.folders_ciphers' doesn't exist
ERROR 1146 (42S02) at line 32: Table 'bitwarden.folders_ciphers' doesn't exist
ERROR 1146 (42S02) at line 33: Table 'bitwarden.folders_ciphers' doesn't exist
ERROR 1146 (42S02) at line 34: Table 'bitwarden.folders_ciphers' doesn't exist
ERROR 1146 (42S02) at line 35: Table 'bitwarden.folders_ciphers' doesn't exist
ERROR 1146 (42S02) at line 36: Table 'bitwarden.folders_ciphers' doesn't exist
ERROR 1146 (42S02) at line 37: Table 'bitwarden.folders_ciphers' doesn't exist
ERROR 1146 (42S02) at line 38: Table 'bitwarden.folders_ciphers' doesn't exist
ERROR 1146 (42S02) at line 39: Table 'bitwarden.folders_ciphers' doesn't exist
ERROR 1146 (42S02) at line 40: Table 'bitwarden.twofactor' doesn't exist
ERROR 1265 (01000) at line 41: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 42: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 43: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 44: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 45: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 46: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 47: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 48: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 49: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 50: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 51: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 52: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 53: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 54: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 55: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 56: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 57: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 58: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 59: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 60: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 61: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 62: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 63: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 64: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 65: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 66: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 67: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 68: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 69: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 70: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 71: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 72: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 73: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 74: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 75: Data truncated for column 'type' at row 1
ERROR 1265 (01000) at line 76: Data truncated for column 'type' at row 1

BlackDex · October 16, 2020, 5:42am

You seem to have skipped a step.
Before the import you need to connect bitwarenrs to that mysql database so that bitwardenrs can create the tables.

After that you can import that generated dump file.
Be sure to stop bitwardenrs first right after the tables are created to prevent issues

kevdog · October 16, 2020, 11:53am

Hey Thanks @BlackDex.

I don’t think I skipped that step although I can definitely try again. I constructed by compose file with mariadb and bitwardenrs-mysql and started the containers via docker-compose. I then just stopped bitwardenrs-mysql and proceeded at that point. I didn’t manually try to create any databases or tables. I’m betting I didn’t do something correctly. I can try the process again and post the visual table structures if you like…I’m not sure how to troubleshoot this one.

Here is a picture of the mariadb (as viewed my phpmyadmin). Not sure if database structure is right. I can definitely try to recreate if there is a problem.

BlackDex · October 16, 2020, 5:44pm

I think there is a version mismatch between the sqlite and mysql versions.

I suggest to first upgrade the sqlite docker image, then export, clear the mysql db and start again.

kevdog · October 16, 2020, 7:36pm

@BlackDex – I’m always willing to try again.

For my docker images I’m already using:

image: bitwardenrs/server-mysql:latest
image: bitwardenrs/server:latest

Just curious if I should be using different images or testing? Looking at the dockerfile it looks like there are libmariadb and libpg libraries brought into the image. Perhaps I should try the same image on both?

BlackDex · October 16, 2020, 7:50pm

You can just use server:latest for all database.
Version 1.17.0 has support for all databases built-in.
So you should only need to change the DATABASE_URL to point to the correct database.

kevdog · October 16, 2020, 11:05pm

So I deleted the database and started just with a mariadb portion in the compose file. After startup of the container I confirmed by bitwarden database had no tables.

I then added in the bitwarden mysql implementation. I examined the bitwarden database after starting of the container and I didn’t see any schema being setup. It bitwarden database was still blank. (which I don’t think is right).

The logs for the bitwarden-mysql implementation shows the following (Note nothing about any mysql/mariadb connection):

[2020-10-16 15:21:56.724][bitwarden_rs][INFO] JWT keys don't exist, checking if OpenSSL is available...
[2020-10-16 15:21:56.759][bitwarden_rs][INFO] OpenSSL detected, creating keys...
[2020-10-16 15:21:56.814][bitwarden_rs][INFO] Keys created correctly.
[2020-10-16 15:22:00.576][routes][INFO] Routes loaded:
[2020-10-16 15:22:00.576][ws][INFO] Listening for new connections on 0.0.0.0:3012.
[2020-10-16 15:22:00.576][routes][INFO] GET    /
[2020-10-16 15:22:00.576][routes][INFO] GET    /<p..> [10]
[2020-10-16 15:22:00.577][routes][INFO] POST   /admin
[2020-10-16 15:22:00.577][routes][INFO] GET    /admin [1]
[2020-10-16 15:22:00.577][routes][INFO] GET    /admin [2]
[2020-10-16 15:22:00.577][routes][INFO] POST   /admin/config
[2020-10-16 15:22:00.577][routes][INFO] POST   /admin/config/backup_db
[2020-10-16 15:22:00.577][routes][INFO] POST   /admin/config/delete
[2020-10-16 15:22:00.577][routes][INFO] GET    /admin/diagnostics
[2020-10-16 15:22:00.578][routes][INFO] POST   /admin/invite
[2020-10-16 15:22:00.578][routes][INFO] GET    /admin/logout
[2020-10-16 15:22:00.578][routes][INFO] GET    /admin/organizations/overview
[2020-10-16 15:22:00.578][routes][INFO] POST   /admin/test/smtp
[2020-10-16 15:22:00.578][routes][INFO] GET    /admin/users
[2020-10-16 15:22:00.578][routes][INFO] POST   /admin/users/<uuid>/deauth
[2020-10-16 15:22:00.579][routes][INFO] POST   /admin/users/<uuid>/delete
[2020-10-16 15:22:00.579][routes][INFO] POST   /admin/users/<uuid>/remove-2fa
[2020-10-16 15:22:00.579][routes][INFO] GET    /admin/users/overview
[2020-10-16 15:22:00.579][routes][INFO] POST   /admin/users/update_revision
[2020-10-16 15:22:00.579][routes][INFO] GET    /alive
[2020-10-16 15:22:00.579][routes][INFO] DELETE /api/accounts
[2020-10-16 15:22:00.580][routes][INFO] POST   /api/accounts/delete
[2020-10-16 15:22:00.580][routes][INFO] POST   /api/accounts/delete-recover
[2020-10-16 15:22:00.580][routes][INFO] POST   /api/accounts/delete-recover-token
[2020-10-16 15:22:00.580][routes][INFO] POST   /api/accounts/email
[2020-10-16 15:22:00.580][routes][INFO] POST   /api/accounts/email-token
[2020-10-16 15:22:00.581][routes][INFO] POST   /api/accounts/kdf
[2020-10-16 15:22:00.581][routes][INFO] POST   /api/accounts/key
[2020-10-16 15:22:00.581][routes][INFO] POST   /api/accounts/keys
[2020-10-16 15:22:00.581][routes][INFO] POST   /api/accounts/password
[2020-10-16 15:22:00.581][routes][INFO] POST   /api/accounts/password-hint
[2020-10-16 15:22:00.582][routes][INFO] POST   /api/accounts/prelogin
[2020-10-16 15:22:00.582][routes][INFO] PUT    /api/accounts/profile
[2020-10-16 15:22:00.582][routes][INFO] POST   /api/accounts/profile
[2020-10-16 15:22:00.582][routes][INFO] GET    /api/accounts/profile
[2020-10-16 15:22:00.582][routes][INFO] POST   /api/accounts/register
[2020-10-16 15:22:00.583][routes][INFO] GET    /api/accounts/revision-date
[2020-10-16 15:22:00.583][routes][INFO] POST   /api/accounts/security-stamp
[2020-10-16 15:22:00.583][routes][INFO] POST   /api/accounts/verify-email
[2020-10-16 15:22:00.583][routes][INFO] POST   /api/accounts/verify-email-token
[2020-10-16 15:22:00.583][routes][INFO] POST   /api/accounts/verify-password
[2020-10-16 15:22:00.584][routes][INFO] POST   /api/ciphers
[2020-10-16 15:22:00.584][routes][INFO] DELETE /api/ciphers
[2020-10-16 15:22:00.584][routes][INFO] GET    /api/ciphers
[2020-10-16 15:22:00.584][routes][INFO] PUT    /api/ciphers/<uuid>
[2020-10-16 15:22:00.584][routes][INFO] POST   /api/ciphers/<uuid>
[2020-10-16 15:22:00.584][routes][INFO] DELETE /api/ciphers/<uuid>
[2020-10-16 15:22:00.585][routes][INFO] GET    /api/ciphers/<uuid>
[2020-10-16 15:22:00.585][routes][INFO] PUT    /api/ciphers/<uuid>/admin
[2020-10-16 15:22:00.585][routes][INFO] POST   /api/ciphers/<uuid>/admin
[2020-10-16 15:22:00.585][routes][INFO] DELETE /api/ciphers/<uuid>/admin
[2020-10-16 15:22:00.585][routes][INFO] GET    /api/ciphers/<uuid>/admin
[2020-10-16 15:22:00.585][routes][INFO] POST   /api/ciphers/<uuid>/attachment
[2020-10-16 15:22:00.586][routes][INFO] POST   /api/ciphers/<uuid>/attachment-admin
[2020-10-16 15:22:00.586][routes][INFO] DELETE /api/ciphers/<uuid>/attachment/<attachment_id>
[2020-10-16 15:22:00.586][routes][INFO] DELETE /api/ciphers/<uuid>/attachment/<attachment_id>/admin
[2020-10-16 15:22:00.586][routes][INFO] POST   /api/ciphers/<uuid>/attachment/<attachment_id>/delete
[2020-10-16 15:22:00.586][routes][INFO] POST   /api/ciphers/<uuid>/attachment/<attachment_id>/delete-admin
[2020-10-16 15:22:00.587][routes][INFO] POST   /api/ciphers/<uuid>/attachment/<attachment_id>/share
[2020-10-16 15:22:00.587][routes][INFO] PUT    /api/ciphers/<uuid>/collections
[2020-10-16 15:22:00.587][routes][INFO] POST   /api/ciphers/<uuid>/collections
[2020-10-16 15:22:00.587][routes][INFO] PUT    /api/ciphers/<uuid>/collections-admin
[2020-10-16 15:22:00.587][routes][INFO] POST   /api/ciphers/<uuid>/collections-admin
[2020-10-16 15:22:00.587][routes][INFO] PUT    /api/ciphers/<uuid>/delete
[2020-10-16 15:22:00.588][routes][INFO] POST   /api/ciphers/<uuid>/delete
[2020-10-16 15:22:00.588][routes][INFO] PUT    /api/ciphers/<uuid>/delete-admin
[2020-10-16 15:22:00.588][routes][INFO] POST   /api/ciphers/<uuid>/delete-admin
[2020-10-16 15:22:00.588][routes][INFO] GET    /api/ciphers/<uuid>/details
[2020-10-16 15:22:00.588][routes][INFO] PUT    /api/ciphers/<uuid>/restore
[2020-10-16 15:22:00.588][routes][INFO] PUT    /api/ciphers/<uuid>/restore-admin
[2020-10-16 15:22:00.589][routes][INFO] PUT    /api/ciphers/<uuid>/share
[2020-10-16 15:22:00.589][routes][INFO] POST   /api/ciphers/<uuid>/share
[2020-10-16 15:22:00.589][routes][INFO] POST   /api/ciphers/admin
[2020-10-16 15:22:00.589][routes][INFO] POST   /api/ciphers/create
[2020-10-16 15:22:00.589][routes][INFO] PUT    /api/ciphers/delete
[2020-10-16 15:22:00.590][routes][INFO] POST   /api/ciphers/delete
[2020-10-16 15:22:00.590][routes][INFO] POST   /api/ciphers/import
[2020-10-16 15:22:00.590][routes][INFO] POST   /api/ciphers/import-organization?<query..>
[2020-10-16 15:22:00.590][routes][INFO] PUT    /api/ciphers/move
[2020-10-16 15:22:00.590][routes][INFO] POST   /api/ciphers/move
[2020-10-16 15:22:00.591][routes][INFO] GET    /api/ciphers/organization-details?<data..>
[2020-10-16 15:22:00.591][routes][INFO] POST   /api/ciphers/purge?<organization..>
[2020-10-16 15:22:00.591][routes][INFO] PUT    /api/ciphers/restore
[2020-10-16 15:22:00.591][routes][INFO] PUT    /api/ciphers/share
[2020-10-16 15:22:00.591][routes][INFO] GET    /api/collections
[2020-10-16 15:22:00.591][routes][INFO] PUT    /api/devices/identifier/<uuid>/clear-token
[2020-10-16 15:22:00.592][routes][INFO] PUT    /api/devices/identifier/<uuid>/token
[2020-10-16 15:22:00.592][routes][INFO] POST   /api/folders
[2020-10-16 15:22:00.592][routes][INFO] GET    /api/folders
[2020-10-16 15:22:00.592][routes][INFO] PUT    /api/folders/<uuid>
[2020-10-16 15:22:00.592][routes][INFO] POST   /api/folders/<uuid>
[2020-10-16 15:22:00.593][routes][INFO] DELETE /api/folders/<uuid>
[2020-10-16 15:22:00.593][routes][INFO] GET    /api/folders/<uuid>
[2020-10-16 15:22:00.593][routes][INFO] POST   /api/folders/<uuid>/delete
[2020-10-16 15:22:00.593][routes][INFO] GET    /api/hibp/breach?<username>
[2020-10-16 15:22:00.593][routes][INFO] POST   /api/organizations
[2020-10-16 15:22:00.593][routes][INFO] POST   /api/organizations/<_org_id>/users/<_org_user_id>/accept
[2020-10-16 15:22:00.594][routes][INFO] PUT    /api/organizations/<org_id>
[2020-10-16 15:22:00.594][routes][INFO] POST   /api/organizations/<org_id>
[2020-10-16 15:22:00.594][routes][INFO] DELETE /api/organizations/<org_id>
[2020-10-16 15:22:00.594][routes][INFO] GET    /api/organizations/<org_id>
[2020-10-16 15:22:00.594][routes][INFO] POST   /api/organizations/<org_id>/collections
[2020-10-16 15:22:00.594][routes][INFO] GET    /api/organizations/<org_id>/collections
[2020-10-16 15:22:00.595][routes][INFO] PUT    /api/organizations/<org_id>/collections/<col_id>
[2020-10-16 15:22:00.595][routes][INFO] POST   /api/organizations/<org_id>/collections/<col_id>
[2020-10-16 15:22:00.595][routes][INFO] DELETE /api/organizations/<org_id>/collections/<col_id>
[2020-10-16 15:22:00.595][routes][INFO] POST   /api/organizations/<org_id>/collections/<col_id>/delete
[2020-10-16 15:22:00.595][routes][INFO] POST   /api/organizations/<org_id>/collections/<col_id>/delete-user/<org_user_id>
[2020-10-16 15:22:00.595][routes][INFO] DELETE /api/organizations/<org_id>/collections/<col_id>/user/<org_user_id>
[2020-10-16 15:22:00.595][routes][INFO] GET    /api/organizations/<org_id>/collections/<coll_id>/details
[2020-10-16 15:22:00.596][routes][INFO] PUT    /api/organizations/<org_id>/collections/<coll_id>/users
[2020-10-16 15:22:00.596][routes][INFO] GET    /api/organizations/<org_id>/collections/<coll_id>/users
[2020-10-16 15:22:00.596][routes][INFO] POST   /api/organizations/<org_id>/delete
[2020-10-16 15:22:00.596][routes][INFO] POST   /api/organizations/<org_id>/leave
[2020-10-16 15:22:00.596][routes][INFO] GET    /api/organizations/<org_id>/policies
[2020-10-16 15:22:00.597][routes][INFO] PUT    /api/organizations/<org_id>/policies/<pol_type>
[2020-10-16 15:22:00.597][routes][INFO] GET    /api/organizations/<org_id>/policies/<pol_type>
[2020-10-16 15:22:00.597][routes][INFO] GET    /api/organizations/<org_id>/policies/token?<token>
[2020-10-16 15:22:00.597][routes][INFO] GET    /api/organizations/<org_id>/users
[2020-10-16 15:22:00.597][routes][INFO] PUT    /api/organizations/<org_id>/users/<org_user_id> [1]
[2020-10-16 15:22:00.597][routes][INFO] POST   /api/organizations/<org_id>/users/<org_user_id> [1]
[2020-10-16 15:22:00.598][routes][INFO] DELETE /api/organizations/<org_id>/users/<org_user_id>
[2020-10-16 15:22:00.598][routes][INFO] GET    /api/organizations/<org_id>/users/<org_user_id>
[2020-10-16 15:22:00.598][routes][INFO] POST   /api/organizations/<org_id>/users/<org_user_id>/confirm
[2020-10-16 15:22:00.598][routes][INFO] POST   /api/organizations/<org_id>/users/<org_user_id>/delete
[2020-10-16 15:22:00.598][routes][INFO] POST   /api/organizations/<org_id>/users/<user_org>/reinvite
[2020-10-16 15:22:00.599][routes][INFO] POST   /api/organizations/<org_id>/users/invite
[2020-10-16 15:22:00.599][routes][INFO] GET    /api/plans
[2020-10-16 15:22:00.599][routes][INFO] PUT    /api/settings/domains
[2020-10-16 15:22:00.599][routes][INFO] POST   /api/settings/domains
[2020-10-16 15:22:00.599][routes][INFO] GET    /api/settings/domains
[2020-10-16 15:22:00.600][routes][INFO] GET    /api/sync?<data..>
[2020-10-16 15:22:00.600][routes][INFO] GET    /api/two-factor
[2020-10-16 15:22:00.600][routes][INFO] PUT    /api/two-factor/authenticator
[2020-10-16 15:22:00.600][routes][INFO] POST   /api/two-factor/authenticator
[2020-10-16 15:22:00.600][routes][INFO] PUT    /api/two-factor/disable
[2020-10-16 15:22:00.600][routes][INFO] POST   /api/two-factor/disable
[2020-10-16 15:22:00.601][routes][INFO] PUT    /api/two-factor/duo
[2020-10-16 15:22:00.601][routes][INFO] POST   /api/two-factor/duo
[2020-10-16 15:22:00.601][routes][INFO] PUT    /api/two-factor/email
[2020-10-16 15:22:00.601][routes][INFO] POST   /api/two-factor/get-authenticator
[2020-10-16 15:22:00.601][routes][INFO] POST   /api/two-factor/get-duo
[2020-10-16 15:22:00.602][routes][INFO] POST   /api/two-factor/get-email
[2020-10-16 15:22:00.602][routes][INFO] POST   /api/two-factor/get-recover
[2020-10-16 15:22:00.602][routes][INFO] POST   /api/two-factor/get-u2f
[2020-10-16 15:22:00.602][routes][INFO] POST   /api/two-factor/get-u2f-challenge
[2020-10-16 15:22:00.602][routes][INFO] POST   /api/two-factor/get-yubikey
[2020-10-16 15:22:00.602][routes][INFO] POST   /api/two-factor/recover
[2020-10-16 15:22:00.603][routes][INFO] POST   /api/two-factor/send-email
[2020-10-16 15:22:00.603][routes][INFO] POST   /api/two-factor/send-email-login
[2020-10-16 15:22:00.603][routes][INFO] PUT    /api/two-factor/u2f
[2020-10-16 15:22:00.603][routes][INFO] POST   /api/two-factor/u2f
[2020-10-16 15:22:00.603][routes][INFO] DELETE /api/two-factor/u2f
[2020-10-16 15:22:00.604][routes][INFO] PUT    /api/two-factor/yubikey
[2020-10-16 15:22:00.604][routes][INFO] POST   /api/two-factor/yubikey
[2020-10-16 15:22:00.604][routes][INFO] GET    /api/users/<uuid>/public-key
[2020-10-16 15:22:00.604][routes][INFO] GET    /app-id.json
[2020-10-16 15:22:00.604][routes][INFO] GET    /attachments/<uuid>/<file..>
[2020-10-16 15:22:00.605][routes][INFO] GET    /bwrs_static/<filename>
[2020-10-16 15:22:00.605][routes][INFO] GET    /icons/<domain>/icon.png
[2020-10-16 15:22:00.605][routes][INFO] POST   /identity/connect/token
[2020-10-16 15:22:00.605][routes][INFO] GET    /notifications/hub
[2020-10-16 15:22:00.605][routes][INFO] POST   /notifications/hub/negotiate
[2020-10-16 15:22:00.605][start][INFO] Rocket has launched from https://0.0.0.0:80
[2020-10-16 15:22:56.731][rustls::server::hs][DEBUG] decided upon suite SupportedCipherSuite { suite: TLS13_AES_256_GCM_SHA384, kx: BulkOnly, bulk: AES_256_GCM, hash: SHA384, sign: Anonymous, enc_key_len: 32, fixed_iv_len: 12, explicit_nonce_len: 0, hkdf_algorithm: Algorithm(Algorithm(SHA384)) }
[2020-10-16 15:22:56.738][request][INFO] GET /alive
[2020-10-16 15:22:56.740][response][INFO] GET /alive (alive) => 200 OK
[2020-10-16 15:23:57.160][rustls::server::hs][DEBUG] decided upon suite SupportedCipherSuite { suite: TLS13_AES_256_GCM_SHA384, kx: BulkOnly, bulk: AES_256_GCM, hash: SHA384, sign: Anonymous, enc_key_len: 32, fixed_iv_len: 12, explicit_nonce_len: 0, hkdf_algorithm: Algorithm(Algorithm(SHA384)) }
[2020-10-16 15:23:57.163][request][INFO] GET /alive
[2020-10-16 15:23:57.163][response][INFO] GET /alive (alive) => 200 OK
[2020-10-16 15:24:57.601][rustls::server::hs][DEBUG] decided upon suite SupportedCipherSuite { suite: TLS13_AES_256_GCM_SHA384, kx: BulkOnly, bulk: AES_256_GCM, hash: SHA384, sign: Anonymous, enc_key_len: 32, fixed_iv_len: 12, explicit_nonce_len: 0, hkdf_algorithm: Algorithm(Algorithm(SHA384)) }
[2020-10-16 15:24:57.604][request][INFO] GET /alive
[2020-10-16 15:24:57.604][response][INFO] GET /alive (alive) => 200 OK
[2020-10-16 15:25:58.316][rustls::server::hs][DEBUG] decided upon suite SupportedCipherSuite { suite: TLS13_AES_256_GCM_SHA384, kx: BulkOnly, bulk: AES_256_GCM, hash: SHA384, sign: Anonymous, enc_key_len: 32, fixed_iv_len: 12, explicit_nonce_len: 0, hkdf_algorithm: Algorithm(Algorithm(SHA384)) }
[2020-10-16 15:25:58.319][request][INFO] GET /alive
[2020-10-16 15:25:58.319][response][INFO] GET /alive (alive) => 200 OK

Here is my docker-compose file (portion dealing with the bitwardenrs attempted mysql implementation):

  db:
    container_name: mariadb
    image: linuxserver/mariadb:latest
    restart: always
    hostname: mariadb-bitwarden_rs
    secrets:
      - mysql-user_secret
      - mysql-root_secret
    networks:
      - bitwarden_rs_net
    ports:
      - 3306:3306
    environment:
      - TZ
      - PUID=0
      - GUID=0
      - MYSQL_DATABASE=bitwarden
      - FILE__MYSQL_ROOT_PASSWORD=/run/secrets/mysql-root_secret
      - MYSQL_USER=bwuser
      - FILE__MYSQL_PASSWORD=/run/secrets/mysql-user_secret
    volumes:
      - /var/data/db:/config

  bitwardenrs-mysql:
    container_name: bitwardenrs-mysql
    image: bitwardenrs/server-mysql:latest
    image: bitwardenrs/server:latest
    restart: always
    hostname: bitwardenrs-mysql-docker
    networks:
      - bitwarden_rs_net
    secrets:
      - smtp_password_secret
    ports:
      - 444:80
      - 3013:3012
    depends_on:
      - db
    environment:
      - LOG_FILE=/data/bitwarden.log
      - LOG_LEVEL=debug
      - EXTENDED_LOGGING=true
      - SIGNUPS_ALLOWED=true
      - ADMIN_TOKEN=hi
      - WEBSOCKET_ENABLED=true
      - DOMAIN=https://bw.<domain>.com
      - SMTP_HOST=postfix.<domain>.com
      - SMTP_FROM=postmaster-bitwarden-mysql@bw.<domain>.com
      - SMTP_SSL=true
      - SMTP_USERNAME=smtp
      - SMTP_PASSWORD=/run/secrets/smtp_password_secret
      - TZ
      - RUST_BACKTRACE=1
      - ROCKET_TLS={certs = "/ssl/fullchain.pem", key = "/ssl/privkey.pem"}

      #For Port 465 Settings are as follows:
      #- SMTP_PORT=465
      #- SMTP_EXPLICIT_TLS=true

      #For Port 587 Settings are as follows:
      - SMTP_PORT=587
      - SMTP_EXPLICIT_TLS=false

      #MYSQL RELATED
      - DATABASE_URL='mysql://bwuser:bwuserpass@mariadb/bitwarden'
      - ENABLE_DB_WAL=false
    volumes:
      - /var/data/bw-mysql-data:/data
      - /etc/letsencrypt/bwbackend.<domain>.com:/ssl:ro

So my problem is the table structure for bitwarden within the database isn’t initially being created – I’m not even sure how to debug this since in the log files of my two containers – mariadb, and bitwardenrs-mysql there is nothing which would indicated either a failed or unsuccessful connection.

kevdog · October 16, 2020, 11:54pm

Ok - so I got past the part above – there was a problem in the docker-compose.yml file:

Incorrect syntax:

DATABASE_URL=‘mysql://bwuser:bwuserpass@mariadb/bitwarden’

Correct syntax

DATABASE_URL=mysql://bwuser:bwuserpass@mariadb/bitwarden

I then stopped the bitwardenrs-mysql container:
docker stop bitwardenrs-mysql

Within the docker host, I changed into the data directory of the old bitwardenrs using sqlite (for me this was /var/data/bwdata.

I ran the following command as root:
sqlite3 db.sqlite3 .dump | grep "^INSERT INTO" | grep -v "__diesel_schema_migrations" > sqlitedump.sql ; echo -ne "SET FOREIGN_KEY_CHECKS=0;\n$(cat sqlitedump.sql)" > mysqldump.sql

I copied the mysqldump.sql file from this directory into the bind mounted directory of the mariadb(which for me was /var/data/db).

I created a shell into the mariadb container:
docker exec -it mariadb /bin/bash

Within the mariadb container I changed into the /config directory, I imported the mysqldump.sql file into the database and then exited the container:

cd /config
mysql -u bwuser -pbwuserpass --force --show-warnings --database=bitwarden  < mysqldump.sql
exit

I then restarted the bitwardenrs-mysql container
docker-compose start bitwardenrs-mysql

Shockingly everything actually worked this time. Just had to verify every step as I went about it. Really help to have a phpmyadmin graphical view of the database while doing this to look for potential mis-steps along the way.

Topic		Replies	Views
Bitwarden_rs with mysql remote server Help	1	1956	April 16, 2021
[FIXED] Using Mysql instead of sqllite3 on a raspberry pi Help	3	513	October 8, 2020
[FIXED] Running on Raspberry PI with mySQL Help	3	1066	October 8, 2020
[SOLVED] Docker and MySQL Help	9	2617	October 3, 2020
I want to use an external database! Help	9	4959	March 31, 2021

Difficulty migrating my sqlite backend to mariadb backend

Related Topics