Query failed, likely because the requested analytics table does not exist

Hi all,
I get this error when I’m trying to visualize data using the Data Visualizer app. I checked the DB and there’s no table named analytics.

The UI shows a “No Data” message and there’s an error throwing from the backend.

Could someone please help me to solve this?

DHIS2 version - dhis2-stable-2.34.0
DB - postgresql 9.6
Tomcat - 9

Log:

* INFO  2020-06-19T09:00:46,088 Query failed, likely because the requested analytics table does not exist (JdbcAnalyticsManager.java [taskScheduler-4])
 org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select ax."dx",ax."monthly", sum(value) as value from analytics as ax where ax."dx" in ('nhCnp6MU3SL') and ax."monthly" in ('201906', '201907', '201908', '201909', '201910', '201911', '201912', '202001', '202002', '202003', '202004', '202005') and ( ax."uidlevel1" in ('sa8qg1wcJ5s') ) group by ax."dx",ax."monthly"]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "analytics" does not exist
  Position: 55
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235) ~[spring-jdbc-5.2.3.RELEASE.jar:5.2.3.RELEASE]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.2.3.RELEASE.jar:5.2.3.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443) ~[spring-jdbc-5.2.3.RELEASE.jar:5.2.3.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:388) ~[spring-jdbc-5.2.3.RELEASE.jar:5.2.3.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:452) ~[spring-jdbc-5.2.3.RELEASE.jar:5.2.3.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:495) ~[spring-jdbc-5.2.3.RELEASE.jar:5.2.3.RELEASE]
	at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getKeyValueMap(JdbcAnalyticsManager.java:633) ~[dhis-service-analytics-2.34.0.jar:?]
	at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getAggregatedDataValues(JdbcAnalyticsManager.java:153) ~[dhis-service-analytics-2.34.0.jar:?]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_202]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_202]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_202]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_202]
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.2.3.RELEASE.jar:5.2.3.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) ~[spring-aop-5.2.3.RELEASE.jar:5.2.3.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.2.3.RELEASE.jar:5.2.3.RELEASE]
	at org.springframework.aop.interceptor.AsyncExecutionInterceptor.lambda$invoke$0(AsyncExecutionInterceptor.java:115) ~[spring-aop-5.2.3.RELEASE.jar:5.2.3.RELEASE]
	at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_202]
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) [?:1.8.0_202]
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) [?:1.8.0_202]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_202]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_202]
	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_202]
Caused by: org.postgresql.util.PSQLException: ERROR: relation "analytics" does not exist
  Position: 55
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) ~[postgresql-42.2.5.jar:42.2.5]
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183) ~[postgresql-42.2.5.jar:42.2.5]
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308) ~[postgresql-42.2.5.jar:42.2.5]
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441) ~[postgresql-42.2.5.jar:42.2.5]
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365) ~[postgresql-42.2.5.jar:42.2.5]
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307) ~[postgresql-42.2.5.jar:42.2.5]
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293) ~[postgresql-42.2.5.jar:42.2.5]
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270) ~[postgresql-42.2.5.jar:42.2.5]
	at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:224) ~[postgresql-42.2.5.jar:42.2.5]
	at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:327) ~[c3p0-0.9.5.5.jar:0.9.5.5]
	at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:439) ~[spring-jdbc-5.2.3.RELEASE.jar:5.2.3.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:376) ~[spring-jdbc-5.2.3.RELEASE.jar:5.2.3.RELEASE]
	... 18 more

About DHIS2 Page:

Strange, but you ran analytics atleast once right? from Data Administration -> Analytics

1 Like

Thanks @jthomas,
I was unaware about that. Thanks It worked!!

By the way,
Do I need to rerun this after certain period?

1 Like

Glad it worked! Yes whenever you want to get the data aggregated to analytics_xxx tables. You can read a bit more here

1 Like

Thanks @jthomas!

1 Like