1 Reply Latest reply on Feb 9, 2012 10:05 AM by method_ben_qc

    Database connection problem

    method_ben_qc Newbie

      Hi,

       

      First of all, here's my datasource definitions in my standalone.xml:

       

                      <datasource jndi-name="java:/myDS" pool-name="MySqlDS" enabled="true" jta="false" use-java-context="true" use-ccm="true">

                          <connection-url>

                              jdbc:mysql://server.db.com/db1?autoReconnect=true

                          </connection-url>

                          <driver>

                              mysql

                          </driver>

                          <pool>

                              <min-pool-size>

                                  5

                              </min-pool-size>

                              <max-pool-size>

                                  20

                              </max-pool-size>

                              <prefill>

                                  false

                              </prefill>

                              <use-strict-min>

                                  false

                              </use-strict-min>

                              <flush-strategy>

                                  FailingConnectionOnly

                              </flush-strategy>

                          </pool>

                          <security>

                              <security-domain>

                                  MyDatabaseDomain

                              </security-domain>

                          </security>

                          <validation>

                              <validate-on-match>

                                  false

                              </validate-on-match>

                              <background-validation>

                                  false

                              </background-validation>

                              <use-fast-fail>

                                  false

                              </use-fast-fail>

                              <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>

                          </validation>

                          <timeout>

                              <idle-timeout-minutes>

                                  0

                              </idle-timeout-minutes>

                          </timeout>

                          <statement>

                              <prepared-statement-cache-size>

                                  100

                              </prepared-statement-cache-size>

                              <share-prepared-statements/>

                          </statement>

                      </datasource>

                      <datasource jndi-name="java:/myDSJTA" pool-name="MySqlDS" enabled="true" jta="true" use-java-context="true" use-ccm="true">

                          <connection-url>

                              jdbc:mysql://server.db.com/db2?autoReconnect=true

                          </connection-url>

                          <driver>

                              mysql

                          </driver>

                          <pool>

                              <min-pool-size>

                                  5

                              </min-pool-size>

                              <max-pool-size>

                                  20

                              </max-pool-size>

                              <prefill>

                                  false

                              </prefill>

                              <use-strict-min>

                                  false

                              </use-strict-min>

                              <flush-strategy>

                                  FailingConnectionOnly

                              </flush-strategy>

                          </pool>

                          <security>

                              <security-domain>

                                  MyDatabaseDomainJTA

                              </security-domain>

                          </security>

                          <validation>

                              <validate-on-match>

                                  true

                              </validate-on-match>

                              <background-validation>

                                  false

                              </background-validation>

                              <use-fast-fail>

                                  false

                              </use-fast-fail>

                              <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>

                          </validation>

                          <timeout>

                              <idle-timeout-minutes>

                                  0

                              </idle-timeout-minutes>

                          </timeout>

                      </datasource>

       

       

      I got the following warning on my environment. Anyone has an idea what does it mean? It seems the connection reaches a timeout. I tried different configurations for the datasource but nothing solved my problem.

       

      2012-01-09 20:07:15,837 WARN  [org.jboss.jca.core.connectionmanager.listener.NoTxConnectionListener] IJ000305: Connection error occured: org.jboss.jca.core.connectionmanager.listener.NoTxConnectionListener@3486113d[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@3294c651 connection handles=1 lastUse=1326138475060 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.PoolBySubject@315a4d11 pool internal context=org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool@1f79b067]: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

       

       

      The last packet successfully received from the server was 1,160,759 milliseconds ago.  The last packet sent successfully to the server was 1,160,762 milliseconds ago.

                at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

                at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

                at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

                at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

                at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)

                at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1119)

                at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3318)

                at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1940)

                at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)

                at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2693)

                at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2102)

                at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2261)

                at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:107)

                at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)

                at org.jboss.security.auth.spi.DatabaseServerLoginModule.getUsersPassword(DatabaseServerLoginModule.java:180)

                at org.jboss.security.auth.spi.UsernamePasswordLoginModule.login(UsernamePasswordLoginModule.java:245)

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

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

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

                at javax.security.auth.login.LoginContext.invoke(LoginContext.java:769)

                at javax.security.auth.login.LoginContext.access$000(LoginContext.java:186)

                at javax.security.auth.login.LoginContext$4.run(LoginContext.java:683)

                at java.security.AccessController.doPrivileged(Native Method)

                at javax.security.auth.login.LoginContext.invokePriv(LoginContext.java:680)

                at javax.security.auth.login.LoginContext.login(LoginContext.java:579)

                at org.jboss.security.authentication.JBossCachedAuthenticationManager.defaultLogin(JBossCachedAuthenticationManager.java:411)

                at org.jboss.security.authentication.JBossCachedAuthenticationManager.authenticate(JBossCachedAuthenticationManager.java:345)

                at org.jboss.security.authentication.JBossCachedAuthenticationManager.isValid(JBossCachedAuthenticationManager.java:154)

                at org.jboss.as.web.security.JBossWebRealm.authenticate(JBossWebRealm.java:127)

                at org.apache.catalina.authenticator.BasicAuthenticator.authenticate(BasicAuthenticator.java:180)

                at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:446)

                at org.jboss.as.web.NamingValve.invoke(NamingValve.java:57)

                at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:154)

                at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)

                at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)

                at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:362)

                at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:877)

                at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:667)

                at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:952)

                at java.lang.Thread.run(Thread.java:619)

      Caused by: java.net.SocketException: Connection reset by peer: socket write error

                at java.net.SocketOutputStream.socketWrite0(Native Method)

                at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)

                at java.net.SocketOutputStream.write(SocketOutputStream.java:136)

                at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)

                at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)

                at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3299)

                ... 33 more

       

      Here's another example but it logs as an error:

       

      2012-01-09 20:07:19,260 ERROR [org.jboss.security.authentication.JBossCachedAuthenticationManager] Login failure: javax.security.auth.login.LoginException: Query failed

                at org.jboss.security.auth.spi.DatabaseServerLoginModule.getUsersPassword(DatabaseServerLoginModule.java:201)

                at org.jboss.security.auth.spi.UsernamePasswordLoginModule.login(UsernamePasswordLoginModule.java:245)

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

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

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

                at javax.security.auth.login.LoginContext.invoke(LoginContext.java:769)

                at javax.security.auth.login.LoginContext.access$000(LoginContext.java:186)

                at javax.security.auth.login.LoginContext$4.run(LoginContext.java:683)

                at java.security.AccessController.doPrivileged(Native Method)

                at javax.security.auth.login.LoginContext.invokePriv(LoginContext.java:680)

                at javax.security.auth.login.LoginContext.login(LoginContext.java:579)

                at org.jboss.security.authentication.JBossCachedAuthenticationManager.defaultLogin(JBossCachedAuthenticationManager.java:411)

                at org.jboss.security.authentication.JBossCachedAuthenticationManager.authenticate(JBossCachedAuthenticationManager.java:345)

                at org.jboss.security.authentication.JBossCachedAuthenticationManager.isValid(JBossCachedAuthenticationManager.java:154)

                at org.jboss.as.web.security.JBossWebRealm.authenticate(JBossWebRealm.java:127)

                at org.apache.catalina.authenticator.BasicAuthenticator.authenticate(BasicAuthenticator.java:180)

                at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:446)

                at org.jboss.as.web.NamingValve.invoke(NamingValve.java:57)

                at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:154)

                at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)

                at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)

                at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:362)

                at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:877)

                at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:667)

                at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:952)

                at java.lang.Thread.run(Thread.java:619)

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

       

       

      The last packet successfully received from the server was 1,160,858 milliseconds ago.  The last packet sent successfully to the server was 1,160,860 milliseconds ago.

                at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

                at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

                at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

                at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

                at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)

                at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1119)

                at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3318)

                at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1940)

                at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)

                at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2693)

                at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2102)

                at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2261)

                at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:107)

                at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)

                at org.jboss.security.auth.spi.DatabaseServerLoginModule.getUsersPassword(DatabaseServerLoginModule.java:180)

                ... 25 more

      Caused by: java.net.SocketException: Connection reset by peer: socket write error

                at java.net.SocketOutputStream.socketWrite0(Native Method)

                at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)

                at java.net.SocketOutputStream.write(SocketOutputStream.java:136)

                at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)

                at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)

                at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3299)

                ... 33 more