[NIFI-5739] CaptureChangeMySQL should maintain its JDBC connection automatically

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.2.0
• Fix Version/s: 1.9.0
• Component/s: Extensions
• Assignee: Koji Kawamura
• Reporter: Koji Kawamura
• Created: 23/Oct/18 06:23
• Updated: 24/Oct/18 07:43
• Resolved: 24/Oct/18 07:43

Description

CaptureChangeMySQL uses two different connections to the target MySQL server. A) binlog client, B) JDBC client. The JDBC client is used to query table column definition, which is not returned by binlog messages.

CaptureChangeMySQL establishes the JDBC connection when it starts. If the connection is not used for the next configured wait_timeout period (defaults to 28800 secs), the connection will be thought as an idle connection and closed by MySQL server. That happens if no new table gets created, and no DML is executed for the new table. The binlog client can stay connected in this situation.

After the connection got closed, if a DML event is received, and if its table column info is not known yet, then CaptureChangeMySQL will get the table column info by executing a query using the connection. As a result, following error occurs:

org.apache.nifi.processor.exception.ProcessException: java.io.IOException: The last packet successfully received from the server was 55,237 milliseconds ago.  The last packet sent successfully to the server was 55,238 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.
        at org.apache.nifi.cdc.mysql.processors.CaptureChangeMySQL.onTrigger(CaptureChangeMySQL.java:593)
        at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1165)
        at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:203)
        at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)
Caused by: java.io.IOException: The last packet successfully received from the server was 55,237 milliseconds ago.  The last packet sent successfully to the server was 55,238 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.
        at org.apache.nifi.cdc.mysql.processors.CaptureChangeMySQL.outputEvents(CaptureChangeMySQL.java:772)
        at org.apache.nifi.cdc.mysql.processors.CaptureChangeMySQL.onTrigger(CaptureChangeMySQL.java:571)
        ... 10 common frames omitted

CaptureChangeMySQL should maintain underlying JDBC connection automatically, so that the processor can keep running.

Issue Links

• links to: GitHub Pull Request #3103