View Issue Details

IDProjectCategoryView StatusLast Update
0001475SymmetricDSBugpublic2013-12-18 10:55
Reporterraja_s_patilAssigned Tochenson 
Priorityurgent 
Status closedResolutionfixed 
Product Version3.5.11 
Target Version3.5.12Fixed in Version3.5.12 
Summary0001475: Replication From Branch to Central Broken (Firebird at Both Ends)
DescriptionCurrently using 3.4.x works Fine for more than a year. Config is Tested and proven. For File Replication needs decided to test 3.5.10/11 applied same config on snapshot copy of live database. Rebuild went fine. Replication from Central to Branch also works Fine. Problem is for replication from Branch to Central where transform Column is used.

Tested 3.5.9, 3.5.10, 3.5.11 same result.

After reading https://sourceforge.net/p/symmetricds/discussion/739236/thread/3a698f13/#792f/2bcd. tested with 3.5.3 which works fine. Hence changes made for that issue might have broken replication.


 

config is

/* RATE_MASTER ***********************************************************/
insert into SYM_TRIGGER
    (TRIGGER_ID, SOURCE_TABLE_NAME, CHANNEL_ID,use_capture_old_data,
     CREATE_TIME, LAST_UPDATE_TIME)
values
    ('Rate_Master','RATE_MASTER','Branch_Masters',0,
     current_timestamp,current_timestamp);
insert into SYM_TRIGGER_ROUTER
    (trigger_id, router_id, initial_load_order, create_time, last_update_time)
values
    ('Rate_Master', 'Branch_2_Central', -1, current_timestamp, current_timestamp);
insert into SYM_TRANSFORM_TABLE
    (TRANSFORM_ID, SOURCE_NODE_GROUP_ID, TARGET_NODE_GROUP_ID, TRANSFORM_POINT, COLUMN_POLICY,
    SOURCE_TABLE_NAME, TARGET_TABLE_NAME, DELETE_ACTION, TRANSFORM_ORDER)
Values
    ('Rate-Master', 'Branch', 'CENTRAL', 'LOAD','IMPLIED',
    'RATE_MASTER', 'RATE_MASTER', 'DEL_ROW', 1);
insert into SYM_TRANSFORM_COLUMN
    (TRANSFORM_ID, INCLUDE_ON, TARGET_COLUMN_NAME, SOURCE_COLUMN_NAME,
    PK, TRANSFORM_TYPE, TRANSFORM_EXPRESSION, TRANSFORM_ORDER)
values
    ('Rate-Master', '*', 'STATION', null,
    1, 'variable', 'source_node_id', 1);

/* RATE_MASTER Rebuild */
insert into sym_trigger
    (TRIGGER_ID, SOURCE_TABLE_NAME, CHANNEL_ID,use_capture_old_data,
    SYNC_ON_INSERT, SYNC_ON_UPDATE, SYNC_ON_DELETE, SYNC_ON_INCOMING_BATCH,
    SYNC_ON_INSERT_CONDITION, SYNC_ON_UPDATE_CONDITION, SYNC_ON_DELETE_CONDITION,
    EXCLUDED_COLUMN_NAMES,
    CREATE_TIME, LAST_UPDATE_TIME)
values
    ('Rate_Master_Rebuild', 'RATE_MASTER', 'Central_Masters',0,
    0, 0, 0, 0,
    null, null, null,
    'STATION',
    current_timestamp, current_timestamp);
insert into SYM_TRIGGER_ROUTER
    (trigger_id, router_id, initial_load_order, create_time, last_update_time,
    initial_load_select)
values
    ('Rate_Master_Rebuild', 'Central_2_Branch', 4041, current_timestamp, current_timestamp,
    't.STATION=''$(externalId)''');


Exception raised is

