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:
After ordering, first sort_order value MUST be 0
There MUST NOT be gaps between sort_order values (like 0,2,3,4 where 1 is missed).
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.
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
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.