Github user ijokarumawak commented on the issue:
https://github.com/apache/nifi/pull/1677
@mattyb149 Thanks for the updated commit. I confirmed that my comments are
incorporated.
It works as expected in most cases, however, I found a case which needs to
be addressed.
Let's say I have a table with a primary key like this:
```sql
create table tutorials_tbl(
tutorial_id INT NOT NULL,
tutorial_title VARCHAR(100) NOT NULL,
tutorial_author VARCHAR(40) NOT NULL,
submission_date DATE,
PRIMARY KEY ( tutorial_id )
);
```
And insert some rows, then update a row, especially update its primary key:
```sql
update tutorials_tbl set tutorial_id = 110 where tutorial_id = 11;
```
Above update query generates following JSON via CaptureChangeMySQL:
```json
{ "type" : "update",
"timestamp" : 1492648209000,
"binlog_filename" : "mysql-server-bin.000004",
"binlog_position" : 97152,
"database" : "nifi_test",
"table_name" : "tutorials_tbl",
"table_id" : 222,
"columns" : [ {
"id" : 1, "name" : "tutorial_id", "column_type" : 4,
"last_value" : 11, "value" : 110
}, {
"id" : 2, "name" : "tutorial_title", "column_type" : 12,
"last_value" : "11th", "value" : "11th"
}, {
"id" : 3, "name" : "tutorial_author", "column_type" : 12,
"last_value" : "koji", "value" : "koji"
}, {
"id" : 4, "name" : "submission_date", "column_type" : 91,
"last_value" : null, "value" : null
} ]}
```
`Transform to Flat JSON` (JoltTransform) flattens the event JSON as below.
At this point, the record image before update is dropped:
```json
[ { "tutorial_id" : 110, "tutorial_title" : "11th", "tutorial_author" :
"koji", "submission_date" : null } ]
```
Finally, PutDatabaseRecord generates an update sql statement with `where
tutorial_id = 110`. But it doesn't update anything, because it should have used
`where tutorial_id = 11` with before update row image.
We might be able to handle this by generating two delete and insert records
in a NiFi flow, or do something smart at PutDatabaseRecord.
How do you think?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at [email protected] or file a JIRA ticket
with INFRA.
---
