- Feature Requests
-
New Feature
Galera/XtraDB
After using TeamPass for a bit, i was looking to make the environment Highly Available.
One way of doing this with MySQL/MariaDB/Percona is using the Galera Clustering. The problem is that the data structure for TeamPass is not supported on Galera/XtraDB environments since every table must have a unique id/primary key to ensure replication.
Looking at the table structure, it looks like some of the tables may be as simple as changing the id column to be a primary key but others look like they are used without a true id column. I'm not 100% sure that will allow support with Galera (there might be more - still researching).
What i do know is that I cannot install on a Galera DB and if I install on a non-Galera DB export to a Galera Cluster, the application doesn't show folders or users properly under the admin console.
-David
Customer support service by UserEcho
We use Galera+Xtradb here (for a different application though). I believe it should work (with teampass) from what I've seen, but would advise setting the Galera nodes to do single-thread-only repllication ( wsrep_slave_threads=1 ) to avoid any chance of a race condition over referential integrity loss. This is a good setting to use on read-mostly galera databases anyway. The lack of a single unique key does put you at more risk if replication goes wrong - as galera will not be able to notice inconsistencies - but the single-thread replication helps protect against that.
Thanks for the suggestion.
I tried what you suggested and restarted the cluster. During Install, I still get the following errors on the DB servers:
2018-02-27T18:58:08.111709Z 0 [Note] End of list of non-natively partitioned tables
2018-02-27T19:12:54.808227Z 2 [ERROR] Slave SQL: Error 'Duplicate key name 'teampass_log_items_id_item_IDX'' on query. Default database: 'teampass'. Query: 'CREATE INDEX teampass_log_items_id_item_IDX ON log_items (id_item,date)', Error_code: 1061
2018-02-27T19:12:54.808252Z 2 [Warning] WSREP: RBR event 1 Query apply warning: 1, 1577077
2018-02-27T19:12:54.808605Z 2 [Warning] WSREP: Ignoring error for TO isolated action: source: 82346138-1bef-11e8-9d5a-6a742f86d462 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 458 trx_id: -1 seqnos (l: 635, g: 1577077, s: 1577076, d: 1577076, ts: 3453010616389726)
2018-02-27T19:14:57.533440Z 2 [ERROR] Slave SQL: Error 'Duplicate key name 'teampass_log_items_id_item_IDX'' on query. Default database: 'teampass'. Query: 'CREATE INDEX teampass_log_items_id_item_IDX ON log_items (id_item,date)', Error_code: 1061
2018-02-27T19:14:57.533478Z 2 [Warning] WSREP: RBR event 1 Query apply warning: 1, 1577127
2018-02-27T19:14:57.533901Z 2 [Warning] WSREP: Ignoring error for TO isolated action: source: 82346138-1bef-11e8-9d5a-6a742f86d462 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 520 trx_id: -1 seqnos (l: 685, g: 1577127, s: 1577124, d: 1577126, ts: 3453133342618811)
2018-02-27T19:57:13.522254Z 2 [ERROR] Slave SQL: Error 'Duplicate key name 'teampass_log_items_id_item_IDX'' on query. Default database: 'teampass'. Query: 'CREATE INDEX teampass_log_items_id_item_IDX ON log_items (id_item,date)', Error_code: 1061
2018-02-27T19:57:13.522284Z 2 [Warning] WSREP: RBR event 1 Query apply warning: 1, 1578747
2018-02-27T19:57:13.522596Z 2 [Warning] WSREP: Ignoring error for TO isolated action: source: 82346138-1bef-11e8-9d5a-6a742f86d462 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 1110 trx_id: -1 seqnos (l: 2330, g: 1578747, s: 1578745, d: 1578746, ts: 3455669346036903)
Sounds like your cluster is already out of synch. I would completely reset your cluster; IE on the non-primary nodes, completey wipe the mysql data directory and restart forcing a full state transfer so that you know all nodes are in synch. Drop the database and recreate it in the new clean cluster. Then run your install via the primary node only (if you have a load balancer over the top or something then make it active-standby rather than active-active to prevent race conditions when doing complex operations). Once all is set up, you can run via the load balancer as you wont be doing large index operations any more.
Thanks for the suggestions. I was unable to get the DB to work with a Galera Cluster.
To be honest, I think you'd be better off having 2 teampass servers with local mysql databases, then set up a master-slave replication from A to B, and have an haproxy over the top in active/passive mode. Set server B to be read-only. Then you have a hot read-only standby, and it's more secure than having a whole galera cluster to bother about. You can also do regular mysql dumps on both servers for backup purposes.