Process failed: StatementCallback

Hi

Each time i run the Analytics tables update the following error of process failed

Process failed: StatementCallback; uncategorized SQLException for SQL [CREATE VIEW “_view_itengetorganisationunit” AS SELECT ou.organisationunitid, ou.name, ou.parentid, ou.shortname, ou.coordinates, ou.lastupdated, ou.latitude, ou.longitude, ou.created, ou.uid, ous.level, ou.featuretype , ou.code from _orgunitstructure ous join organisationunit ou on ous.organisationunitid=ou.organisationunitid]; SQL state [25P02]; error code [0]; ERROR: current transaction is aborted, commands ignored until end of transaction block; nested exception is org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

What could be causing this failure

Hi John,

could you try to first delete the SQL view called “itenget organisationunit” or similar, run analytics again and see if that avoids the error?

regards,

Lars

···

On Mon, Dec 14, 2015 at 5:14 PM, Gichangi John gichangijohn3@gmail.com wrote:

Hi

Each time i run the Analytics tables update the following error of process failed

Process failed: StatementCallback; uncategorized SQLException for SQL [CREATE VIEW “_view_itengetorganisationunit” AS SELECT ou.organisationunitid, ou.name, ou.parentid, ou.shortname, ou.coordinates, ou.lastupdated, ou.latitude, ou.longitude, ou.created, ou.uid, ous.level, ou.featuretype , ou.code from _orgunitstructure ous join organisationunit ou on ous.organisationunitid=ou.organisationunitid]; SQL state [25P02]; error code [0]; ERROR: current transaction is aborted, commands ignored until end of transaction block; nested exception is org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

What could be causing this failure


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

http://www.dhis2.org

Hi Lars

Thanks for the quick reply. I dropped the sql view and the analytics ran without the error but no data was available on pivot and other reports and analysis module.

It also takes 30 mins to run the whole analytics as compared to the usual 3-5 hrs.

The analytics issue arose on upgrade to 2.20.

···

On Tue, Dec 15, 2015 at 11:30 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi John,

could you try to first delete the SQL view called “itenget organisationunit” or similar, run analytics again and see if that avoids the error?

regards,

Lars

On Mon, Dec 14, 2015 at 5:14 PM, Gichangi John gichangijohn3@gmail.com wrote:

Hi

Each time i run the Analytics tables update the following error of process failed

Process failed: StatementCallback; uncategorized SQLException for SQL [CREATE VIEW “_view_itengetorganisationunit” AS SELECT ou.organisationunitid, ou.name, ou.parentid, ou.shortname, ou.coordinates, ou.lastupdated, ou.latitude, ou.longitude, ou.created, ou.uid, ous.level, ou.featuretype , ou.code from _orgunitstructure ous join organisationunit ou on ous.organisationunitid=ou.organisationunitid]; SQL state [25P02]; error code [0]; ERROR: current transaction is aborted, commands ignored until end of transaction block; nested exception is org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

What could be causing this failure


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

http://www.dhis2.org

Hi John,
There is an error being thrown that shows a very big value causing analytics to fail. i.e.

