Cannot generate analytics tables

[JaimeLopez]

Hello.

We have a 2.40.7 version of dhis2 installed in one of our production servers that is throwing errors when I try to generate analytics tables. Also there is a problem showing images inside traker events. Both are related and I will try to explain why I think so.
Last week we upgraded the server from 2.36.13.2 version. The server is Ubuntu 20.04.
When I try to generate analytics several error messages are shown “ERROR: more than one row returned by a subquery used as an expression”. At the end of this message I copy one of them.
When we try to access the events in tracker capture, the images in the events do not show up and this is shown:

And If the image is clicked this error occurs:

I am trying to know which table is the one with the duplicated data and so far I managed to look into programstageinstance. I understand that this table should have unique ID column programstageinstaceid but when selecting its contents from psql comand line it shows this (the select command is this: select programstageinstanceid, count(programstageinstanceid) from programstageinstance group by programstageinstanceid;):

I hope someone can help me.

Here is the complete message when I try to populate analytics tables:

StatementCallback; SQL [insert into analytics_event_temp_a4voowanl2i_2023 (“uidlevel1”,“uidlevel2”,ounamehierarchy,“daily”,“weekly”,“weeklywednesday”,“weeklythursday”,“weeklysaturday”,“weeklysunday”,“biweekly”,“monthly”,“bimonthly”,“quarterly”,“quarterlynov”,“sixmonthly”,“sixmonthlyapril”,“sixmonthlynov”,“yearly”,“financialapril”,“financialjuly”,“financialoct”,“financialnov”,“b4MDRnPFbLF”,“hduXLzrnlYC”,“jcXIUjObbbn”,“psi”,“pi”,“ps”,“ao”,“enrollmentdate”,“incidentdate”,“executiondate”,“duedate”,“completeddate”,“created”,“lastupdated”,“storedby”,“createdbyusername”,“createdbyname”,“createdbylastname”,“createdbydisplayname”,“lastupdatedbyusername”,“lastupdatedbyname”,“lastupdatedbylastname”,“lastupdatedbydisplayname”,“pistatus”,“psistatus”,“psigeometry”,“longitude”,“latitude”,“ou”,“ouname”,“oucode”,“oulevel”,“ougeometry”,“pigeometry”,“registrationou”,“enrollmentou”,“tei”,“teigeometry”) select ous.“uidlevel1”,ous.“uidlevel2”,concat_ws(’ / ',ous.namelevel1,ous.namelevel2) as ounamehierarchy,dps.“daily”,dps.“weekly”,dps.“weeklywednesday”,dps.“weeklythursday”,dps.“weeklysaturday”,dps.“weeklysunday”,dps.“biweekly”,dps.“monthly”,dps.“bimonthly”,dps.“quarterly”,dps.“quarterlynov”,dps.“sixmonthly”,dps.“sixmonthlyapril”,dps.“sixmonthlynov”,dps.“yearly”,dps.“financialapril”,dps.“financialjuly”,dps.“financialoct”,dps.“financialnov”,(select eventdatavalues #>> ‘{b4MDRnPFbLF, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “b4MDRnPFbLF”,(select eventdatavalues #>> ‘{hduXLzrnlYC, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “hduXLzrnlYC”,(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=7911) as “jcXIUjObbbn”,psi.uid,pi.uid,ps.uid,ao.uid,pi.enrollmentdate,pi.incidentdate,psi.executiondate,psi.duedate,psi.completeddate,CASE WHEN psi.createdatclient IS NOT NULL THEN psi.createdatclient ELSE psi.created END,CASE WHEN psi.lastupdatedatclient IS NOT NULL THEN psi.lastupdatedatclient ELSE psi.lastupdated END,psi.storedby,psi.createdbyuserinfo ->> ‘username’ as createdbyusername,psi.createdbyuserinfo ->> ‘firstName’ as createdbyname,psi.createdbyuserinfo ->> ‘surname’ as createdbylastname,case when coalesce(trim(psi.createdbyuserinfo ->> ‘surname’), ‘’) = ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘firstName’), ‘’) = ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘username’), ‘’) = ‘’ then null when coalesce(trim(psi.createdbyuserinfo ->> ‘surname’), ‘’) = ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘firstName’), ‘’) = ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘username’), ‘’) <> ‘’ then trim(psi.createdbyuserinfo ->> ‘username’) when coalesce(trim(psi.createdbyuserinfo ->> ‘surname’), ‘’) = ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘firstName’), ‘’) <> ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘username’), ‘’) = ‘’ then trim(psi.createdbyuserinfo ->> ‘firstName’) when coalesce(trim(psi.createdbyuserinfo ->> ‘surname’), ‘’) <> ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘firstName’), ‘’) = ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘username’), ‘’) = ‘’ then trim(psi.createdbyuserinfo ->> ‘surname’) when coalesce(trim(psi.createdbyuserinfo ->> ‘surname’), ‘’) <> ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘firstName’), ‘’) <> ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘username’), ‘’) = ‘’ then concat(trim(psi.createdbyuserinfo ->> ‘surname’), ‘, ‘, trim(psi.createdbyuserinfo ->> ‘firstName’)) when coalesce(trim(psi.createdbyuserinfo ->> ‘surname’), ‘’) = ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘firstName’), ‘’) <> ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘username’), ‘’) <> ‘’ then concat(trim(psi.createdbyuserinfo ->> ‘firstName’), ’ (’, trim(psi.createdbyuserinfo ->> ‘username’), ‘)’) when coalesce(trim(psi.createdbyuserinfo ->> ‘surname’), ‘’) <> ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘firstName’), ‘’) = ‘’ and coalesce(trim(psi.createdbyuserinfo ->> ‘username’), ‘’) <> ‘’ then concat(trim(psi.createdbyuserinfo ->> ‘surname’), ’ (’, trim(psi.createdbyuserinfo ->> ‘username’), ‘)’) else concat(trim(psi.createdbyuserinfo ->> ‘surname’), ', ‘, trim(psi.createdbyuserinfo ->> ‘firstName’), ’ (’, trim(psi.createdbyuserinfo ->> ‘username’), ‘)’) end as createdbydisplayname,psi.lastupdatedbyuserinfo ->> ‘username’ as lastupdatedbyusername,psi.lastupdatedbyuserinfo ->> ‘firstName’ as lastupdatedbyname,psi.lastupdatedbyuserinfo ->> ‘surname’ as lastupdatedbylastname,case when coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘surname’), ‘’) = ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘firstName’), ‘’) = ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘username’), ‘’) = ‘’ then null when coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘surname’), ‘’) = ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘firstName’), ‘’) = ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘username’), ‘’) <> ‘’ then trim(psi.lastupdatedbyuserinfo ->> ‘username’) when coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘surname’), ‘’) = ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘firstName’), ‘’) <> ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘username’), ‘’) = ‘’ then trim(psi.lastupdatedbyuserinfo ->> ‘firstName’) when coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘surname’), ‘’) <> ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘firstName’), ‘’) = ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘username’), ‘’) = ‘’ then trim(psi.lastupdatedbyuserinfo ->> ‘surname’) when coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘surname’), ‘’) <> ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘firstName’), ‘’) <> ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘username’), ‘’) = ‘’ then concat(trim(psi.lastupdatedbyuserinfo ->> ‘surname’), ‘, ‘, trim(psi.lastupdatedbyuserinfo ->> ‘firstName’)) when coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘surname’), ‘’) = ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘firstName’), ‘’) <> ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘username’), ‘’) <> ‘’ then concat(trim(psi.lastupdatedbyuserinfo ->> ‘firstName’), ’ (’, trim(psi.lastupdatedbyuserinfo ->> ‘username’), ‘)’) when coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘surname’), ‘’) <> ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘firstName’), ‘’) = ‘’ and coalesce(trim(psi.lastupdatedbyuserinfo ->> ‘username’), ‘’) <> ‘’ then concat(trim(psi.lastupdatedbyuserinfo ->> ‘surname’), ’ (’, trim(psi.lastupdatedbyuserinfo ->> ‘username’), ‘)’) else concat(trim(psi.lastupdatedbyuserinfo ->> ‘surname’), ', ‘, trim(psi.lastupdatedbyuserinfo ->> ‘firstName’), ’ (’, trim(psi.lastupdatedbyuserinfo ->> ‘username’), ‘)’) end as lastupdatedbydisplayname,pi.status,psi.status,psi.geometry,CASE WHEN ‘POINT’ = GeometryType(psi.geometry) THEN ST_X(psi.geometry) ELSE null END,CASE WHEN ‘POINT’ = GeometryType(psi.geometry) THEN ST_Y(psi.geometry) ELSE null END,ou.uid,ou.name,ou.code,ous.level,ou.geometry,pi.geometry,coalesce(registrationou.uid,ou.uid),coalesce(enrollmentou.uid,ou.uid),tei.uid,tei.geometry from programstageinstance psi inner join programinstance pi on psi.programinstanceid=pi.programinstanceid inner join programstage ps on psi.programstageid=ps.programstageid inner join program pr on pi.programid=pr.programid and pi.deleted is false inner join categoryoptioncombo ao on psi.attributeoptioncomboid=ao.categoryoptioncomboid left join trackedentityinstance tei on pi.trackedentityinstanceid=tei.trackedentityinstanceid and tei.deleted is false left join organisationunit registrationou on tei.organisationunitid=registrationou.organisationunitid inner join organisationunit ou on psi.organisationunitid=ou.organisationunitid left join _orgunitstructure ous on psi.organisationunitid=ous.organisationunitid left join _organisationunitgroupsetstructure ougs on psi.organisationunitid=ougs.organisationunitid and (cast(date_trunc(‘month’, CASE WHEN ‘SCHEDULE’ = psi.status THEN psi.duedate ELSE psi.executiondate END) as date)=ougs.startdate or ougs.startdate is null) left join organisationunit enrollmentou on pi.organisationunitid=enrollmentou.organisationunitid inner join _categorystructure acs on psi.attributeoptioncomboid=acs.categoryoptioncomboid left join _dateperiodstructure dps on cast(CASE WHEN ‘SCHEDULE’ = psi.status THEN psi.duedate ELSE psi.executiondate END as date)=dps.dateperiod where psi.lastupdated < ‘2025-03-17T12:45:47’ and (CASE WHEN ‘SCHEDULE’ = psi.status THEN psi.duedate ELSE psi.executiondate END) >= ‘2023-01-01T00:00:00’ and (CASE WHEN ‘SCHEDULE’ = psi.status THEN psi.duedate ELSE psi.executiondate END) < ‘2024-01-01T00:00:00’ and pr.programid=7918 and psi.organisationunitid is not null and (CASE WHEN ‘SCHEDULE’ = psi.status THEN psi.duedate ELSE psi.executiondate END) is not null and dps.year >= 1975 and dps.year <= 2050 and psi.status in (‘COMPLETED’,‘ACTIVE’,‘SCHEDULE’)and psi.deleted is false ]; ERROR: more than one row returned by a subquery used as an expression; nested exception is org.postgresql.util.PSQLException: ERROR: more than one row returned by a subquery used as an expression

Thank you

[Gassim (AL-Gassim Sharaf Addin)]

Hi @JaimeLopez

Sorry you’re facing this issue. I suspect that the issue you are facing has to do with a data issue as explained below by @maikel

@JaimeLopez

ERROR: more than one row returned by a subquery used as an expression

@maikel

After reading the log file you shared, it seems like one of the “sub-select” statements is returning more than one row when we are expecting only one. Having more than one row could be a data issue.

As we are inserting one value for each column, we must have one unique value (from the “sub-select”) per column.

You can see all the “sub-select” statements that I could isolate from the log in the file attached.

Do you think you or someone in your team would be able to identify which of those “sub-select” queries are returning two (or more) or rows? This will help to narrow the problem.

[JaimeLopez]

Hello.

We will try to isolate the subquery and I will come back to you.

thank you

[JaimeLopez]

Hello.

I isolated the first subquery.
If I try this select sentence:

select (select eventdatavalues #>> ‘{b4MDRnPFbLF, value}’ from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as “b4MDRn PFbLF” from programstageinstance psi inner join programinstance pi on psi.programinstanceid=pi.programinstanceid;

This is the result:

Further more, If I perform a query the programstageinstaceid table with that eventdatavalues field and the programstageinstanceid field this is the result:

select programstageinstanceid, eventdatavalues #>> ‘{b4MDRnPFbLF, value}’ from programstageinstance order by programstageinstanceid desc limit 100;

So the tabe that has the duplicates is programstageinstace in the field programstageinstanceid.

Would it be safe if I implement a constraint based on that field?
Would postgres cosider a unique record and discard the other records?

Thank you.

[JaimeLopez]

Hello.

I managed to solve the problem. I post here the soultion in case anyone needs it.
I realized that the problem was with the programstageinstance table. First I performed some tests. The records in the table were unique but regarding the field code (which is the unique key in the table). They were not unique regarding the programstageinstanceid field. All the other fields of the table had the same value for an specific programstageinstanceid. an therefore, the query of the api showed the “More than one entity found” error. This is an example:

I tried several ways to recover the uniqueness of the field programstageinstanceid field. This is the one that worked at last:

1 - Copy the unique records to a temp table:

CREATE TABLE programstageinstancebk
AS
SELECT programstageinstanceid, uid, min(code) as code, created, lastupdated, createdatclient, lastupdatedatclient, lastsynchronized, programinstanceid, programstageid, attributeoptioncomboid, deleted, storedby, duedate, executiondate, organisationunitid, status, completedby, completeddate, geometry, eventdatavalues, assigneduserid, createdbyuserinfo, lastupdatedbyuserinfo
FROM programstageinstance
GROUP BY programstageinstanceid, uid, created, lastupdated, createdatclient, lastupdatedatclient, lastsynchronized, programinstanceid, programstageid, attributeoptioncomboid, deleted, storedby, duedate, executiondate, organisationunitid, status, completedby, completeddate, geometry, eventdatavalues, assigneduserid, createdbyuserinfo, lastupdatedbyuserinfo;

2 - Rename the tables:
ALTER TABLE programstageinstance RENAME TO programstageinstanceold;
ALTER TABLE programstageinstancebk RENAME TO programstageinstance;

3 - Check te result: I can now access the images files properly without problems and the Analytics tables are generated.

4 - Delete the old table
drop table programstageinstanceold

The analtics tables are now generated without errors. I have to note down that it took some time and I had to be patient after clicking the button manually,

The dashboards are now back to normal (they were showing an error) and the images included in the stages of the patients can be accessed (they showed the “More than one entity found” error)

Thank you.

[Ameen (Ameen Mohamed)]

Hi @JaimeLopez
It feels like your database constraints has been tampered with.
Can you check what is the primary key of the table “programstageinstance” in your database?

The programstageinstanceid is supposed to be the primary key column of that table. In your case, it seems like that constraint has been manually removed at some point?

[JaimeLopez]

Hello @Ameen .

There was no unique constraint in the table.
I included the constraint using this sql statment:
ALTER TABLE programstageinstance
ADD CONSTRAINT UK_programstageinstanceid UNIQUE (programstageinstanceid);

Everything seems to function fine.

Thank you.