Removing duplicate collection and received events

Error #1.

org.hibernate.HibernateException: More than one row with the given identifier was found: <N>, for class: com.krishagni.catissueplus.core.biospecimen.domain.SpecimenCollectionReceiveDetail

Error #2.

ERROR org.springframework.web.context.ContextLoader- Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'os-liquibase' defined in ServletContext resource [/WEB-INF/applicationContext.xml]: Invocation of init method failed; nested exception is liquibase.exception.MigrationFailedException: Migration failed for change set db/6.3/migration.xml::Initialize collection event specimen IDs::vpawar:
     Reason: liquibase.exception.DatabaseException: Duplicate entry '136973' for key 'catissue_coll_event_param.COLL_EVENT_SPMN_ID_UQ' [Failed SQL: (1062) update
        catissue_specimen s
        inner join catissue_form_record_entry re on re.object_id = s.identifier
        inner join catissue_form_context fc on fc.identifier = re.form_ctxt_id
        inner join dyextn_containers f on f.identifier = fc.container_id
        inner join catissue_coll_event_param ce on ce.identifier = re.record_id
      set
        ce.specimen_id = s.identifier
      where
        f.name = 'SpecimenCollectionEvent' and
        f.deleted_on is null and
        fc.deleted_on is null and
        s.activity_status != 'Disabled' and
        re.activity_status = 'ACTIVE']
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1619)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:553)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:481)
	at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:312)
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:308)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:756)
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:867)
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542)
	at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:443)
	at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:325)
	at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:107)
	at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4768)
	at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5230)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
	at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:728)
	at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:700)
	at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:696)
	at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:1024)
	at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1911)
	at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.util.concurrent.FutureTask.run(Unknown Source)
	at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
	at java.util.concurrent.AbstractExecutorService.submit(Unknown Source)
	at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:825)
	at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:475)
	at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1618)
	at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:319)
	at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:123)
	at org.apache.catalina.util.LifecycleBase.setStateInternal(LifecycleBase.java:423)
	at org.apache.catalina.util.LifecycleBase.setState(LifecycleBase.java:366)
	at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:948)
	at org.apache.catalina.core.StandardHost.startInternal(StandardHost.java:835)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
	at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1398)
	at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1388)
	at java.util.concurrent.FutureTask.run(Unknown Source)
	at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
	at java.util.concurrent.AbstractExecutorService.submit(Unknown Source)
	at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:921)
	at org.apache.catalina.core.StandardEngine.startInternal(StandardEngine.java:263)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
	at org.apache.catalina.core.StandardService.startInternal(StandardService.java:437)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
	at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:934)
	at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
	at org.apache.catalina.startup.Catalina.start(Catalina.java:772)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:345)
	at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:476)
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set db/6.3/migration.xml::Initialize collection event specimen IDs::vpawar:
     Reason: liquibase.exception.DatabaseException: Duplicate entry '136973' for key 'catissue_coll_event_param.COLL_EVENT_SPMN_ID_UQ' [Failed SQL: (1062) update
        catissue_specimen s
        inner join catissue_form_record_entry re on re.object_id = s.identifier
        inner join catissue_form_context fc on fc.identifier = re.form_ctxt_id
        inner join dyextn_containers f on f.identifier = fc.container_id
        inner join catissue_coll_event_param ce on ce.identifier = re.record_id
      set
        ce.specimen_id = s.identifier
      where
        f.name = 'SpecimenCollectionEvent' and
        f.deleted_on is null and
        fc.deleted_on is null and
        s.activity_status != 'Disabled' and
        re.activity_status = 'ACTIVE']
	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:646)
	at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:53)
	at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:83)
	at liquibase.Liquibase.update(Liquibase.java:202)
	at liquibase.Liquibase.update(Liquibase.java:179)
	at liquibase.integration.spring.SpringLiquibase.performUpdate(SpringLiquibase.java:366)
	at liquibase.integration.spring.SpringLiquibase.afterPropertiesSet(SpringLiquibase.java:314)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1677)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1615)
	... 53 more
Caused by: liquibase.exception.DatabaseException: Duplicate entry '136973' for key 'catissue_coll_event_param.COLL_EVENT_SPMN_ID_UQ' [Failed SQL: (1062) update
        catissue_specimen s
        inner join catissue_form_record_entry re on re.object_id = s.identifier
        inner join catissue_form_context fc on fc.identifier = re.form_ctxt_id
        inner join dyextn_containers f on f.identifier = fc.container_id
        inner join catissue_coll_event_param ce on ce.identifier = re.record_id
      set
        ce.specimen_id = s.identifier
      where
        f.name = 'SpecimenCollectionEvent' and
        f.deleted_on is null and
        fc.deleted_on is null and
        s.activity_status != 'Disabled' and
        re.activity_status = 'ACTIVE']
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:402)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:59)
	at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:131)
	at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1276)
	at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1258)
	at liquibase.changelog.ChangeSet.execute(ChangeSet.java:609)
	... 61 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '136973' for key 'catissue_coll_event_param.COLL_EVENT_SPMN_ID_UQ'
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
	at org.apache.tomcat.dbcp.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193)
	at org.apache.tomcat.dbcp.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:193)
	at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:398)
	... 66 more

Cause

In caTissue, the collection and received event details were copied to every descendent of the primary specimen. As a result, during our migration, every specimen got collection/received event as well. In v3.3, we added a view to allow querying of child specimens based on collection/received event details. As a result, every child specimen will have more than 1 row in this view (one from the parent and another from itself). However the view is mapped in our app in such a way that only one collection/received event row can exist for every specimen. Therefore the above error.
There were bugs in caTissue that allowed multiple collection and received events for parent specimens.

Solution

Run below SQLs to confirm that there exists one or more specimens with more than one collection/received events:

select
  count(ce.identifier)
from
  catissue_form_record_entry fre
  inner join catissue_form_context fc on fc.identifier = fre.form_ctxt_id
  inner join dyextn_containers f on f.identifier = fc.container_id
  inner join catissue_coll_event_param ce on ce.identifier = fre.record_id
  inner join catissue_specimen s on s.identifier = fre.object_id
where
  f.name = 'SpecimenCollectionEvent' and
  f.deleted_on is null and
  fc.deleted_on is null and
  fre.activity_status = 'ACTIVE' and
  s.activity_status != 'Disabled'
group by
  s.identifier
having
  count(ce.identifier) > 1;

select
  count(rv.identifier)
from
  catissue_form_record_entry fre
  inner join catissue_form_context fc on fc.identifier = fre.form_ctxt_id
  inner join dyextn_containers f on f.identifier = fc.container_id
  inner join catissue_received_event_param rv on rv.identifier = fre.record_id
  inner join catissue_specimen s on s.identifier = fre.object_id
where
  f.name = 'SpecimenReceivedEvent' and
  f.deleted_on is null and
  fc.deleted_on is null and
  fre.activity_status = 'ACTIVE' and
  s.activity_status != 'Disabled'
group by
  s.identifier
having
  count(rv.identifier) > 1;

select
  cv.specimen_id, count(*)
from
  os_spmn_coll_recv_details_view cv
  inner join catissue_specimen s on s.identifier = cv.specimen_id
where
  s.activity_status != 'Disabled'
group by
  cv.specimen_id
having
  count(*) > 1;

Run below SQL to remove collection and received event records for all the child specimens:

update 
  catissue_form_record_entry re 
set 
  re.activity_status = 'CLOSED' 
where 
  re.object_id in (
    select 
      identifier 
    from 
      catissue_specimen 
    where 
      parent_specimen_id is not null
  ) and 
  re.form_ctxt_id in (
    select
      fc.identifier 
    from 
      catissue_form_context fc 
      inner join dyextn_containers f on f.identifier = fc.container_id
    where
      f.name in ('SpecimenCollectionEvent', 'SpecimenReceivedEvent') and
      fc.entity_type = 'SpecimenEvent'
  );

Run SQL of step #1 to ensure it returns no rows. If no rows are returned, then stop. If one or more rows are returned, it means the database has duplicate collection/received events for primary specimens. To remove duplicate events at primary specimen level, run below SQL.

update
  catissue_form_record_entry re
set
  re.activity_status = 'CLOSED'
where
  re.object_id in (
    select
      identifier 
    from 
      catissue_specimen 
    where 
      parent_specimen_id is null
  ) and 
  re.form_ctxt_id in ( 
    select
      fc.identifier 
    from 
      catissue_form_context fc 
      inner join dyextn_containers f on f.identifier = fc.container_id
    where
      f.name in ('SpecimenCollectionEvent', 'SpecimenReceivedEvent') and
      fc.entity_type = 'SpecimenEvent'
  ) and
  re.identifier not in ( 
    select
      * 
    from ( 
      select 
        max(ire.identifier)
      from
        catissue_form_record_entry ire
        inner join catissue_form_context ifc on ifc.identifier = ire.form_ctxt_id
        inner join dyextn_containers ic on ic.identifier = ifc.container_id
        inner join catissue_specimen ispmn on ispmn.identifier = ire.object_id
      where
        ifc.entity_type = 'SpecimenEvent' and
        ic.name in ('SpecimenCollectionEvent', 'SpecimenReceivedEvent') and
        ire.activity_status != 'CLOSED'
      group by
        ispmn.identifier, ifc.identifier
    ) t
  );

Run SQL in step 1 to ensure no rows are returned.