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.
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).
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])
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
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.
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.
Probably the ticket will be closed if it’s not possible to reproduce this issue in any of the updated instances.
@chase.freeman it’s possible to reproduce this issue in any of the play.dhis2.org instances, would you please share the steps to reproduce? I will make sure to triage with the software developer team if we can reproduce this.