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 Oldest First
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.

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.  Some version of PostgreSQL(Merge is not supported till postgres 14 . As Merge is introduced in postgres 15) does not support the MERGE statement .The you can go for update query else you can use merge query if this is supported in your version. Three queries to Sync , you can opt any query.


I. Run the below query and replace ## LE NOM DE TON SCHEMA 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 
            ) 
        ) 
      ;

 II. 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));

III. For SQL SERVER you can use below query ( You can use this if other query show any error)You need to add your data location name in query  :-


WITH SourceData AS (

    SELECT

        il.uuid AS uuid,

        il.revision AS il_revision,

        MAX(ib.revision) AS ib_revision  -- Prevent duplicate rows by taking the highest 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 ('DATA LOCATION NAME')

        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)

        )

    GROUP BY il.uuid, il.revision

)

MERGE INTO mta_integ_load AS t

USING SourceData AS s

ON t.uuid = s.uuid

WHEN MATCHED THEN

    UPDATE SET

        t.last_dl_batch_synced_revision =

        CASE

            WHEN COALESCE(s.il_revision, 0) > COALESCE(s.ib_revision, 0) THEN COALESCE(s.il_revision, 0)

            ELSE COALESCE(s.ib_revision, 0)

        END;

 



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.
 
Login to post a comment
More topics in xDM - Platform Configuration & Administration
See all 67 topics
Help Desk Software by Freshdesk