View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0006619 | SymmetricDS Pro | Bug | public | 2024-10-11 20:50 | 2024-12-04 19:01 |
| Reporter | pmarzullo | Assigned To | |||
| Priority | normal | ||||
| Status | new | Resolution | open | ||
| Product Version | 3.15.0 | ||||
| Summary | 0006619: Initial load of table with self-referencing foreign keys does not get all rows | ||||
| Description | Initial load of table with self-referencing foreign keys does not get all rows. I tested with MariaDB version 10.6. Example table DDL attached (table-definitions-cabin.xml). Table data attached (cabin-no-null.csv, 11189 rows). Make sure to execute SET FOREIGN_KEY_CHECKS=0; before importing data. Then set it back (SET FOREIGN_KEY_CHECKS=1;) The current logic with this table definition and set of data executes 4 queries to attempt to get all of the data. Queries are below. | ||||
| Additional Information | The number of rows returned from all 4 queries was 9103 rows, instead of 11189 rows. Here are the 4 queries that were executed to retrieve the data: select `cabin_id` as x__0,`linked_cabin_id` as x__1,`work_location_id` as x__2,`cabin_type_id` as x__3,`cabin_number` as x__4,`is_crew_cabin` as x__5,`is_IRGS` as x__6,`cabin_window_type_id` as x__7,`cabin_deck_id` as x__8,`cabin_location_type_id` as x__9,`cabin_stairwell_id` as x__10,`cabin_bed_type_id` as x__11,`cabin_firezone_id` as x__12,`cabin_bathroom_type_id` as x__13,`capacity` as x__14,`status` as x__15,`created_dt` as x__16,`updated_dt` as x__17,`by_user` as x__18,`updated_by_user` as x__19,`change_control_seq` as x__20,`cabin_note` as x__21 from `cabin` t where (`linked_cabin_id` is null or `linked_cabin_id` = `cabin_id`); select `cabin_id` as x__0,`linked_cabin_id` as x__1,`work_location_id` as x__2,`cabin_type_id` as x__3,`cabin_number` as x__4,`is_crew_cabin` as x__5,`is_IRGS` as x__6,`cabin_window_type_id` as x__7,`cabin_deck_id` as x__8,`cabin_location_type_id` as x__9,`cabin_stairwell_id` as x__10,`cabin_bed_type_id` as x__11,`cabin_firezone_id` as x__12,`cabin_bathroom_type_id` as x__13,`capacity` as x__14,`status` as x__15,`created_dt` as x__16,`updated_dt` as x__17,`by_user` as x__18,`updated_by_user` as x__19,`change_control_seq` as x__20,`cabin_note` as x__21 from `cabin` t where `linked_cabin_id` in (select `cabin_id` from `cabin` where `linked_cabin_id` is null or `cabin_id` = `linked_cabin_id` ) and `linked_cabin_id` != `cabin_id`; select `cabin_id` as x__0,`linked_cabin_id` as x__1,`work_location_id` as x__2,`cabin_type_id` as x__3,`cabin_number` as x__4,`is_crew_cabin` as x__5,`is_IRGS` as x__6,`cabin_window_type_id` as x__7,`cabin_deck_id` as x__8,`cabin_location_type_id` as x__9,`cabin_stairwell_id` as x__10,`cabin_bed_type_id` as x__11,`cabin_firezone_id` as x__12,`cabin_bathroom_type_id` as x__13,`capacity` as x__14,`status` as x__15,`created_dt` as x__16,`updated_dt` as x__17,`by_user` as x__18,`updated_by_user` as x__19,`change_control_seq` as x__20,`cabin_note` as x__21 from `cabin` t where `linked_cabin_id` in (select `cabin_id` from `cabin` where `linked_cabin_id` in (select `cabin_id` from `cabin` where `linked_cabin_id` is null or `cabin_id` = `linked_cabin_id` ) and `linked_cabin_id` != `cabin_id`); select `cabin_id` as x__0,`linked_cabin_id` as x__1,`work_location_id` as x__2,`cabin_type_id` as x__3,`cabin_number` as x__4,`is_crew_cabin` as x__5,`is_IRGS` as x__6,`cabin_window_type_id` as x__7,`cabin_deck_id` as x__8,`cabin_location_type_id` as x__9,`cabin_stairwell_id` as x__10,`cabin_bed_type_id` as x__11,`cabin_firezone_id` as x__12,`cabin_bathroom_type_id` as x__13,`capacity` as x__14,`status` as x__15,`created_dt` as x__16,`updated_dt` as x__17,`by_user` as x__18,`updated_by_user` as x__19,`change_control_seq` as x__20,`cabin_note` as x__21 from `cabin` t where `linked_cabin_id` in (select `cabin_id` from `cabin` where `linked_cabin_id` in (select `cabin_id` from `cabin` where `linked_cabin_id` in (select `cabin_id` from `cabin` where `linked_cabin_id` is null or `cabin_id` = `linked_cabin_id` ) and `linked_cabin_id` != `cabin_id`)); | ||||
| Tags | No tags attached. | ||||
|
|
table-definitions-cabin.xml (4,835 bytes)
<database xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="dbexport" encoding="UTF-8"> <table name="cabin"> <column name="cabin_id" primaryKey="true" primaryKeySeq="1" required="true" type="INTEGER" size="10" autoIncrement="true"> <platform-column name="mysql" type="INT" size="10"/> </column> <column name="linked_cabin_id" type="INTEGER" size="10" default="NULL"> <platform-column name="mysql" type="INT" size="10" default="NULL"/> </column> <column name="work_location_id" required="true" type="INTEGER" size="10"> <platform-column name="mysql" type="INT" size="10"/> </column> <column name="cabin_type_id" required="true" type="INTEGER" size="10"> <platform-column name="mysql" type="INT" size="10"/> </column> <column name="cabin_number" required="true" type="INTEGER" size="10"> <platform-column name="mysql" type="INT" size="10"/> </column> <column name="is_crew_cabin" required="true" type="TINYINT" size="3" default="1"> <platform-column name="mysql" type="TINYINT" size="3" default="1"/> </column> <column name="is_IRGS" type="TINYINT" size="3" default="0"> <platform-column name="mysql" type="TINYINT" size="3" default="0"/> </column> <column name="cabin_window_type_id" type="INTEGER" size="10" default="NULL"> <platform-column name="mysql" type="INT" size="10" default="NULL"/> </column> <column name="cabin_deck_id" type="INTEGER" size="10" default="NULL"> <platform-column name="mysql" type="INT" size="10" default="NULL"/> </column> <column name="cabin_location_type_id" type="INTEGER" size="10" default="NULL"> <platform-column name="mysql" type="INT" size="10" default="NULL"/> </column> <column name="cabin_stairwell_id" type="INTEGER" size="10" default="NULL"> <platform-column name="mysql" type="INT" size="10" default="NULL"/> </column> <column name="cabin_bed_type_id" type="INTEGER" size="10" default="NULL"> <platform-column name="mysql" type="INT" size="10" default="NULL"/> </column> <column name="cabin_firezone_id" type="INTEGER" size="10" default="NULL"> <platform-column name="mysql" type="INT" size="10" default="NULL"/> </column> <column name="cabin_bathroom_type_id" type="INTEGER" size="10" default="NULL"> <platform-column name="mysql" type="INT" size="10" default="NULL"/> </column> <column name="capacity" required="true" type="INTEGER" size="10"> <platform-column name="mysql" type="INT" size="10"/> </column> <column name="status" type="TINYINT" size="3" default="1"> <platform-column name="mysql" type="TINYINT" size="3" default="1"/> </column> <column name="created_dt" type="TIMESTAMP" size="0" default="current_timestamp()"> <platform-column name="mysql" type="DATETIME" default="current_timestamp()"/> </column> <column name="updated_dt" type="TIMESTAMP" size="0" default="current_timestamp()"> <platform-column name="mysql" type="DATETIME" default="current_timestamp()"/> </column> <column name="by_user" type="VARCHAR" size="25" default="SYSTEM"> <platform-column name="mysql" type="VARCHAR" size="25" default="SYSTEM"/> </column> <column name="updated_by_user" type="VARCHAR" size="25" default="NULL"> <platform-column name="mysql" type="VARCHAR" size="25" default="NULL"/> </column> <column name="change_control_seq" type="TIMESTAMP" size="0" default="current_timestamp()"> <platform-column name="mysql" type="TIMESTAMP" default="current_timestamp()"/> </column> <column name="cabin_note" type="VARCHAR" size="256" default="NULL"> <platform-column name="mysql" type="VARCHAR" size="256" default="NULL"/> </column> <foreign-key name="fk_cabin_cabin1" foreignTable="cabin" foreignTableCatalog="" foreignTableSchema=""> <reference local="linked_cabin_id" foreign="cabin_id"/> </foreign-key> <unique name="cabin_id_UNIQUE"> <unique-column name="cabin_id"/> </unique> <index name="cabin_work_location_fk1_idx"> <index-column name="work_location_id"/> </index> <index name="fk_cabin_cabin1_idx"> <index-column name="linked_cabin_id"/> </index> <index name="fk_cabin_cabin_bathroom_type1_idx"> <index-column name="cabin_bathroom_type_id"/> </index> <index name="fk_cabin_cabin_bed_type1_idx"> <index-column name="cabin_bed_type_id"/> </index> <index name="fk_cabin_cabin_deck1_idx"> <index-column name="cabin_deck_id"/> </index> <index name="fk_cabin_cabin_frame_location1_idx"> <index-column name="cabin_firezone_id"/> </index> <index name="fk_cabin_cabin_location_type1_idx"> <index-column name="cabin_location_type_id"/> </index> <index name="fk_cabin_cabin_stairwell1_idx"> <index-column name="cabin_stairwell_id"/> </index> <index name="fk_cabin_cabin_window_type1_idx"> <index-column name="cabin_window_type_id"/> </index> </table> </database> |
|
|
Here is a cabin that is not coming back: select * from cabin where cabin_id in (11,9); Cabin 11 links to 9 and cabin 9 links back to 11. The only way that this can successfully occur is if each row is inserted with a null linked_cabin_id, then the linked_cabin_id value is updated to point to the corresponding cabin_id value. With the way that SymmetricDS only inserts records for a load, there is no way to successfully load this table without dropping the foreign key constraints, loading all rows, then adding the foreign key constraints back. |
|
|
If constraints have been deferred, then SymmetricDS shouldn't run the extra queries to handle self-referencing foreign keys. |