Any insights and suggestions on the following are very welcome.
Recently upgraded instance to version: 2.32.1
Build revision:290d889
Build date:2019-07-04 21:53
Jasper reports version:6.3.1
Last analytics table runtime: 2 m, 7 s
Database version: PostgreSQL 10.9
Java version: 1.8.0_221
Data is not showing in visualisation despite triggering the analytics process manually. Analytics is not running completely (they run only for 2.7min) which is too short a time for the amount of data in the database.
When loading dashboards.,log throws the following:
* INFO 2019-08-27 14:19:55,004 Query failed, likely because the requested analytics table does not exist (JdbcAnalyticsManager.java [taskScheduler-8])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select ax.“dx”,ax.“uidlevel3”, sum(daysxvalue) / 365 as value from analytics_2018 as ax where ax.“dx” in (‘ZDUDEOYQuHs’) and ax.“uidlevel3” in (‘ZMhvihfvanx’, ‘PoWQdy5S9Xi’, ‘EQg6N2v2TXj’, ‘IQCeFVtGUtg’, ‘P1HnUkQDPw0’, ‘HxjTgTNq2YD’, ‘LswTbnG3C0c’, ‘ENQA7sOVrPw’, ‘EbpvGZRGG04’, ‘A2Kgu7zMgJr’, ‘aimBmvkBWy3’, ‘M8moEd4dNYo’, ‘hQXbPPfQtLy’, ‘gd6cNp4HjsE’, ‘uJG4WFdUJSv’, ‘S1l6Df6SvLL’, ‘hrbAl7aUBwV’) and ( ax.“yearly” in (‘2018’) ) and ax.“year” in (2018) group by ax.“dx”,ax.“uidlevel3”]; nested exception is org.postgresql.util.PSQLException: ERROR: column ax.year does not exist
Hint: Perhaps you meant to reference the column “ax.yearly”.
Position: 436
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:419)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:478)
at org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:519)
at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getKeyValueMap(JdbcAnalyticsManager.java:651)
at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getAggregatedDataValues(JdbcAnalyticsManager.java:173)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
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:333)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.aop.interceptor.AsyncExecutionInterceptor$1.call(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: column ax.year does not exist
Hint: Perhaps you meant to reference the column “ax.yearly”.
Position: 436
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.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:224)
at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:220)
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:461)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:408)
… 18 more
* INFO 2019-08-27 14:19:55,004 Query failed, likely because the requested analytics table does not exist (JdbcAnalyticsManager.java [taskScheduler-25])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select ax.“dx”,ax.“uidlevel3”, sum(daysxvalue) / 365 as value from analytics_2018 as ax where ax.“dx” in (‘ZDUDEOYQuHs’) and ax.“uidlevel3” in (‘qubeTudDNSC’, ‘FEGZA6BEAjj’, ‘GeGgihdOJ2p’, ‘CTdWtNCPRNh’, ‘cd0I6ndBMrT’, ‘MHcRPWjeguM’, ‘S4Lr8zJqfHx’, ‘RY0I8Ha0azq’, ‘WVBSPqTMrPP’, ‘QWwg51OvU0a’, ‘F2sNoOfO4u3’, ‘MDkBXGJ5CyR’, ‘o7sh3DOqth2’, ‘RBK39eC5LuY’, ‘UeDRejssxJd’, ‘rUBJcTYUnAE’, ‘VbGP71DRL3K’, ‘bVY4PcjsciS’) and ( ax.“yearly” in (‘2018’) ) and ax.“year” in (2018) group by ax.“dx”,ax.“uidlevel3”]; nested exception is org.postgresql.util.PSQLException: ERROR: column ax.year does not exist
Hint: Perhaps you meant to reference the column “ax.yearly”.
Position: 451
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:419)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:478)
at org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:519)
at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getKeyValueMap(JdbcAnalyticsManager.java:651)
at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getAggregatedDataValues(JdbcAnalyticsManager.java:173)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
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:333)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.aop.interceptor.AsyncExecutionInterceptor$1.call(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: column ax.year does not exist
Hint: Perhaps you meant to reference the column “ax.yearly”.
Position: 451
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.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:224)
at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:220)
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:461)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:408)
… 18 more
* INFO 2019-08-27 14:19:55,019 Query failed, likely because the requested analytics table does not exist (JdbcAnalyticsManager.java [taskScheduler-15])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select ax.“dx”,ax.“uidlevel3”, sum(value) as value from analytics_2018 as ax where ax.“dx” in (‘Is1XcpgeN4I’) an