Analytics table process failed, please check the logs. Time: 2015-12-15T17:34:48.156+03:00. Application title: Kenya Health Information System Message: Exception during execution Cause: java.util.concurrent.ExecutionException: org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [insert into analytics_temp_2015 (“vWhzGZMkDh9”,“rbvYf4IjcGf”,“jvksQn3RnbT”,“FSoqQFDES0U”,“Vww46znsPsj”,“JlW9OiK1eR4”,“qSkvkJpTGWN”,“OE9Qlwr8XFv”,“A2DKecGXz73”,“uidlevel1”,“uidlevel2”,“uidlevel3”,“uidlevel4”,“uidlevel5”,“uidlevel6”,“uidlevel7”,“daily”,“weekly”,“monthly”,“bimonthly”,“quarterly”,“sixmonthly”,“sixmonthlyapril”,“yearly”,“financialapril”,“financialjuly”,“financialoct”,“dx”,“co”,“ao”,“ou”,“level”,daysxvalue, daysno, value, textvalue) select degs.“vWhzGZMkDh9”,ougs.“rbvYf4IjcGf”,ougs.“jvksQn3RnbT”,ougs.“FSoqQFDES0U”,ougs.“Vww46znsPsj”,ougs.“JlW9OiK1eR4”,ougs.“qSkvkJpTGWN”,ougs.“OE9Qlwr8XFv”,ougs.“A2DKecGXz73”,ous.“uidlevel1”,ous.“uidlevel2”,ous.“uidlevel3”,ous.“uidlevel4”,ous.“uidlevel5”,ous.“uidlevel6”,ous.“uidlevel7”,ps.“daily”,ps.“weekly”,ps.“monthly”,ps.“bimonthly”,ps.“quarterly”,ps.“sixmonthly”,ps.“sixmonthlyapril”,ps.“yearly”,ps.“financialapril”,ps.“financialjuly”,ps.“financialoct”,de.uid,co.uid,ao.uid,ou.uid,ous.level,cast(dv.value as double precision) * ps.daysno as daysxvalue, ps.daysno as daysno, cast(dv.value as double precision) as value, null as textvalue from datavalue dv left join _dataelementgroupsetstructure degs on dv.dataelementid=degs.dataelementid left join _organisationunitgroupsetstructure ougs on dv.sourceid=ougs.organisationunitid left join _categoryoptiongroupsetstructure cogs on dv.categoryoptioncomboid=cogs.categoryoptioncomboid left join _categoryoptiongroupsetstructure aogs on dv.attributeoptioncomboid=aogs.categoryoptioncomboid left join _categorystructure dcs on dv.categoryoptioncomboid=dcs.categoryoptioncomboid left join _categorystructure acs on dv.attributeoptioncomboid=acs.categoryoptioncomboid left join _orgunitstructure ous on dv.sourceid=ous.organisationunitid left join _dataelementstructure des on dv.dataelementid = des.dataelementid inner join dataelement de on dv.dataelementid=de.dataelementid inner join categoryoptioncombo co on dv.categoryoptioncomboid=co.categoryoptioncomboid inner join categoryoptioncombo ao on dv.attributeoptioncomboid=ao.categoryoptioncomboid inner join _categoryoptioncomboname aon on dv.attributeoptioncomboid=aon.categoryoptioncomboid inner join period pe on dv.periodid=pe.periodid inner join _periodstructure ps on dv.periodid=ps.periodid inner join organisationunit ou on dv.sourceid=ou.organisationunitid where de.valuetype = ‘int’ and de.domaintype = ‘AGGREGATE’ and pe.startdate >= ‘2015-01-01’ and pe.startdate <= ‘2015-12-31’ and dv.value is not null and dv.value ~* ‘^(-?[0-9]+)(.[0-9]+)?$’ and ( dv.value != ‘0’ or de.aggregationtype in (‘avg,avg_sum_org_unit’) or de.zeroissignificant = true ) ]; ERROR: “777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777” is out of range for type double precision; nested exception is org.postgresql.util.PSQLException: ERROR: “777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777” is out of range for type double precision
at java.util.concurrent.FutureTask.report(FutureTask.java:122)
at java.util.concurrent.FutureTask.get(FutureTask.java:192)
at org.hisp.dhis.commons.util.ConcurrentUtils.waitForCompletion(ConcurrentUtils.java:53)
at org.hisp.dhis.analytics.table.DefaultAnalyticsTableService.populateTables(DefaultAnalyticsTableService.java:211)
at org.hisp.dhis.analytics.table.DefaultAnalyticsTableService.update(DefaultAnalyticsTableService.java:128)
at org.hisp.dhis.analytics.table.scheduling.AnalyticsTableTask.run(AnalyticsTableTask.java:142)
at org.hisp.dhis.scheduling.ScheduledTasks.run(ScheduledTasks.java:60)
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:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [insert into analytics_temp_2015 (“vWhzGZMkDh9”,“rbvYf4IjcGf”,“jvksQn3RnbT”,“FSoqQFDES0U”,“Vww46znsPsj”,“JlW9OiK1eR4”,“qSkvkJpTGWN”,“OE9Qlwr8XFv”,“A2DKecGXz73”,“uidlevel1”,“uidlevel2”,“uidlevel3”,“uidlevel4”,“uidlevel5”,“uidlevel6”,“uidlevel7”,“daily”,“weekly”,“monthly”,“bimonthly”,“quarterly”,“sixmonthly”,“sixmonthlyapril”,“yearly”,“financialapril”,“financialjuly”,“financialoct”,“dx”,“co”,“ao”,“ou”,“level”,daysxvalue, daysno, value, textvalue) select degs.“vWhzGZMkDh9”,ougs.“rbvYf4IjcGf”,ougs.“jvksQn3RnbT”,ougs.“FSoqQFDES0U”,ougs.“Vww46znsPsj”,ougs.“JlW9OiK1eR4”,ougs.“qSkvkJpTGWN”,ougs.“OE9Qlwr8XFv”,ougs.“A2DKecGXz73”,ous.“uidlevel1”,ous.“uidlevel2”,ous.“uidlevel3”,ous.“uidlevel4”,ous.“uidlevel5”,ous.“uidlevel6”,ous.“uidlevel7”,ps.“daily”,ps.“weekly”,ps.“monthly”,ps.“bimonthly”,ps.“quarterly”,ps.“sixmonthly”,ps.“sixmonthlyapril”,ps.“yearly”,ps.“financialapril”,ps.“financialjuly”,ps.“financialoct”,de.uid,co.uid,ao.uid,ou.uid,ous.level,cast(dv.value as double precision) * ps.daysno as daysxvalue, ps.daysno as daysno, cast(dv.value as double precision) as value, null as textvalue from datavalue dv left join _dataelementgroupsetstructure degs on dv.dataelementid=degs.dataelementid left join _organisationunitgroupsetstructure ougs on dv.sourceid=ougs.organisationunitid left join _categoryoptiongroupsetstructure cogs on dv.categoryoptioncomboid=cogs.categoryoptioncomboid left join _categoryoptiongroupsetstructure aogs on dv.attributeoptioncomboid=aogs.categoryoptioncomboid left join _categorystructure dcs on dv.categoryoptioncomboid=dcs.categoryoptioncomboid left join _categorystructure acs on dv.attributeoptioncomboid=acs.categoryoptioncomboid left join _orgunitstructure ous on dv.sourceid=ous.organisationunitid left join _dataelementstructure des on dv.dataelementid = des.dataelementid inner join dataelement de on dv.dataelementid=de.dataelementid inner join categoryoptioncombo co on dv.categoryoptioncomboid=co.categoryoptioncomboid inner join categoryoptioncombo ao on dv.attributeoptioncomboid=ao.categoryoptioncomboid inner join _categoryoptioncomboname aon on dv.attributeoptioncomboid=aon.categoryoptioncomboid inner join period pe on dv.periodid=pe.periodid inner join _periodstructure ps on dv.periodid=ps.periodid inner join organisationunit ou on dv.sourceid=ou.organisationunitid where de.valuetype = ‘int’ and de.domaintype = ‘AGGREGATE’ and pe.startdate >= ‘2015-01-01’ and pe.startdate <= ‘2015-12-31’ and dv.value is not null and dv.value ~* ‘^(-?[0-9]+)(.[0-9]+)?$’ and ( dv.value != ‘0’ or de.aggregationtype in (‘avg,avg_sum_org_unit’) or de.zeroissignificant = true ) ]; ERROR: “777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777” is out of range for type double precision; nested exception is org.postgresql.util.PSQLException: ERROR: “777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777” is out of range for type double precision
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:102)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:416)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:440)
at org.hisp.dhis.analytics.table.AbstractJdbcTableManager.populateAndLog(AbstractJdbcTableManager.java:334)
at org.hisp.dhis.analytics.table.JdbcAnalyticsTableManager.populateTable(JdbcAnalyticsTableManager.java:258)
at org.hisp.dhis.analytics.table.JdbcAnalyticsTableManager.populateTableAsync(JdbcAnalyticsTableManager.java:176)
at sun.reflect.GeneratedMethodAccessor1620.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
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:110)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
… 1 more
Caused by: org.postgresql.util.PSQLException: ERROR: “777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777” is out of range for type double precision
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:405)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:397)
at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:1006)
at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:432)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:405)
… 13 more

