OK, finally I got it!
Scenario:
Two pairs of organisation units needs to be merged. When the merging is done using the Front-end functionality, the resultant message displays “Merging done” BUT in fact the merging did not take place.
Symptoms:
The orgunit hierarchy doesn’t change at all;
In the catalina-out log file, the following error can be seen: “Delete was not allowed by ReportTableDeletionHandler” with error messages before and after this line.
First case: (No tracker data linked to the OU to be eliminated)
Delete the specific orgunit entry for the unit to be eliminated from the reporttable_organisationunits and run the update as Jason indicated below. Re-run the merge operation.
Second case: (Tracker data exists against the OU to be eliminated)
Re-assign all Tracker data linked to this orgunit to the one to be preserved, and THEN perform the step in case 1.
Both cases was resolved.
Thanks for the support Jason!
There is a table “reporttable_organisationunit” which is causing this error. The deletion handler refuses to mess with this table [The orgunit that needs to be eliminated as part of the merging operation. I am not sure why this is the case. Maybe the devs can answer.
You will need to manually delete the offending organisation unit from reporttable_organisationunit, but you must be careful here, as this table represents a Java list. If you delete the organisation unit from the table, the list will have an empty member in the list, and will result in a null pointer exception when executing the report. So, you must reorder everything as part of deleting the organisation unit. You can accomplish this with a query like
update reporttable_organisationunits set sort_order = -t.i
from (select row_number() over (ORDER BY sort_order) as i, reporttableid, sort_order, organisationunitid
from reporttable_organisationunits where reporttableid=A order by sort_order) t
where reporttable_organisationunits.organisationunitid = t.organisationunitid and reporttable_organisationunits.reporttableid=A;
where “A” is the report table ID which you need to fix.
···
From: Ferdie Botha [mailto:ferdie@hisp.org]
Sent: 01 July 2015 05:31 PM
To: ‘Jason Pickering’
Cc: ‘Knut Staring’; ‘dhis2-users@lists.launchpad.net’
Subject: RE: [Dhis2-users] SQL query pointers requested
Jason – just double-checking – being a bit slow here…
You said below I need to DELETE the offending unit – but your script is an UPDATE script. What am I missing?
When running the update script, I get duplicate key errors. Should I change the UPDATE to DELETE?
See attached spreadsheet for explanation of what expired in the meantime…
Thanks for your assistance!
Ferdie
From: Jason Pickering [mailto:jason.p.pickering@gmail.com]
Sent: 01 July 2015 02:49 PM
To: Ferdie Botha
Cc: Knut Staring; dhis2-users@lists.launchpad.net
Subject: Re: [Dhis2-users] SQL query pointers requested
To be quite honest, I am not sure if it is a bug. DHIS2 is doing what it is supposed to do. There is no error. It is simply that the deletion handler cannot deal with the fact that you are trying to delete an organisation unit which has a report table linked to it. Sounds more like a feature request/blueprint to me.
Regards,
Jason
On Wed, Jul 1, 2015 at 2:35 PM, Ferdie Botha ferdie@hisp.org wrote:
Thanks Jason! I’ll confirm when I could get this done. (Hopefully today still). Should I report this as a bug?
From: Jason Pickering [mailto:jason.p.pickering@gmail.com]
Sent: 01 July 2015 08:51 AM
To: Ferdie Botha; Knut Staring
Cc: dhis2-users@lists.launchpad.net
Subject: Re: [Dhis2-users] SQL query pointers requested
Hi Ferdie,
This is a bit of a pain. There is a table “reporttable_organisationunit” which is causing this error. The deletion handler refuses to mess with this table. I am not sure why this is the case. Maybe the devs can answer.
You will need to manually delete the offending organisation unit from reporttable_organisationunit, but you must be careful here, as this table represents a Java list. If you delete the organisation unit from the table, the list will have an empty member in the list, and will result in a null pointer exception when executing the report. So, you must reorder everything as part of deleting the organisation unit. You can accomplish this with a query like
update reporttable_organisationunits set sort_order = -t.i
from (select row_number() over (ORDER BY sort_order) as i, reporttableid, sort_order, organisationunitid
from reporttable_organisationunits where reporttableid=A order by sort_order) t
where reporttable_organisationunits.organisationunitid = t.organisationunitid and reporttable_organisationunits.reporttableid=A;
where “A” is the report table ID which you need to fix.
As I said, this is a bit of a pain, but should work.
Regards,
Jason
On Tue, Jun 30, 2015, 15:14 Ferdie Botha ferdie@hisp.org wrote:
Hi there –
Thanks for your responses. I’ll try to give the bigger picture being less cryptic …
A new instance of the db was created from the client’s Live (2.18) instance (now called ‘training’, running Tomcat 8 and Postgresql 9.4), and then upgraded to dhis2.19.
On this new instance -
· I ran the Maintenance function with options shown as attached;
· I ran Analytics with it’s defaults as attached;
· I went to the Merge functionality screen;
· I made a copy of the catalina.out file;
· I performed the merge as attached;
· I then made a second copy of the catalina.out file and extracted the changes, also attached.
In the catalina.out file (attached file on line 7) I saw an error where the delete of the indicated orgunit was ‘prohibited’ “Delete was not allowed by ReportTableDeletionHandler” .
I’m unsure what this really means and how this can be resolved?
(If required, I can provide you a sql dump of the training instance – it is currently undergoing lots of changes and doesn’t have a lot of data in it).
Kind regards
Ferdie
From: Jason Pickering [mailto:jason.p.pickering@gmail.com]
Sent: 18 June 2015 05:24 PM
To: Knut Staring; Ferdie Botha
Cc: dhis2-users@lists.launchpad.net
Subject: Re: [Dhis2-users] SQL query pointers requested
After the query, you must clear the DHIS2 cache. Did you do this?
This operation could be more reliably done with the hierarchy operations, followed by an organization unit merge (through the user interface).
Regards,
Jason
On Thu, Jun 18, 2015, 16:59 Knut Staring knutst@gmail.com wrote:
Do you see anything in the Tomcat log when you try to merge?
Knut
On Thu, Jun 18, 2015 at 3:43 PM, Ferdie Botha ferdie@hisp.org wrote:
Hi all, I need someone to point me in the right direction on the SQL backend, using DHIS2.18.
I moved 2 orgunits from 1 parentid to another using the following 2 sql statements:
update organisationunit set parentid = ‘14948’ where organisationunitid = ‘632’ ; – re-link parentid to ls_ap_Additional Partners instead of District Partners
update organisationunit set parentid = ‘14948’ where organisationunitid = ‘633’ ; – re-link parentid to ls_ap_Additional Partners instead of District Partners
Now I want to merge these 2 orgunits, but no luck. I tested the merging with 2 dummy units and this worked fine. Anything I can go check/verify to look for the cause of this?
Thanks!
…
Ferdie Botha
Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp
–
Knut Staring
Dept. of Informatics, University of Oslo
Norway: +4791880522
Skype: knutstar
http://dhis2.org
Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049