View Issue Details

IDProjectCategoryView StatusLast Update
0002292SymmetricDSBugpublic2022-09-21 21:06
Reporterazarubkin Assigned To 
Prioritynormal 
Status closedResolutionfixed 
Product Version3.7.14 
Target Version3.12.0Fixed in Version3.12.0 
Summary0002292: Cannot replicate the table with foreign key to non-replicated table
DescriptionI have a replicated table which has a column to a non-replicated table. This column is excluded from replication.
During the initial schema creation, the version 3.6.16 works, the version 3.7.14 doesn't, because it still tries to create foreign key on target node, which version 3.6.16 doesn't do.

I'm attaching a part of log file which shows the error.

Steps To ReproduceCreate a table with a foreign key to another table, which is excluded from replication. My table is called 'cards', the column is called 'location' (it is foreign key to 'locations' table, which is not replicated):

INSERT [symmetricds].[sym_trigger] ([trigger_id], [source_catalog_name], [source_schema_name], [source_table_name], [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_on_update_text], [custom_on_insert_text], [custom_on_delete_text], [external_select], [tx_id_expression], [excluded_column_names], [sync_key_names], [use_stream_lobs], [use_capture_lobs], [use_capture_old_data], [use_handle_key_updates], [create_time], [last_update_by], [last_update_time], [reload_channel_id], [channel_expression])
VALUES (N'cards', NULL, N'dbo', N'cards', N'access_control', 1, 1, 1, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'location', NULL, 0, 0, 1, 0, CAST(0x0000A2AF010D2DF1 AS DateTime), NULL, CAST(0x0000A2B200BD83A0 AS DateTime), N'reload', NULL)

Now try to send schema to a new child node.
Additional InformationMain node runs on MS SQL 2005, child nodes run on SQLite.
Tagsddl/schema

Activities

azarubkin

2015-05-07 12:18

reporter  

excerpt.log (10,082 bytes)

azarubkin

2016-02-09 11:33

reporter   ~0000761

It's possible now to set create.table.without.foreign.keys=true, but if foreign keys are still wanted, SymmetricDS should omit only those foreign keys, which are impossible to create due to missing columns.

azarubkin

2016-02-11 11:59

reporter   ~0000762

I've checked that SymmetricDS 3.6 sends table definition with foreign key on the missing column, but it is correctly ignored on the receiving side.

SymmetricDS 3.7 doesn't ignore foreign key definition if the column is missing, and throws the error. It's likely a bug and should be fixed.

maxwellpettit

2017-04-20 17:19

manager   ~0001022

In version 3.6.16, SQLite foreign keys were not supported at all. In 3.7 support for foreign keys was added in addition to the create.table.without.foreign.keys parameter. Using this parameter will result in the same functionality as 3.6.16. As of now, this would be a new feature and is not currently a high priority.

Issue History

Date Modified Username Field Change
2015-05-07 12:18 azarubkin New Issue
2015-05-07 12:18 azarubkin File Added: excerpt.log
2016-02-09 11:33 azarubkin Note Added: 0000761
2016-02-11 11:59 azarubkin Note Added: 0000762
2017-04-05 01:09 chenson Assigned To => maxwellpettit
2017-04-05 01:09 chenson Status new => assigned
2017-04-20 17:19 maxwellpettit Note Added: 0001022
2017-04-20 17:19 maxwellpettit Status assigned => new
2017-04-20 17:19 maxwellpettit Assigned To maxwellpettit =>
2019-04-23 17:12 elong Tag Attached: ddl/schema
2022-09-09 21:11 elong Status new => resolved
2022-09-09 21:11 elong Resolution open => fixed
2022-09-21 21:06 elong Status resolved => closed