What will happen to old reports based on reporttable on v2.37

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 ?

1 Like

Thanks @jthomas! This is an interesting question. (:

1 Like

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

3 Likes

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

3 Likes

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,

3 Likes

Hello @vincentminde while we wait for experts’ opinions, the following are my observations.

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

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

2 Likes

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,

2 Likes

@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:

2 Likes

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! :pray:

1 Like

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.

2 Likes

Hello @Gassim . I have registered a Jira Issue for this called Upgrade Failure to 2.37 [cannot drop table reporttable].

Cheers.

2 Likes

Great, thanks! I added the CoP label and a comment to this topic. :+1:

2 Likes

@jthomas @vincentminde, as you can see in the Jira issue the bug has been fixed. Thanks! :+1:

1 Like

Thanks @Gassim

1 Like

I am still facing the same issue when upgrading, 2.37.6. When you say, it has been resolved @Gassim , which patch?

1 Like

@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