Uploaded image for project: 'StreamSets Data Collector'
  1. StreamSets Data Collector
  2. SDC-10690

introduce conflict detection methods for JDBC producer

    Details

    • Type: Improvement
    • Status: Open
    • Priority: P3 (Limited Impact)
    • Resolution: Unresolved
    • Affects Version/s: 3.4.0
    • Fix Version/s: None
    • Component/s: jdbc
    • Testing Status:
      Required

      Description

      see https://ask.streamsets.com/question/3947/what-about-introducing-conflict-detection-methods-for-jdbc-producer/

      to be able to detect conflicts when using SDC for database replication where JDBC producer is used  it would be needed to check the outcome of an insert, update, delete at the destination database.

      Whenever we implement database replication with cdc-enabled sources and JDBC producer as destination at the moment we have the following situation regarding conflicts which might happen: - insert or uniqueness conflict: a row with the same PK already exists at target, SDC JDBC producer throws an error, this is fine, but - update and delete conflict: a row with the same PK does not exist at target, SDC JDBC producer just goes on without any warning, thus no chance of noticing this.

      I would recommend to add some additional and optional parameters to SDC JDBC producer, with the option to notice data out-of-sync scenarios. If an update from a cdc-enabled source does not find exactly one row at the target site when using the PK, we have a conflict. How to implement? In the source code, in class com/streamsets/pipeline/lib/jdbc/JdbcMultiRowRecordWriter.java resp. com/streamsets/pipeline/lib/jdbc/JdbcGenericRecordWriter.java you use "statement.executeUpdate();"

      This is actually a function, returning number of changed rows, if this is not 1 in a scenario as described above, I would wish to have an option to generate some events or errors.

      Thanks for your comments Mathias Zarick

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              Mathias Zarick Mathias Zarick
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: