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

Start a new topic

Answered

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

Subham Dixit

started a topic about 2 years ago

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

Subham Dixit said about 2 years ago

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 Comments

Subham Dixit

said about 2 years ago

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.

Toshish Chauhan

said 5 months ago

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.