TAC azure base mysql db frequently showing error "Cannot flush and commit transaction." "No operations allowed after connection closed"

[TalendSolutionExpert]

Problem:  TAC v731 v801  using Azure mysql as backend db frequently showing  "Cannot flush and commit transaction." + No operations allowed after connection closed.

Log :

!!!Cannot flush and commit transaction.!!!
org.talend.administrator.common.persistence.hibernate.HibernateTransactionHolder.commitWithFlush(HibernateTransactionHolder.java:119)
org.talend.administrator.common.persistence.hibernate.HibernateTransactionHolder.commitWithFlushAndBeginNewTransaction(HibernateTransactionHolder.java:197)
org.talend.administrator.scheduler.business.ExecutionTaskHandler.runTaskUnit(ExecutionTaskHandler.java:1156)
org.talend.administrator.scheduler.jobs.RemoteTaskExecution.executeRemoteJob(RemoteTaskExecution.java:526)
org.talend.administrator.scheduler.jobs.RemoteTaskExecution.execute(RemoteTaskExecution.java:251)
org.talend.administrator.scheduler.jobs.RemoteExecutionSwitcher.execute(RemoteExecutionSwitcher.java:54)
Caused by: The last packet successfully received from the server was 427,423 milliseconds ago. The last packet sent successfully to the server was 427,425 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.
org.talend.administrator.common.exception.DBException: !!!Cannot flush and commit transaction.!!!
org.talend.administrator.scheduler.persistence.helper.ExecutionVirtualServerHelper.findById(ExecutionVirtualServerHelper.java:135)
org.talend.administrator.scheduler.business.ExecutionServerResolver.getBestExecutionServer(ExecutionServerResolver.java:73)
org.talend.administrator.scheduler.business.ExecutionTaskHandler.findExecutionServer(ExecutionTaskHandler.java:1948)
org.talend.administrator.scheduler.business.ExecutionTaskHandler.findExecutionServer(ExecutionTaskHandler.java:1925)
org.talend.administrator.scheduler.business.ExecutionTaskHandler.runTaskUnit(ExecutionTaskHandler.java:1152)
org.talend.administrator.scheduler.jobs.RemoteTaskExecution.executeRemoteJob(RemoteTaskExecution.java:526)
org.talend.administrator.scheduler.jobs.RemoteTaskExecution.execute(RemoteTaskExecution.java:251)
org.talend.administrator.scheduler.jobs.RemoteExecutionSwitcher.execute(RemoteExecutionSwitcher.java:54)
org.quartz.core.JobRunShell.run(JobRunShell.java:199)
org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)
Caused by: !!!Cannot flush and commit transaction.!!!
Caused by: No operations allowed after connection closed.

Analysis : 

log above related issue is caused by long query and Azure MySQL default 300sec based timeout will not meet tac long query requirements

Fix :

1. change MySQL wait_timeout  from 300 to 28800 from Azure MySQL system properties( Azure mysql system properties dashboard),  as azure mysql instance have 300sec based default setting, this can guarantee the long-running query commit before the connection close.
'wait_timeout', '28800'

2. enable  autoReconnect=true in  tac db JDBC url to avoid network glitches caused by a connection timeout

3. change MySQL isolation level to a lower one to avoid race conditions caused by slow queries
SET tx_isolation = 'READ-COMMITTED';
set transaction_isolation='READ-COMMITTED';
SET GLOBAL tx_isolation = 'READ-COMMITTED';
set @@global.transaction_isolation='READ-COMMITTED';