Michel Elias created NIFI-7240:
----------------------------------
Summary: wrong table mapping when using the nifi
CaptureChangeMySQL modules combinded with mysql triggers:
Key: NIFI-7240
URL: https://issues.apache.org/jira/browse/NIFI-7240
Project: Apache NiFi
Issue Type: Bug
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)
