Fix: Error 500 in /api/visualizations due to unexpected order in table visualization_organisationunits

Development - Développement
1

We are facing the issue of “wrong sorted items” in dhis2 instances that are running for a long time.

Environment: dhis2 instance running for a long time (several upgrades perfomed) and currently on version 40.4.1

Case: a GET request to api/visualizations?paging=false endpoint returns 500 (the message could appear or not).

{
httpStatus: "Internal Server Error",
httpStatusCode: 500,
status: "ERROR",
message: "Cannot invoke "org.hisp.dhis.organisationunit.OrganisationUnit.getUid()" because "organisationUnit" is null"
}

Reason: There are visualizations with an unexpected order in the table visualization_organisationunits. The expected values of the sort_order are 0,1,2,3,4....
The two main rules are:

  1. After ordering, first sort_order value MUST be 0
  2. There MUST NOT be gaps between sort_order values (like 0,2,3,4 where 1 is missed).

Example of a correct order
Example of a correct order525×171 2.46 KB

Solution: Run a SQL function created specifically for this issue. You can access it here

Do you have another examples to share ?

1 Like
2

Hi @ctejo

Thank you for your informative post which hopefully will help many. Do you know why this issue is happening in the first place? We might benefit from creating a Jira ticket if this is a bug issue.

Thanks!

3

Hi @Gassim ,

Honestly, we haven’t digged more, so we haven’t reproduced the issue from the beginning.