View Issue Details

Related ChangesetsJump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0003847SymmetricDSNew Featurepublic2019-01-04 13:412019-03-14 12:48
Reporterelong Assigned Toelong  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.10.0 
Target Version3.10.0Fixed in Version3.10.0 
Summary0003847: Enhance fallback to handle duplicate key and foreign key errors
DescriptionHandle additional conflicts by removing blocking rows when a unique index or foreign key constraint is violated. When a unique constraint is violated by an insert or update that causes a resulting duplicate value, the blocking row should be removed. When an update or delete causes a missing child violation by foreign key, then the blocking row should be removed. When removing blocking rows, if additional foreign key violations occur, those blocking rows should also be removed. The original operation can then be attempted again. For Postgres, since it rolls back the transaction on any violation, let the batch retry instead.
Steps To Reproducecreate table mytest (id integer primary key, pid integer references mytest(id), name varchar(50));
create unique index i_mytest on mytest(name);

-- insert gets unique violation - delete by constraint - delete gets dependent FK violation - delete by FK - insert
insert into mytest values (1, null, 'one'); -- dst
insert into mytest values (2, 1, 'two'); -- dst
insert into mytest values (3, null, 'one'); -- src (sync)

-- insert gets PK violation - update - update gets unique violation - delete by constraint - delete gets FK violation - delete by FK - update
insert into mytest values (1, null, 'one'); -- dst
insert into mytest values (2, null, 'two'); -- dst
insert into mytest values (3, 2, 'three'); -- dst
insert into mytest values (1, null, 'two'); -- src (sync)

-- update gets unique violation - delete by constraint - delete gets dependent FK violation - delete by FK - update
insert into mytest values (1, null, 'one'); -- src (sync)
insert into mytest values (2, null, 'two'); -- dst
insert into mytest values (3, 2, 'three'); -- dst
insert into mytest values (4, 3, 'four'); -- dst
update mytest set id = 2, name = 'two' where id = 1; -- src (sync)

-- update gets FK dependent violation - delete by FK - delete gets dependent FK violation - delete by FK - update
insert into mytest values (1, null, 'one'); -- src (sync)
insert into mytest values (2, 1, 'two'); -- dst
insert into mytest values (4, 2, 'four'); -- dst
update mytest set id = 3 where id = 1; -- src (sync)

-- update gets 0 rows affected - insert - fails unique violation - delete by constraint - delete gets FK violation - delete by FK - update
insert into mytest values (1, null, 'one'); -- src (sync)
delete from mytest where id = 1; -- dst
insert into mytest values (2, null, 'won'); -- dst
insert into mytest values (3, 2, 'three'); -- dst
update mytest set name = 'won' where id = 1; -- src (sync)

-- delete gets fk constraint violation - delete by FK - delete
insert into mytest values (1, null, 'one'); -- src (sync)
insert into mytest values (2, 1, 'two'); -- dst
insert into mytest values (3, 2, 'three'); -- dst
delete from mytest where id = 1; -- src (sync)
TagsNo tags attached.

Activities

There are no notes attached to this issue.

Related Changesets

SymmetricDS: 3.10 cba2df0b

2019-01-04 08:49:17

admin

Details Diff 		0003847: Enhance fallback to handle duplicate key and foreign key errors Affected Issues
0003847
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/DataService.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/DatabaseMetaDataWrapper.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/IDdlReader.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/cassandra/CassandraDdlReader.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/kafka/KafkaDdlReader.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/sqlite/SqliteDdlReader.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/sql/AbstractJavaDriverSqlTemplate.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/sql/AbstractSqlTemplate.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/sql/ISqlTemplate.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/sql/Row.java Diff File
add - symmetric-db/src/main/java/org/jumpmind/db/util/TableRow.java Diff File
mod - symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/AbstractDatabaseWriterConflictResolver.java Diff File
mod - symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/DefaultDatabaseWriter.java Diff File
mod - symmetric-io/src/main/java/org/jumpmind/symmetric/io/data/writer/DefaultDatabaseWriterConflictResolver.java Diff File
mod - symmetric-io/src/test/java/org/jumpmind/symmetric/io/AbstractWriterTest.java Diff File
add - symmetric-io/src/test/java/org/jumpmind/symmetric/io/data/writer/DatabaseWriterConflictTest.java Diff File
mod - symmetric-io/src/test/resources/testDatabaseWriter.xml Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/AbstractJdbcDdlReader.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/db2/Db2JdbcSqlTemplate.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/derby/DerbyJdbcSqlTemplate.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/firebird/FirebirdJdbcSqlTemplate.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/h2/H2JdbcSqlTemplate.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/hsqldb2/HsqlDb2JdbcSqlTemplate.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/informix/InformixJdbcSqlTemplate.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/mssql/MsSqlJdbcSqlTemplate.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/mysql/MySqlJdbcSqlTemplate.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/nuodb/NuoDbJdbcSqlTemplate.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/oracle/OracleJdbcSqlTemplate.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlJdbcSqlTemplate.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/sybase/SybaseJdbcSqlTemplate.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/tibero/TiberoJdbcSqlTemplate.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/sql/JdbcSqlTemplate.java Diff File

SymmetricDS: 3.10 8f69432f

2019-01-08 08:18:55

admin

Details Diff 		0003847: Enhance fallback to handle duplicate key and foreign key errors Affected Issues
0003847
mod - symmetric-io/src/test/java/org/jumpmind/symmetric/io/data/writer/DatabaseWriterConflictTest.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/AbstractJdbcDdlReader.java Diff File

Issue History

Date Modified Username Field Change
2019-01-04 13:41 elong New Issue
2019-01-04 13:41 elong Status new => assigned
2019-01-04 13:41 elong Assigned To => elong
2019-01-04 13:55 elong Status assigned => resolved
2019-01-04 13:55 elong Resolution open => fixed
2019-01-04 13:55 elong Fixed in Version => 3.10.0
2019-01-04 14:00 admin Changeset attached => SymmetricDS 3.10 cba2df0b
2019-01-08 14:00 admin Changeset attached => SymmetricDS 3.10 8f69432f
2019-03-14 12:48 admin Status resolved => closed