DHIS2 Can't finish the analytic in version 2.37.1

initially we have set DHIS2 version 2.37.1
Then we found some issues regarding the analytics that cannot be proceed

could you please to give any recommending on fixing this case. thank you

Here is the log:
https://gist.github.com/darmawan01/260c1be588b0977509cbb457b34091e3

@Ameen @dhis2-backend

2 Likes

cc @Gassim

1 Like

yes, I face the same issue

2 Likes

Thanks @darmawan_zulkifli and @ratih.syabrina for your question, and thanks @taufiqhs for the tag!

My first question would be if you tried to run the Analytics table management in the Data Administration app? This seems to be the most successful solution in all related CoP topics! (:

If you run it and still face an issue, would you please share the Catalina log (without sharing any sensitive info!) from the start of the process?

Thanks!

1 Like

Thanks for your suggestion sir, i already run the analytic even manually. but that still didn’t fix the problem.

updated the full log:
https://gist.github.com/darmawan01/260c1be588b0977509cbb457b34091e3

2 Likes

I think it might be related to this Jira issue: Log in - DHIS 2 JIRA

The analytics seems to be kicked off by a schedule in your case. Have you tried generating the analytics tables manually via Data Administration → Analytics Tables → Start Export? (perhaps turn off the scheduled job to avoid conflicting runs).

Regards,
Phil

Can’t see the JIRA Issues btw.

And yea, like i say in comment above sir. and I already turned off for schedule, restarting the server, postgres, redis but the problem analytic is still running some how.

You’d have to create a DHIS2 Jira account (link to sign-up!) and be logged in to be able to view, vote, and comment as well as watch the Jira issue to receive an email notification for all updates to the issue.

Yes, I understand, thanks, and I think that the cause is precisely what the Jira issue describes as a bug “large continuous analytics temporary tables fill the disk space”

Thanks! (:

1 Like

Thanks @Gassim , is there any way to solve it in short for now ?

2 Likes

You’re welcome! I think for now it would be for you to run a script against your database to drop the temporary tables.

I’m not 100% sure of the method but the idea is in the Jira issue, so maybe @phil please correct me if I’m wrong:
DROP TABLE analytics_temp_2021

2021 because it’s the table mentioned in the log (not a 2020).

1 Like

Yes, something like that (with a semicolon ; at the end, of course.

But that will only help if there are indeed some leftover temp tables filling the space.
@darmawan_zulkifli is your DB using up most of the space on your server?

You can check the actual DB table sizes with something like:

SELECT tablename, pg_size_pretty (pg_indexes_size(tablename::text)) as size 
FROM pg_indexes 
WHERE schemaname = 'public' ORDER BY size DESC;

or check that table directly with:

SELECT pg_size_pretty( pg_total_relation_size('analytics_temp_2021') );
3 Likes

Here @phil , i think that is not the problem. the size not to big

dhis2=> SELECT pg_size_pretty( pg_total_relation_size('analytics_temp_2021') );
 pg_size_pretty
----------------
 10168 kB
(1 row)
1 Like

I have try to. delete to for analytics_temp_2021 and try run manually again for analytic but still not work.
one question @phil @Gassim Where is the query level generated/come from.

Caused by: org.postgresql.util.PSQLException: ERROR: column "level" does not exist
  Hint: Perhaps you meant to reference the column "analytics_temp_2021.oulevel".
  Position: 95

refer to that error we got a missing level column. Here is the actual analytics_temp_2021 column

                                          Table "public.analytics_temp_2020"
     Column      |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description
-----------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 id              | text                        |           |          |         | extended |              |
 KO0F8fcRl9B     | character(11)               |           |          |         | extended |              |
 uidlevel1       | character(11)               |           |          |         | extended |              |
 uidlevel2       | character(11)               |           |          |         | extended |              |
 uidlevel3       | character(11)               |           |          |         | extended |              |
 uidlevel4       | character(11)               |           |          |         | extended |              |
 daily           | text                        |           |          |         | extended |              |
 weekly          | text                        |           |          |         | extended |              |
 weeklywednesday | text                        |           |          |         | extended |              |
 weeklythursday  | text                        |           |          |         | extended |              |
 weeklysaturday  | text                        |           |          |         | extended |              |
 weeklysunday    | text                        |           |          |         | extended |              |
 biweekly        | text                        |           |          |         | extended |              |
 monthly         | text                        |           |          |         | extended |              |
 bimonthly       | text                        |           |          |         | extended |              |
 quarterly       | text                        |           |          |         | extended |              |
 sixmonthly      | text                        |           |          |         | extended |              |
 sixmonthlyapril | text                        |           |          |         | extended |              |
 sixmonthlynov   | text                        |           |          |         | extended |              |
 yearly          | text                        |           |          |         | extended |              |
 financialapril  | text                        |           |          |         | extended |              |
 financialjuly   | text                        |           |          |         | extended |              |
 financialoct    | text                        |           |          |         | extended |              |
 financialnov    | text                        |           |          |         | extended |              |
 approvallevel   | integer                     |           |          |         | plain    |              |
 dx              | character(11)               |           | not null |         | extended |              |
 co              | character(11)               |           | not null |         | extended |              |
 ao              | character(11)               |           | not null |         | extended |              |
 pestartdate     | timestamp without time zone |           |          |         | plain    |              |
 peenddate       | timestamp without time zone |           |          |         | plain    |              |
 year            | integer                     |           | not null |         | plain    |              |
 pe              | text                        |           | not null |         | extended |              |
 ou              | character(11)               |           | not null |         | extended |              |
 oulevel         | integer                     |           |          |         | plain    |              |
 daysxvalue      | double precision            |           |          |         | plain    |              |
 daysno          | integer                     |           | not null |         | plain    |              |
 value           | double precision            |           |          |         | plain    |              |
 textvalue       | text                        |           |          |         | extended |              |
Access method: heap

and from the error there Hint: Perhaps you meant to reference the column "analytics_temp_2021.oulevel". . i think the generated query there is mis for that column. should be oulevel, wdyt sir ?

1 Like

I am also getting this error for my system

1 Like

We are getting this error as well in 2.37.1

Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [update analytics_temp_2021 set “uidlevel1” = null,“uidlevel2” = null,“uidlevel3” = null,“uidlevel4” = null,“uidlevel5” = null,“uidlevel6” = null where level > 6 and dx in (‘TrVLrr9XjEs’, ‘Owp8M4yz6ui’, ‘gmVIMDTQnUd’, ‘iTIKyO8WMnq’, ‘KwBy35dfFsz’, ‘IjIxQgu9q9P’, ‘s72OqIRFVnK’, ‘lEIsJooQMot’, ‘bq3gIZEuEEB’, ‘aavds7gYBW5’, ‘QPdnxYPhKa2’, ‘un6lxdVc02x’, ‘uiYOnolIqKl’, ‘RUurLS8eTac’, ‘mIO7hVfPIw5’, ‘l6alQrMaUqP’, ‘Iu3an3M2LXe’, ‘v15p7fAK8LT’, ‘Y2CsnWncvT6’, ‘BdofjRMyo0U’, ‘IuYD59mI8H9’, ‘OJnul9wZCUM’, ‘znpPhLOE4AA’, ‘knCSX72hkNg’, ‘F95KLid26PZ’, ‘E7ipLtEGd9X’, ‘RnFss77thCy’, ‘ZR8URIA255i’, ‘uGqlfDUZYvp’, ‘QFTpOngJ1s4’, ‘KWZHkHrR31a’, ‘C5ij8YpIuDs’, ‘C9yqu3YlTNL’, ‘RawdWreAhmw’, ‘zdTgBth9feB’, ‘deuPbC3iqLq’, ‘OzowH2888Cc’, ‘mOE5w8jVtuh’, ‘e0cPRcP5XNc’)]; nested exception is org.postgresql.util.PSQLException: ERROR: column “level” does not exist
Hint: Perhaps you meant to reference the column “analytics_temp_2021.oulevel”.
Position: 152

2 Likes

@Tomas_Sala , @jthomas

have you also just updated from previous versions?

1 Like

Hi @phil Yes I updated from 2.29 => 2.30 => 2.33 => 2.36.4 => 2.37.1

1 Like

Yes, I updated from 2.33 to 2.37.1

It is a bug. It most likely affects all versions, but only when data element aggregation levels are present.
See: [TECH-899] - Jira.
The fix is on the way, PR will be ready today.

There is no relation to [DHIS2-12210] - Jira

2 Likes

Thanks for the correction @dusan!:+1:

BTW, the link to the Jira issue you shared (https://jira.dhis2.org/browse/TECH-899) is inaccessible.

1 Like