DHIS2 2.38.1 - Analytics issues - bad SQL grammar

Hi,
Last week I did upgrade our DHIS2 from 2.37.8 to 2.38.1 and I have noticed that some indicators do not show data. When I checked the logs I got the errors below for some indicators:

* INFO  2022-11-15T12:14:24,995 Query failed, likely because the requested analytics table does not exist (AbstractJdbcEventAnalyticsManager.java [http-nio-8080-exec-2]) IDEVXMamKl0W44AlSKVxVmy4ASwNupSzO673MSsCEHBL8=
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select count(pi) as value,ax."uidlevel5" from analytics_enrollment_yxgxkpzqvgh as ax where **and** (uidlevel5 = 'ksZUP1q6OpG' or uidlevel5 = 'RFejNcofI2z' or uidlevel5 = 'vTf6BtMqyvm' or uidlevel5 = 'au8xfXFDBMl' or uidlevel5 = 'g06pTJJ94yF' or uidlevel5 = 'O9jSGxKG5hT' or uidlevel5 = 'TbPaMnsOMRH' or uidlevel5 = 'ZlRi4NDTBn0' or uidlevel5 = 'iWqb1EW08jR' or uidlevel5 = 'zsfWT3KIb4U' or uidlevel5 = 'kzW1eMXFhUQ' or uidlevel5 = 't7qnaY7K9Uu' or uidlevel5 = 'KmRYYYpS8ER' or uidlevel5 = 'vSOCAPlReid' or uidlevel5 = 'TO1Zs8Sy0nQ' ) and (coalesce((select "JHkFdosNpRE" from analytics_event_yXgxkpZQVGH where analytics_event_yXgxkpZQVGH.pi = ax.pi and "JHkFdosNpRE" is not null and executiondate < cast( '2022-12-01' as date ) and executiondate >= cast( '2021-11-01' as date ) and ps = 'QHtAjmOLU9H' order by executiondate desc limit 1 ),'') = 'Positivo') group by ax."uidlevel5" ]; nested exception is org.postgresql.util.PSQLException: **ERROR: syntax error at or near "and"**
  Position: 92

I have also tried with release 2.39 and the errors are the same. Please advise how to sort it out.

Thanks,

Hi @ezbarretos

Thank you for your post! I would like to ask if you tried to use the Data Administration → Maintenance , Resource, and running Analytics tables export?

Please try to clear analytics tables and clear application cache as well as reload apps.

And then after running the analytics tables, clearing the cache, please check again, so if it’s not working please share the full Catalina.out log (without the sensitive info).

Thanks you!

Hi @Gassim
Thanks for your prompt suggestion.
I have followed your suggestion so many times but doesn’t solve the issues. I am sharing the link where you can see the full log.

dhis-analytics-table log

* INFO  2022-11-16T11:00:22,750 Created indexes: 00:00:08.996 (Clock.java [taskScheduler-1])
* INFO  2022-11-16T11:00:22,886 Analyzed tables: 00:00:09.132 (Clock.java [taskScheduler-1])
* INFO  2022-11-16T11:00:22,899 Swapping master table analytics_event_yxgxkpzqvgh, and its partitions (AbstractJdbcTableManager.java [taskScheduler-1])
* ERROR 2022-11-16T11:00:23,075 StatementCallback; bad SQL grammar [ alter table if exists analytics_event_yxgxkpzqvgh detach partition analytics_event_yxgxkpzqvgh_2022]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "analytics_event_yxgxkpzqvgh_2022" does not exist (AbstractJdbcTableManager.java [taskScheduler-1])
* INFO  2022-11-16T11:00:23,084 Swapping master table analytics_event_mvp4jas6pi9, and its partitions (AbstractJdbcTableManager.java [taskScheduler-1])
* ERROR 2022-11-16T11:00:23,300 StatementCallback; bad SQL grammar [ alter table if exists analytics_event_mvp4jas6pi9 detach partition analytics_event_mvp4jas6pi9_2022]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "analytics_event_mvp4jas6pi9_2022" does not exist (AbstractJdbcTableManager.java [taskScheduler-1])
* INFO  2022-11-16T11:00:23,315 Table update done: analytics_event: 00:00:09.561 (Clock.java [taskScheduler-1])
* INFO  2022-11-16T11:00:23,316 Analytics table update parameters: AnalyticsTableUpdateParams{last years=2, skip resource tables=false, skip table types=[], skip programs=[], start time=2022-11-16T11:00:00} (DefaultAnalyticsTableService.java [taskScheduler-1])

dhis full log

Hi @ezbarretos

Thank you for sharing the log. Please what are the versions for PostgreSQL and Postgis you are using?

Hi @Gassim,

PostgreSQL 13.8.1
Postgis 3.2

I have also tried on PostgreSQL 11 but the error persists.

Thanks!

Could you please try PostgreSQL 10?

It could be related to this issue:

Hi @Gassim,

Definitely, this is not bug related with postgreSQL, I have tested on postgreSQL 10, 11, 12 and 13, and I am ending with the same issues.

I extracted the a block of SQL statement from the log error

select count(pi) as value,ax."uidlevel3" from analytics_enrollment_yxgxkpzqvgh as ax where and (uidlevel3 = 'lXRPMiG5QSL' or uidlevel3 = 'y7JoWvoRc3f' or uidlevel3 = 'gIdf9KhS2VR' or uidlevel3 = 'lOafBl1S9ms' or uidlevel3 = 'kZGcEGb1PTT' or uidlevel3 = 'O8ozoPHMhJc' or uidlevel3 = 'E7c3Oj9P7Ov' or uidlevel3 = 'FDZPE97OLTK' or uidlevel3 = 'RLPbDKqOnED' or uidlevel3 = 'Om1cmbFwpMx' or uidlevel3 = 'jK9PE4S46cA' ) and (coalesce((select "LyOewhuG5CV" from analytics_event_yXgxkpZQVGH where analytics_event_yXgxkpZQVGH.pi = ax.pi and "LyOewhuG5CV" is not null and executiondate < cast( '2022-12-01' as date ) and executiondate >= cast( '2021-11-01' as date ) and ps = 'QHtAjmOLU9H' order by executiondate desc limit 1 ),'') = 'Positivo') group by ax."uidlevel3"

and run direct on postgreSQL query tool and the error below pops up

ERROR:  syntax error at or near "and"
LINE 1: ...from analytics_enrollment_yxgxkpzqvgh as ax where and (uidle...
                                                             ^
SQL state: 42601
Character: 92
1 Like

Hi @ezbarretos,

this seems to be a bug.
Can you reproduce it in Play/Dev or Play/2.39.0? Would be nice if we could get the steps to reproduce it.

If you can’t, could you give us an example of an Indicator expression you have defined that’s failing?

I suggest you open a bug in Jira with the all details and logs you have, especially concerning the above questions. We will be able to investigate from there.

Thanks,
Maikel

1 Like

Thanks @maikel!

@ezbarretos please create a jira issue here: https://dhis2.atlassian.net/ and share it to the post here so other community members can follow up too!

Hi @Gassim and @maikel,

Seems it will be long way to fix the bug. Do you know how to downgrade the DHIS2 instance from 2.38.1 to 2.37.8? Because the affected DHIS2 is our production instance and we want to share the reports and indicators with our partners.

Thanks,