Get the id of the data value causing the above by running

select * from datavalue where value=’

rgds777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777’

delete the single record causing this and then run analytics

ps note. I have also added you to the group.

rgds

David

···

2015-12-16 14:50 GMT+03:00 Gichangi John gichangijohn3@gmail.com:

Hi Lars

Thanks for the quick reply. I dropped the sql view and the analytics ran without the error but no data was available on pivot and other reports and analysis module.

It also takes 30 mins to run the whole analytics as compared to the usual 3-5 hrs.

The analytics issue arose on upgrade to 2.20.


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

On Tue, Dec 15, 2015 at 11:30 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi John,

could you try to first delete the SQL view called “itenget organisationunit” or similar, run analytics again and see if that avoids the error?

regards,

Lars

On Mon, Dec 14, 2015 at 5:14 PM, Gichangi John gichangijohn3@gmail.com wrote:

Hi

Each time i run the Analytics tables update the following error of process failed

Process failed: StatementCallback; uncategorized SQLException for SQL [CREATE VIEW “_view_itengetorganisationunit” AS SELECT ou.organisationunitid, ou.name, ou.parentid, ou.shortname, ou.coordinates, ou.lastupdated, ou.latitude, ou.longitude, ou.created, ou.uid, ous.level, ou.featuretype , ou.code from _orgunitstructure ous join organisationunit ou on ous.organisationunitid=ou.organisationunitid]; SQL state [25P02]; error code [0]; ERROR: current transaction is aborted, commands ignored until end of transaction block; nested exception is org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

What could be causing this failure


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

http://www.dhis2.org

Seems someone had lunch with their elbow on the keyboard :wink:

Max value of postgres double datatype is a number with approximately 300 digits in it, and this value has 582 digits.

We could put in a check for this.

Like David says this SQL will remove the value:

delete from datavalue where value=’

rgds777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777’;

regards,

Lars

···

On Wed, Dec 16, 2015 at 1:31 PM, David Muturi dnmuturi@gmail.com wrote:

Hi John,
There is an error being thrown that shows a very big value causing analytics to fail. i.e.

