MySQL Connector/J using loadbalance mode get Communications link failure

Help!
1

I am using tomcat 9, Mysql connector/j 8 and HAProxy 1.8.4.

I have 2 haproxy servers connecting to 2 db nodes in active-passive mode such that the haproxy servers always passes the db connections to node1 as master. It passes the db connection to node2 when node1 is down. Then my application makes DB connection by connecting to haproxy servers.

Then I set up the load balance mode in the tomcat data source db connection setting as follows:

url=“jdbc:mysql:loadbalance://haproxy1:19306,happroxy2:19306/mydb?noAccessToProcedureBodies=true”

With the load balance mode, my application always get the Communications link failure like:

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet successfully received from the server was 1,200,107 milliseconds ago. The last packet sent successfully to the server was 1,200,107 milliseconds ago.

    at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)

    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)

    at com.mysql.cj.jdbc.ConnectionImpl.commit(ConnectionImpl.java:814)

    at com.mysql.cj.jdbc.ha.MultiHostMySQLConnection.commit(MultiHostMySQLConnection.java:164)

    at sun.reflect.GeneratedMethodAccessor124.invoke(Unknown Source)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

    at java.lang.reflect.Method.invoke(Method.java:498)

    at com.mysql.cj.jdbc.ha.LoadBalancedConnectionProxy.invokeMore(LoadBalancedConnectionProxy.java:565)

    at com.mysql.cj.jdbc.ha.MultiHostConnectionProxy.invoke(MultiHostConnectionProxy.java:502)

    at com.sun.proxy.$Proxy4.commit(Unknown Source)

    at sun.reflect.GeneratedMethodAccessor124.invoke(Unknown Source)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

    at java.lang.reflect.Method.invoke(Method.java:498)

    at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:125)

    at com.sun.proxy.$Proxy8.commit(Unknown Source)

    at hk.gov.egis.portal.batch.SendMail.run(SendMail.java:177)

    at hk.gov.egis.portal.batch.SendMail.execute(SendMail.java:434)

    at org.quartz.core.JobRunShell.run(JobRunShell.java:202)

    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)

If I set the failover mode in like:

url=“jdbc:mysql://haproxy1:19306,happroxy2:19306/mydb?noAccessToProcedureBodies=true&failOverReadOnly=false”

I don’t get the Communications link failure any more.

Please advise how can eliminate the Communication link failure when using load balance mode.

Thanks