question Re: CaptureChangeMySQL Error when restart the nifi server or re-configure any processor in Support Questions

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

AbhishekSingh — Thu, 07 Jul 2022 12:31:01 GMT

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

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

AbhishekSingh — Wed, 13 Jul 2022 16:47:26 GMT

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.

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

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

AbhishekSingh — Thu, 21 Jul 2022 08:52:09 GMT

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;

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

Gagan1707 — Sat, 25 Feb 2023 11:27:28 GMT

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

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

SandyClouds — Wed, 26 Apr 2023 16:05:44 GMT

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