Michel Elias created NIFI-7252:
----------------------------------
Summary: 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
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.3.4#803005)
