Project

General

Profile

bug #9657

OccurrenceServiceImpl.listRootUnitDTOsByAssociatedTaxon fails with HibernateException: More than one row with the given identifier was found: 51

Added by Andreas Kohlbecker 4 months ago. Updated 3 months ago.

Status:
Closed
Priority:
Highest
Category:
cdmlib
Target version:
Start date:
06/08/2021
Due date:
% Done:

100%

Severity:
normal
Found in Version:

Description

http://api.cybertaxonomy.org/flora-malesiana-prospective/portal/taxon/2debf5ee-cb57-40bc-af89-173d1d17cefe/rootUnitDTOs.json

Caused by: org.hibernate.HibernateException: More than one row with the given identifier was found: 51, for class: eu.etaxonomy.cdm.model.name.NomenclaturalSource
    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:86)
    at org.hibernate.loader.entity.EntityLoader.loadByUniqueKey(EntityLoader.java:143)
    at org.hibernate.persister.entity.AbstractEntityPersister.loadByUniqueKey(AbstractEntityPersister.java:2238)
    at org.hibernate.type.EntityType.loadByUniqueKey(EntityType.java:694)
    at org.hibernate.type.EntityType.resolve(EntityType.java:436)
    at org.hibernate.engine.internal.TwoPhaseLoad.doInitializeEntity(TwoPhaseLoad.java:165)
    at org.hibernate.engine.internal.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:125)
    at org.hibernate.loader.plan.exec.process.internal.AbstractRowReader.performTwoPhaseLoad(AbstractRowReader.java:238)
    at org.hibernate.loader.plan.exec.process.internal.AbstractRowReader.finishUp(AbstractRowReader.java:209)
    at org.hibernate.loader.plan.exec.process.internal.ResultSetProcessorImpl.extractResults(ResultSetProcessorImpl.java:133)
    at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:122)
    at org.hibernate.loader.plan.exec.internal.AbstractLoadPlanBasedLoader.executeLoad(AbstractLoadPlanBasedLoader.java:86)
    at org.hibernate.loader.entity.plan.AbstractLoadPlanBasedEntityLoader.load(AbstractLoadPlanBasedEntityLoader.java:167)
    at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:4083)
    at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:508)
    at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:478)
    at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:219)
    at org.hibernate.event.internal.DefaultLoadEventListener.doOnLoad(DefaultLoadEventListener.java:116)
    at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:89)
    at org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:1142)
    at org.hibernate.internal.SessionImpl.immediateLoad(SessionImpl.java:1000)
    at org.hibernate.proxy.AbstractLazyInitializer.initialize(AbstractLazyInitializer.java:156)
    at org.hibernate.Hibernate.initialize(Hibernate.java:60)
    at eu.etaxonomy.cdm.hibernate.HibernateProxyHelper.deproxy(HibernateProxyHelper.java:61)
    at eu.etaxonomy.cdm.model.common.CdmBase.deproxy(CdmBase.java:312)
    at eu.etaxonomy.cdm.persistence.hibernate.CdmPreDataChangeListener.generateCaches(CdmPreDataChangeListener.java:148)
    at eu.etaxonomy.cdm.persistence.hibernate.CacheStrategyGenerator.saveOrUpdateOrMerge(CacheStrategyGenerator.java:47)
    at eu.etaxonomy.cdm.persistence.hibernate.CacheStrategyGenerator.onSaveOrUpdate(CacheStrategyGenerator.java:32)
    at org.hibernate.internal.SessionImpl.fireSaveOrUpdate(SessionImpl.java:651)
    at org.hibernate.internal.SessionImpl.saveOrUpdate(SessionImpl.java:643)
    at org.hibernate.engine.spi.CascadingActions$5.cascade(CascadingActions.java:218)
    at org.hibernate.engine.internal.Cascade.cascadeToOne(Cascade.java:423)
    at org.hibernate.engine.internal.Cascade.cascadeAssociation(Cascade.java:348)
    at org.hibernate.engine.internal.Cascade.cascadeProperty(Cascade.java:187)
    at org.hibernate.engine.internal.Cascade.cascade(Cascade.java:136)
    at org.hibernate.event.internal.AbstractFlushingEventListener.cascadeOnFlush(AbstractFlushingEventListener.java:150)
    at org.hibernate.event.internal.AbstractFlushingEventListener.prepareEntityFlushes(AbstractFlushingEventListener.java:141)
    at org.hibernate.event.internal.AbstractFlushingEventListener.flushEverythingToExecutions(AbstractFlushingEventListener.java:74)
    at org.hibernate.event.internal.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:44)
    at org.hibernate.internal.SessionImpl.autoFlushIfRequired(SessionImpl.java:1264)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1332)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87)
    at eu.etaxonomy.cdm.persistence.dao.initializer.AdvancedBeanInitializer.bulkLoadLazyCollections(AdvancedBeanInitializer.java:530)
    at eu.etaxonomy.cdm.persistence.dao.initializer.AdvancedBeanInitializer.bulkLoadLazies(AdvancedBeanInitializer.java:432)
    at eu.etaxonomy.cdm.persistence.dao.initializer.AdvancedBeanInitializer.initializeNodeNoWildcard(AdvancedBeanInitializer.java:241)
    at eu.etaxonomy.cdm.persistence.dao.initializer.AdvancedBeanInitializer.initializeNode(AdvancedBeanInitializer.java:122)
    at eu.etaxonomy.cdm.persistence.dao.initializer.AdvancedBeanInitializer.initializeNodeRecursive(AdvancedBeanInitializer.java:102)
    at eu.etaxonomy.cdm.persistence.dao.initializer.AdvancedBeanInitializer.initializeNodeRecursive(AdvancedBeanInitializer.java:104)
    at eu.etaxonomy.cdm.persistence.dao.initializer.AdvancedBeanInitializer.initializeAll(AdvancedBeanInitializer.java:84)
    at eu.etaxonomy.cdm.persistence.dao.hibernate.occurrence.OccurrenceDaoHibernateImpl.listByAssociatedTaxon(OccurrenceDaoHibernateImpl.java:705)
    at eu.etaxonomy.cdm.api.service.OccurrenceServiceImpl.listRootUnitDTOsByAssociatedTaxon(OccurrenceServiceImpl.java:539)

