View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0002292||SymmetricDS||Bug||public||2015-05-07 12:18||2022-09-21 21:06|
|Target Version||3.12.0||Fixed in Version||3.12.0|
|Summary||0002292: Cannot replicate the table with foreign key to non-replicated table|
|Description||I 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 Reproduce||Create 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 Information||Main node runs on MS SQL 2005, child nodes run on SQLite.|
excerpt.log (10,082 bytes)
||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.|
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.
||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.|
|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|