dhis2-devs team mailing list archive

[Raphael Pundo <rpundo@xxxxxxxxx>]

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

> 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>
>>
>>
>
> _______________________________________________
> 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
>
>