! 21:10:01 ~ 868 ~ ERROR ~ ATC-CMD ~ Failed to load batch KPR-10 because: Detected conflict while executing UPDATE on RATE_MASTER. The primary key data was: {STATION=KPR, PARTY_CD=AADHAR-KPR , APP_DATE=2008-07-01, SR_NO=6}. ~ SymDS ~ org.jumpmind.symmetric.service.impl.DataLoaderService ~ 2013-11-24 !
org.jumpmind.symmetric.io.data.writer.ConflictException: Detected conflict while executing UPDATE on RATE_MASTER. The primary key data was: {STATION=KPR, PARTY_CD=AADHAR-KPR , APP_DATE=2008-07-01, SR_NO=6}.
at org.jumpmind.symmetric.io.data.writer.DatabaseWriter.write(DatabaseWriter.java:210)
at org.jumpmind.symmetric.io.data.writer.DefaultTransformWriterConflictResolver.performFallbackToUpdate(DefaultTransformWriterConflictResolver.java:84)
at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriterConflictResolver.needsResolved(DefaultDatabaseWriterConflictResolver.java:106)
at org.jumpmind.symmetric.io.data.writer.DatabaseWriter.write(DatabaseWriter.java:208)
at org.jumpmind.symmetric.io.data.writer.DatabaseWriter.write(DatabaseWriter.java:165)
at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:65)
at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:198)
at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:194)
at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:164)
at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:114)
at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener.end(DataLoaderService.java:769)
at org.jumpmind.symmetric.io.data.writer.StagingDataWriter.notifyEndBatch(StagingDataWriter.java:75)
at org.jumpmind.symmetric.io.data.writer.AbstractProtocolDataWriter.end(AbstractProtocolDataWriter.java:219)
at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:124)
at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromTransport(DataLoaderService.java:394)
at org.jumpmind.symmetric.service.impl.DataLoaderService.loadDataFromPush(DataLoaderService.java:325)
at org.jumpmind.symmetric.web.PushUriHandler.push(PushUriHandler.java:79)
at org.jumpmind.symmetric.web.PushUriHandler.handle(PushUriHandler.java:66)
at org.jumpmind.symmetric.web.SymmetricServlet.service(SymmetricServlet.java:98)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:929)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1002)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:585)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1146)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:701)
! 21:10:16 ~ 863 ~ WARN ~ ATC-CMD ~ Retrying batch KPR-10 ~ SymDS ~ org.jumpmind.symmetric.service.impl.IncomingBatchService ~ 2013-11-24 !
TagsNo tags attached.

Activities

elong

2013-11-29 07:49

developer   ~0000398

Is there a unique constraint on RATE_MASTER that is preventing the row from being inserted or updated?

raja_s_patil

2013-12-07 14:31

reporter   ~0000402

! 21:10:01 ~ 868 ~ ERROR ~ ATC-CMD ~ Failed to load batch KPR-10 because: Detected conflict while executing UPDATE on RATE_MASTER. The primary key data was: {STATION=KPR, PARTY_CD=AADHAR-KPR , APP_DATE=2008-07-01, SR_NO=6}. ~ SymDS ~ org.jumpmind.symmetric.service.impl.DataLoaderService ~ 2013-11-24 !

Please refer above line from log included in issue. The operation is UPDATE and PK is STATION, PARTY_CD, APP_DATE, SR_NO. The record exists both at HO and Branch. At Branch record has been modified and is suppose to replicate to HO. During this process exception has been raised at HO.

At Branch RATE_MASTER ddl is
CREATE TABLE RATE_MASTER (
    PARTY_CD CODE_BIG NOT NULL /* CODE_BIG = CHAR(15) */,
    APP_DATE GEN_DATE NOT NULL /* GEN_DATE = DATE */,
    SR_NO GEN_INTEGER NOT NULL /* GEN_INTEGER = INTEGER */,
    DOC_NON_DOC DOC_NONDOC /* DOC_NONDOC = CHAR(1) Default 'D' NOT NULL Check (VALUE IN ('D','N')) */,
    UPTO_WT WEIGHT /* WEIGHT = NUMERIC(18,3) */,
    UPTO_WT_RATE CURRENCY /* CURRENCY = NUMERIC(18,2) */,
    ADD_WT WEIGHT /* WEIGHT = NUMERIC(18,3) */,
    ADD_WT_RATE CURRENCY /* CURRENCY = NUMERIC(18,2) */,
    REMARKS DESC_GENERAL /* DESC_GENERAL = VARCHAR(100) */
);
ALTER TABLE RATE_MASTER ADD CONSTRAINT PK_RATE_MASTER PRIMARY KEY (PARTY_CD, APP_DATE, SR_NO);

ALTER TABLE RATE_MASTER ADD CONSTRAINT FK_RATEMAST_PARTY FOREIGN KEY (PARTY_CD) REFERENCES PARTY_MASTER (PARTY_CD) ON DELETE NO ACTION ON UPDATE NO ACTION;


AT HO RATE_MASTER DDL is

