500 error when posting EML - homepage unique

[mdoering]

When posting an EML document to the registry I see a 500 error. Turns out to be a unique constraint error for the homepage.

Not sure if that constraint is meant to be there, but if so we should at least return some meaningful error like 4xx.

org.postgresql.util.PSQLException: ERROR: new row for relation "dataset" violates check constraint "dataset_homepage_check"
  Detail: Failing row contains (f7f4ba83-8c7f-40b0-876a-018d37332086, 7ddf754f-d193-4cc9-b351-99906754a03b, null, 996d4350-f762-11e1-a439-00145eb45e9a, f4ce3c03-7b38-445e-86e6-5f6b04b649d4, f, CHECKLIST, TAXONOMIC_AUTHORITY, Global Species Database of Odonata, null, null, The Global Species Database of Odonata is a list of all names of..., en, https://web.archive.org/web/20120701124136/http://www.odonata.in..., https://github.com/Sp2000/colplus-repo/raw/master/logos/Odonata...., null, null, null, f, crawler.gbif.org, markus, 2021-03-19 19:48:00.586517+00, 2021-03-20 02:23:42.671136+00, null, '/web/20120701124136/http://www.odonata.info:80/odonata/odonata/..., 10.15468/bryuzy, CC_BY_4_0, null, null).
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
    at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:153)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
    at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
    at sun.reflect.GeneratedMethodAccessor1867.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
    at com.sun.proxy.$Proxy110.update(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:64)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:58)
    at com.sun.proxy.$Proxy128.update(Unknown Source)
    at sun.reflect.GeneratedMethodAccessor2358.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
    at com.sun.proxy.$Proxy129.update(Unknown Source)
    at org.gbif.registry.persistence.WithMyBatis.update(WithMyBatis.java:86)
    at org.gbif.registry.persistence.WithMyBatis$$FastClassBySpringCGLIB$$49e4cad9.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
    at org.gbif.registry.persistence.WithMyBatis$$EnhancerBySpringCGLIB$$fbad216f.update(<generated>)
    at org.gbif.registry.ws.resources.BaseNetworkEntityResource.update(BaseNetworkEntityResource.java:247)
    at org.gbif.registry.ws.resources.DatasetResource.update(DatasetResource.java:642)
    at org.gbif.registry.ws.resources.DatasetResource.update(DatasetResource.java:587)
    at org.gbif.registry.ws.resources.DatasetResource.updateFromPreferredMetadata(DatasetResource.java:449)
    at org.gbif.registry.ws.resources.DatasetResource.insertMetadata(DatasetResource.java:364)
    at org.gbif.registry.ws.resources.DatasetResource.insertMetadata(DatasetResource.java:289)

[mdoering]

Because of this we end up now with a few COL constituent datasets that have rather little metadata: https://www.gbif.org/dataset/f7f4ba83-8c7f-40b0-876a-018d37332086

[fmendezh]

@mdoering that constraint checks that the home_page is an HTTP URL, see:

https://github.com/gbif/registry/blob/master/registry-persistence/src/main/resources/liquibase/schema.sql#L117

https://github.com/gbif/registry/blob/master/registry-persistence/src/main/resources/liquibase/schema.sql#L10

[mdoering]

should we then return a 400 bad request maybe? I have a jersey exception handler that converts org.postgresql.util.PSQLException looking for any constraint violations in COL that might be useful to adopt in the registry:

https://github.com/CatalogueOfLife/backend/blob/master/webservice/src/main/java/life/catalogue/dw/jersey/exception/PersistenceExceptionMapper.java#L23