Analytics table process failed, please check the logs. Time: 2015-12-15T17:34:48.156+03:00. Application title: Kenya Health Information System Message: Exception during execution Cause: java.util.concurrent.ExecutionException: org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [insert into analytics_temp_2015 (“vWhzGZMkDh9”,“rbvYf4IjcGf”,“jvksQn3RnbT”,“FSoqQFDES0U”,“Vww46znsPsj”,“JlW9OiK1eR4”,“qSkvkJpTGWN”,“OE9Qlwr8XFv”,“A2DKecGXz73”,“uidlevel1”,“uidlevel2”,“uidlevel3”,“uidlevel4”,“uidlevel5”,“uidlevel6”,“uidlevel7”,“daily”,“weekly”,“monthly”,“bimonthly”,“quarterly”,“sixmonthly”,“sixmonthlyapril”,“yearly”,“financialapril”,“financialjuly”,“financialoct”,“dx”,“co”,“ao”,“ou”,“level”,daysxvalue, daysno, value, textvalue) select degs.“vWhzGZMkDh9”,ougs.“rbvYf4IjcGf”,ougs.“jvksQn3RnbT”,ougs.“FSoqQFDES0U”,ougs.“Vww46znsPsj”,ougs.“JlW9OiK1eR4”,ougs.“qSkvkJpTGWN”,ougs.“OE9Qlwr8XFv”,ougs.“A2DKecGXz73”,ous.“uidlevel1”,ous.“uidlevel2”,ous.“uidlevel3”,ous.“uidlevel4”,ous.“uidlevel5”,ous.“uidlevel6”,ous.“uidlevel7”,ps.“daily”,ps.“weekly”,ps.“monthly”,ps.“bimonthly”,ps.“quarterly”,ps.“sixmonthly”,ps.“sixmonthlyapril”,ps.“yearly”,ps.“financialapril”,ps.“financialjuly”,ps.“financialoct”,de.uid,co.uid,ao.uid,ou.uid,ous.level,cast(dv.value as double precision) * ps.daysno as daysxvalue, ps.daysno as daysno, cast(dv.value as double precision) as value, null as textvalue from datavalue dv left join _dataelementgroupsetstructure degs on dv.dataelementid=degs.dataelementid left join _organisationunitgroupsetstructure ougs on dv.sourceid=ougs.organisationunitid left join _categoryoptiongroupsetstructure cogs on dv.categoryoptioncomboid=cogs.categoryoptioncomboid left join _categoryoptiongroupsetstructure aogs on dv.attributeoptioncomboid=aogs.categoryoptioncomboid left join _categorystructure dcs on dv.categoryoptioncomboid=dcs.categoryoptioncomboid left join _categorystructure acs on dv.attributeoptioncomboid=acs.categoryoptioncomboid left join _orgunitstructure ous on dv.sourceid=ous.organisationunitid left join _dataelementstructure des on dv.dataelementid = des.dataelementid inner join dataelement de on dv.dataelementid=de.dataelementid inner join categoryoptioncombo co on dv.categoryoptioncomboid=co.categoryoptioncomboid inner join categoryoptioncombo ao on dv.attributeoptioncomboid=ao.categoryoptioncomboid inner join _categoryoptioncomboname aon on dv.attributeoptioncomboid=aon.categoryoptioncomboid inner join period pe on dv.periodid=pe.periodid inner join _periodstructure ps on dv.periodid=ps.periodid inner join organisationunit ou on dv.sourceid=ou.organisationunitid where de.valuetype = ‘int’ and de.domaintype = ‘AGGREGATE’ and pe.startdate >= ‘2015-01-01’ and pe.startdate <= ‘2015-12-31’ and dv.value is not null and dv.value ~* ‘^(-?[0-9]+)(.[0-9]+)?$’ and ( dv.value != ‘0’ or de.aggregationtype in (‘avg,avg_sum_org_unit’) or de.zeroissignificant = true ) ]; ERROR: “777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777” is out of range for type double precision; nested exception is org.postgresql.util.PSQLException: ERROR: “777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777” is out of range for type double precision
at java.util.concurrent.FutureTask.report(FutureTask.java:122)
at java.util.concurrent.FutureTask.get(FutureTask.java:192)
at org.hisp.dhis.commons.util.ConcurrentUtils.waitForCompletion(ConcurrentUtils.java:53)
at org.hisp.dhis.analytics.table.DefaultAnalyticsTableService.populateTables(DefaultAnalyticsTableService.java:211)
at org.hisp.dhis.analytics.table.DefaultAnalyticsTableService.update(DefaultAnalyticsTableService.java:128)
at org.hisp.dhis.analytics.table.scheduling.AnalyticsTableTask.run(AnalyticsTableTask.java:142)
at org.hisp.dhis.scheduling.ScheduledTasks.run(ScheduledTasks.java:60)
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:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [insert into analytics_temp_2015 (“vWhzGZMkDh9”,“rbvYf4IjcGf”,“jvksQn3RnbT”,“FSoqQFDES0U”,“Vww46znsPsj”,“JlW9OiK1eR4”,“qSkvkJpTGWN”,“OE9Qlwr8XFv”,“A2DKecGXz73”,“uidlevel1”,“uidlevel2”,“uidlevel3”,“uidlevel4”,“uidlevel5”,“uidlevel6”,“uidlevel7”,“daily”,“weekly”,“monthly”,“bimonthly”,“quarterly”,“sixmonthly”,“sixmonthlyapril”,“yearly”,“financialapril”,“financialjuly”,“financialoct”,“dx”,“co”,“ao”,“ou”,“level”,daysxvalue, daysno, value, textvalue) select degs.“vWhzGZMkDh9”,ougs.“rbvYf4IjcGf”,ougs.“jvksQn3RnbT”,ougs.“FSoqQFDES0U”,ougs.“Vww46znsPsj”,ougs.“JlW9OiK1eR4”,ougs.“qSkvkJpTGWN”,ougs.“OE9Qlwr8XFv”,ougs.“A2DKecGXz73”,ous.“uidlevel1”,ous.“uidlevel2”,ous.“uidlevel3”,ous.“uidlevel4”,ous.“uidlevel5”,ous.“uidlevel6”,ous.“uidlevel7”,ps.“daily”,ps.“weekly”,ps.“monthly”,ps.“bimonthly”,ps.“quarterly”,ps.“sixmonthly”,ps.“sixmonthlyapril”,ps.“yearly”,ps.“financialapril”,ps.“financialjuly”,ps.“financialoct”,de.uid,co.uid,ao.uid,ou.uid,ous.level,cast(dv.value as double precision) * ps.daysno as daysxvalue, ps.daysno as daysno, cast(dv.value as double precision) as value, null as textvalue from datavalue dv left join _dataelementgroupsetstructure degs on dv.dataelementid=degs.dataelementid left join _organisationunitgroupsetstructure ougs on dv.sourceid=ougs.organisationunitid left join _categoryoptiongroupsetstructure cogs on dv.categoryoptioncomboid=cogs.categoryoptioncomboid left join _categoryoptiongroupsetstructure aogs on dv.attributeoptioncomboid=aogs.categoryoptioncomboid left join _categorystructure dcs on dv.categoryoptioncomboid=dcs.categoryoptioncomboid left join _categorystructure acs on dv.attributeoptioncomboid=acs.categoryoptioncomboid left join _orgunitstructure ous on dv.sourceid=ous.organisationunitid left join _dataelementstructure des on dv.dataelementid = des.dataelementid inner join dataelement de on dv.dataelementid=de.dataelementid inner join categoryoptioncombo co on dv.categoryoptioncomboid=co.categoryoptioncomboid inner join categoryoptioncombo ao on dv.attributeoptioncomboid=ao.categoryoptioncomboid inner join _categoryoptioncomboname aon on dv.attributeoptioncomboid=aon.categoryoptioncomboid inner join period pe on dv.periodid=pe.periodid inner join _periodstructure ps on dv.periodid=ps.periodid inner join organisationunit ou on dv.sourceid=ou.organisationunitid where de.valuetype = ‘int’ and de.domaintype = ‘AGGREGATE’ and pe.startdate >= ‘2015-01-01’ and pe.startdate <= ‘2015-12-31’ and dv.value is not null and dv.value ~* ‘^(-?[0-9]+)(.[0-9]+)?$’ and ( dv.value != ‘0’ or de.aggregationtype in (‘avg,avg_sum_org_unit’) or de.zeroissignificant = true ) ]; ERROR: “777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777” is out of range for type double precision; nested exception is org.postgresql.util.PSQLException: ERROR: “777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777” is out of range for type double precision
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:102)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:416)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:440)
at org.hisp.dhis.analytics.table.AbstractJdbcTableManager.populateAndLog(AbstractJdbcTableManager.java:334)
at org.hisp.dhis.analytics.table.JdbcAnalyticsTableManager.populateTable(JdbcAnalyticsTableManager.java:258)
at org.hisp.dhis.analytics.table.JdbcAnalyticsTableManager.populateTableAsync(JdbcAnalyticsTableManager.java:176)
at sun.reflect.GeneratedMethodAccessor1620.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
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:110)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
… 1 more
Caused by: org.postgresql.util.PSQLException: ERROR: “777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777” is out of range for type double precision
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:405)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:397)
at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:1006)
at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:432)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:405)
… 13 more

Get the id of the data value causing the above by running

select * from datavalue where value=’

rgds777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777’

delete the single record causing this and then run analytics

ps note. I have also added you to the group.

rgds

David

2015-12-16 14:50 GMT+03:00 Gichangi John gichangijohn3@gmail.com:

Hi Lars

Thanks for the quick reply. I dropped the sql view and the analytics ran without the error but no data was available on pivot and other reports and analysis module.

It also takes 30 mins to run the whole analytics as compared to the usual 3-5 hrs.

The analytics issue arose on upgrade to 2.20.


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

On Tue, Dec 15, 2015 at 11:30 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi John,

could you try to first delete the SQL view called “itenget organisationunit” or similar, run analytics again and see if that avoids the error?

