Continuous analysis task cause error :Analytics table process failed:

I have added a continuous analysis task ,as while do import event by api.
dhis237 (newest). jdk 11.
after done import events about 100000, got this error: (just part).

Click here to view error log

IDRcYH5o/+LfmLUy54IjNUtX1cM3udK6jU7V67GfguRAw=* INFO 2022-02-23T09:00:21,170 Analyzed tables: 00:00:49.444 (Clock.java [taskScheduler-25])

  • INFO 2022-02-23T09:00:21,171 Removing updated and deleted data (NotificationLoggerUtil.java [taskScheduler-25])
  • INFO 2022-02-23T09:00:21,182 Remove updated events for table: ‘analytics_event_olnclw5apdy’ in: 0.004910 sec. (AbstractJdbcTableManager.java [taskScheduler-25])
  • INFO 2022-02-23T09:00:21,182 Removed updated and deleted data: 00:00:49.456 (Clock.java [taskScheduler-25])
  • INFO 2022-02-23T09:00:21,183 Swapping analytics tables (NotificationLoggerUtil.java [taskScheduler-25])
  • INFO 2022-02-23T09:00:21,196 Swapping table, master table exists: true, skip master table: true (AbstractJdbcTableManager.java [taskScheduler-25])
  • ERROR 2022-02-23T09:00:21,211 Analytics table process failed: org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [ alter table analytics_event_olnclw5apdy detach partition analyti
    cs_event_olnclw5apdy_0; drop table analytics_event_olnclw5apdy_0 cascade; alter table analytics_event_temp_olnclw5apdy_0 rename to analytics_event_olnclw5apdy_0; alter table analytics_event_olnclw5apdy attach pa
    rtition analytics_event_olnclw5apdy_0 for values in (0);]; ERROR: partition constraint is violated by some row; nested exception is org.postgresql.util.PSQLException: ERROR: partition constraint is violated by s
    ome row
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:251)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:393)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:431)
    at org.hisp.dhis.analytics.table.AbstractJdbcTableManager.executeSilently(AbstractJdbcTableManager.java:378)
    at org.hisp.dhis.analytics.table.AbstractJdbcTableManager.swapTable(AbstractJdbcTableManager.java:641)
    at org.hisp.dhis.analytics.table.AbstractJdbcTableManager.lambda$swapTable$0(AbstractJdbcTableManager.java:237)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1541)
    at org.hisp.dhis.analytics.table.AbstractJdbcTableManager.swapTable(AbstractJdbcTableManager.java:237)
    at jdk.internal.reflect.GeneratedMethodAccessor2687.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:205)
    at com.sun.proxy.$Proxy230.swapTable(Unknown Source)

(Slf4jMLog.java [C3P0PooledConnectionPoolManager[identityToken->1bqrxewan2u840k1uh8hlu|4ec6ca3b]-AdminTaskTimer])

  • WARN 2022-02-23T10:11:30,045 com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@d43fe09 – APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks! (Slf4jMLog.java [C3P0P
    ooledConnectionPoolManager[identityToken->1bqrxewan2u840k1uh8hlu|4ec6ca3b]-AdminTaskTimer])
  • WARN 2022-02-23T10:11:30,048 com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@d43fe09 – APPARENT DEADLOCK!!! Complete Status:
    Managed Threads: 3
    Active Threads: 3
    Active Tasks:
    com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@477f53bf
    on thread: C3P0PooledConnectionPoolManager[identityToken->1bqrxewan2u840k1uh8hlu|4ec6ca3b]-HelperThread-#2
    com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@332ec7f4
    on thread: C3P0PooledConnectionPoolManager[identityToken->1bqrxewan2u840k1uh8hlu|4ec6ca3b]-HelperThread-#0
    com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@36a25d45
    on thread: C3P0PooledConnectionPoolManager[identityToken->1bqrxewan2u840k1uh8hlu|4ec6ca3b]-HelperThread-#1
    Pending Tasks:
    com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@2f33ae53
    com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@126cf2a1
    com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@57b8867f
    Pool thread stack traces:
    Thread[C3P0PooledConnectionPoolManager[identityToken->1bqrxewan2u840k1uh8hlu|4ec6ca3b]-HelperThread-#2,5,main]
    java.base@11.0.14/java.net.SocketInputStream.socketRead0(Native Method)
    java.base@11.0.14/java.net.SocketInputStream.socketRead(SocketInputStream.java:115)
    java.base@11.0.14/java.net.SocketInputStream.read(SocketInputStream.java:168)
    java.base@11.0.14/java.net.SocketInputStream.read(SocketInputStream.java:140)
    org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:161)
    org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:128)

…back 1 page
Thread[C3P0PooledConnectionPoolManager[identityToken->1bqrxewan2u840k1uh8hlu|4ec6ca3b]-HelperThread-#1,5,main]
java.base@11.0.14/java.net.SocketInputStream.socketRead0(Native Method)
java.base@11.0.14/java.net.SocketInputStream.socketRead(SocketInputStream.java:115)
java.base@11.0.14/java.net.SocketInputStream.read(SocketInputStream.java:168)
java.base@11.0.14/java.net.SocketInputStream.read(SocketInputStream.java:140)
org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:161)
org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:128)
org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:113)
org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
org.postgresql.core.PGStream.receiveChar(PGStream.java:443)
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2057)

