CaptureChangeMySQL - throwing Binlog connector communications failure

[Anand Dev <an...@gmail.com>]

Thanks Koji for the resolution of previous issue.



But I see below error again and again once in a day. There is nothing else
in the logs apart from below error.



Can you please tell what could be the problem here? Is it from server side
or from processor or any network issue? We are not able to reproduce it.





2018-11-12 11:46:31,952 ERROR [Timer-Driven Process Thread-9]
o.a.n.c.m.processors.CaptureChangeMySQL
CaptureChangeMySQL[id=df913697-f589-121f-b681-da192917092a] Binlog
connector communications failure: could not find next log; the first event
'bin_8101.000001' at 142923964, the last event read from
'/u01/mysql/8101/var/log/binlogs/bin_8101.000001' at 142924061, the last
byte read from '/u01/mysql/8101/var/log/binlogs/bin_8101.000001' at
142924061.: com.github.shyiko.mysql.binlog.network.ServerException: could
not find next log; the first event 'bin_8101.000001' at 142923964, the last
event read from '/u01/mysql/8101/var/log/binlogs/bin_8101.000001' at
142924061, the last byte read from
'/u01/mysql/8101/var/log/binlogs/bin_8101.000001' at 142924061.

com.github.shyiko.mysql.binlog.network.ServerException: could not find next
log; the first event 'bin_8101.000001' at 142923964, the last event read
from '/u01/mysql/8101/var/log/binlogs/bin_8101.000001' at 142924061, the
last byte read from '/u01/mysql/8101/var/log/binlogs/bin_8101.000001' at
142924061.

        at
com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:882)

        at
com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:559)

        at
com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:793)

        at java.lang.Thread.run(Thread.java:745)





*From:* Koji Kawamura <ij...@gmail.com>
*Sent:* 23 October 2018 12:02
*To:* users@nifi.apache.org
*Subject:* Re: CaptureChangeMySQL - throwing Binlog connector
communications failure



Hello,

Thanks for reporting the issue and the detailed analysis.
I was able to reproduce by setting short wait_timeout as 30 sec.

I'm not aware of any work-around to make keep alive CaptureChangeMySQL
at the moment.
But you can write a script to stop/start CaptureChangeMySQL processor
using NiFi API to refresh underlying JDBC connection.
NipyAPI can be useful to write such scripts.
https://github.com/Chaffelson/nipyapi
<https://protect-us.mimecast.com/s/Nh36C0Ror8fn8gqqf2l1du?domain=github.com>

I submit a JIRA for this issue.
https://issues.apache.org/jira/browse/NIFI-5739
<https://protect-us.mimecast.com/s/weZuCgJD9ptvkwOOi3K0iZ?domain=issues.apache.org>

Thanks,
Koji


On Wed, Oct 10, 2018 at 9:42 PM Anand Dev <an...@gmail.com> wrote:
>
> In logs, below error is reported
>
> 2018-10-10 00:16:19,766 ERROR [Timer-Driven Process Thread-1]
o.a.n.c.m.processors.CaptureChangeMySQL
CaptureChangeMySQL[id=3c3f6e89-8401-3a67-a49d-3af14abf13dc] Error in
getting DDL Table info create table .... :
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No
operations allowed after connection closed.
>
> Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The
last packet successfully received from the server was 58,006,344
milliseconds ago. The last packet sent successfully to the server was
58,006,345 milliseconds ago. is longer than the server configured value of
'wait_timeout'. You should consider either expiring and/or testing
connection validity before use in your application, increasing the server
configured values for client timeouts, or using the Connector/J connection
property 'autoReconnect=true' to avoid this problem.
>
> It happened because the connection got stale / dead after 8 hours.
>
> How to keep alive CaptureChangeMySQL processor connection with DB ?
> One way would be increasing 'wait_timeout' to high value but the error
can still occur post that time.
> How to keep alive connection forever unless processor is stopped?
>
> On Wed, Oct 10, 2018 at 4:41 PM Anand Dev <an...@gmail.com> wrote:
>>
>> When I start the CaptureChangeMySQL processor, it worked fine till the
point when DDL event occured for Creating a table.
>>
>> Processor captured the CREATE table event successfully.
>> Next, when record was inserted in the newly created table, it threw
below error and stopped capturing further events.
>>
>> ERROR
>> ===============
>> Binlog connector communications failure: could not find next log; the
first event 'bin_5101.000013' at 921485, the last event read from
'/u01/mysql/5101/var/log/binlogs/bin_5101.000013' at 921565, the last byte
read from '/u01/mysql/5101/var/log/binlogs/bin_5101.000013' at 921565
>>
>> com.github.shyiko.mysql.binlog.network.ServerException: could not find
next log; the first event 'bin_5101.000013' at 921485, the last event read
from '/u01/mysql/5101/var/log/binlogs/bin_5101.000013' at 921565, the last
byte read from '/u01/mysql/5101/var/log/binlogs/bin_5101.000013' at 921565
>> ================
>>
>> Can someone suggest what's going wrong here?