Hi All,
I am currently building a pipeline that fetches all changes from 2 mysql DBs into a final mysql warehouse.
I am using changecapturemysql processor to identify inserts,updates and deletes and then sending them to putdatabaserecord processor. All inserts work good. My source has a PK (primary key) column. My destination has the same column + another column as PK . I am able to replicate inserts. But when it comes to updates and deletes, updates on non primary keys were getting captured, but if i update primary key column in source, it is unchanged in destination. same with deletes. a delete with where clause on PK, doesnot deleting the record in destination. As i read the documentation this is expected behaviour - "MPORTANT: If the Statement Type is UPDATE, then the incoming records must not alter the value(s) of the primary keys (or user-specified Update Keys). If such records are encountered, the UPDATE statement issued to the database may do nothing (if no existing records with the new primary key values are found), or could inadvertently corrupt the existing data (by changing records for which the new values of the primary keys exist)." as per nifi official documentation. Now my question is how can i acheive end-end replication into the destination DB table by using changepaturemysql processor ? is there any alternative that i can get the updates and deletes happen even though they are based on primary keys...? does putsql here helps here ?
Please guide me.