regards,

Lars

On Mon, Dec 14, 2015 at 5:14 PM, Gichangi John gichangijohn3@gmail.com wrote:

Hi

Each time i run the Analytics tables update the following error of process failed

Process failed: StatementCallback; uncategorized SQLException for SQL [CREATE VIEW “_view_itengetorganisationunit” AS SELECT ou.organisationunitid, ou.name, ou.parentid, ou.shortname, ou.coordinates, ou.lastupdated, ou.latitude, ou.longitude, ou.created, ou.uid, ous.level, ou.featuretype , ou.code from _orgunitstructure ous join organisationunit ou on ous.organisationunitid=ou.organisationunitid]; SQL state [25P02]; error code [0]; ERROR: current transaction is aborted, commands ignored until end of transaction block; nested exception is org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

What could be causing this failure


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

http://www.dhis2.org

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

http://www.dhis2.org

Haha

Hi David

Thanks, will delete the data values with length > 20 and of numeric dataelement types and run the analytics.

Thanks again

···

On Wed, Dec 16, 2015 at 3:57 PM, Lars Helge Øverland larshelge@gmail.com wrote:

Seems someone had lunch with their elbow on the keyboard :wink:

Max value of postgres double datatype is a number with approximately 300 digits in it, and this value has 582 digits.

We could put in a check for this.

Like David says this SQL will remove the value:

delete from datavalue where value=’

rgds777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777’;

regards,

Lars

On Wed, Dec 16, 2015 at 1:31 PM, David Muturi dnmuturi@gmail.com wrote:

Hi John,
There is an error being thrown that shows a very big value causing analytics to fail. i.e.

