11286 Reporter: kbraak Assignee: jcuadra Type: Bug Summary: registry-persistence-service: exception encountered Priority: Major Resolution: Fixed Status: Closed Created: 2012-06-01 18:43:41.001 Updated: 2013-12-16 17:50:52.197 Resolved: 2012-06-28 15:53:12.515 Description: Note: this occurred using a version of the registry-ws I deployed against the mogo.registry_uat database While paging over datasets, I encountered the following exception: org.apache.ibatis.exceptions.PersistenceException: Error returning paged list at org.gbif.registry.service.impl.AgentManagerImpl.listByPage(AgentManagerImpl.java:77) at org.gbif.registry.service.AgentSupport.list(AgentSupport.java:157) at org.gbif.registry.service.NetworkEntityServiceImpl.list(NetworkEntityServiceImpl.java:104) at org.gbif.registry.service.OrganizationServiceImpl.list(OrganizationServiceImpl.java:23) at org.gbif.registry.ws.resources.NetworkEntityResource.list(NetworkEntityResource.java:145) at sun.reflect.GeneratedMethodAccessor484.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60) at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:185) at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75) at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:288) at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108) at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84) at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1469) at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1400) at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1349) at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1339) at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:416) at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:537) at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:708) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263) at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178) at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91) at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62) at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118) at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489) at java.lang.Thread.run(Thread.java:662) Caused by: org.apache.ibatis.exceptions.PersistenceException: ### Error querying database. Cause: java.sql.SQLException: Bad format for BigDecimal '46,99285' in column 13. ### The error may involve org.gbif.registry.mybatis.mapper.CommonAgentMapper.selectMetadataProperties-Inline ### The error occurred while setting parameters ### Cause: java.sql.SQLException: Bad format for BigDecimal '46,99285' in column 13. at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:81) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:73) at sun.reflect.GeneratedMethodAccessor392.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.apache.ibatis.session.SqlSessionManager$SqlSessionInterceptor.invoke(SqlSessionManager.java:269) at $Proxy26.selectList(Unknown Source) at org.apache.ibatis.session.SqlSessionManager.selectList(SqlSessionManager.java:158) at org.apache.ibatis.binding.MapperMethod.executeForList(MapperMethod.java:100) at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:70) at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:25) at $Proxy117.listOfAgentTypeByPage(Unknown Source) at org.gbif.registry.service.impl.AgentManagerImpl.listByPage(AgentManagerImpl.java:72) ... 40 more Caused by: java.sql.SQLException: Bad format for BigDecimal '46,99285' in column 13. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927) at com.mysql.jdbc.ResultSetImpl.getBigDecimal(ResultSetImpl.java:1307) at com.mysql.jdbc.ResultSetImpl.getBigDecimal(ResultSetImpl.java:1414) at org.apache.ibatis.type.BigDecimalTypeHandler.getNullableResult(BigDecimalTypeHandler.java:18) at org.apache.ibatis.type.BigDecimalTypeHandler.getNullableResult(BigDecimalTypeHandler.java:9) at org.apache.ibatis.type.BaseTypeHandler.getResult(BaseTypeHandler.java:29) at org.apache.ibatis.executor.resultset.FastResultSetHandler.getPropertyMappingValue(FastResultSetHandler.java:267) at org.apache.ibatis.executor.resultset.FastResultSetHandler.applyPropertyMappings(FastResultSetHandler.java:250) at org.apache.ibatis.executor.resultset.NestedResultSetHandler.getRowValue(NestedResultSetHandler.java:97) at org.apache.ibatis.executor.resultset.NestedResultSetHandler.handleRowValues(NestedResultSetHandler.java:67) at org.apache.ibatis.executor.resultset.FastResultSetHandler.handleResultSet(FastResultSetHandler.java:146) at org.apache.ibatis.executor.resultset.FastResultSetHandler.handleResultSets(FastResultSetHandler.java:112) at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:40) at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:55) at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:41) at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:243) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:117) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:60) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:79) ... 52 more]]>
Author: kbraak@gbif.org
Created: 2012-06-04 16:41:20.181
Updated: 2012-06-04 16:41:20.181
This problem arises due to the decimal latitude or decimal longitude fields (in agent.decimal_latitude or agent.decimal_longitude) using a comma "," instead of a period "."
For example, this can be converted into a BigDecimal: "46.99285", but "46,99285" cannot and throws this error.
This error only occurred on the latest dataset-aware registry that was created from the migration scripts, it doesn't exist in registry_staging.
Therefore, it requires a change to the registry-migration project.
Author: kbraak@gbif.org
Created: 2012-06-20 16:05:52.014
Updated: 2012-06-20 16:05:52.014
Great, so the registry-migration is working nicely after checking the latest version built.
The only way this exception gets seen again, is if the client is persisting lat/long using "," instead of "." - can we please check this before closing? Jose?
Author: kbraak@gbif.org Comment: Jose, can you please do me a favor and check how the you are saving lat/long for an organization for example? Created: 2012-06-20 16:06:47.296 Updated: 2012-06-20 16:06:47.296
Author: jcuadra@gbif.org Comment: I will add a custom constraint on the latitude & longitude attributes on the WritableMember class. A validation error will be thrown if a client tries to persist a non-numeric value. Created: 2012-06-22 11:49:29.279 Updated: 2012-06-22 11:49:29.279
Author: trobertson@gbif.org
Created: 2012-06-22 11:56:01.699
Updated: 2012-06-22 11:56:01.699
Would it not be better to do something along the lines of this, and then test it is numeric?
latitude = latitude.replaceAll(",", ".");
Author: mdoering@gbif.org Comment: As suggested in REG-230, why not make the db field numeric in the first place? Created: 2012-06-22 11:57:52.898 Updated: 2012-06-22 11:58:05.489
Author: jcuadra@gbif.org
Created: 2012-06-22 12:39:33.776
Updated: 2012-06-22 12:40:11.73
ah. I thought for a second that lat&long where represented as Strings in the API but not. Doing a client test trying to write a non-numeric value to those fields I get a 500 errors but an awful jersey mapping error (error on the lower part of the comment)
=======================
request:
curl -H "Accept: application/json" -H "Content-type: application/json" -d '{"title":"Title", "latitude":"45d", "longitude":"45aa"}' http://localhost:8080/organization -v
========================
I think the API modification is not needed then , as I presume the error will be thrown anyways before even doing the API validation. Then the question is how we handle gracefully this jersey mapping errors?
=====
error stack
=====
HTTP ERROR 500
Problem accessing /organization. Reason:
Can not construct instance of java.math.BigDecimal from String value '45aa': not a valid representation
at [Source: org.eclipse.jetty.server.HttpInput@75c57e85; line: 1, column: 34] (through reference chain: org.gbif.registry.api.model.WritableOrganization["longitude"])
Caused by:
org.codehaus.jackson.map.JsonMappingException: Can not construct instance of java.math.BigDecimal from String value '45aa': not a valid representation
at [Source: org.eclipse.jetty.server.HttpInput@75c57e85; line: 1, column: 34] (through reference chain: org.gbif.registry.api.model.WritableOrganization["longitude"])
Author: jcuadra@gbif.org
Created: 2012-06-28 15:51:32.342
Updated: 2012-06-28 15:52:25.784
This is no longer a problem. Fede's migration script handles lat&longs that include ","s on those fields and transform them into proper numeric values. So the migrated Registry DB (registry_uat) does not hold this problem anymore.
There is still an open issue on validation of input data from web service. Example: when a latitude value receives a non-numeric value (via a POST/PUT request). Jackson's JSON processor goes wild when deserializing the JSON to the corresponding api object.
http://dev.gbif.org/issues/browse/REG-188