You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@nifi.apache.org by 杨量（上海-技术部-开发） <li...@feiniu.com> on 2020/10/07 12:29:16 UTC
Re: CaptureChangeMySQL processor capture maybe wrong when updating
more than one table in join statement
HI, Team
I think it should be a bug. Could you plan to fix it or could I register a bug for it?
The wrong issue can be reproduce with 1.9.*, 1.11.4 of NIFI version.
The mysql version is 5.7.20-log MySQL Community Server (GPL)
Wrong behavior cause by TABLE_MAP event just got the last one table is the currentTable.
It should be a map of tableinfo between BEGIN and COMMIT envent.
When update more table by join statement.
E.g. the sql statement is:
UPDATE a a inner join b b m a.id=b.id
left join c c on a.id=c.id
set a.name='y'
,b.name='x'
,c. name='z';
The binlog event like belows:
BEGIN
TABLE_MAP a
TABLE_MAP b
TABLE_MAP c
Update_rows a
Update_rows b
Update_rows c
COMMIT
For current codes: The java object currentTable capture the TABLE_MAP c event, so, All three update events are resolved to the last table c.
Can you look at this issue?
BR
Paul Yang
----- -----
[mailto:liang.yang@feiniu.com]
2020-9-30 9:56
dev@nifi.apache.org
CaptureChangeMySQL processor capture maybe wrong when updating more than one table in join statement
Hi, Team
I'm working on CaptureChangeMySQL processor with nifi version 1.11.4. I just capture update event of one table when updating more than one table in join statement.
The CaptureChangeMySQL behavior is wrong, maybe is a bug. The reason is the below code: cannot got the correctly table info when multi Table_map event happened.
The currentTable should be the latest Table_map , so the follow binlog parser is going to wrong table name, the table_name is wrong in json.
Is this a known limitation? Could you fix if it is a bug.
case TABLE_MAP:
// This is sent to inform which table is about to be changed by subsequent events
TableMapEventData data = event.getData();
......
if (!skipTable) {
TableInfoCacheKey key = new TableInfoCacheKey(this.getIdentifier(), data.getDatabase(), data.getTable(), data.getTableId());
if (cacheClient != null) {
try {
currentTable = cacheClient.get(key, cacheKeySerializer, cacheValueDeserializer);
The belows is my sql.
# UPDATE wdk_sub_orders_drf5089_9 s
# inner join wdk_main_order_drf5089 m on m.main_order_id=s.main_order_id
# left join wdk_sub_order_mc_drf5089 ms on ms.sub_order_id=s.sub_order_id
# left join wdk_order_promotion_info_drf5089 p on p.sub_order_id=s.sub_order_id
# set
# s.merchant_id='3'
# , s.merchant_store_id=1
# , s.store_sales_type=m.store_sales_type
# , s.store_channel_id=2
# , s.presell_type=m.order_type
# , ms.store_sales_type=m.store_sales_type
# , ms.order_type=3
# , p.store_sales_type=4
# , p.presell_type=m.order_type
# where
# s.id between 100382538 and 100382539 and s.source=1 and s.merchant_id='2'
Retrieve All Records: false
Include DDL Events: false
I got the binlog like belows:
#200930 8:46:19 server id 100 end_log_pos 29660 CRC32 0x1e9b0378 Table_map: `test`.`wdk_sub_orders_drf5089_9` mapped to number 219
# at 29660
#200930 8:46:19 server id 100 end_log_pos 29762 CRC32 0xfac7a7b0 Table_map: `test`.`wdk_sub_order_mc_drf5089` mapped to number 221
# at 29762
#200930 8:46:19 server id 100 end_log_pos 29882 CRC32 0x8b437c40 Table_map: `test`.`wdk_order_promotion_info_drf5089` mapped to number 222
# at 29882
#200930 8:46:19 server id 100 end_log_pos 30470 CRC32 0x3e0a2dab Update_rows: table id 219
# at 30470
#200930 8:46:19 server id 100 end_log_pos 30776 CRC32 0x4335186c Update_rows: table id 221
# at 30776
#200930 8:46:19 server id 100 end_log_pos 30959 CRC32 0x8153af67 Update_rows: table id 222 flags: STMT_END_F
### UPDATE `test`.`wdk_sub_orders_drf5089_9` ### WHERE
### @1=100382538 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @3=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @4=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @5=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @6=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @7=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @8='1' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @9=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @10=-1 (18446744073709551615) /* LONGINT meta=0 nullable=0 is_null=0 */
### @11=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @12=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @13=-1 (4294967295) /* INT meta=0 nullable=0 is_null=0 */
### @14='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @15='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @16='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @17='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @18='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @19=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @20='2020-09-29 18:08:30' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
### @21='2020-09-29 18:08:30' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
### @22='1' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @23=0 /* INT meta=0 nullable=0 is_null=0 */
### @24=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @25=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
### @26=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @27=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @28=0.00 /* DECIMAL(15,2) meta=3842 nullable=0 is_null=0 */
### @29=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @30='' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
### @31='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @32=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @33=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @34='' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
### @35=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @36=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @37=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @38='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @39='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @40='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @41=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @42=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @43=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @44=0.000 /* DECIMAL(11,3) meta=2819 nullable=0 is_null=0 */
### @45=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
### @46=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @47='' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
### @48=1 /* TINYINT meta=0 nullable=0 is_null=0 */
### @49='' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */
### @50=0.000 /* DECIMAL(11,3) meta=2819 nullable=0 is_null=0 */
### @51='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @52='' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
### @53=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @54='' /* VARSTRING(3000) meta=3000 nullable=0 is_null=0 */
### @55=1 /* TINYINT meta=0 nullable=0 is_null=0 */
### @56=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @57='' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */
### @58=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @59=1 /* TINYINT meta=0 nullable=0 is_null=0 */
### @60='2' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @61='1' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @62='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @63='1' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @64=1 /* TINYINT meta=0 nullable=0 is_null=0 */
### @65=1 /* TINYINT meta=0 nullable=0 is_null=0 */
### SET
### @1=100382538 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @3=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @4=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @5=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @6=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @7=1 /* LONGINT meta=0 nullable=0 is_null=0 */
### @8='1' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @9=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @10=-1 (18446744073709551615) /* LONGINT meta=0 nullable=0 is_null=0 */
### @11=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @12=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @13=-1 (4294967295) /* INT meta=0 nullable=0 is_null=0 */
### @14='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @15='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @16='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @17='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @18='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @19=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @20='2020-09-29 18:08:30' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
### @21='2020-09-29 18:08:30' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
### @22='1' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @23=0 /* INT meta=0 nullable=0 is_null=0 */
### @24=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @25=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
### @26=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @27=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @28=0.00 /* DECIMAL(15,2) meta=3842 nullable=0 is_null=0 */
### @29=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @30='' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
### @31='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @32=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @33=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @34='' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
### @35=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @36=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @37=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @38='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @39='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @40='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @41=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @42=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @43=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @44=0.000 /* DECIMAL(11,3) meta=2819 nullable=0 is_null=0 */
### @45=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
### @46=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @47='' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
### @48=1 /* TINYINT meta=0 nullable=0 is_null=0 */
### @49='' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */
### @50=0.000 /* DECIMAL(11,3) meta=2819 nullable=0 is_null=0 */
### @51='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @52='' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
### @53=0 /* LONGINT meta=0 nullable=0 is_null=0 */
### @54='' /* VARSTRING(3000) meta=3000 nullable=0 is_null=0 */
### @55=1 /* TINYINT meta=0 nullable=0 is_null=0 */
### @56=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @57='' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */
### @58=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @59=1 /* TINYINT meta=0 nullable=0 is_null=0 */
### @60='3' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @61='1' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @62='10' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @63='2' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
### @64=1 /* TINYINT meta=0 nullable=0 is_null=0 */
### @65=1 /* TINYINT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`wdk_sub_order_mc_drf5089` ### WHERE
### @1=30735 /* LONGINT meta=0 nullable=1 is_null=0 */
### @2='' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @3=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @4=1 /* LONGINT meta=0 nullable=1 is_null=0 */
### @5=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @6=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @7=0.000 /* DECIMAL(11,3) meta=2819 nullable=1 is_null=0 */
### @8=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @9=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @10=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @11=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @12=0 /* TINYINT meta=0 nullable=1 is_null=0 */
### @13=0.0000 /* DECIMAL(10,4) meta=2564 nullable=1 is_null=0 */
### @14=1601374464 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
### @15=1601374464 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
### @16='' /* VARSTRING(1536) meta=1536 nullable=1 is_null=0 */
### @17=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @18=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @19=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @20=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @21=1 /* TINYINT meta=0 nullable=1 is_null=0 */
### @22='' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
### SET
### @1=30735 /* LONGINT meta=0 nullable=1 is_null=0 */
### @2='' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### @3=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @4=1 /* LONGINT meta=0 nullable=1 is_null=0 */
### @5=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @6=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @7=0.000 /* DECIMAL(11,3) meta=2819 nullable=1 is_null=0 */
### @8=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @9=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @10=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @11=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @12=0 /* TINYINT meta=0 nullable=1 is_null=0 */
### @13=0.0000 /* DECIMAL(10,4) meta=2564 nullable=1 is_null=0 */
### @14=1601374464 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
### @15=1601426779 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
### @16='' /* VARSTRING(1536) meta=1536 nullable=1 is_null=0 */
### @17=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @18=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @19=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @20=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @21=3 /* TINYINT meta=0 nullable=1 is_null=0 */
### @22='10' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
### UPDATE `test`.`wdk_order_promotion_info_drf5089`
### WHERE
### @1=4675456 /* LONGINT meta=0 nullable=1 is_null=0 */
### @2='1' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
### @3=1 /* LONGINT meta=0 nullable=1 is_null=0 */
### @4=1 /* LONGINT meta=0 nullable=1 is_null=0 */
### @5=1 /* TINYINT meta=0 nullable=1 is_null=0 */
### @6=0 /* INT meta=0 nullable=1 is_null=0 */
### @7=0 /* INT meta=0 nullable=1 is_null=0 */
### @8=-1 (4294967295) /* INT meta=0 nullable=1 is_null=0 */
### @9='' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
### @10='' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
### @11='' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
### @12=0 /* INT meta=0 nullable=1 is_null=0 */
### @13='' /* VARSTRING(3072) meta=3072 nullable=1 is_null=0 */
### @14='2020-09-29 18:16:23' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### @15='2020-09-29 18:16:23' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### @16='1' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
### @17='' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
### @18='' /* VARSTRING(600) meta=600 nullable=1 is_null=0 */
### @19='' /* VARSTRING(3000) meta=3000 nullable=1 is_null=0 */
### @20=0 /* TINYINT meta=0 nullable=1 is_null=0 */
### @21=1 /* TINYINT meta=0 nullable=1 is_null=0 */
### @22='' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
### SET
### @1=4675456 /* LONGINT meta=0 nullable=1 is_null=0 */
### @2='1' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
### @3=1 /* LONGINT meta=0 nullable=1 is_null=0 */
### @4=1 /* LONGINT meta=0 nullable=1 is_null=0 */
### @5=1 /* TINYINT meta=0 nullable=1 is_null=0 */
### @6=0 /* INT meta=0 nullable=1 is_null=0 */
### @7=0 /* INT meta=0 nullable=1 is_null=0 */
### @8=-1 (4294967295) /* INT meta=0 nullable=1 is_null=0 */
### @9='' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
### @10='' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
### @11='' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
### @12=0 /* INT meta=0 nullable=1 is_null=0 */
### @13='' /* VARSTRING(3072) meta=3072 nullable=1 is_null=0 */
### @14='2020-09-29 18:16:23' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### @15='2020-09-29 18:16:23' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### @16='1' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
### @17='' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
### @18='' /* VARSTRING(600) meta=600 nullable=1 is_null=0 */
### @19='' /* VARSTRING(3000) meta=3000 nullable=1 is_null=0 */
### @20=0 /* TINYINT meta=0 nullable=1 is_null=0 */
### @21=1 /* TINYINT meta=0 nullable=1 is_null=0 */
### @22='4' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
# at 30959
#200930 8:46:19 server id 100 end_log_pos 30990 CRC32 0x496fe17f Xid = 28836
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
Thanks!
Paul Yang
Re: CaptureChangeMySQL processor capture maybe wrong when updating
more than one table in join statement
Posted by Matt Burgess <ma...@apache.org>.
Paul,
Although the description may not be the same, I think you might be
running into NIFI-7240 [1]. That Jira mentions triggers but I think
the same would occur any time you can have TABLE MAP events "out of
order". I have a PR up to solve this issue, hopefully a committer can
review and get it into the next release. In the meantime please feel
free to build the PR and see if it fixes the issue you're seeing.
Regards,
Matt
[1] https://issues.apache.org/jira/browse/NIFI-7240
[2] https://github.com/apache/nifi/pull/4179
On Wed, Oct 7, 2020 at 8:30 AM 杨量（上海-技术部-开发） <li...@feiniu.com> wrote:
>
> HI, Team
> I think it should be a bug. Could you plan to fix it or could I register a bug for it?
> The wrong issue can be reproduce with 1.9.*, 1.11.4 of NIFI version.
> The mysql version is 5.7.20-log MySQL Community Server (GPL)
> Wrong behavior cause by TABLE_MAP event just got the last one table is the currentTable.
> It should be a map of tableinfo between BEGIN and COMMIT envent.
> When update more table by join statement.
> E.g. the sql statement is:
> UPDATE a a inner join b b m a.id=b.id
> left join c c on a.id=c.id
> set a.name='y'
> ,b.name='x'
> ,c. name='z';
> The binlog event like belows:
> BEGIN
> TABLE_MAP a
> TABLE_MAP b
> TABLE_MAP c
> Update_rows a
> Update_rows b
> Update_rows c
> COMMIT
> For current codes: The java object currentTable capture the TABLE_MAP c event, so, All three update events are resolved to the last table c.
> Can you look at this issue?
>
> BR
> Paul Yang
>
>
> ----- -----
> [mailto:liang.yang@feiniu.com]
> 2020-9-30 9:56
> dev@nifi.apache.org
> CaptureChangeMySQL processor capture maybe wrong when updating more than one table in join statement
>
> Hi, Team
>
> I'm working on CaptureChangeMySQL processor with nifi version 1.11.4. I just capture update event of one table when updating more than one table in join statement.
> The CaptureChangeMySQL behavior is wrong, maybe is a bug. The reason is the below code: cannot got the correctly table info when multi Table_map event happened.
> The currentTable should be the latest Table_map , so the follow binlog parser is going to wrong table name, the table_name is wrong in json.
>
> Is this a known limitation? Could you fix if it is a bug.
>
> case TABLE_MAP:
> // This is sent to inform which table is about to be changed by subsequent events
> TableMapEventData data = event.getData();
>
> ......
>
> if (!skipTable) {
> TableInfoCacheKey key = new TableInfoCacheKey(this.getIdentifier(), data.getDatabase(), data.getTable(), data.getTableId());
> if (cacheClient != null) {
> try {
> currentTable = cacheClient.get(key, cacheKeySerializer, cacheValueDeserializer);
>
> The belows is my sql.
>
> # UPDATE wdk_sub_orders_drf5089_9 s
> # inner join wdk_main_order_drf5089 m on m.main_order_id=s.main_order_id
> # left join wdk_sub_order_mc_drf5089 ms on ms.sub_order_id=s.sub_order_id
> # left join wdk_order_promotion_info_drf5089 p on p.sub_order_id=s.sub_order_id
> # set
> # s.merchant_id='3'
> # , s.merchant_store_id=1
> # , s.store_sales_type=m.store_sales_type
> # , s.store_channel_id=2
> # , s.presell_type=m.order_type
> # , ms.store_sales_type=m.store_sales_type
> # , ms.order_type=3
> # , p.store_sales_type=4
> # , p.presell_type=m.order_type
> # where
> # s.id between 100382538 and 100382539 and s.source=1 and s.merchant_id='2'
> Retrieve All Records: false
> Include DDL Events: false
>
> I got the binlog like belows:
>
>
> #200930 8:46:19 server id 100 end_log_pos 29660 CRC32 0x1e9b0378 Table_map: `test`.`wdk_sub_orders_drf5089_9` mapped to number 219
> # at 29660
> #200930 8:46:19 server id 100 end_log_pos 29762 CRC32 0xfac7a7b0 Table_map: `test`.`wdk_sub_order_mc_drf5089` mapped to number 221
> # at 29762
> #200930 8:46:19 server id 100 end_log_pos 29882 CRC32 0x8b437c40 Table_map: `test`.`wdk_order_promotion_info_drf5089` mapped to number 222
> # at 29882
> #200930 8:46:19 server id 100 end_log_pos 30470 CRC32 0x3e0a2dab Update_rows: table id 219
> # at 30470
> #200930 8:46:19 server id 100 end_log_pos 30776 CRC32 0x4335186c Update_rows: table id 221
> # at 30776
> #200930 8:46:19 server id 100 end_log_pos 30959 CRC32 0x8153af67 Update_rows: table id 222 flags: STMT_END_F
> ### UPDATE `test`.`wdk_sub_orders_drf5089_9` ### WHERE
> ### @1=100382538 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @2=1 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @3=1 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @4=1 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @5=1 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @6=1 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @7=1 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @8='1' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @9=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @10=-1 (18446744073709551615) /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @11=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @12=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @13=-1 (4294967295) /* INT meta=0 nullable=0 is_null=0 */
> ### @14='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @15='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @16='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @17='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @18='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @19=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @20='2020-09-29 18:08:30' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
> ### @21='2020-09-29 18:08:30' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
> ### @22='1' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @23=0 /* INT meta=0 nullable=0 is_null=0 */
> ### @24=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @25=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
> ### @26=0 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### @27=0 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### @28=0.00 /* DECIMAL(15,2) meta=3842 nullable=0 is_null=0 */
> ### @29=0 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### @30='' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
> ### @31='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @32=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @33=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @34='' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
> ### @35=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @36=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @37=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @38='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @39='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @40='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @41=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @42=0 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### @43=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @44=0.000 /* DECIMAL(11,3) meta=2819 nullable=0 is_null=0 */
> ### @45=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
> ### @46=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @47='' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
> ### @48=1 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### @49='' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */
> ### @50=0.000 /* DECIMAL(11,3) meta=2819 nullable=0 is_null=0 */
> ### @51='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @52='' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
> ### @53=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @54='' /* VARSTRING(3000) meta=3000 nullable=0 is_null=0 */
> ### @55=1 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### @56=0 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### @57='' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */
> ### @58=0 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### @59=1 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### @60='2' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @61='1' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @62='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @63='1' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @64=1 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### @65=1 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### SET
> ### @1=100382538 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @2=1 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @3=1 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @4=1 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @5=1 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @6=1 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @7=1 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @8='1' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @9=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @10=-1 (18446744073709551615) /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @11=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @12=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @13=-1 (4294967295) /* INT meta=0 nullable=0 is_null=0 */
> ### @14='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @15='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @16='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @17='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @18='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @19=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @20='2020-09-29 18:08:30' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
> ### @21='2020-09-29 18:08:30' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
> ### @22='1' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @23=0 /* INT meta=0 nullable=0 is_null=0 */
> ### @24=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @25=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
> ### @26=0 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### @27=0 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### @28=0.00 /* DECIMAL(15,2) meta=3842 nullable=0 is_null=0 */
> ### @29=0 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### @30='' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
> ### @31='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @32=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @33=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @34='' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
> ### @35=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @36=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @37=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @38='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @39='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @40='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @41=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @42=0 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### @43=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @44=0.000 /* DECIMAL(11,3) meta=2819 nullable=0 is_null=0 */
> ### @45=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
> ### @46=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @47='' /* VARSTRING(300) meta=300 nullable=0 is_null=0 */
> ### @48=1 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### @49='' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */
> ### @50=0.000 /* DECIMAL(11,3) meta=2819 nullable=0 is_null=0 */
> ### @51='' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @52='' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
> ### @53=0 /* LONGINT meta=0 nullable=0 is_null=0 */
> ### @54='' /* VARSTRING(3000) meta=3000 nullable=0 is_null=0 */
> ### @55=1 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### @56=0 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### @57='' /* VARSTRING(96) meta=96 nullable=0 is_null=0 */
> ### @58=0 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### @59=1 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### @60='3' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @61='1' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @62='10' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @63='2' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
> ### @64=1 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### @65=1 /* TINYINT meta=0 nullable=0 is_null=0 */
> ### UPDATE `test`.`wdk_sub_order_mc_drf5089` ### WHERE
> ### @1=30735 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @2='' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
> ### @3=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @4=1 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @5=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @6=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @7=0.000 /* DECIMAL(11,3) meta=2819 nullable=1 is_null=0 */
> ### @8=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @9=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @10=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @11=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @12=0 /* TINYINT meta=0 nullable=1 is_null=0 */
> ### @13=0.0000 /* DECIMAL(10,4) meta=2564 nullable=1 is_null=0 */
> ### @14=1601374464 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
> ### @15=1601374464 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
> ### @16='' /* VARSTRING(1536) meta=1536 nullable=1 is_null=0 */
> ### @17=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @18=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @19=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @20=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @21=1 /* TINYINT meta=0 nullable=1 is_null=0 */
> ### @22='' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
> ### SET
> ### @1=30735 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @2='' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
> ### @3=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @4=1 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @5=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @6=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @7=0.000 /* DECIMAL(11,3) meta=2819 nullable=1 is_null=0 */
> ### @8=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @9=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @10=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @11=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @12=0 /* TINYINT meta=0 nullable=1 is_null=0 */
> ### @13=0.0000 /* DECIMAL(10,4) meta=2564 nullable=1 is_null=0 */
> ### @14=1601374464 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
> ### @15=1601426779 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */
> ### @16='' /* VARSTRING(1536) meta=1536 nullable=1 is_null=0 */
> ### @17=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @18=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @19=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @20=0 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @21=3 /* TINYINT meta=0 nullable=1 is_null=0 */
> ### @22='10' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
> ### UPDATE `test`.`wdk_order_promotion_info_drf5089`
> ### WHERE
> ### @1=4675456 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @2='1' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
> ### @3=1 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @4=1 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @5=1 /* TINYINT meta=0 nullable=1 is_null=0 */
> ### @6=0 /* INT meta=0 nullable=1 is_null=0 */
> ### @7=0 /* INT meta=0 nullable=1 is_null=0 */
> ### @8=-1 (4294967295) /* INT meta=0 nullable=1 is_null=0 */
> ### @9='' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
> ### @10='' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
> ### @11='' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
> ### @12=0 /* INT meta=0 nullable=1 is_null=0 */
> ### @13='' /* VARSTRING(3072) meta=3072 nullable=1 is_null=0 */
> ### @14='2020-09-29 18:16:23' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
> ### @15='2020-09-29 18:16:23' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
> ### @16='1' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
> ### @17='' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
> ### @18='' /* VARSTRING(600) meta=600 nullable=1 is_null=0 */
> ### @19='' /* VARSTRING(3000) meta=3000 nullable=1 is_null=0 */
> ### @20=0 /* TINYINT meta=0 nullable=1 is_null=0 */
> ### @21=1 /* TINYINT meta=0 nullable=1 is_null=0 */
> ### @22='' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
> ### SET
> ### @1=4675456 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @2='1' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
> ### @3=1 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @4=1 /* LONGINT meta=0 nullable=1 is_null=0 */
> ### @5=1 /* TINYINT meta=0 nullable=1 is_null=0 */
> ### @6=0 /* INT meta=0 nullable=1 is_null=0 */
> ### @7=0 /* INT meta=0 nullable=1 is_null=0 */
> ### @8=-1 (4294967295) /* INT meta=0 nullable=1 is_null=0 */
> ### @9='' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
> ### @10='' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
> ### @11='' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
> ### @12=0 /* INT meta=0 nullable=1 is_null=0 */
> ### @13='' /* VARSTRING(3072) meta=3072 nullable=1 is_null=0 */
> ### @14='2020-09-29 18:16:23' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
> ### @15='2020-09-29 18:16:23' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
> ### @16='1' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
> ### @17='' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
> ### @18='' /* VARSTRING(600) meta=600 nullable=1 is_null=0 */
> ### @19='' /* VARSTRING(3000) meta=3000 nullable=1 is_null=0 */
> ### @20=0 /* TINYINT meta=0 nullable=1 is_null=0 */
> ### @21=1 /* TINYINT meta=0 nullable=1 is_null=0 */
> ### @22='4' /* VARSTRING(192) meta=192 nullable=1 is_null=0 */
> # at 30959
> #200930 8:46:19 server id 100 end_log_pos 30990 CRC32 0x496fe17f Xid = 28836
> COMMIT/*!*/;
> SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file
> /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
> /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
>
>
>
>
> Thanks!
>
> Paul Yang