Analytics failing due to non-integer values

Support - Assistance technique
1

Dear team,

My analytics has been consistently failing due to a non-integer value accepted during data capture but rejected by an analytics_event table because the target/destination field in the table is set to accept only integer values. For instance, a value of “0.3” accepted and saved into the event table during normal data capture fails to save in the analytics_event table during analytics. This happens in 2.41.5. An upgrade to 2.41.6 did not resolve the issue, but it works perfectly well in 2.39.x. Below is the extracts from the logs. Kindly help me resolve

ERROR: invalid input syntax for integer: “0.3”; nested exception is org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: “0.3”

See log 
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:104) ~[spring-jdbc-5.3.39.jar:5.3.39]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-5.3.39.jar:5.3.39]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82) ~[spring-jdbc-5.3.39.jar:5.3.39]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82) ~[spring-jdbc-5.3.39.jar:5.3.39]
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1575) ~[spring-jdbc-5.3.39.jar:5.3.39]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:398) ~[spring-jdbc-5.3.39.jar:5.3.39]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:434) ~[spring-jdbc-5.3.39.jar:5.3.39]
    at org.hisp.dhis.analytics.table.AbstractJdbcTableManager.invokeTimeAndLog(AbstractJdbcTableManager.java:427) ~[dhis-service-analytics-2.41.4.2.jar:?]
    at org.hisp.dhis.analytics.table.AbstractEventJdbcTableManager.populateTableInternal(AbstractEventJdbcTableManager.java:173) ~[dhis-service-analytics-2.41.4.2.jar:?]
    at org.hisp.dhis.analytics.table.JdbcEventAnalyticsTableManager.populateTable(JdbcEventAnalyticsTableManager.java:573) ~[dhis-service-analytics-2.41.4.2.jar:?]
    at jdk.internal.reflect.GeneratedMethodAccessor2714.invoke(Unknown Source) ~[?:?]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
    at java.base/java.lang.reflect.Method.invoke(Method.java:569) ~[?:?]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.3.39.jar:5.3.39]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:234) ~[spring-aop-5.3.39.jar:5.3.39]
    at jdk.proxy3/jdk.proxy3.$Proxy514.populateTable(Unknown Source) ~[?:?]
    at org.hisp.dhis.analytics.table.DefaultAnalyticsTableService.lambda$populateTables$2(DefaultAnalyticsTableService.java:241) ~[dhis-service-analytics-2.41.4.2.jar:?]
    at org.hisp.dhis.scheduling.JobProgress.lambda$runStageInParallel$5(JobProgress.java:527) ~[dhis-api-2.41.4.2.jar:?]
    at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197) ~[?:?]
    at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1625) ~[?:?]
    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509) ~[?:?]
    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499) ~[?:?]
    at java.base/java.util.stream.ReduceOps$ReduceTask.doLeaf(ReduceOps.java:960) ~[?:?]
    at java.base/java.util.stream.ReduceOps$ReduceTask.doLeaf(ReduceOps.java:934) ~[?:?]
    at java.base/java.util.stream.AbstractTask.compute(AbstractTask.java:327) ~[?:?]
    at java.base/java.util.concurrent.CountedCompleter.exec(CountedCompleter.java:754) ~[?:?]
    at java.base/java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:373) ~[?:?]
    at java.base/java.util.concurrent.ForkJoinPool.helpComplete(ForkJoinPool.java:1950) ~[?:?]
    at java.base/java.util.concurrent.ForkJoinTask.awaitDone(ForkJoinTask.java:433) ~[?:?]
    at java.base/java.util.concurrent.ForkJoinTask.invoke(ForkJoinTask.java:687) ~[?:?]
    at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateParallel(ReduceOps.java:927) ~[?:?]
    at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:233) ~[?:?]
    at java.base/java.util.stream.ReferencePipeline.reduce(ReferencePipeline.java:662) ~[?:?]
    at org.hisp.dhis.scheduling.JobProgress.lambda$runStageInParallel$6(JobProgress.java:537) ~[dhis-api-2.41.4.2.jar:?]
    at java.base/java.util.concurrent.ForkJoinTask$AdaptedCallable.exec(ForkJoinTask.java:1428) [?:?]
    at java.base/java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:373) [?:?]
    at java.base/java.util.concurrent.ForkJoinPool$WorkQueue.topLevelExec(ForkJoinPool.java:1182) [?:?]
    at java.base/java.util.concurrent.ForkJoinPool.scan(ForkJoinPool.java:1655) [?:?]
    at java.base/java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1622) [?:?]
    at java.base/java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:165) [?:?]

Caused by: org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: “0.3”
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:356) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:341) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:317) ~[postgresql-42.7.5.jar:42.7.5]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:312) ~[postgresql-42.7.5.jar:42.7.5]
at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:1341) ~[c3p0-0.11.0-pre2.jar:?]
at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:425) ~[spring-jdbc-5.3.39.jar:5.3.39]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:38

2 Likes
2

Thank you @smomoh for reaching out! Other community members have faced this issue before and the solution was to find out where the value that’s causing an error is stored (to which specific metadata) and to change it to the expected value. Here’s a post where someone faced the same issue and was able to solve it, Analytics Tables export failure due to decimal value cannot be cast to bigint (DHIS2 2.38.4.3) - #2 by Gassim

Thanks!

1 Like
3

Hi Gassim,
THanks for sharing these solutions. I’ve looked through them. However, these are data elements that should accept decimal numbers. To me this seems like a bug, but I cant find it reported anywhere.

1 Like
4

Thanks! I understand but if someone sets a data element type to decimal, stores decimal values in the DB then changes the data element to integer then this will cause the issue that we are seeing

Would you like to share to reproduce the issue itself on any of the play.dhis2.org instances?

Thanks!