Hello all
We had a dhis2 instance on 2.29 which we upgraded to 2.30=>2.33=>2.36.4
We have some records in reportable and we had to drop that table manually for 2.37 upgrade.
So what will happen to those old reports based on reporttable, can we migrate those records ?
Thanks @jthomas! This is an interesting question. (:
Hi @jthomas
In DHIS 2.34, reporttables and charts were combined into a new object, “visualizations”. As part of the internal migrations, the records should have been moved to the visualizations table when you performed the upgrade from 2.33 => 2.36.4, and should still be available, as DHIS2 core should only be using the visualizations interface. Is that not the case for you?
Kind regards,
Phil
Thanks @phil I checked in my DB with a query like below and also checked few pivot table reports through data visualization app. So it’s working as you told. Thank you
Hello everyone,
I have been trying to upgrade DHIS2 from 2.35.8 to 2.37.2 and unfortunately, it failed while performing database migrations. Take a look at the logs here.
It seems the upgrade fails because it fails to remove “Chart and ReportTable” with this I have the challenge of upgrading but also I am not sure why these tables are being dropped since they contain important visualizations that should still be in the database.
Any help will be appreciated.
Cheers,
Hello @vincentminde while we wait for experts’ opinions, the following are my observations.
I had to drop reporttable cascade to make it work during upgrade. I reported a similar issue here Upgrade to 2.37 Failed - #4 by jthomas
ReportTable seems to be redundant now because everything is based on visualizations table and from 2.37 pivot table is removed, so dropping was okay. I had asked about this here What will happen to old reports based on reporttable on v2.37
If you want to try based on my feedback, please do take backups before dropping.
Please also note: there is an analytics failure issue that exists at the moment for 2.37.1/2 so it would be great to wait for 2.37.3 if it works for your project, just my personal thought
Hello, @jthomas thanks for the response. Based on this I will definitely try dropping those tables manually.
I would also expect that this issue could be handled by flyway. I am wondering whether it should be registered as a Jira issue so when resolved anyone trying to upgrade does not need to encounter the same.
Cheers,
@vincentminde I was going to refer you to this topic page! @jthomas thanks! (I moved the post here because it’s the same topic)
It should probably work, as @phil’s said above:
Yes please @vincentminde or @jthomas (I will tag you in the bug hunters in this month’s copmonthly :D) if you’d like to create a Jira bug issue (or I can create on your behalf)
Please include this in description as noted by @maikel and then I will ask him to follow up on the Jira issue:
SQL State : 2BP01
Error Code : 0
Message : ERROR: cannot drop table reporttable because other objects depend on it
Detail: constraint fk_dashboarditem_reporttables_reporttableid on table dashboarditem_reporttables depends on table reporttable
Hint: Use DROP ... CASCADE to drop the dependent objects too.
Location : org/hisp/dhis/db/migration/2.37/V2_37_17__Remove_Chart_and_ReportTable.sql (/home/dhis2admin/tomcat-dhis/webapps/file:/home/dhis2admin/tomcat-dhis/webapps/ROOT/WEB-INF/lib/dhis-support-db-migration-2.37.0.jar!/org/hisp/dhis/db/migration/2.37/V2_37_17__Remove_Chart_and_ReportTable.sql)
Line : 36
Statement : -- Drop all ReportTable tables
DROP TABLE IF EXISTS reporttable
and this:
at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.resolveFieldValue(AutowiredAnnotationBeanPostProcessor.java:657) ~[spring-beans-.3.6.jar:5.3.6] ... 57 more
Caused by: org.postgresql.util.PSQLException: ERROR: cannot drop table chart because other objects depend on it
Detail: constraint fk_interpretation_chartid on table interpretation depends on table chart constraint fk_objecttranslation_chartid on table charttranslations depends on table chart
Hint: Use DROP ... CASCADE to drop the dependent objects too.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553) ~[postgresql-42.2.19.jar:42.2.19] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285) ~[postgresql-42.2.19.jar:42.2.19] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323) ~[postgresql-42.2.19.jar:42.2.19] at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481) ~[postgresql-42.2.19.jar:42.2.19]
-----------------------
Thanks! 
Hello @Gassim,
I have been experiencing some issues upgrading to version 2.37. I registered a topic here. Is this something that is already fixed in this new release or is there something not right when I am upgrading?
Cheers.
Hello @Gassim . I have registered a Jira Issue for this called Upgrade Failure to 2.37 [cannot drop table reporttable].
Cheers.
Great, thanks! I added the CoP label and a comment to this topic. 
@jthomas @vincentminde, as you can see in the Jira issue the bug has been fixed. Thanks!
Thanks @Gassim
I am still facing the same issue when upgrading, 2.37.6. When you say, it has been resolved @Gassim , which patch?
@moses_mwale sorry the patch will be for: 2.38, 2.37.7
so this is why you still see the issue in 2.37.6
We are still seeing this is issue upgrading from 2.36 directly to 2.38.0 as well. Could you confirm if patch is expected to be in 2.38.1? Thank you!