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

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).

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

Do you have another examples to share ?

1 Like

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!

Hi @Gassim ,

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

Something similar could happen with this error (data dimension item instead of organisation unit)

{
"httpStatus":"Internal Server Error",
"httpStatusCode":500,
"status":"ERROR",
"message":"Cannot invoke \"org.hisp.dhis.common.DataDimensionItem.getExpressionDimensionItem()\" because \"ddi\" is null"}

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

1 Like

Hi @ctejo

Thank you for sharing the SQL function. Does this solve the issue when it happens with ‘data dimension’ or ‘organisation unit’? Or does it work regardless?

Would you like to mark your post as solved? In order to be able to triage this as a bug issue, we would need to be able to reproduce this on any of the play.dhis2.org instances.

Thanks!

Hi @Gassim

On each post I have shared a different SQL function (one for Org Unit other for Data Dimension).

BTW, I have marked my previous post as a solution.

1 Like

That’s quite helpful, thanks!

If you do figure out what is causing the issue, please do share an update. :smiley: