Analytics Failure in 2.33.1

I recently upgraded to 2.33.1 (Build revision:
7ab6211) and my analytics can’t run. Attempts to run it manually usually fail with the logs below:

  • INFO 2020-01-22 12:08:03,566 Query failed, likely because the requested analytics table does not exist (JdbcAnalyticsManager.java [taskScheduler-23])
    org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select ax.“dx”,ax.“co”,ax.“yearly”, sum(value) as value from (select ap.* from analytics_2017 as ap union all select ap.* from analytics_2018 as ap union all select ap.* from analytics_2019 as ap ) as ax where ax.“dx” in (‘pUZ0BKgsAXp’) and ax.“co” in (‘Y2SrnZVI87k’) and ax.“yearly” in (‘2017’, ‘2018’, ‘2019’) and ( ax.“uidlevel1” in (‘s5DPBsdoE8b’) ) and ax.“year” in (2017, 2018, 2019) group by ax.“dx”,ax.“co”,ax.“yearly”]; nested exception is org.postgresql.util.PSQLException: ERROR: each UNION query must have the same number of columns
    Position: 118
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:234)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1442)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:387)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:451)
    at org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:494)
    at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getKeyValueMap(JdbcAnalyticsManager.java:662)
    at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getAggregatedDataValues(JdbcAnalyticsManager.java:182)
    at sun.reflect.GeneratedMethodAccessor895.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.interceptor.AsyncExecutionInterceptor.lambda$invoke$0(AsyncExecutionInterceptor.java:115)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
    Caused by: org.postgresql.util.PSQLException: ERROR: each UNION query must have the same number of columns
    Position: 118
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
    at org.postgresql.jdbc.PgStatemen

Anyone with a solution to this?

2 Likes

Hi @Barnabas_Akumba have you tried dropping the analytics table in Maintenance and running analytics report again?

3 Likes

Hello Busoye,
Haven’t dropped them. Though I’ve dropped SQL Views.
Will do that now and revert.

Regards

2 Likes

Hello Busoye,

Tried that but still no luck.

Regards

2 Likes

UNION query must have the same number of columns

Given the above, you may want to check that you successfully upgraded the db and ran all required scripts.

2 Likes

I’ll look at that

2 Likes

A look at my DB tables shows that analytics creates the analytics_temp and analytics_completeness tables. It hangs afterward and doesn’t create the final analytics tables.
image
I also noticed something strange. A table “analytics_completeness_2055”. Is it possible for analytics to be generated for a future period?

2 Likes

Tagging @scott to advise.

1 Like

@Barnabas_Akumba this is likely caused by erroneous data in your database. Do a quick search to see if there is a data for a future year and correct the date. We’ve seen this before. It’s quite a pain but I thought there was an update in the core that prevented this scenario.

3 Likes

I drop, and then can’t do analysis.
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint “_dateperiodstructure_temp_pkey1”
unique key (dateperiod)=(1988-04-10) already exists.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.flushIfDeadlockRisk(QueryExecutorImpl.java:1299)
at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1324)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:467)
at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:840)
at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1538)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:2437)

1 Like

Hi @Barnabas_Akumba,

What is the status of this issue now, did @Busoye’s tip work?

Best regards,
Karoline

Hey, does anyone knows if this is solved?

1 Like

Hi @Karoline. The error was coming up because the analytics tables were not created during the analytics process. This was due to the fact that as at January 2020, there wasn’t any data to be considered when the period “Last One Year” was selected. The version was considering months in the current year (2020) as the Last One Year instead of counting 12 months backwards.
The solution was to run analytics for all the periods and the analytics_2020 table was created which solved the issue.
I believe the period issue has been sorted out in subsequent builds.

Regards

1 Like

Thanks for your response @Barnabas_Akumba!

@waviles, are you still having the issue, though? What version of DHIS2 are you using?

Karoline

Hi Karoline,

Yes, I am still facing the issue, but it is happening after upgrading PostgreSQL from 9.5 to 11. DHIS2 version is 2.30, but it was working before upgrading the database cluster.

I think it is a bit different from the issue in this thread, I will compile more information and open a new thread.

Thank you!

William

I sloved the problem by change the jdk to 1.8.0_181(<1.8.0_20)