Lose the connection to Wildfly after restarting mysql server

Question

After restarting MySQL loses the Wildfly connection

We used 2 versions MySQL MySQL Ver 14.14 Distrib 5.6.51, for Linux (x86_64) using EditLine wrappe and MySQL Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)

We tried on 2 versions of Wildfly wildfly-10.1.0.Final and wildfly-19.1.0.Final

and we used JDBC a database connector version is - 8.0.19

public static Connection getConnection() throws Exception {
        LOG.debug("returning new connection");
        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/xxx?allowPublicKeyRetrieval=true&useSSL=false", "xxxx", "xxxx");
        return con;
    }

How can we reconnect automatically after restarting MySQL server, what is the best approach?

We're getting error messages

1. 2022-04-08 16:09:36,614 ERROR [xxx.xxx.mysql.MysqlSentSMSDAo] (DefaultQuartzScheduler_Worker-9) No operations allowed after connection closed.: java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed. at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:73) at com.mysql.cj.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:1659) at com.mysql.cj.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:1575) at xxx.xxxx.loader.mysql.MysqlSentSMSDAo.selectReplyRecord(MysqlSentSMSDAo.java:85) at xxx.xxx.loader.schedular.UpdateReplyReport.execute(UpdateReplyReport.java:109) at org.quartz.core.JobRunShell.run(JobRunShell.java:202) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:525) Caused by: com.mysql.cj.exceptions.ConnectionIsClosedException: No operations allowed after connection closed. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105) at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151) at com.mysql.cj.NativeSession.checkClosed(NativeSession.java:1171) at com.mysql.cj.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:576) at com.mysql.cj.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:1590) ... 5 more Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

2. The last packet successfully received from the server was 4,742 milliseconds ago. The last packet sent successfully to the server was 4,744 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.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003) at xxx.xxx.loader.mysql.MysqlSentSMSDAo.getListOfSQLSentSMSEntities(MysqlSentSMSDAo.java:39) at xxx.xxx.loader.schedular.UpdateBulkReceipts.execute(UpdateBulkReceipts.java:59) at org.quartz.core.JobRunShell.run(JobRunShell.java:202) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:525) Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

Answer 1

Add property autoReconnect=true in the connection string, which makes the connection to auto recover. If you are using Wildfly, then better to define data source in standalone.xml, with autoReconnect=true in <connection-url> tag.

.