DHIS2 analytics failed

Support - Assistance technique
1

please help me with this error why this error is pop up when I run the analytics.

Job failed: ‘inMemoryAnalyticsJob’
System title: COVID Vaccination Tool
Base URL: null Time: 2024-09-10T10:47:37.144+03:00

Message: StatementCallback; bad SQL grammar [create table analytics_event_temp_cqjo30dykvi_2024 (check (yearly = ‘2024’), check (executiondate >= ‘2024-01-01’), check (executiondate < ‘2025-01-01’)) inherits (analytics_event_temp_cqjo30dykvi) with(autovacuum_enabled = false)]; nested exception is org.postgresql.util.PSQLException: ERROR: relation “analytics_event_temp_cqjo30dykvi_2024” already exists

Cause: org.postgresql.util.PSQLException: ERROR: relation “analytics_event_temp_cqjo30dykvi_2024” 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.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.execute(PgStatement.java:266) at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:75) at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:422) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:431) at org.hisp.dhis.analytics.table.AbstractJdbcTableManager.createTempTablePartitions(AbstractJdbcTableManager.java:435) at org.hisp.dhis.analytics.table.AbstractJdbcTableManager.createTable(AbstractJdbcTableManager.java:188) at sun.reflect.GeneratedMethodAccessor2463.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:344) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:205) at com.sun.proxy.$Proxy273.createTable(Unknown Source) at org.hisp.dhis.analytics.table.DefaultAnalyticsTableService.lambda$createTables$3(DefaultAnalyticsTableService.java:252) at java.util.ArrayList.forEach(ArrayList.java:1259) at org.hisp.dhis.analytics.table.DefaultAnalyticsTableService.createTables(DefaultAnalyticsTableService.java:252) at org.hisp.dhis.analytics.table.DefaultAnalyticsTableService.update(DefaultAnalyticsTableService.java:162) at org.hisp.dhis.analytics.table.DefaultAnalyticsTableGenerator.generateTables(DefaultAnalyticsTableGenerator.java:137) at org.hisp.dhis.analytics.table.scheduling.AnalyticsTableJob.execute(AnalyticsTableJob.java:81) at org.hisp.dhis.scheduling.DefaultJobInstance.executeJob(DefaultJobInstance.java:175) at org.hisp.dhis.scheduling.DefaultJobInstance.execute(DefaultJobInstance.java:96) at org.hisp.dhis.scheduling.DefaultSchedulingManager.lambda$internalExecuteJobConfiguration$6(DefaultSchedulingManager.java:290) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) 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:750)

2

Hi
@kasahun54 , did you try the application cache cleaner and restarts apps?

Not clear how this table got duplicated but maybe a server restart and the above suggestion might help.

Please post back how it goes, thanks!

3

Thanks @Gassim for your swift response

I have tried both clearing the cache and reloading the app. The issue started when we cloned our existing DHIS instance, which already had a program. After adding a new program, the problem occurred.

|2024-09-10 20:35:51|Process failed: StatementCallback; bad SQL grammar [create table analytics_event_temp_cqjo30dykvi_2024 (check (yearly = ‘2024’), check (executiondate >= ‘2024-01-01’), check (executiondate < ‘2025-01-01’)) inherits (analytics_event_temp_cqjo30dykvi) with(autovacuum_enabled = false)]; nested exception is org.postgresql.util.PSQLException: ERROR: relation “analytics_event_temp_cqjo30dykvi_2024” already exists error

4

Hi @kasahun54

Would it be possible to try an attempt the upgrade again? @Ulanbek had faced an ‘already exists’ error after upgrading and was able to resolve this using the following steps: Can't upgrade from 2.36 to 2.39 - #11 by Ulanbek would you give it a try as well?

Additionally, please the version number of the DHIs2 instance you are working on. Thanks!

5

The version I’m using is 2.36.2