Analytics table process failed, please check the logs. Time: 2015-12-15T17:34:48.156+03:00. Application title: Kenya Health Information System Message: Exception during execution Cause: java.util.concurrent.ExecutionException: org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [insert into analytics_temp_2015 (“vWhzGZMkDh9”,“rbvYf4IjcGf”,“jvksQn3RnbT”,“FSoqQFDES0U”,“Vww46znsPsj”,“JlW9OiK1eR4”,“qSkvkJpTGWN”,“OE9Qlwr8XFv”,“A2DKecGXz73”,“uidlevel1”,“uidlevel2”,“uidlevel3”,“uidlevel4”,“uidlevel5”,“uidlevel6”,“uidlevel7”,“daily”,“weekly”,“monthly”,“bimonthly”,“quarterly”,“sixmonthly”,“sixmonthlyapril”,“yearly”,“financialapril”,“financialjuly”,“financialoct”,“dx”,“co”,“ao”,“ou”,“level”,daysxvalue, daysno, value, textvalue) select degs.“vWhzGZMkDh9”,ougs.“rbvYf4IjcGf”,ougs.“jvksQn3RnbT”,ougs.“FSoqQFDES0U”,ougs.“Vww46znsPsj”,ougs.“JlW9OiK1eR4”,ougs.“qSkvkJpTGWN”,ougs.“OE9Qlwr8XFv”,ougs.“A2DKecGXz73”,ous.“uidlevel1”,ous.“uidlevel2”,ous.“uidlevel3”,ous.“uidlevel4”,ous.“uidlevel5”,ous.“uidlevel6”,ous.“uidlevel7”,ps.“daily”,ps.“weekly”,ps.“monthly”,ps.“bimonthly”,ps.“quarterly”,ps.“sixmonthly”,ps.“sixmonthlyapril”,ps.“yearly”,ps.“financialapril”,ps.“financialjuly”,ps.“financialoct”,de.uid,co.uid,ao.uid,ou.uid,ous.level,cast(dv.value as double precision) * ps.daysno as daysxvalue, ps.daysno as daysno, cast(dv.value as double precision) as value, null as textvalue from datavalue dv left join _dataelementgroupsetstructure degs on dv.dataelementid=degs.dataelementid left join _organisationunitgroupsetstructure ougs on dv.sourceid=ougs.organisationunitid left join _categoryoptiongroupsetstructure cogs on dv.categoryoptioncomboid=cogs.categoryoptioncomboid left join _categoryoptiongroupsetstructure aogs on dv.attributeoptioncomboid=aogs.categoryoptioncomboid left join _categorystructure dcs on dv.categoryoptioncomboid=dcs.categoryoptioncomboid left join _categorystructure acs on dv.attributeoptioncomboid=acs.categoryoptioncomboid left join _orgunitstructure ous on dv.sourceid=ous.organisationunitid left join _dataelementstructure des on dv.dataelementid = des.dataelementid inner join dataelement de on dv.dataelementid=de.dataelementid inner join categoryoptioncombo co on dv.categoryoptioncomboid=co.categoryoptioncomboid inner join categoryoptioncombo ao on dv.attributeoptioncomboid=ao.categoryoptioncomboid inner join _categoryoptioncomboname aon on dv.attributeoptioncomboid=aon.categoryoptioncomboid inner join period pe on dv.periodid=pe.periodid inner join _periodstructure ps on dv.periodid=ps.periodid inner join organisationunit ou on dv.sourceid=ou.organisationunitid where de.valuetype = ‘int’ and de.domaintype = ‘AGGREGATE’ and pe.startdate >= ‘2015-01-01’ and pe.startdate <= ‘2015-12-31’ and dv.value is not null and dv.value ~* ‘^(-?[0-9]+)(.[0-9]+)?$’ and ( dv.value != ‘0’ or de.aggregationtype in (‘avg,avg_sum_org_unit’) or de.zeroissignificant = true ) ]; ERROR: “777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777” is out of range for type double precision; nested exception is org.postgresql.util.PSQLException: ERROR: “777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777” is out of range for type double precision
at java.util.concurrent.FutureTask.report(FutureTask.java:122)
at java.util.concurrent.FutureTask.get(FutureTask.java:192)
at org.hisp.dhis.commons.util.ConcurrentUtils.waitForCompletion(ConcurrentUtils.java:53)
at org.hisp.dhis.analytics.table.DefaultAnalyticsTableService.populateTables(DefaultAnalyticsTableService.java:211)
at org.hisp.dhis.analytics.table.DefaultAnalyticsTableService.update(DefaultAnalyticsTableService.java:128)
at org.hisp.dhis.analytics.table.scheduling.AnalyticsTableTask.run(AnalyticsTableTask.java:142)
at org.hisp.dhis.scheduling.ScheduledTasks.run(ScheduledTasks.java:60)
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:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [insert into analytics_temp_2015 (“vWhzGZMkDh9”,“rbvYf4IjcGf”,“jvksQn3RnbT”,“FSoqQFDES0U”,“Vww46znsPsj”,“JlW9OiK1eR4”,“qSkvkJpTGWN”,“OE9Qlwr8XFv”,“A2DKecGXz73”,“uidlevel1”,“uidlevel2”,“uidlevel3”,“uidlevel4”,“uidlevel5”,“uidlevel6”,“uidlevel7”,“daily”,“weekly”,“monthly”,“bimonthly”,“quarterly”,“sixmonthly”,“sixmonthlyapril”,“yearly”,“financialapril”,“financialjuly”,“financialoct”,“dx”,“co”,“ao”,“ou”,“level”,daysxvalue, daysno, value, textvalue) select degs.“vWhzGZMkDh9”,ougs.“rbvYf4IjcGf”,ougs.“jvksQn3RnbT”,ougs.“FSoqQFDES0U”,ougs.“Vww46znsPsj”,ougs.“JlW9OiK1eR4”,ougs.“qSkvkJpTGWN”,ougs.“OE9Qlwr8XFv”,ougs.“A2DKecGXz73”,ous.“uidlevel1”,ous.“uidlevel2”,ous.“uidlevel3”,ous.“uidlevel4”,ous.“uidlevel5”,ous.“uidlevel6”,ous.“uidlevel7”,ps.“daily”,ps.“weekly”,ps.“monthly”,ps.“bimonthly”,ps.“quarterly”,ps.“sixmonthly”,ps.“sixmonthlyapril”,ps.“yearly”,ps.“financialapril”,ps.“financialjuly”,ps.“financialoct”,de.uid,co.uid,ao.uid,ou.uid,ous.level,cast(dv.value as double precision) * ps.daysno as daysxvalue, ps.daysno as daysno, cast(dv.value as double precision) as value, null as textvalue from datavalue dv left join _dataelementgroupsetstructure degs on dv.dataelementid=degs.dataelementid left join _organisationunitgroupsetstructure ougs on dv.sourceid=ougs.organisationunitid left join _categoryoptiongroupsetstructure cogs on dv.categoryoptioncomboid=cogs.categoryoptioncomboid left join _categoryoptiongroupsetstructure aogs on dv.attributeoptioncomboid=aogs.categoryoptioncomboid left join _categorystructure dcs on dv.categoryoptioncomboid=dcs.categoryoptioncomboid left join _categorystructure acs on dv.attributeoptioncomboid=acs.categoryoptioncomboid left join _orgunitstructure ous on dv.sourceid=ous.organisationunitid left join _dataelementstructure des on dv.dataelementid = des.dataelementid inner join dataelement de on dv.dataelementid=de.dataelementid inner join categoryoptioncombo co on dv.categoryoptioncomboid=co.categoryoptioncomboid inner join categoryoptioncombo ao on dv.attributeoptioncomboid=ao.categoryoptioncomboid inner join _categoryoptioncomboname aon on dv.attributeoptioncomboid=aon.categoryoptioncomboid inner join period pe on dv.periodid=pe.periodid inner join _periodstructure ps on dv.periodid=ps.periodid inner join organisationunit ou on dv.sourceid=ou.organisationunitid where de.valuetype = ‘int’ and de.domaintype = ‘AGGREGATE’ and pe.startdate >= ‘2015-01-01’ and pe.startdate <= ‘2015-12-31’ and dv.value is not null and dv.value ~* ‘^(-?[0-9]+)(.[0-9]+)?$’ and ( dv.value != ‘0’ or de.aggregationtype in (‘avg,avg_sum_org_unit’) or de.zeroissignificant = true ) ]; ERROR: “777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777” is out of range for type double precision; nested exception is org.postgresql.util.PSQLException: ERROR: “777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777” is out of range for type double precision
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:102)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:416)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:440)
at org.hisp.dhis.analytics.table.AbstractJdbcTableManager.populateAndLog(AbstractJdbcTableManager.java:334)
at org.hisp.dhis.analytics.table.JdbcAnalyticsTableManager.populateTable(JdbcAnalyticsTableManager.java:258)
at org.hisp.dhis.analytics.table.JdbcAnalyticsTableManager.populateTableAsync(JdbcAnalyticsTableManager.java:176)
at sun.reflect.GeneratedMethodAccessor1620.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
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:110)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
… 1 more
Caused by: org.postgresql.util.PSQLException: ERROR: “777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777” is out of range for type double precision
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:405)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:397)
at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:1006)
at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:432)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:405)
… 13 more

Get the id of the data value causing the above by running

select * from datavalue where value=’

rgds777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777’

delete the single record causing this and then run analytics

ps note. I have also added you to the group.

rgds

David


Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

http://www.dhis2.org

2015-12-16 14:50 GMT+03:00 Gichangi John gichangijohn3@gmail.com:

Hi Lars

Thanks for the quick reply. I dropped the sql view and the analytics ran without the error but no data was available on pivot and other reports and analysis module.

It also takes 30 mins to run the whole analytics as compared to the usual 3-5 hrs.

The analytics issue arose on upgrade to 2.20.


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

On Tue, Dec 15, 2015 at 11:30 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi John,

could you try to first delete the SQL view called “itenget organisationunit” or similar, run analytics again and see if that avoids the error?

regards,

Lars

On Mon, Dec 14, 2015 at 5:14 PM, Gichangi John gichangijohn3@gmail.com wrote:

Hi

Each time i run the Analytics tables update the following error of process failed

Process failed: StatementCallback; uncategorized SQLException for SQL [CREATE VIEW “_view_itengetorganisationunit” AS SELECT ou.organisationunitid, ou.name, ou.parentid, ou.shortname, ou.coordinates, ou.lastupdated, ou.latitude, ou.longitude, ou.created, ou.uid, ous.level, ou.featuretype , ou.code from _orgunitstructure ous join organisationunit ou on ous.organisationunitid=ou.organisationunitid]; SQL state [25P02]; error code [0]; ERROR: current transaction is aborted, commands ignored until end of transaction block; nested exception is org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

