Upgrade to 2.41

Support - Assistance technique
1

Hi all,

We are migrating a DHIS2 instance from a very old version (2.23, June 2016, Windows Server 2008 R2) to a new Ubuntu 24.04 LTS server. We successfully completed the full upgrade path through the following steps:

2.23 → 2.30 (Java 8) → 2.31 (Java 8) → 2.33 (Java 11) → 2.35 (Java 11) → 2.38 (Java 17) → 2.40 (Java 17)

All paliers completed successfully with Server startup in [xxxxx] milliseconds confirmed at each step. The 2.40 instance is fully functional — we can log in, dashboards work, analytics regeneration completes in ~30 seconds.

The problem occurs when upgrading from 2.40 to 2.41.

Error

When deploying the 2.41 WAR, Flyway fails on migration V2_41_24__TrackedEntityInstanceToTrackedEntity.sql:

Caused by: org.flywaydb.core.internal.command.DbMigrate$FlywayMigrateException: 
  Migration V2_41_24__TrackedEntityInstanceToTrackedEntity.sql failed

Caused by: org.postgresql.util.PSQLException: 
  ERROR: relation "trackedentity" already exists

Root cause analysis

After the successful 2.40 upgrade, the database contains both tables:

sql

SELECT tablename FROM pg_tables 
WHERE tablename IN ('trackedentity', 'trackedentityinstance');

       tablename
-----------------------
 trackedentity           -- created by 2.40, contains 1 row
 trackedentityinstance   -- legacy table, contains 0 rows

The 2.41 migration script attempts to ALTER TABLE trackedentityinstance RENAME TO trackedentity, but the table trackedentity already exists (created during the 2.40 migration).

What we tried

  1. Renaming trackedentity to trackedentity_backup before running 2.41 → fails with column "trackedentityid" of relation "trackedentity" already exists (the rename succeeds but the column rename step conflicts)
  2. Dropping trackedentityinstance without CASCADE → fails due to 11 foreign key constraints depending on it
  3. Dropping trackedentityinstance with CASCADE → removes foreign keys on relationship, programinstance, programmessage, etc., which then causes further migration failures (column "inactive" does not exist)
  4. Cleaning flyway_schema_history (DELETE WHERE success = false) → no effect as the failed migration is not recorded

Environment

  • Source: DHIS2 2.23 (PostgreSQL 9.5.3, Java 8, Windows Server 2008 R2)
  • Target: Ubuntu Server 24.04 LTS, PostgreSQL 16.13, Java 17 (OpenJDK), Tomcat 9.0.98
  • Database size: ~3 GB, ~1.8M datavalues, 410 org units, 55 users, 0 SQL views
  • Upgrade path: 2.23 → 2.30 → 2.31 → 2.33 → 2.35 → 2.38 → 2.40 (all successful)
  • Last successful Flyway migration: 2.40.34 (add column shortname for validation rule)

Questions

  1. Is there a recommended SQL fix to resolve the trackedentity table conflict before running the 2.41 migration?
  2. Is this a known issue when upgrading from very old versions (pre-2.30) through the full palier chain to 2.40 and then 2.41?
  3. Should the migration script V2_41_24 be idempotent (using IF NOT EXISTS) for this rename operation?

Any guidance would be greatly appreciated. We are currently stable on 2.40 but would like to reach 2.41 before going live in production.

Thank you!

2

Hi @alihabb
Thanks for a detailed post.

My guess is, your current database is not a clean v40 database. The very first attempt of v41 upgrade might have failed due to some other reason. That migration failure may have left some partial migrations in the database. Since the trackedentity table should be created only in v41 and not in v40.

The fact that you see the trackedentity table in your db suggests this.

Flyway migrations are run in “mixed” mode, which means not all migrations are run in the same transaction. Some (or most) changes could be rolled back in case of failure, but not all. This is why creating a backup of the db before an upgrade is recommended. The first failure would lead to an incompatible db state. Retrying the upgrade (i.e attempting to deploy the war file again) would fail due to the incompatibility anyway.

Do you by any chance have a backup of an intermediate version? For example 2.38? And could you try upgrading again through 2.40 (and then take a backup) and then upgrade to 2.41 exactly once and checking if there are any failures? We need to find out the first failure.

Let us know how it goes.

Thanks and regards
Ameen