View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0004710 | SymmetricDS | Bug | public | 2020-12-22 11:31 | 2023-09-13 17:25 |
Reporter | klippit | Assigned To | elong | ||
Priority | high | ||||
Status | closed | Resolution | open | ||
Product Version | 3.10.4 | ||||
Summary | 0004710: Wrong order or skipped delete statements | ||||
Description | Sometimes symmetric service does not execute the delete statments in correct order or skips one delete. In sym_data on source database the delete entries are in correct order and none of them is missing. We performe many other deletes in this database but only this one sometimes causes the problem. | ||||
Steps To Reproduce | deleting orderpositions with compositionpositions refering to them sometimes causes the problem we currently have 100 databases running with this configuration and the error occurs once or twice a day some instances never had the error and some instances have it twice a week | ||||
Additional Information | sym_incomming_error and sym_incoming_batch joined and json encoded { "0":"28391","batch_id":"28391","1":"6","node_id":"6","2":"saledata","channel_id":"saledata","3":"ER","status":"ER","4":1,"error_flag":1,"5":"FK","sql_state":"FK","6":-900,"sql_code":-900,"7":"Die DELETE-Anweisung steht in Konflikt mit der REFERENCE-Einschr\u00e4nkung 'fk_mmsordercomppostoordercomp'. Der Konflikt trat in der postest-Datenbank, Tabelle 'dbo.mms_ordercompositionposition', column 'composition_id' auf.","sql_message":"Die DELETE-Anweisung steht in Konflikt mit der REFERENCE-Einschr\u00e4nkung 'fk_mmsordercomppostoordercomp'. Der Konflikt trat in der postest-Datenbank, Tabelle 'dbo.mms_ordercompositionposition', column 'composition_id' auf.","8":"DB01","last_update_hostname":"DB01","9":"2020-12-22 10:24:58.7100000","last_update_time":"2020-12-22 10:06:58.5570000","10":"2020-12-22 10:00:58.4030000","create_time":"2020-12-22 10:06:58.5570000","11":"mms_order, fin_invoiceposition, fin_invoice, mms_ordercompositionposition, mms_ordercomposition","summary":"mms_order, fin_invoiceposition, fin_invoice, mms_ordercompositionposition, mms_ordercomposition","12":0,"ignore_count":0,"13":"13494","byte_count":"13494","14":0,"load_flag":0,"15":0,"extract_count":0,"16":0,"sent_count":0,"17":0,"load_count":0,"18":0,"reload_row_count":0,"19":0,"other_row_count":0,"20":0,"data_row_count":0,"21":0,"extract_row_count":0,"22":20,"load_row_count":20,"23":0,"data_insert_row_count":0,"24":0,"data_update_row_count":0,"25":0,"data_delete_row_count":0,"26":0,"extract_insert_row_count":0,"27":0,"extract_update_row_count":0,"28":0,"extract_delete_row_count":0,"29":0,"load_insert_row_count":0,"30":14,"load_update_row_count":14,"31":5,"load_delete_row_count":5,"32":0,"network_millis":0,"33":0,"filter_millis":0,"34":16,"load_millis":16,"35":0,"router_millis":0,"36":0,"extract_millis":0,"37":0,"transform_extract_millis":0,"38":0,"transform_load_millis":0,"39":"-1","load_id":"-1","40":0,"common_flag":0,"41":0,"fallback_insert_count":0,"42":0,"fallback_update_count":0 ,"43":0,"ignore_row_count":0,"44":0,"missing_delete_count":0! ,"45":0,"skip_count":0,"46":20,"failed_row_number":"20","47":20,"failed_line_number":"20","48":"0","failed_data_id":"0","49":"28391","50":"6","51":"20","52":"20","53":null,"target_catalog_name":null,"54":null,"target_schema_name":null,"55":"mms_ordercomposition","target_table_name":"mms_ordercomposition","56":"D","event_type":"D","57":"BASE64","binary_encoding":"BASE64","58":"id,orderpos_id,name","column_names":"id,orderpos_id,name","59":"id","pk_column_names":"id","60":null,"row_data":null,"61":"\"1688849860300887\",\"1688849860300886\",\"discount\"","old_data":"\"1688849860300887\",\"1688849860300886\",\"discount\"","62":null,"cur_data":null,"63":null,"resolve_data":null,"64":0,"resolve_ignore":0,"65":null,"conflict_id":null,"66":"2020-12-22 10:06:58.5570000","67":"symmetricds","last_update_by":"symmetricds","68":"2020-12-22 10:06:58.5570000"} create statments for our tables create table mms_order ( id bigint NOT NULL, customer_id bigint NULL, shop_id bigint NOT NULL, employee_id bigint NOT NULL, number bigint NOT NULL, createtime ${datetimetype} NOT NULL, finishtime ${datetimetype} NULL, state int NOT NULL,--0 created, 1 processing,2 finished,3 canceled ship_state int NOT NULL, --0 undefined, 1 processing, 2 shiped pay_state int NOT NULL,-- 0 undefined, 1 processing, 2 payed orderindicator varchar(50) NULL, total_incl_tax decimal(15,6) NULL, total_excl_tax decimal(15,6) NULL, total_tax_amount decimal(15,6) NULL, flags bigint NULL, primary key(id) ); alter table mms_order add constraint fk_mmsordertocompshop foreign key(shop_id) references comp_shop(id); alter table mms_order add constraint fk_mmsordertoemployee foreign key(employee_id) references comp_employee(id); alter table mms_order add constraint fk_mmsordertocrmcustomer foreign key(customer_id) references crm_customer(id); create index idx_mms_order_fk1 ON mms_order ( customer_id ASC ); create index idx_mms_order_fk2 ON mms_order ( shop_id ASC ); create table mms_orderposition ( id bigint NOT NULL, order_id bigint NOT NULL, connected_order_id bigint NULL, articleidentity_id bigint NULL, article_id bigint NOT NULL, article_variantid bigint NULL, print_text varchar(256) NOT NULL, priceidentity_id bigint NULL, price_id bigint NOT NULL, unit_id bigint NOT NULL, quantity_ordered int NOT NULL, quantity_shipped int NULL, quantity_refunded int NULL, price_incl_tax decimal(15,4) NOT NULL, price_excl_tax decimal(15,4) NOT NULL, tax_amount decimal(15,4) NOT NULL, parentorderposition bigint NULL, belongs_to bigint NULL, flags bigint NULL, user_data varchar(127) NULL, complete ${booleantype}, refund_price_incl_tax decimal(15,4) NULL, refund_price_excl_tax decimal(15,4) NULL, refund_tax_amount decimal(15,4) NULL, employee_id bigint NOT NULL, orderintelligenceclass varchar(256) NULL, primary key(id) ); alter table mms_orderposition add constraint fk_mmsorderpositiontommsorder foreign key(order_id) references mms_order(id); alter table mms_orderposition add constraint fk_mmsorderpositiontommsconnectedorder foreign key(connected_order_id) references mms_order(id); alter table mms_orderposition add constraint fk_mmsorderpositiontommsarticleidentity foreign key(articleidentity_id) references mms_articleidentity(id); alter table mms_orderposition add constraint fk_mmsorderpositiontommsarticle foreign key(article_id) references mms_article(id); alter table mms_orderposition add constraint fk_mmsorderpositiontommsarticlevariant foreign key(article_variantid) references mms_articlevariant(id); alter table mms_orderposition add constraint fk_mmsorderpositiontommsarticpriceidentity foreign key(priceidentity_id) references mms_priceidentity(id); alter table mms_orderposition add constraint fk_mmsorderpositiontommsarticprice foreign key(price_id) references mms_price(id); alter table mms_orderposition add constraint fk_mmsorderpositiontounit foreign key(unit_id) references mms_unit(id); alter table mms_orderposition add constraint fk_mmsorderpositiontoparentorderposition foreign key(parentorderposition) references mms_orderposition(id); alter table mms_orderposition add constraint fk_mmsorderpositiontobelongingorderposition foreign key(belongs_to) references mms_orderposition(id); alter table mms_orderposition add constraint fk_mmsorderpositiontoemployee foreign key(employee_id) references comp_employee(id); create index idx_mms_orderposition_fk1 ON mms_orderposition ( order_id ASC ); create index idx_mms_orderposition_fk2 ON mms_orderposition ( articleidentity_id ASC ); create index idx_mms_orderposition_fk3 ON mms_orderposition ( article_id ASC ); create index idx_mms_orderposition_fk4 ON mms_orderposition ( article_variantid ASC ); create index idx_mms_orderposition_fk5 ON mms_orderposition ( priceidentity_id ASC ); create index idx_mms_orderposition_fk6 ON mms_orderposition ( price_id ASC ); create index idx_mms_orderposition_fk7 ON mms_orderposition ( unit_id ASC ); create index idx_mms_orderposition_fk8 ON mms_orderposition ( parentorderposition ASC ); create index idx_mms_orderposition_fk9 ON mms_orderposition ( belongs_to ASC ); create table mms_ordercomposition ( id bigint NOT NULL, orderpos_id bigint NOT NULL, name varchar(64) NULL, primary key(id) ); alter table mms_ordercomposition add constraint fk_mmsordercomptoorderpos foreign key(orderpos_id) references mms_orderposition(id); create table mms_ordercompositionposition ( id bigint NOT NULL, composition_id bigint NOT NULL, orderpos_id bigint NOT NULL, lock float NULL, amount decimal(15,4) NULL, --brutto amount1 decimal(15,4) NULL, --netto amount2 decimal(15,4) NULL, --steuern primary key(id) ); alter table mms_ordercompositionposition add constraint fk_mmsordercomppostoordercomp foreign key(composition_id) references mms_ordercomposition(id); alter table mms_ordercompositionposition add constraint fk_mmsordercomppostoorderpos foreign key(orderpos_id) references mms_orderposition(id); | ||||
Tags | data sync | ||||
|
What is the database platform and version? For example, are you using MySQL 8? |
|
Source db Postgres 9.4 Target db Mssql 2012 |
|
Have you set a conflict detection to anything other than USE_PRIMARY_KEY? |
|
conflict detection is running on default value |
|
Could you try setting the channel to nontransactional and see if the issue goes away? update sym_channel set batch_algorithm = 'nontransactional' where channel_id = 'default' |
Date Modified | Username | Field | Change |
---|---|---|---|
2020-12-22 11:31 | klippit | New Issue | |
2020-12-23 18:23 | elong | Assigned To | => elong |
2020-12-23 18:23 | elong | Status | new => feedback |
2020-12-23 18:23 | elong | Note Added: 0001870 | |
2021-04-14 05:46 | klippit | Note Added: 0001911 | |
2021-04-14 05:46 | klippit | Status | feedback => assigned |
2021-04-14 18:33 | pmarzullo | Note Added: 0001915 | |
2021-04-16 09:38 | klippit | Note Added: 0001918 | |
2021-04-22 13:45 | elong | Note Added: 0001926 | |
2022-08-02 20:13 | elong | Tag Attached: data sync | |
2023-05-16 12:56 | elong | Status | assigned => feedback |
2023-09-13 17:25 | emiller | Status | feedback => closed |