MySQL Communications link failure

[rsoika]

Hi,

I have a question about the correct configuration of datasources. I am running Wildfly 9 and have deployed a EAR using a MySQL DataSource connection. I also uses a secuity domain configuration which uses the same datasource connection as the application.

I noticed that when restart the MySQL sever and did not restart the WildFly Server after that login into my application fails.

I got the following Error message:

2015-09-27 09:22:34,400 DEBUG [org.jboss.security] (default task-8) PBOX00206: Login failure: javax.security.auth.login.LoginException: PBOX00065: Error processing query
     at org.jboss.security.auth.spi.DatabaseServerLoginModule.getUsersPassword(DatabaseServerLoginModule.java:202)
     at org.jboss.security.auth.spi.UsernamePasswordLoginModule.login(UsernamePasswordLoginModule.java:265)
     at sun.reflect.GeneratedMethodAccessor84.invoke(Unknown Source)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
     at java.lang.reflect.Method.invoke(Method.java:606)
     at javax.security.auth.login.LoginContext.invoke(LoginContext.java:762)
     at javax.security.auth.login.LoginContext.access$000(LoginContext.java:203)
     at javax.security.auth.login.LoginContext$4.run(LoginContext.java:690)
     at javax.security.auth.login.LoginContext$4.run(LoginContext.java:688)
     at java.security.AccessController.doPrivileged(Native Method)
     at javax.security.auth.login.LoginContext.invokePriv(LoginContext.java:687)
     at javax.security.auth.login.LoginContext.login(LoginContext.java:595)
     at org.jboss.security.authentication.JBossCachedAuthenticationManager.defaultLogin(JBossCachedAuthenticationManager.java:406)
....
.........
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error:


** BEGIN NESTED EXCEPTION ** 

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
MESSAGE: Communications link failure

Last packet sent to the server was 8 ms ago.

STACKTRACE:

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

Last packet sent to the server was 8 ms ago.
     at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
     at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
     at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
     at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
     at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
     at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3009)
     at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2895)
     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3438)
     at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
     at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554)
     at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1761)
     at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1912)
     at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
     at org.jboss.security.auth.spi.DatabaseServerLoginModule.getUsersPassword(DatabaseServerLoginModule.java:185)
     at org.jboss.security.auth.spi.UsernamePasswordLoginModule.login(UsernamePasswordLoginModule.java:265)
......

My Question is I there is a way to configure a DataSource connection in a way that the connection can be established even if the MySQL server was restarted. Currently I use the following DataSource configuration of my standalone.xml file:

                <datasource jta="true" jndi-name="java:/jdbc/mypool" pool-name="mypool" enabled="true" use-ccm="true">
                    <connection-url>jdbc:mysql://localhost:3306/mydb</connection-url>
                    <driver-class>com.mysql.jdbc.Driver</driver-class>
                    <driver>mysql-connector-java-5.1.7-bin.jar</driver>
                    <security>
                        <user-name>aaaa</user-name>
                        <password>bbbb</password>
                    </security>
                    <validation>
                        <validate-on-match>true</validate-on-match>
                        <background-validation>false</background-validation>
                    </validation>
                    <timeout>
                        <set-tx-query-timeout>false</set-tx-query-timeout>
                        <blocking-timeout-millis>0</blocking-timeout-millis>
                        <idle-timeout-minutes>0</idle-timeout-minutes>
                        <query-timeout>0</query-timeout>
                        <use-try-lock>0</use-try-lock>
                        <allocation-retry>0</allocation-retry>
                        <allocation-retry-wait-millis>0</allocation-retry-wait-millis>
                    </timeout>
                    <statement>
                        <share-prepared-statements>false</share-prepared-statements>
                    </statement>
                </datasource>

I first thought that the flag 'validate-on-match' should do the trick. But this is not the case.

Thanks for help

====
Ralph

[rsoika]

ok - it looks like adding the "?autoReconnect=true" to the connection URL did the trick:

1. <datasource jta="true" jndi-name="java:/jdbc/mypool" pool-name="mypool" enabled="true" use-ccm="true"> 
2.                     <connection-url>jdbc:mysql://localhost:3306/mydb?autoReconnect=true</connection-url>
3. .....
4. </datasource> 

But this solution looks like it is only MySQL related? Isn't it a general problem of the connection pooling in Wildfly?

===

Ralph

[vcornejo]

Ralph.

In the DataSource section adds the following:

<validation>  
  <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"></valid-connection-checker>
  <validate-on-match>true</validate-on-match>  
  <background-validation>false</background-validation>  
</validation>  

Víctor.

[rsoika]

Victor,

what exactly did this Validator? And what is the different to the URL param 'autoReconnect=true' ?

Is this a MySQL specifiy problem? I had some other installations where I use PostgreSQL. Are there also such Validator available?

===
Ralph

[rsoika]

I tested the org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker and it looks fine now. Thanks for that tip.