View Issue Details

IDProjectCategoryView StatusLast Update
0003892SymmetricDSImprovementpublic2019-04-23 11:14
ReporterpmarzulloAssigned Topmarzullo 
Prioritynormal 
Status closedResolutionfixed 
Product Version3.9.18 
Target Version3.9.19Fixed in Version3.9.19 
Summary0003892: MySQL Version 8 returning error code 1216 when foreign key constraint is violated (used to be 1452)
DescriptionMySQL Version 8 returning error code 1216 when foreign key constraint is violated (used to be 1452)
Steps To Reproduce1. 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.
Additional Informationcreate 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');
TagsNo tags attached.

Activities

pmarzullo

2019-03-22 14:38

developer   ~0001399

Also fixed in 3.10.1

pmarzullo

2019-03-22 14:38

developer   ~0001400

Also fixed in 3.10.1

Related Changesets

SymmetricDS: 3.9 fcd3a4ce

2019-03-22 14:31:25

Philip Marzullo

Details Diff
0003892: MySQL Version 8 returning error code 1216 when foreign key
constraint is violated (used to be 1452)

0003892
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/mysql/MySqlJdbcSqlTemplate.java Diff File

SymmetricDS: 3.10 672b2f12

2019-03-22 14:36:02

Philip Marzullo

Details Diff
0003892: MySQL Version 8 returning error code 1216 when foreign key
constraint is violated (used to be 1452)

0003892
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/mysql/MySqlJdbcSqlTemplate.java Diff File

Issue History

Date Modified Username Field Change
2019-03-22 13:15 pmarzullo New Issue
2019-03-22 13:15 pmarzullo Status new => assigned
2019-03-22 13:15 pmarzullo Assigned To => pmarzullo
2019-03-22 14:08 pmarzullo Steps to Reproduce Updated View Revisions
2019-03-22 14:08 pmarzullo Additional Information Updated View Revisions
2019-03-22 14:38 pmarzullo Fixed in Version => 3.9.19
2019-03-22 14:38 pmarzullo Note Added: 0001399
2019-03-22 14:38 pmarzullo Status assigned => resolved
2019-03-22 14:38 pmarzullo Resolution open => fixed
2019-03-22 14:38 pmarzullo Note Added: 0001400
2019-03-22 15:00 Changeset attached => SymmetricDS 3.9 fcd3a4ce
2019-03-22 15:00 Changeset attached => SymmetricDS 3.10 672b2f12
2019-04-23 11:14 elong Status resolved => closed