CaptureChangeMySQL Error when restart the nifi server or re-configure any processor

[#1]

Hi All,

I am getting error when I restart the NiFi server and run the CaptureChangeMySql processor or re-configure any processor and start the processor. It works fine when I creates  CaptureChangeMySql processor and run it. When I restart the server this I start getting below error. It works fine again when processor is recreated freshly. 

Any Idea how I can fix this issue.

2022-07-07 15:10:58,307 ERROR [Timer-Driven Process Thread-8] o.a.n.c.m.processors.CaptureChangeMySQL CaptureChangeMySQL[id=d7f0536f-0181-1000-db94-5950dddc0439] Processing failed
org.apache.nifi.processor.exception.ProcessException: java.io.IOException: COMMIT event received while not processing a transaction (i.e. no corresponding BEGIN event). This could indicate that your binlog position is invalid.
at 

[#2]

Can anyone suggest how to fix above issue. This issue come when i stop the all processor and again start the all processor then I can see the error at CaptureChangeMySql processor level error. 

2022-07-07 15:10:58,307 ERROR [Timer-Driven Process Thread-8] o.a.n.c.m.processors.CaptureChangeMySQL CaptureChangeMySQL[id=d7f0536f-0181-1000-db94-5950dddc0439] Processing failed
org.apache.nifi.processor.exception.ProcessException: java.io.IOException: COMMIT event received while not processing a transaction (i.e. no corresponding BEGIN event). This could indicate that your binlog position is invalid.
at 

Note: This issue get resolved when i just delete the existing CaptureChangeMySQL processor and again create and configure the CaptureChangeMySQL processor.

[#3 · Accepted solution]

After enabling the GTID of mysql service and enabled captureChange mySql processor  configuration to "Use Binlog GTID" : true, This issue got resolved.

below script to enabled GTID in mysql: 

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

After enabling above properties run below query to check status. 

SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT'; 

If count is 0 then enabled GTID using below query:

SET @@GLOBAL.GTID_MODE = ON;

To Check the GTID status:

select @@GLOBAL.GTID_MODE;
select @@GLOBAL.ENFORCE_GTID_CONSISTENCY;

[#4]

Hi do you know of any other way by which we can resolve this issue, i cannot set the gtid flag. So cannot use this approach.

i am using nifi 1.19.1

thanks

[#5]

What is your run schedule time for the processor ? 

if its too low (say 1 sec or 30 sec), gtry increasing it to reasonable timeline like a minute based on your refresh requirements. @Gagan1707