What could be causing this failure


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

http://www.dhis2.org

Hi Lars and David,
This is to thank you guys for supporting MOH DHIS2 and John our new technical point of contact

Regards,

Raphael

···

On Wed, Dec 16, 2015 at 4:10 PM, Gichangi John gichangijohn3@gmail.com wrote:

Haha

Hi David

Thanks, will delete the data values with length > 20 and of numeric dataelement types and run the analytics.

Thanks again


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

On Wed, Dec 16, 2015 at 3:57 PM, Lars Helge Øverland larshelge@gmail.com wrote:

Seems someone had lunch with their elbow on the keyboard :wink:

Max value of postgres double datatype is a number with approximately 300 digits in it, and this value has 582 digits.

We could put in a check for this.

Like David says this SQL will remove the value:

delete from datavalue where value=’

rgds777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777’;

regards,

Lars

On Wed, Dec 16, 2015 at 1:31 PM, David Muturi dnmuturi@gmail.com wrote:

Hi John,
There is an error being thrown that shows a very big value causing analytics to fail. i.e.

Analytics table process failed, please check the logs. Time: 2015-12-15T17:34:48.156+03:00. Application title: Kenya Health Information System Message: Exception during execution Cause: java.util.concurrent.ExecutionException: org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [insert into analytics_temp_2015 (“vWhzGZMkDh9”,“rbvYf4IjcGf”,“jvksQn3RnbT”,“FSoqQFDES0U”,“Vww46znsPsj”,“JlW9OiK1eR4”,“qSkvkJpTGWN”,“OE9Qlwr8XFv”,“A2DKecGXz73”,“uidlevel1”,“uidlevel2”,“uidlevel3”,“uidlevel4”,“uidlevel5”,“uidlevel6”,“uidlevel7”,“daily”,“weekly”,“monthly”,“bimonthly”,“quarterly”,“sixmonthly”,“sixmonthlyapril”,“yearly”,“financialapril”,“financialjuly”,“financialoct”,“dx”,“co”,“ao”,“ou”,“level”,daysxvalue, daysno, value, textvalue) select degs.“vWhzGZMkDh9”,ougs.“rbvYf4IjcGf”,ougs.“jvksQn3RnbT”,ougs.“FSoqQFDES0U”,ougs.“Vww46znsPsj”,ougs.“JlW9OiK1eR4”,ougs.“qSkvkJpTGWN”,ougs.“OE9Qlwr8XFv”,ougs.“A2DKecGXz73”,ous.“uidlevel1”,ous.“uidlevel2”,ous.“uidlevel3”,ous.“uidlevel4”,ous.“uidlevel5”,ous.“uidlevel6”,ous.“uidlevel7”,ps.“daily”,ps.“weekly”,ps.“monthly”,ps.“bimonthly”,ps.“quarterly”,ps.“sixmonthly”,ps.“sixmonthlyapril”,ps.“yearly”,ps.“financialapril”,ps.“financialjuly”,ps.“financialoct”,de.uid,co.uid,ao.uid,ou.uid,ous.level,cast(dv.value as double precision) * ps.daysno as daysxvalue, ps.daysno as daysno, cast(dv.value as double precision) as value, null as textvalue from datavalue dv left join _dataelementgroupsetstructure degs on dv.dataelementid=degs.dataelementid left join _organisationunitgroupsetstructure ougs on dv.sourceid=ougs.organisationunitid left join _categoryoptiongroupsetstructure cogs on dv.categoryoptioncomboid=cogs.categoryoptioncomboid left join _categoryoptiongroupsetstructure aogs on dv.attributeoptioncomboid=aogs.categoryoptioncomboid left join _categorystructure dcs on dv.categoryoptioncomboid=dcs.categoryoptioncomboid left join _categorystructure acs on dv.attributeoptioncomboid=acs.categoryoptioncomboid left join _orgunitstructure ous on dv.sourceid=ous.organisationunitid left join _dataelementstructure des on dv.dataelementid = des.dataelementid inner join dataelement de on dv.dataelementid=de.dataelementid inner join categoryoptioncombo co on dv.categoryoptioncomboid=co.categoryoptioncomboid inner join categoryoptioncombo ao on dv.attributeoptioncomboid=ao.categoryoptioncomboid inner join _categoryoptioncomboname aon on dv.attributeoptioncomboid=aon.categoryoptioncomboid inner join period pe on dv.periodid=pe.periodid inner join _periodstructure ps on dv.periodid=ps.periodid inner join organisationunit ou on dv.sourceid=ou.organisationunitid where de.valuetype = ‘int’ and de.domaintype = ‘AGGREGATE’ and pe.startdate >= ‘2015-01-01’ and pe.startdate <= ‘2015-12-31’ and dv.value is not null and dv.value ~* ‘^(-?[0-9]+)(.[0-9]+)?$’ and ( dv.value != ‘0’ or de.aggregationtype in (‘avg,avg_sum_org_unit’) or de.zeroissignificant = true ) ]; ERROR: “777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777” is out of range for type double precision; nested exception is org.postgresql.util.PSQLException: ERROR: “777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777” is out of range for type double precision
at java.util.concurrent.FutureTask.report(FutureTask.java:122)
at java.util.concurrent.FutureTask.get(FutureTask.java:192)
at org.hisp.dhis.commons.util.ConcurrentUtils.waitForCompletion(ConcurrentUtils.java:53)
at org.hisp.dhis.analytics.table.DefaultAnalyticsTableService.populateTables(DefaultAnalyticsTableService.java:211)
at org.hisp.dhis.analytics.table.DefaultAnalyticsTableService.update(DefaultAnalyticsTableService.java:128)
at org.hisp.dhis.analytics.table.scheduling.AnalyticsTableTask.run(AnalyticsTableTask.java:142)
at org.hisp.dhis.scheduling.ScheduledTasks.run(ScheduledTasks.java:60)
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:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [insert into analytics_temp_2015 (“vWhzGZMkDh9”,“rbvYf4IjcGf”,“jvksQn3RnbT”,“FSoqQFDES0U”,“Vww46znsPsj”,“JlW9OiK1eR4”,“qSkvkJpTGWN”,“OE9Qlwr8XFv”,“A2DKecGXz73”,“uidlevel1”,“uidlevel2”,“uidlevel3”,“uidlevel4”,“uidlevel5”,“uidlevel6”,“uidlevel7”,“daily”,“weekly”,“monthly”,“bimonthly”,“quarterly”,“sixmonthly”,“sixmonthlyapril”,“yearly”,“financialapril”,“financialjuly”,“financialoct”,“dx”,“co”,“ao”,“ou”,“level”,daysxvalue, daysno, value, textvalue) select degs.“vWhzGZMkDh9”,ougs.“rbvYf4IjcGf”,ougs.“jvksQn3RnbT”,ougs.“FSoqQFDES0U”,ougs.“Vww46znsPsj”,ougs.“JlW9OiK1eR4”,ougs.“qSkvkJpTGWN”,ougs.“OE9Qlwr8XFv”,ougs.“A2DKecGXz73”,ous.“uidlevel1”,ous.“uidlevel2”,ous.“uidlevel3”,ous.“uidlevel4”,ous.“uidlevel5”,ous.“uidlevel6”,ous.“uidlevel7”,ps.“daily”,ps.“weekly”,ps.“monthly”,ps.“bimonthly”,ps.“quarterly”,ps.“sixmonthly”,ps.“sixmonthlyapril”,ps.“yearly”,ps.“financialapril”,ps.“financialjuly”,ps.“financialoct”,de.uid,co.uid,ao.uid,ou.uid,ous.level,cast(dv.value as double precision) * ps.daysno as daysxvalue, ps.daysno as daysno, cast(dv.value as double precision) as value, null as textvalue from datavalue dv left join _dataelementgroupsetstructure degs on dv.dataelementid=degs.dataelementid left join _organisationunitgroupsetstructure ougs on dv.sourceid=ougs.organisationunitid left join _categoryoptiongroupsetstructure cogs on dv.categoryoptioncomboid=cogs.categoryoptioncomboid left join _categoryoptiongroupsetstructure aogs on dv.attributeoptioncomboid=aogs.categoryoptioncomboid left join _categorystructure dcs on dv.categoryoptioncomboid=dcs.categoryoptioncomboid left join _categorystructure acs on dv.attributeoptioncomboid=acs.categoryoptioncomboid left join _orgunitstructure ous on dv.sourceid=ous.organisationunitid left join _dataelementstructure des on dv.dataelementid = des.dataelementid inner join dataelement de on dv.dataelementid=de.dataelementid inner join categoryoptioncombo co on dv.categoryoptioncomboid=co.categoryoptioncomboid inner join categoryoptioncombo ao on dv.attributeoptioncomboid=ao.categoryoptioncomboid inner join _categoryoptioncomboname aon on dv.attributeoptioncomboid=aon.categoryoptioncomboid inner join period pe on dv.periodid=pe.periodid inner join _periodstructure ps on dv.periodid=ps.periodid inner join organisationunit ou on dv.sourceid=ou.organisationunitid where de.valuetype = ‘int’ and de.domaintype = ‘AGGREGATE’ and pe.startdate >= ‘2015-01-01’ and pe.startdate <= ‘2015-12-31’ and dv.value is not null and dv.value ~* ‘^(-?[0-9]+)(.[0-9]+)?$’ and ( dv.value != ‘0’ or de.aggregationtype in (‘avg,avg_sum_org_unit’) or de.zeroissignificant = true ) ]; ERROR: “777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777” is out of range for type double precision; nested exception is org.postgresql.util.PSQLException: ERROR: “777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777” is out of range for type double precision
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:102)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:416)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:440)
at org.hisp.dhis.analytics.table.AbstractJdbcTableManager.populateAndLog(AbstractJdbcTableManager.java:334)
at org.hisp.dhis.analytics.table.JdbcAnalyticsTableManager.populateTable(JdbcAnalyticsTableManager.java:258)
at org.hisp.dhis.analytics.table.JdbcAnalyticsTableManager.populateTableAsync(JdbcAnalyticsTableManager.java:176)
at sun.reflect.GeneratedMethodAccessor1620.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
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:110)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
… 1 more
Caused by: org.postgresql.util.PSQLException: ERROR: “777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777” is out of range for type double precision
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:405)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:397)
at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:1006)
at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:432)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:405)
… 13 more

