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 <[email protected]> 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 <[email protected]>: > >> 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 < >> [email protected]> 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 <[email protected]> >>> 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 : [email protected] >>>> 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 : [email protected] >> 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 : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
