[
https://issues.apache.org/jira/browse/NIFI-7252?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Matt Burgess resolved NIFI-7252.
--------------------------------
Resolution: Duplicate
> wrong table mapping when using the nifi CaptureChangeMySQL modules combinded
> with mysql triggers:
> -------------------------------------------------------------------------------------------------
>
> Key: NIFI-7252
> URL: https://issues.apache.org/jira/browse/NIFI-7252
> Project: Apache NiFi
> Issue Type: Bug
> Environment: Linux
> Reporter: Michel Elias
> Priority: Major
>
> wrong table mapping when using the nifi CaptureChangeMySQL modules combinded
> with mysql triggers:
> when using the nifi CaptureChangeMySQL module
> ([https://github.com/apache/nifi/blob/master/nifi-nar-bundles/nifi-cdc/nifi-cdc-mysql-bundle/nifi-cdc-mysql-processors/src/main/java/org/apache/nifi/cdc/mysql/processors/CaptureChangeMySQL.java])
> combinded with mysql triggers the table mapping does not work correctly.
> when using triggers the order of the events is slightly different than when
> using regular inserts.
> ======================================================================================================================================
> regular insert:
> BEGIN;
> PREPARE stmt1 FROM 'insert into changes set Header_RefID=?';
> PREPARE stmt2 FROM 'insert into tab_tracedata set ZE140_RT_Aufnahme=?';
> SET @a = 3;
> SET @b = 4;
> EXECUTE stmt1 USING @a;
> EXECUTE stmt2 USING @b;
> COMMIT;
> events in binlog:
> |mysql-bin.000029|349004|Gtid|2|349042|BEGIN GTID 0-2-6431|
> |mysql-bin.000029|349042|Table_map|2|349091|table_id: 19 (asic.changes)|
> |mysql-bin.000029|349091|Write_rows_v1|2|349127|table_id: 19 flags:
> STMT_END_F|
> |mysql-bin.000029|349127|Table_map|2|349911|table_id: 24 (asic.tab_tracedata)|
> |mysql-bin.000029|349911|Write_rows_v1|2|350041|table_id: 24 flags:
> STMT_END_F|
> |mysql-bin.000029|350041|Xid|2|350068|COMMIT /* xid=366 */|
> ======================================================================================================================================
> triggered insert:
> CREATE DEFINER=`root`@`%` TRIGGER `insertChanges` AFTER INSERT ON
> `tab_tracedata` FOR EACH ROW BEGIN
> INSERT INTO changes (changes.Header_RefID) SELECT NEW.Header_RefID;
> END
> events in eventlog:
> |mysql-bin.000029|343|Gtid|1|381|BEGIN GTID 0-1-6289|
> |mysql-bin.000029|381|Table_map|1|1165|table_id: 21 (asic.tab_tracedata)|
> |mysql-bin.000029|1165|Table_map|1|1214|table_id: 19 (asic.changes)|
> |mysql-bin.000029|1214|Write_rows_v1|1|3020|table_id: 21|
> |mysql-bin.000029|3020|Write_rows_v1|1|3071|table_id: 19 flags: STMT_END_F|
> |mysql-bin.000029|3071|Xid|1|3098|COMMIT /* xid=18 */|
>
> showcase:
> CREATE TABLE test (id int auto_increment, data varchar(255),PRIMARY KEY (id));
> CREATE TABLE triggered (id int);
> create trigger testtrigger AFTER insert ON test FOR EACH ROW INSERT INTO
> triggered (triggered.id) SELECT NEW.id;
> insert into test set data="lala";
> subscribe to eventlog via CaptureChangeMySQL module. There are 2 events
> generated. Both inserts are mapped to the "triggered" table. thats wrong. one
> should be mapped to the "test" table - the other one to the "triggered" table.
> {"type":"insert","timestamp":1580393721000,"binlog_filename":"mysql-bin.000029","binlog_position":5035809,"database":"nifi_test_case","table_name":"triggered","table_id":31,"columns":[\\{"id":1,"name":"id","column_type":4,"value":1}
> ,\{"id":2,"value":null}]}
> {"type":"insert","timestamp":1580393721000,"binlog_filename":"mysql-bin.000029","binlog_position":5035849,"database":"nifi_test_case","table_name":"triggered","table_id":31,"columns":[\\{"id":1,"name":"id","column_type":4,"value":1}
> ]}--=ixed 004F59E8C12584FF_=
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