Get the id of the data value causing the above by running

select * from datavalue where value=’

rgds777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777’

delete the single record causing this and then run analytics

ps note. I have also added you to the group.

rgds

David


Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

http://www.dhis2.org

2015-12-16 14:50 GMT+03:00 Gichangi John gichangijohn3@gmail.com:

Hi Lars

Thanks for the quick reply. I dropped the sql view and the analytics ran without the error but no data was available on pivot and other reports and analysis module.

It also takes 30 mins to run the whole analytics as compared to the usual 3-5 hrs.

The analytics issue arose on upgrade to 2.20.


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

On Tue, Dec 15, 2015 at 11:30 AM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi John,

could you try to first delete the SQL view called “itenget organisationunit” or similar, run analytics again and see if that avoids the error?

regards,

Lars

On Mon, Dec 14, 2015 at 5:14 PM, Gichangi John gichangijohn3@gmail.com wrote:

Hi

Each time i run the Analytics tables update the following error of process failed

Process failed: StatementCallback; uncategorized SQLException for SQL [CREATE VIEW “_view_itengetorganisationunit” AS SELECT ou.organisationunitid, ou.name, ou.parentid, ou.shortname, ou.coordinates, ou.lastupdated, ou.latitude, ou.longitude, ou.created, ou.uid, ous.level, ou.featuretype , ou.code from _orgunitstructure ous join organisationunit ou on ous.organisationunitid=ou.organisationunitid]; SQL state [25P02]; error code [0]; ERROR: current transaction is aborted, commands ignored until end of transaction block; nested exception is org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

What could be causing this failure


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

http://www.dhis2.org