CREATE TABLE RATE_MASTER (
    STATION CODE_SMALL NOT NULL /* CODE_SMALL = CHAR(6) */,
    PARTY_CD CODE_BIG NOT NULL /* CODE_BIG = CHAR(15) */,
    APP_DATE GEN_DATE NOT NULL /* GEN_DATE = DATE */,
    SR_NO GEN_INTEGER NOT NULL /* GEN_INTEGER = INTEGER */,
    DOC_NON_DOC DOC_NONDOC /* DOC_NONDOC = CHAR(1) Default 'D' NOT NULL Check (VALUE IN ('D','N')) */,
    UPTO_WT WEIGHT /* WEIGHT = NUMERIC(18,3) */,
    UPTO_WT_RATE CURRENCY /* CURRENCY = NUMERIC(18,2) */,
    ADD_WT WEIGHT /* WEIGHT = NUMERIC(18,3) */,
    ADD_WT_RATE CURRENCY /* CURRENCY = NUMERIC(18,2) */,
    REMARKS DESC_GENERAL /* DESC_GENERAL = VARCHAR(100) */
);
ALTER TABLE RATE_MASTER ADD CONSTRAINT PK_RATE_MASTER PRIMARY KEY (STATION, PARTY_CD, APP_DATE, SR_NO);

ALTER TABLE RATE_MASTER ADD CONSTRAINT FK_RATEMAST_PARTY FOREIGN KEY (PARTY_CD) REFERENCES PARTY_MASTER (PARTY_CD) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE RATE_MASTER ADD CONSTRAINT FK_RATE_STATION FOREIGN KEY (STATION) REFERENCES STATIONS (STATION) ON DELETE NO ACTION ON UPDATE NO ACTION;


STATION field is Transform Column.

insert into SYM_TRANSFORM_TABLE
    (TRANSFORM_ID, SOURCE_NODE_GROUP_ID, TARGET_NODE_GROUP_ID, TRANSFORM_POINT, COLUMN_POLICY,
    SOURCE_TABLE_NAME, TARGET_TABLE_NAME, DELETE_ACTION, TRANSFORM_ORDER)
Values
    ('Rate-Master', 'Branch', 'CENTRAL', 'LOAD','IMPLIED',
    'RATE_MASTER', 'RATE_MASTER', 'DEL_ROW', 1);
insert into SYM_TRANSFORM_COLUMN
    (TRANSFORM_ID, INCLUDE_ON, TARGET_COLUMN_NAME, SOURCE_COLUMN_NAME,
    PK, TRANSFORM_TYPE, TRANSFORM_EXPRESSION, TRANSFORM_ORDER)
values
    ('Rate-Master', '*', 'STATION', null,
    1, 'variable', 'source_node_id', 1);


Thanks and warm regards.

chenson

2013-12-09 10:42

administrator   ~0000404

There was a bug with transformations. Can you try this version to see if it fixes your issue?

http://snapshots.repository.codehaus.org/org/jumpmind/symmetric/symmetric-assemble/3.5.12-SNAPSHOT/symmetric-assemble-3.5.12-20131209.051002-7-server.zip

raja_s_patil

2013-12-11 15:03

reporter   ~0000405

Thanks Chris,

I tested the snapshot on my laptop and Issue seems to be disappeared.
Tomorrow I will Test it with Copy of Live Data on Test Server in Office and
Confirm the same. File Replication is also working nicely as expected.
It seems that File Sync Config need to be Modified to suit the requirement.
I hope I can confirm by tomorrow with Insert/update/Delete tests through
application.

Thanks for quick feedback with bug fixing and warm Regards.

Raja Patil.

raja_s_patil

2013-12-13 12:41

reporter   ~0000406

Thanks Chris,

Sorry for a bit delayed response but due to some issues we could not test on promised time but today we did it and happy to inform that issue is resolved.

You may proceed to mark this as resolved.

However in File Synchronization another issue surfaced for which I will open another issue.

Thanks and warm Regards

Raja Patil.

Issue History

Date Modified Username Field Change
2013-11-26 01:10 raja_s_patil New Issue
2013-11-29 07:49 elong Note Added: 0000398
2013-12-07 14:31 raja_s_patil Note Added: 0000402
2013-12-09 10:42 chenson Note Added: 0000404
2013-12-11 15:03 raja_s_patil Note Added: 0000405
2013-12-13 12:41 raja_s_patil Note Added: 0000406
2013-12-13 12:44 chenson Status new => resolved
2013-12-13 12:44 chenson Fixed in Version => 3.5.12
2013-12-13 12:44 chenson Resolution open => fixed
2013-12-13 12:44 chenson Assigned To => chenson
2013-12-13 12:44 chenson Target Version => 3.5.12
2013-12-18 10:55 chenson Status resolved => closed