dhis2-devs team mailing list archive

[Gichangi John <gichangijohn3@xxxxxxxxx>]

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

> Seems someone had lunch with their elbow on the keyboard ;)
>
> 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='
> rgds
> 777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777';
>
> regards,
>
>
> Lars
>
>
> On Wed, Dec 16, 2015 at 1:31 PM, David Muturi <dnmuturi@xxxxxxxxx> 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='
>> 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
>>>
>>>
>>
>
>
> --
> Lars Helge Øverland
> Lead developer, DHIS 2
> University of Oslo
> Skype: larshelgeoverland
> http://www.dhis2.org <https://www.dhis2.org>
>
>