View Issue Details

IDProjectCategoryView StatusLast Update
0004710SymmetricDSBugpublic2023-09-13 17:25
Reporterklippit Assigned Toelong  
Priorityhigh 
Status closedResolutionopen 
Product Version3.10.4 
Summary0004710: Wrong order or skipped delete statements
DescriptionSometimes 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 Reproducedeleting 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 Informationsym_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);

Tagsdata sync

Activities

elong

2020-12-23 18:23

developer   ~0001870

What is the database platform and version? For example, are you using MySQL 8?

klippit

2021-04-14 05:46

reporter   ~0001911

Source db Postgres 9.4
Target db Mssql 2012

pmarzullo

2021-04-14 18:33

developer   ~0001915

Have you set a conflict detection to anything other than USE_PRIMARY_KEY?

klippit

2021-04-16 09:38

reporter   ~0001918

conflict detection is running on default value

elong

2021-04-22 13:45

developer   ~0001926

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'

Issue History

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