Starting from version 2.31, DHIS2 will be leveraging flyway for database migrations. Flyway is an open-source database migration tool.
Some of the relevant/interesting sections of flyway documentations for dhis2 developers are
The maven project dhis2-support-db-migration under dhis2-support is responsible for handling flyway migrations in dhis2. It contains flyway configurations and all migration scripts. Migration Scripts can be defined both in Java classes or in sql files. Most of the upgrade/migration scripts would ideally fit in .sql files. But in case there is more complex migrations, Java classes can be the way to go.
The location where flyway searches for migration scripts will be org/hisp/dhis/db/migration Therefore all classes (and subpackages) under org.hisp.dhis.db.migration.* will be scanned for java based migrations. Likewise, all sql files in resources under the path org/hisp/dhis/db/migration/* will also be scanned.
Here is a snapshot of how the dhis2-support-db-migration project looks like.
Hibernate hbm2ddl will always be set to validate. Users will not be able to override this setting anymore (using the connection.schema property in dhis.conf).
This also means that when we modify any hbm xml files, we also need to take care of the corresponding DDL scripts ourselves using flyway.
However, since our unit tests are going to be run on H2 , flyway will be disabled for unit tests, and we will let hibernate create the schema i.e hbm2ddl will be set to create only for unit tests.
-
The naming is of the format
V<Major>_<Minor>_<Patch>__<Description_separated_by_underscores>. Eg: V2_31_1__Table_alterations_for_adding_sharing_properties_to_Datastore.sql V2_31_2__Upgrading_Scheduler_to_change_jobparameters_column_to_jsonb.java Please use sensible descriptions separated by underscores for spaces and refrain from using camel cases for multiple words. Thumb rule : Think from a db admin perspective, and ensure the description is self explanatory. -
We may have multiple migration scripts (as sql files or java classes) for a single release. For eg: 2.31.1 , 2.31.2,........, 2.31.100. Developers can decide whether they need to append their migrations to an already existing migration file (Say V2.31.1) or they can create a new migration file (V2.31.2). In most cases, it is ideal to create new migration file for your changes, by picking up the next unused integer for the patch version.
-
Always use lower case for sql scripts added into .sql files.
-
From 2.31, the database will always have a function named generate_uid() for generating uids. Reuse this function for all future versions.
-
Transactions and sql connection resource handlings are done by flyway. Refrain from explicitly beginning/commiting transactions in sql migration scripts. Also refrain from explicitly closing connections in java migration scripts.
-
All migration scripts should be made idempotent as much as possible. Scripts should also consider that it could be executed on a fresh db (with no data). Idempotency in most cases simply means using the IF NOT EXISTS / IF EXISTS wherever possible. For constraints with explicit names, when modifying them, its also advised to drop the constraint (if exists) first and then create the constraint which ensures the scripts are rerunnable without any side effects. In a highly unlikely event of having to write a migration script that cannot be made idempotent in a clean way, just add a comment on top of them and leave them non-idempotent . These would help other developers (if required) to manually undo those migrations during debugging (or they could simply curse you and load a fresh database to start over again 😆 ).
-
Set the configuration property
flyway.migrate_out_of_orderto true indhis.conf. It ensures that if version 2.31.1 and version 2.31.3 are already installed, but the latest build has version 2.31.2, then it tries to apply that too. This is useful for development instances. -
Important: When backporting fixes, always use the latest unused integer for that particular branch in which you are fixing. This means the same script will exist as 2.33.2 (in 2.33/master branch) , as 2.32.9 (in 2.32 branch) and as 2.31.11 (in 2.31 branch). Only backport flyway scripts if they are idempotent . In other words never backport non-idempotent flyway scripts.
flyway_schema_history table looks like this
| installed_rank | version | description | type | script | checksum | installed_by | installed_on | execution_time | success |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 2.30.0 | Populate dhis2 schema if empty database | JDBC | org.hisp.dhis.db.migration.base.V2_30_0__Populate_dhis2_schema_if_empty_database | dhis | 2018-11-01 13:21:06.770871 | 5004 | true | |
| 2 | 2.31.1 | Table alterations for 31 | SQL | 2.31/V2_31_1__Table_alterations_for_31.sql | -1758192732 | dhis | 2018-11-01 13:21:06.781845 | 31 | true |
| 3 | 2.31.2 | Option sharing menu | SQL | 2.31/V2_31_2__Option_sharing_menu.sql | 1231089097 | dhis | 2018-11-01 13:21:06.818149 | 10 | true |
| 4 | 2.31.3 | DataStore sharing menu | SQL | 2.31/V2_31_3__DataStore_sharing_menu.sql | -26438716 | dhis | 2018-11-01 13:21:06.832763 | 9 | true |
| 5 | 2.31.4 | Sample java based migration | JDBC | org.hisp.dhis.db.migration.v31.V2_31_4__Sample_java_based_migration | dhis | 2018-11-01 13:21:06.846571 | 0 | true | |
| 6 | 2.31.5 | Create chart yearlyseries table | SQL | 2.31/V2_31_5__Create_chart_yearlyseries_table.sql | -963276479 | dhis | 2018-11-01 13:21:06.850797 | 4 | true |
- Error : FlywayException: Validate failed: Migration description mismatch for migration version 2.30.0
Reason: This is because a previous war had already executed the migration v2.30.0 (either java or sql based). Your current war has the same version, but the description has changed. In short, the migration file name has been renamed since your previous deployment.
Possible quick resolution: Delete the failing version row from flyway_schema_history table and start your application again.
- Error : FlywayException: Validate failed. Found differences between applied migrations and available migrations: Migration Checksum mismatch for migration V2_31_3__Jsonb_changes.sql
Reason: This is because a previous war had already executed the migration v2.31.3 (sql based). Your current war has the same version, but the content of the migration file has changed. In short, the migration file content has been modified since your previous deployment.
Possible quick resolution: Delete the failing version row from flyway_schema_history table and start your application again.
- Error : FlywayException: Validate failed: Detected applied migration not resolved locally: 2.32.1
Reason: This error tells you that your database has 2.32.1 which is not present in your classpth. The most probable cause is you are trying to deploy an older version of dhis2 into a newer version of the database. For example, a successfull deployment of a 2.32 war onto a 2.31 db , will upgrade the db into a 2.32 db. From then on, you cannot deploy a 2.31 war on that same db again.
Resolution : If you get the error and you are certain that you are not deploying an old version of war into a new db, then please contact the Flyway Support Helpdesk.
- Error : FlywayException: Validate failed: Detected resolved migration not applied to database: 2.31.2
Reason: This error means that your classpath/war has 2.31.2 script which is not present in your database but you have versions greater than 2.31.2 in your database. This should happen only in development streams where you are working with bleeding edge build (latest build from master). The scripts are added based on the order of developer branches getting merged into master, which may not always be in sequence.
Possible quick resolution: Add the configuration property flyway.migrate_out_of_order=true in your dhis.conf .
Resolution : If you continue to get the error even after having the configuration property, then please contact the Flyway Support Helpdesk.
- Error : Syntax error at or near "NOT"
Reason: Your postgres version is lower than the required 9.6.
Resolution: Upgrade your postgres to version 9.6 or higher.
- Error : You can't operate on a closed Connection!!!
Reason: In you java migration class, you have explicitly closed the connection.
Resolution. Do not close the connection. Do not use try with resources on the connection object (which closes the connection at the end of try).
- Error : Schema-validation: missing column [uid] in table [relationship]
Reason: Hibernate does the schema validation after flyway has migrated the db. This error means that there are some changes in some of the hbm.xml files, but the same has not been applied through flyway scripts.
Resolution : Add the corresponding alter table statements into a new(or existing) flyway migration script file.
Note: Appending scripts to an already existing script (which may already have been applied to the db you are working), will have some consequences for development instances. On those instances, flyway validates whether there is any checksum mismatches(file changes) and fails. If flyway fails because of a latest pull of other developers work (or your own work) which has modified one of the installed scripts, you can explicitly delete the row with DELETE from flyway_schema_history where installed_rank=4 and then restart your application. Flyway then considers the script as an uninstalled version and proceeds to apply that script. For this reasons, in development instances you need to have flyway outOfOrder setting to true. This can be done by setting the configuration property flyway.migrate_out_of_order to true in dhis.conf. In case deleting the record also does not work, then you may have to start with a fresh demo db again.
- Error : Checksum mismatch for version 2.31.1
Reason: A bug was introduced in 2.31 which was corrected later in the 2.31.1 script. This leads to a checksum mismatch when upgrading.
Resolution: Run the following SQL:
update flyway_schema_history set checksum = '-271885416' where version = '2.31.1';
Conversely, if you for some reason are reverting to an older version you can run the following SQL:
update flyway_schema_history set checksum = '1602390773' where version = '2.31.1';