Associated revisions

Revision 2fb46fd8 (diff)
Added by Andreas Müller 3 months ago

ref #9657 fix gatheringDate compare for empty dates (e.g. freetext dates)

Revision 8c341841 (diff)
Added by Andreas Müller 3 months ago

ref #9657 fully implement compare method for partials used in CDM

Revision 315d359e (diff)
Added by Andreas Müller 3 months ago

ref #9657 cleanup and make Partial comparator configurable for null handling

History

#1 Updated by Andreas Müller 4 months ago

I tested by calling

appCtr.getOccurrenceService().findRootUnitDTOs(UUID.fromString("2debf5ee-cb57-40bc-af89-173d1d17cefe"));

from commandline but could not reproduce. Seems like autoflush behavior is different. E.g.

at eu.etaxonomy.cdm.persistence.hibernate.CdmPreDataChangeListener.generateCaches(CdmPreDataChangeListener.java:148)

is not called

#2 Updated by Andreas Müller 4 months ago

But there are multiple nomSources that link to the same name:

SELECT *
FROM OriginalSourceBase osb 
WHERE osb.sourcedName_id = 51

more generell:

SELECT osb.sourcedName_id , osb.*
FROM OriginalSourceBase osb
WHERE osb.sourcedName_id IN (
SELECT osb.sourcedName_id
FROM OriginalSourceBase osb
WHERE osb.DTYPE LIKE 'Nomen%'
GROUP BY osb.sourcedName_id
HAVING COUNT(*) > 1
)

#3 Updated by Andreas Müller 4 months ago

Strange is, this problem exists only in flora_malesiana_prospective. In no other production database such duplicate records exist.

Also strange, the records are all completely empty except for the sourcedName_id (and base data like id, uuid, created, DTYPE and sourceType)

#4 Updated by Andreas Müller 4 months ago

  • Status changed from New to Resolved
  • % Done changed from 0 to 50

I checked in old databases from 12_2020 (before nomenclatural sources movement) and 04_2021 (before deleting old TaxonName.nomenclaturalCitation_id column).
In none of the databases there was an indication why these records were created. So I deleted them form OriginalSourceBase adn OriginalSourceBase_AUD (for strange reason theses records were created also in AUD while other nom.source records are not audited before the 12_2020 move).

#5 Updated by Andreas Müller 4 months ago

  • Assignee changed from Andreas Müller to Andreas Kohlbecker

#6 Updated by Andreas Müller 4 months ago

Andreas Müller wrote:

Please review. The page shows no error anymore: http://portal.cybertaxonomy.org/flora-malesiana-prospective/cdm_dataportal/taxon/2debf5ee-cb57-40bc-af89-173d1d17cefe/specimens

