Start a new topic
Discussions xDM xDM - Platform Configuration & Administration
Answered

How to solve- !MESSAGE Error synchronizing RepoLoad 4309 for dloc Eternity? 

org.springframework.dao.IncorrectResultSizeDataAccessException: Incorrect result size: expected 1, actual 2
  at org.springframework.dao.support.DataAccessUtils.nullableSingleResult(DataAccessUtils.java:100)
  at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:237)
  at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:245)
  at com.semarchy.platform.integration.polling.dlbatch.RepoLoadDao.queryRepoLoad(RepoLoadDao.java:153)
  at com.semarchy.platform.integration.polling.dlbatch.DlBatchSynchronizer.checkRepoLoadOptimisticLocking(DlBatchSynchronizer.java:205)
  at com.semarchy.platform.integration.polling.dlbatch.DlBatchSynchronizer.lambda$6(DlBatchSynchronizer.java:199)
  at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
  at com.semarchy.platform.integration.polling.dlbatch.DlBatchSynchronizer.lambda$5(DlBatchSynchronizer.java:196)
  at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
  at com.semarchy.platform.integration.polling.dlbatch.DlBatchSynchronizer.synchronizeRepoLoad(DlBatchSynchronizer.java:196)
  at com.semarchy.platform.integration.polling.dlbatch.DlBatchSynchronizer.synchronizeDlBatchForDesynchronizedLoads(DlBatchSynchronizer.java:111)
  at com.semarchy.platform.integration.polling.IntegrationLoadDequeuer.dequeue(IntegrationLoadDequeuer.java:514)
  at com.semarchy.platform.integration.polling.AbstractIntegrationBatchPoller.dequeue(AbstractIntegrationBatchPoller.java:155)
  at com.semarchy.platform.integration.polling.quartz.QuartzIntegrationBatchPoller.execute(QuartzIntegrationBatchPoller.java:214)
  at com.semarchy.platform.integration.polling.quartz.QuartzIntegrationBatchPoller$SemarchyJob.execute(QuartzIntegrationBatchPoller.java:240)
  at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
  at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)


Best Answer
You must have grabbed a data location from another environment with pending loads or something so.
You have to pass this query to put it in order:
merge into sem_repository.MTA_INTEG_LOAD T
using (select  IL.UUID UUID,        IL.REVISION IL_REVISION,        IB.REVISION IB_REVISION
           from sem_repository.MTA_INTEG_LOAD IL
           inner join sem_repository.MTA_DATA_LOCATION DL on (IL.O_DATALOCATION = DL.UUID)
           left join sem_repository.MTA_INTEG_BATCH IB on IB.R_INTEGLOAD = IL.UUID
           where IL.CLASSNAME <> 'ContinuousLoad' and DL. NAME in ('$$DL_NAMES$$')  
           and (IL.LAST_DL_BATCH_SYNCED_REVISION is null  or IL.LAST_DL_BATCH_SYNCED_REVISION < IL.REVISION  or (IB.REVISION is not null and IL.LAST_DL_BATCH_SYNCED_REVISION < IB.REVISION) ) ) S 
on (T.UUID = S.UUID)
when matched then update set T.LAST_DL_BATCH_SYNCED_REVISION = GREATEST(COALESCE( IL_REVISION, 0), COALESCE(IB_REVISION, 0)); 
commit

Replace $$DL_NAMES$$ with your Data location name.

2 CommentsSorted by Newest First

The following Error occurs due to Desynchronization between the MTA_INTEG_BATC and MTA_INTEG_LOAD_ and DL_BATCH tables. Probable cause is a loss of connection to the database. 



org.springframework.dao.IncorrectResultSizeDataAccessException:Incorrect result size: expected 1, actual 2   at org.springframework.dao.support.DataAccessUtils.nullableSingleResult(DataAccessUtils.java:100)   at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:237)   at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:245)   at com.semarchy.platform.integration.polling.dlbatch.RepoLoadDao.queryRepoLoad(RepoLoadDao.java:153)   at com.semarchy.platform.integration.polling.dlbatch.DlBatchSynchronizer.checkRepoLoadOptimisticLocking(DlBatchSynchronizer.java:205)   at com.semarchy.platform.integration.polling.dlbatch.DlBatchSynchronizer.lambda$6(DlBatchSynchronizer.java:199)   at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)   at com.semarchy.platform.integration.polling.dlbatch.DlBatchSynchronizer.lambda$5(DlBatchSynchronizer.java:196)   at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)   at com.semarchy.platform.integration.polling.dlbatch.DlBatchSynchronizer.synchronizeRepoLoad(DlBatchSynchronizer.java:196)   at com.semarchy.platform.integration.polling.dlbatch.DlBatchSynchronizer.synchronizeDlBatchForDesynchronizedLoads(DlBatchSynchronizer.java:111)   at com.semarchy.platform.integration.polling.IntegrationLoadDequeuer.dequeue(IntegrationLoadDequeuer.java:514)   at com.semarchy.platform.integration.polling.AbstractIntegrationBatchPoller.dequeue(AbstractIntegrationBatchPoller.java:155)   at com.semarchy.platform.integration.polling.quartz.QuartzIntegrationBatchPoller.execute(QuartzIntegrationBatchPoller.java:214)   at com.semarchy.platform.integration.polling.quartz.QuartzIntegrationBatchPoller$SemarchyJob.execute(QuartzIntegrationBatchPoller.java:240)   at org.quartz.core.JobRunShell.run(JobRunShell.java:202)   at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)



Steps to Resolve the issue :- 


1.  Run this query  and see the result ( there will be last_dl_batch_synced_revision   Column will be null for some records due to this problem is generated): 


select        IL.UUID UUID,        IL.REVISION IL_REVISION,        IB.REVISION IB_REVISION    , loadid , LAST_DL_BATCH_SYNCED_REVISION
from MTA_INTEG_LOAD IL    
inner join MTA_DATA_LOCATION DL on (IL.O_DATALOCATION = DL.UUID)    
left join MTA_INTEG_BATCH IB on IB.R_INTEGLOAD = IL.UUID 
where        IL.CLASSNAME <> 'ContinuousLoad'  
   
   and (            
       IL.LAST_DL_BATCH_SYNCED_REVISION is null            
       or IL.LAST_DL_BATCH_SYNCED_REVISION < IL.REVISION            
       or (IB.REVISION is not null and IL.LAST_DL_BATCH_SYNCED_REVISION < IB.REVISION)            
   ) 

2. After seeing result of above query then go for pending and running batch status by running below query :- 

select STATUS, COUNT(*)
from mta_integ_batch
group by status 
order by status
;

You should have few of them either in 'RUNNING' or 'PENDING' or 'SCHEDULED'  and if the number checked with the error message, run

update mta_integ_batch
where status in ('PENDING', 'RUNNING')
set status = 'ERROR'


3.  Run the below query and replace ## LE NOM DE TON SHCEMA REPO ##  With your Schema Repository Name and ## LE NOM DE TA DATA LOCATION ## with your Data location name.


UPDATE ## LE NOM DE TON SHCEMA REPO ##.MTA_INTEG_LOAD IL 
    SET LAST_DL_BATCH_SYNCED_REVISION = greatest( 
        coalesce(IL.REVISION, 0), 
        coalesce(IB.REVISION, 0)) 
FROM ## LE NOM DE TON SHCEMA REPO ##.MTA_INTEG_BATCH as IB, ## LE NOM DE TON SHCEMA REPO ##.MTA_DATA_LOCATION as DL 
where      IB.R_INTEGLOAD = IL.UUID 
    AND IL.O_DATALOCATION = DL.UUID 
    AND IL.CLASSNAME <> 'DirectDupsManagementLoad' 
    and DL.NAME in ('## LE NOM DE TA DATA LOCATION ##') 
    and ( 
        IL.LAST_DL_BATCH_SYNCED_REVISION is null 
        or IL.LAST_DL_BATCH_SYNCED_REVISION < IL.REVISION 
        or ( 
            IB.REVISION is not null 
            and IL.LAST_DL_BATCH_SYNCED_REVISION < IB.REVISION 
            ) 
        ) 
      ;

Then run below query ( Replace $$DL_NAMES$$ with your Data location name. ). ( You must have grabbed a data location from another environment with pending loads or something so. You have to pass this query to put it in order ) :- 


MERGE INTO mta_integ_load t
USING (
    SELECT
        il.uuid     uuid,
        il.revision il_revision,
        ib.revision ib_revision
    FROM
             mta_integ_load il
        INNER JOIN mta_data_location dl ON ( il.o_datalocation = dl.uuid )
        LEFT JOIN mta_integ_batch   ib ON ib.r_integload = il.uuid
    WHERE
            il.classname <> 'ContinuousLoad'
        AND dl.name IN ( '$$DL_NAMES$$' )
        AND ( il.last_dl_batch_synced_revision IS NULL
              OR il.last_dl_batch_synced_revision < il.revision
              OR ( ib.revision IS NOT NULL
                   AND il.last_dl_batch_synced_revision < ib.revision ) )
) s ON ( t.uuid = s.uuid )
WHEN MATCHED THEN UPDATE
SET t.last_dl_batch_synced_revision = greatest(coalesce(il_revision, 0),
                                               coalesce(ib_revision, 0));


4.  Try again after clearing cache of tomcat If possible :- 

    Stop Tomcat.
    Delete the folder semarchy folder in webapps/
    Delete the folder Catalina folder in work/
    Delete all logs.
    Start the tomcat.

Answer
You must have grabbed a data location from another environment with pending loads or something so.
You have to pass this query to put it in order:
merge into sem_repository.MTA_INTEG_LOAD T
using (select  IL.UUID UUID,        IL.REVISION IL_REVISION,        IB.REVISION IB_REVISION
           from sem_repository.MTA_INTEG_LOAD IL
           inner join sem_repository.MTA_DATA_LOCATION DL on (IL.O_DATALOCATION = DL.UUID)
           left join sem_repository.MTA_INTEG_BATCH IB on IB.R_INTEGLOAD = IL.UUID
           where IL.CLASSNAME <> 'ContinuousLoad' and DL. NAME in ('$$DL_NAMES$$')  
           and (IL.LAST_DL_BATCH_SYNCED_REVISION is null  or IL.LAST_DL_BATCH_SYNCED_REVISION < IL.REVISION  or (IB.REVISION is not null and IL.LAST_DL_BATCH_SYNCED_REVISION < IB.REVISION) ) ) S 
on (T.UUID = S.UUID)
when matched then update set T.LAST_DL_BATCH_SYNCED_REVISION = GREATEST(COALESCE( IL_REVISION, 0), COALESCE(IB_REVISION, 0)); 
commit

Replace $$DL_NAMES$$ with your Data location name.

Login to post a comment
More topics in xDM - Platform Configuration & Administration
See all 62 topics
Help Desk Software by Freshdesk