5 Replies Latest reply on Jun 7, 2014 12:33 AM by arun2arunraj

    MySQL datasource pool problem

    jota

      I have a system in production and after some days (3-4) database pool can't obtain a connection. Stack trace shows:

       

      javax.ejb.EJBException: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Could not open connection

      22:00:32,246 ERROR [stderr] (Thread-202)           at org.jboss.as.ejb3.tx.CMTTxInterceptor.handleExceptionInOurTx(CMTTxInterceptor.java:166)

      22:00:32,268 ERROR [stderr] (Thread-202)           at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInOurTx(CMTTxInterceptor.java:230)

      ....

      Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Could not open connection

      22:00:32,321 ERROR [stderr] (Thread-202)           at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1361)

      22:00:32,324 ERROR [stderr] (Thread-202)           at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1289)

      ...

      Caused by: org.hibernate.exception.GenericJDBCException: Could not open connection

      22:00:32,400 ERROR [stderr] (Thread-202)           at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:52)

      22:00:32,403 ERROR [stderr] (Thread-202)           at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)

      22:00:32,405 ERROR [stderr] (Thread-202)           at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)

      ...

      Caused by: java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/jdbc/municipios

      22:00:32,485 ERROR [stderr] (Thread-202)           at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:137)

      22:00:32,487 ERROR [stderr] (Thread-202)           at org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider.getConnection(InjectedDataSour

      ...

      Caused by: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/jdbc/municipios

      22:00:32,521 ERROR [stderr] (Thread-202)           at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:390)

      22:00:32,524 ERROR [stderr] (Thread-202)           at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnectionManag

      ...

      Caused by: javax.resource.ResourceException: IJ000655: No managed connections available within configured blocking timeout (30000 [ms])

      22:00:32,537 ERROR [stderr] (Thread-202)           at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:377)

      22:00:32,541 ERROR [stderr] (Thread-202)           at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getTransactionNewConnection(AbstractPool.java:498

       

      Pool configuraton is:

                      <datasource jndi-name="java:/jdbc/municipios" pool-name="municipios_Pool">

                          <connection-url>jdbc:mysql://localhost:3306/municipios</connection-url>

                          <driver>mysql</driver>

                          <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

                          <pool>

                              <min-pool-size>10</min-pool-size>

                              <max-pool-size>200</max-pool-size>

                              <prefill>true</prefill>

                              <flush-strategy>EntirePool</flush-strategy>

                          </pool>

                          <security>

                              <user-name>xxx</user-name>

                              <password>xxx</password>

                          </security>

                          <statement>

                              <track-statements>TRUE</track-statements>

                              <prepared-statement-cache-size>32</prepared-statement-cache-size>

                              <share-prepared-statements>true</share-prepared-statements>

                          </statement>

                      </datasource>

       

       

      When I check active connections in pool (using cli) and real connections to database (show processlist in mysql) numbers are not equal after some running time (from night to next morning). Example:

      MySQL show processlist: 5 (even when datasource minimum is set to 10)

      Pool statistics:

      {

          "outcome" => "success",

          "result" => {

              "ActiveCount" => "58",

              "AvailableCount" => "146",

              "AverageBlockingTime" => "0",

              "AverageCreationTime" => "20",

              "CreatedCount" => "74",

              "DestroyedCount" => "16",

              "MaxCreationTime" => "356",

              "MaxUsedCount" => "58",

              "MaxWaitTime" => "6",

              "TimedOut" => "16",

              "TotalBlockingTime" => "22",

              "TotalCreationTime" => "1525"

          }

      }

       

      I suppose the exception throws when ActiveCount reaches 200, but real database connections should always be equal to ActiveCount ... or not?

       

      Database calls are made from stateless EJB. Most using a persistence context:

      <?xml version="1.0" encoding="UTF-8"?>

      <persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">

                <persistence-unit name="municipios">

                          <jta-data-source>java:/jdbc/municipios</jta-data-source>

                          <class>.....</class>

         ....

      </persistence-unit>

      </persistence>

       

      Some services uses an injected (@resource) datasource to the same pool and some other (a couple of them) uses a second pool. These services uses both connections (to both databases) and, as they are read-only and I don't want to use XA, they are marked as @TransactionAttribute=NotSupported

       

      All connections are closed in code (I use <track-statements>TRUE</track-statements> to be sure)

       

      System is in production a I have to flush all connections every day to keep it running.

       

      I need some help,

      Thanxs in advance,

      J