@linxd, thank you for sharing! This is one of the database errors which require investigating the database itself, since the play.dhis2.org instances use the DB is Sierra Leone, it will not be possible to reproduce this issue on play either. so it would help if you’’ be able to share anonymized version of your DB for testing.

Thank you!

Today ,got this error:

Click here to view error

Caused by: org.postgresql.util.PSQLException: ERROR: partition constraint is violated by some row
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279)
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)
… 29 more
(AbstractSchedulingManager.java [taskScheduler-14])

  • INFO 2022-02-24T19:42:04,968 Query failed, likely because the requested analytics table does not exist (AbstractJdbcEventAnalyticsManager.java [http-nio-10008-exec-5])
    IDlVOcjNKf4EO2CXOTQ+PRVbj6oHbXovMHeU3OyGTduJg= org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select none(ax.“OdsMmIee37x”) as value from analytics_event_olnclw5apdy as ax where ax.“yearly” in (‘2013’, ‘2014’, ‘2015’, ‘2016’, ‘2017’, ‘2018’, ‘2019’, ‘2020’, ‘2021’, ‘2022’, ‘2003’, ‘2004’, ‘2005’, ‘2006’, ‘2007’, ‘2008’, ‘2009’, ‘2010’, ‘2011’, ‘2012’, ‘1993’, ‘1994’, ‘1995’, ‘1996’, ‘1997’, ‘1998’, ‘1999’, ‘2000’, ‘2001’, ‘2002’, ‘1990’, ‘1991’, ‘1992’) and (ax.“uidlevel2” = ‘h0KJ3RHnK5t’ or ax.“uidlevel2” = ‘h3zo0iCj0Up’ or ax.“uidlevel2” = ‘V1yp0ZXzYv6’ or ax.“uidlevel2” = ‘FxiaKtzuSDI’ or ax.“uidlevel2” = ‘qt1IikeR8qC’ or ax.“uidlevel2” = ‘zSphl5MRb9d’ or ax.“uidlevel2” = ‘z3wQvPnQOGi’ or ax.“uidlevel2” = ‘Szl7omAFHm2’ or ax.“uidlevel2” = ‘QJdoc2hLO8l’ or ax.“uidlevel2” = ‘d552H0FEKv0’ or ax.“uidlevel2” = ‘b7O1iGTbcGI’ or ax.“uidlevel2” = ‘RJZPRlXTssz’ or ax.“uidlevel2” = ‘ExPSfrs0Rck’ or ax.“uidlevel2” = ‘ocKID12OhZA’ or ax.“uidlevel2” = ‘C1f1GxOM240’ or ax.“uidlevel2” = ‘LzsDvk7YjyN’ or ax.“uidlevel2” = ‘rN03m79TnJ3’ or ax.“uidlevel2” = ‘MTkHLEww58b’ or ax.“uidlevel2” = ‘hk3bgrHMvAO’ or ax.“uidlevel2” = ‘LDS4JEoEz5z’ or ax.“uidlevel2” = ‘PDycFQ5yYvx’ or ax.“uidlevel2” = ‘CnKBeno7OOT’ ) and ax.“yearly” in (‘1990’, ‘1991’, ‘1992’, ‘1993’, ‘1994’, ‘1995’, ‘1996’, ‘1997’, ‘1998’, ‘1999’, ‘2000’, ‘2001’, ‘2002’, ‘2003’, ‘2004’, ‘2005’, ‘2006’, ‘2007’, ‘2008’, ‘2009’, ‘2010’, ‘2011’, ‘2012’, ‘2013’, ‘2014’, ‘2015’, ‘2016’, ‘2017’, ‘2018’, ‘2019’, ‘2020’, ‘2021’, ‘2022’) limit 100001]; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near “(”
    Position: 12
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239) ~[spring-jdbc-5.3.6.jar:5.3.6]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) ~[spring-jdbc-5.3.6.jar:5.3.6]
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541) ~[spring-jdbc-5.3.6.jar:5.3.6]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:393) ~[spring-jdbc-5.3.6.jar:5.3.6]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:465) ~[spring-jdbc-5.3.6.jar:5.3.6]
    at org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:530) ~[spring-jdbc-5.3.6.jar:5.3.6]
    at org.hisp.dhis.analytics.event.data.AbstractJdbcEventAnalyticsManager.getAggregatedEventData(AbstractJdbcEventAnalyticsManager.java:381) ~[dhis-service-analytics-2.37.4-SNAPSHOT.jar:?]
    at org.hisp.dhis.analytics.event.data.AbstractJdbcEventAnalyticsManager.getAggregatedEventData(AbstractJdbcEventAnalyticsManager.java:362) ~[dhis-service-analytics-2.37.4-SNAPSHOT.jar:?]
    at org.hisp.dhis.analytics.event.data.DefaultEventAnalyticsService.getAggregatedEventDataGrid(DefaultEventAnalyticsService.java:564) ~[dhis-service-analytics-2.37.4-SNAPSHOT.jar:?]
    at org.hisp.dhis.analytics.event.data.DefaultEventAnalyticsService.lambda$getAggregatedEventData$0(DefaultEventAnalyticsService.java:245) ~[dhis-service-analytics-2.37.4-SNAPSHOT.jar:?]
    at org.hisp.dhis.analytics.cache.AnalyticsCache.getOrFetch(AnalyticsCache.java:105) ~[dhis-service-analytics-2.37.4-SNAPSHOT.jar:?]
    at org.hisp.dhis.analytics.event.data.DefaultEventAnalyticsService.getAggregatedEventData(DefaultEventAnalyticsService.java:245) ~[dhis-service-analytics-2.37.4-SNAPSHOT.jar:?]
    at org.hisp.dhis.analytics.data.handler.DataHandler.addProgramDataElementAttributeIndicatorValues(DataHandler.java:373) ~[dhis-service-analytics-2.37.4-SNAPSHOT.jar:?]
    at org.hisp.dhis.analytics.data.handler.DataAggregator.getAggregatedDataValueGrid(DataAggregator.java:101) ~[dhis-service-analytics-2.37.4-SNAPSHOT.jar:?]
    at org.hisp.dhis.analytics.data.DefaultAnalyticsService.lambda$getAggregatedDataValues$0(DefaultAnalyticsService.java:111) ~[dhis-service-analytics-2.37.4-SNAPSHOT.jar:?]
    at org.hisp.dhis.analytics.cache.AnalyticsCache.getOrFetch(AnalyticsCache.java:105) [dhis-service-analytics-2.37.4-SNAPSHOT.jar:?]
    at org.hisp.dhis.analytics.data.DefaultAnalyticsService.getAggregatedDataValues(DefaultAnalyticsService.java:110) [dhis-service-analytics-2.37.4-SNAPSHOT.jar:?]
    at org.hisp.dhis.analytics.data.DefaultAnalyticsService.getAggregatedDataValues(DefaultAnalyticsService.java:121) [dhis-service-analytics-2.37.4-SNAPSHOT.jar:?]
    at org.hisp.dhis.webapi.controller.AnalyticsController.getGrid(AnalyticsController.java:276) [dhis-web-api-2.37.4-SNAPSHOT.jar:?]
    at org.hisp.dhis.webapi.controller.AnalyticsController.getJson(AnalyticsController.java:91) [dhis-web-api-2.37.4-SNAPSHOT.jar:?]
    at jdk.internal.reflect.GeneratedMethodAccessor1424.invoke(Unknown Source) ~[?:?]
    at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
    at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:197) [spring-web-5.3.6.jar:5.3.6]
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:141) [spring-web-5.3.6.jar:5.3.6]
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106) [spring-webmvc-5.3.6.jar:5.3.6]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:894) [spring-webmvc-5.3.6.jar:5.3.6]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808) [spring-webmvc-5.3.6.jar:5.3.6]
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) [spring-webmvc-5.3.6.jar:5.3.6]
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1060) [spring-webmvc-5.3.6.jar:5.3.6]
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:962) [spring-webmvc-5.3.6.jar:5.3.6]
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) [spring-webmvc-5.3.6.jar:5.3.6]
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) [spring-webmvc-5.3.6.jar:5.3.6]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:655) [servlet-api.jar:4.0.FR]
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) [spring-webmvc-5.3.6.jar:5.3.6]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:764) [servlet-api.jar:4.0.FR]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227) [catalina.jar:9.0.52]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) [catalina.jar:9.0.52]
    at org.hisp.dhis.commons.jsonfiltering.web.JsonFilteringRequestFilter.doFilter(JsonFilteringRequestFilter.java:71) [dhis-support-json-filtering-2.37.4-SNAPSHOT.jar:?]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) [catalina.jar:9.0.52]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) [catalina.jar:9.0.52]
    at org.hisp.dhis.servlet.filter.AppOverrideFilter.doFilterInternal(AppOverrideFilter.java:175) [dhis-web-commons-2.37.4-SNAPSHOT.jar:?]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) [spring-web-5.3.6.jar:5.3.6]
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:358) [spring-web-5.3.6.jar:5.3.6]

@linxd maybe try to manually remove all analytics tables and then start full export

For what it is worth, I am also seeing this in two different databases (cannot share).

@Gassim @linxd Any solution for this?? We are also facing the same issue with 2.37.7.1 latest.

Hello,

I was having a look at the issues described…

For the initial problem, at the top, we have two JIRA tickets open. They are on the radar and should be investigated and fixed in the near future. You can find them at [DHIS2-13514] - Jira and [DHIS2-13478] - Jira. The two issues could trigger the initial problem.

Regarding the second issue raised, it seems that the element used in the analytics query is an “Event data item” of type “TEXT”. Internally the application is interpreting it incorrectly and trying to apply the function “none” as you can see in the logs above.
For this one, I just created a JIRA ticket so we can add to our workload. You can find it at [DHIS2-13700] - Jira

Thanks,
Maikel

2 Likes