However, I still get an empty result for the api query in the description (http://api.cybertaxonomy.org/flora-malesiana-prospective/portal/taxon/2debf5ee-cb57-40bc-af89-173d1d17cefe/rootUnitDTOs.json)

Upps, sorry, that was not correct. It only worked because "derivate path" was used. But now the other error also observed for alga terra (no ticket yet) seems to appear when switching to tree version.

#7 Updated by Andreas Kohlbecker 3 months ago

  • Status changed from Resolved to Feedback
  • Assignee changed from Andreas Kohlbecker to Andreas Müller

the original exception has disappeared, but now the request portal/taxon/2debf5ee-cb57-40bc-af89-173d1d17cefe/rootUnitDTOs.json

fails with a ClassCastException:

Caused by: java.lang.ClassCastException: ReadablePartial objects must have matching field types
    at org.joda.time.base.AbstractPartial.compareTo(AbstractPartial.java:315)
    at eu.etaxonomy.cdm.api.service.OccurrenceServiceImpl$1.compare(OccurrenceServiceImpl.java:586)
    at eu.etaxonomy.cdm.api.service.OccurrenceServiceImpl$1.compare(OccurrenceServiceImpl.java:1)
    at java.util.TimSort.binarySort(TimSort.java:296)
    at java.util.TimSort.sort(TimSort.java:239)
    at java.util.Arrays.sort(Arrays.java:1512)
    at java.util.ArrayList.sort(ArrayList.java:1454)
    at java.util.Collections.sort(Collections.java:175)
    at eu.etaxonomy.cdm.api.service.OccurrenceServiceImpl.listRootUnitDTOsByAssociatedTaxon(OccurrenceServiceImpl.java:570)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    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:333)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:283)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
    at com.sun.proxy.$Proxy179.listRootUnitDTOsByAssociatedTaxon(Unknown Source)
    at eu.etaxonomy.cdm.remote.controller.TaxonController.doListRooUnitDTOs(TaxonController.java:344)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:854)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:765)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
    ... 67 more

this should be fixed before releasing

#8 Updated by Andreas Müller 3 months ago

  • Status changed from Feedback to Resolved
  • Assignee changed from Andreas Müller to Andreas Kohlbecker

This problem occurs due to using the compareTo method for gatheringEvent.timeperiod.start in the newly updated comparator for listRootUnitDTOsByAssociatedTaxon.
Timeperiod.start is a partial and compareTo therefore must only be used if partial.size() is equal as documented in the javadoc.

In a first step I excluded now all empty timeperiod.start partials from comparison to avoid the above exception which is probably thrown due to a timeperiod using freetext instead of start.
However, further adaptation is needed to allow comparison of partials also for partials only having any of the year, month or day fields set.

#9 Updated by Andreas Müller 3 months ago

Can you please test this? As we do do not have a test server for flora-malesiana-prospective I can't do this easily.

#10 Updated by Andreas Kohlbecker 3 months ago

I tested again locally the line on OccurrenceServiceImpl$1.compare(OccurrenceServiceImpl.java:588) has changed but the error persists:

Caused by: java.lang.ClassCastException: ReadablePartial objects must have matching field types
    at org.joda.time.base.AbstractPartial.compareTo(AbstractPartial.java:315)
    at eu.etaxonomy.cdm.api.service.OccurrenceServiceImpl$1.compare(OccurrenceServiceImpl.java:588)
    at eu.etaxonomy.cdm.api.service.OccurrenceServiceImpl$1.compare(OccurrenceServiceImpl.java:1)
    at java.util.TimSort.binarySort(TimSort.java:296)
    at java.util.TimSort.sort(TimSort.java:239)
    at java.util.Arrays.sort(Arrays.java:1512)
    at java.util.ArrayList.sort(ArrayList.java:1454)
    at java.util.Collections.sort(Collections.java:175)
    at eu.etaxonomy.cdm.api.service.OccurrenceServiceImpl.listRootUnitDTOsByAssociatedTaxon(OccurrenceServiceImpl.java:571)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    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:333)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:283)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
    at com.sun.proxy.$Proxy179.listRootUnitDTOsByAssociatedTaxon(Unknown Source)
    at eu.etaxonomy.cdm.remote.controller.TaxonController.doListRooUnitDTOs(TaxonController.java:344)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:854)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:765)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
    ... 67 more

#11 Updated by Andreas Kohlbecker 3 months ago

  • Status changed from Resolved to In Progress
  • Assignee changed from Andreas Kohlbecker to Andreas Müller

#12 Updated by Andreas Müller 3 months ago

  • Status changed from In Progress to Resolved
  • Assignee changed from Andreas Müller to Andreas Kohlbecker

This should be fully fixed now. Comparing partials may not throw an exception anymore. Only the order of gathering events with date and without date may have changed so we need to check if all dataportal tests still run.

Generally we order dates with no information to the end (=largest date). This previoursly has been implemented in listRootUnitDTOsByAssociatedTaxon() the other way round as far as I can see.

Please reveiw.

#13 Updated by Andreas Kohlbecker 3 months ago

  • Status changed from Resolved to Closed
  • Assignee changed from Andreas Kohlbecker to Andreas Müller
  • % Done changed from 50 to 100

The above request is now ok and does no longer throw an exception.

Sort order deviation in the portal an be neglected for now. We can handle these later if needed.

Also available in: Atom PDF

Add picture from clipboard (Maximum size: 40 MB)