4 Replies Latest reply: Mar 20, 2012 9:19 AM by Jorge Jimenez RSS

MySQL datasource pool problem

Jorge Jimenez Newbie

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