dhis2-devs team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

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>