Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
Contributor

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.Screenshot from 2022-07-07 17-44-51.png

 

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 

1 ACCEPTED SOLUTION

avatar
Contributor

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;

View solution in original post

4 REPLIES 4

avatar
Contributor

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.

 

avatar
Contributor

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;

avatar
New Contributor

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

avatar
Expert Contributor

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