dhis2-devs team mailing list archive

[David Muturi <dnmuturi@xxxxxxxxx>]

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='
rgds
777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777'

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@xxxxxxxxx>:

> 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@xxxxxxxxx
> > 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@xxxxxxxxx>
>> 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@xxxxxxxxxxxxxxxxxxx
>>> 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 <https://www.dhis2.org>
>>
>>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs
> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help   : https://help.launchpad.net/ListHelp
>
>