You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@nifi.apache.org by "Matt Burgess (Jira)" <ji...@apache.org> on 2020/04/03 16:50:00 UTC
[jira] [Assigned] (NIFI-7240) wrong table mapping when using the
nifi CaptureChangeMySQL modules combinded with mysql triggers:
[ https://issues.apache.org/jira/browse/NIFI-7240?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Matt Burgess reassigned NIFI-7240:
----------------------------------
Assignee: Matt Burgess
> 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
> Assignee: Matt Burgess
> Priority: Major
> Time Spent: 10m
> Remaining Estimate: 0h
>
> 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)