Database
Database management
The server uses a series of database changesets to automatically initialise and update its tables, indexes and occasionally also the contained data, to accommodate new software versions. Each time the server starts up, the system checks for new changesets to apply before completing the bootstrap sequence.
The database update begins by acquiring a lock on the database, to prevent multiple clients from simultaneously running updates on the server. The update then begins a transaction. If the update fails, all database operations will be rolled back and leave the database in the exact same state that it had before the update begun.
Once a changeset has been successfully applied to the server, it will not be applied again. Please keep this in mind, since it might have consequences in rollback scenarios. When a new version contains a changeset that might need special treatment in case of rollback, this should be clearly stated in the release notes.
WARNING: If the server is shut down during the startup sequence, before the database lock is released, the lock will remain indefinitely and needs to be removed manually.
The beginning and end of the update task is indicated in the application log with the lines
[DbManagementVerticle] INFO: Running database management... [DbManagementVerticle] INFO: Database management finished
If the second line fails to show up within reasonable time, it might indicate that a previous unreleased lock is stopping the update task from running.
Make sure that all servers that are accessing the database is shut down, then log into the database and check if this is the case by running the following query
SELECT * FROM DATABASECHANGELOGLOCK
If the result shows is similar to
| ID | LOCKED | LOCKGRANTED | LOCKEDBY |
|---|---|---|---|
| 1 | 1 | 2021-04-22 08:27:26.588 |
10.0.1.28 (10.0.1.28) |
the database lock has not been properly released. Remove it by deleting the row:
DELETE FROM DATABASECHANGELOGLOCK
Important: before deleting the lock, make absolutely sure that no server is waiting to executing the update job.
Managing upgrade to 1.13.6 or later
Versions previous to 1.13.6 are vulnerable to a potential bug, that introduces inconsistencies in the database that prevent users from seeing their orders in the UI. Version 1.13.6 fixes this bug, but if the bug has been triggered, it is necessary to fix the inconsistencies before applying the upgrade.
To check if there are inconsistencies in the database, first run the following query to find orders that have multiple active documents:
WITH t1 AS (SELECT order_internal_id, COUNT(*) AS active_documents FROM documents WHERE state = 1 GROUP BY order_internal_id) SELECT * FROM t1 WHERE active_documents != 1
For each row in the result, take a note of order_internal_id and execute the following queries, replacing <order_internal_id> with the actual id:
UPDATE documents SET state = 0 WHERE order_internal_id =<order_internal_id>
UPDATE requested_signers SET action = 0, action_timestamp = NULL, action_message = NULL, notification = 1, reminder = 1 WHERE action != 8 AND order_internal_id =<order_internal_id>
These two updates will block the orders from all except the solicitor and make sure that no notifications are sent. Make sure to notify the order solicitor that the order needs to be re-created.