Galera cluster and Vaultwarden

[Tivin-i]

Just making a note here for everyone who may stumble across the same issue in the future - this is related for MariaDB Galera Clusters.

If you get an error about primary keys on a glaera cluster, check that innodb_force_primary_key = 0 in your galera.conf file, if not then temporarily set it; once the vaultwarden database is up, you can set it back to 1.

Now waiting for an upgrade to see how it may break again 🤣

[BlackDex]

@Tivin-i you might better take a look at https://github.com/dani-garcia/vaultwarden/wiki/Using-the-MariaDB-%28MySQL%29-Backend#foreign-key-errors-collation-and-charset

That should solve your issues permanently.

[Tivin-i]

I did, did not work.
Can't do the migration because of the enforcement of the primary key by the engine.

[BlackDex]

You have validated that all the tables and the database has the correct collation and charset?

[Tivin-i]

I have! Several times :)
Followed the instructions to the tee.
Then stumbled across the config change when troubleshooting primary key issues.

Let me bring up a test instance.

This is the error from vaultwarden:

vaultwarden-test  | [2023-12-07 06:42:25.650][panic][ERROR] thread 'main' panicked at 'Error running migrations: QueryError(DieselMigrationName { name: "2022-03-02-210038_update_devices_primary_key", version: MigrationVersion("20220302210038") }, DatabaseError(Unknown, "This table type requires a primary key"))': src/db/mod.rs:473
vaultwarden-test  |    0: vaultwarden::init_logging::{{closure}}
vaultwarden-test  |    1: std::panicking::rust_panic_with_hook
vaultwarden-test  |    2: std::panicking::begin_panic_handler::{{closure}}
vaultwarden-test  |    3: std::sys_common::backtrace::__rust_end_short_backtrace
vaultwarden-test  |    4: rust_begin_unwind
vaultwarden-test  |    5: core::panicking::panic_fmt
vaultwarden-test  |    6: core::result::unwrap_failed
vaultwarden-test  |    7: vaultwarden::db::mysql_migrations::run_migrations
vaultwarden-test  |    8: vaultwarden::db::DbPool::from_config
vaultwarden-test  |    9: vaultwarden::main::{{closure}}
vaultwarden-test  |   10: vaultwarden::main
vaultwarden-test  |   11: std::sys_common::backtrace::__rust_begin_short_backtrace
vaultwarden-test  |   12: std::rt::lang_start::{{closure}}
vaultwarden-test  |   13: std::rt::lang_start_internal
vaultwarden-test  |   14: main
vaultwarden-test  | 
vaultwarden-test exited with code 0

This is the error from the db server log:

2023-12-07  6:42:24 8 [ERROR] Slave SQL: Error 'This table type requires a primary key' on query. Default database: 'vaultwarden-test'. Query: '-- First remove the previous primary key
ALTER TABLE devices DROP PRIMARY KEY', Internal MariaDB error code: 1173
2023-12-07  6:42:24 8 [Warning] WSREP: Ignoring error 'This table type requires a primary key' on query. Default database: 'vaultwarden-test'. Query: '-- First remove the previous primary key
ALTER TABLE devices DROP PRIMARY KEY', Error_code: 1173
2023-12-07  6:42:32 10 [ERROR] Slave SQL: Error 'This table type requires a primary key' on query. Default database: 'vaultwarden-test'. Query: '-- First remove the previous primary key
ALTER TABLE devices DROP PRIMARY KEY', Internal MariaDB error code: 1173
2023-12-07  6:42:32 10 [Warning] WSREP: Ignoring error 'This table type requires a primary key' on query. Default database: 'vaultwarden-test'. Query: '-- First remove the previous primary key
ALTER TABLE devices DROP PRIMARY KEY', Error_code: 1173

Output of SHOW CREATE TABLE `users`;

| users | CREATE TABLE `users` (
  `uuid` char(36) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
 -----
  `stamp_exception` text DEFAULT NULL,
  `api_key` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`uuid`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

Output of SHOW CREATE DATABASE `vaultwarden`;

MariaDB [vaultwarden-test]> SHOW CREATE DATABASE `vaultwarden`;
+-------------+----------------------------------------------------------------------------------------------------+
| Database    | Create Database                                                                                    |
+-------------+----------------------------------------------------------------------------------------------------+
| vaultwarden | CREATE DATABASE `vaultwarden` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
+-------------+----------------------------------------------------------------------------------------------------+

[BlackDex]

And what about SHOW CREATE TABEL devices;?

[Tivin-i]

| devices | CREATE TABLE `devices` (
  `uuid` char(36) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `user_uuid` char(36) NOT NULL,
  `name` text NOT NULL,
  `atype` int(11) NOT NULL,
  `push_token` text DEFAULT NULL,
  `refresh_token` text NOT NULL,
  `twofactor_remember` text DEFAULT NULL,
  PRIMARY KEY (`uuid`),
  KEY `user_uuid` (`user_uuid`),
  CONSTRAINT `devices_ibfk_1` FOREIGN KEY (`user_uuid`) REFERENCES `users` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

[BlackDex]

Strange, might be a galera specific item then.
I Have not tested galera my aelf at all.

it might also be something strange in the devices table.
One way to solve that is too fully clear that table. That does result in every device needs to login again.

[Tivin-i]

I did try that, unfortunately the error is during the initialisation phase of vaultwarden, I can't bring up the instance until db population has finished.
Is there some dump file to build the db?

[Tivin-i]

Or would I have to go the create vaultwarden with sqlite > migrate to mysql?

[BlackDex]

We do not have a dump, since it could change everytime.
All changes are done via the migrations.

Also, we do disable foreignkey's during the migration. I'm not sure if that innodb setting is part of that flag.

[Tivin-i]

Just wanted to update, that there were no breaks after updates.