View Revisions: Issue #3892

Back to Issue
Summary 0003892: MySQL Version 8 returning error code 1216 when foreign key constraint is violated (used to be 1452)
Revision 2019-03-22 17:15 by pmarzullo
Steps To Reproduce 1. Run Symmetric against MySQL version 8 (at least in the target).
2. Set up tables with foreign key constraints.
3. Set up synchronization.
4. Set up data to allow a foreign key constraint violation in the target database.
5. Create the data change to create the foreign key constraint violation in the target database.
6. SymmetricDS should not be able to resolve the foreign key constraint because the database error code returned by the database is 1216 instead of 1452.

TODO: Create DDL and insert here to make it easy to recreate the problem.
Revision 2019-03-22 18:08 by pmarzullo
Steps To Reproduce 1. Run Symmetric against MySQL version 8 (at least in the target).
2. Set up tables with foreign key constraints.
3. Set up synchronization.
4. Set up data to allow a foreign key constraint violation in the target database.
5. Create the data change to create the foreign key constraint violation in the target database.
6. SymmetricDS should not be able to resolve the foreign key constraint because the database error code returned by the database is 1216 instead of 1452.

DDL specified in additional information.
Revision 2019-03-22 17:15 by pmarzullo
Additional Information
Revision 2019-03-22 18:08 by pmarzullo
Additional Information create table parent (id varchar(1) not null, constraint primary key(id));
create table child (id varchar(1) not null, parent_id varchar(1) not null, constraint primary key(id), constraint foreign key (parent_id) references parent(id));
insert into parent values('1');
insert into child values('1','1');
insert into parent values('2');

insert into sym_trigger (trigger_id,source_catalog_name,source_schema_name,source_table_name,channel_id,reload_channel_id,sync_on_update,sync_on_insert,sync_on_delete,sync_on_incoming_batch,name_for_update_trigger,name_for_insert_trigger,name_for_delete_trigger,sync_on_update_condition,sync_on_insert_condition,sync_on_delete_condition,custom_before_update_text,custom_before_insert_text,custom_before_delete_text,custom_on_update_text,custom_on_insert_text,custom_on_delete_text,external_select,tx_id_expression,channel_expression,excluded_column_names,included_column_names,sync_key_names,use_stream_lobs,use_capture_lobs,use_capture_old_data,use_handle_key_updates,stream_row,create_time,last_update_by,last_update_time,description)
values('parent','','','parent','default','reload','1','1','1','0','','','','1=1','1=1','1=1','','','','','','','','','','','','','0','0','1','1','0',current_timestamp,'admin',current_timestamp,'');

insert into sym_trigger (trigger_id,source_catalog_name,source_schema_name,source_table_name,channel_id,reload_channel_id,sync_on_update,sync_on_insert,sync_on_delete,sync_on_incoming_batch,name_for_update_trigger,name_for_insert_trigger,name_for_delete_trigger,sync_on_update_condition,sync_on_insert_condition,sync_on_delete_condition,custom_before_update_text,custom_before_insert_text,custom_before_delete_text,custom_on_update_text,custom_on_insert_text,custom_on_delete_text,external_select,tx_id_expression,channel_expression,excluded_column_names,included_column_names,sync_key_names,use_stream_lobs,use_capture_lobs,use_capture_old_data,use_handle_key_updates,stream_row,create_time,last_update_by,last_update_time,description)
values('child','','','child','default','reload','1','1','1','0','','','','1=1','1=1','1=1','','','','','','','','','','','','','0','0','1','1','0',current_timestamp,'admin',current_timestamp,'');


insert into sym_trigger_router (trigger_id,router_id,enabled,initial_load_order,initial_load_select,initial_load_delete_stmt,ping_back_enabled,create_time,last_update_by,last_update_time,description)
values('child','truck to truck_secondary','1','50','','','0',current_timestamp,'admin',current_timestamp),'');

insert into sym_trigger_router (trigger_id,router_id,enabled,initial_load_order,initial_load_select,initial_load_delete_stmt,ping_back_enabled,create_time,last_update_by,last_update_time,description)
values('parent','truck to truck_secondary','1','50','','','0',current_timestamp,'admin',current_timestamp),'');

-- initial load from parent node to child node the two tables called parent and child
-- On the child node, in order to create the foreign key constraint issue
delete from parent where id='2';

-- On the parent node
insert into child values('2','2');