jboss wildfly datasource connection failing - No managed connections available within configured blocking timeout

Question

I have a jboss wildfly server connected to a mysql database via a datasource configured on the app server.

It hapens some time that the mysql database is not accessible for an amount of time (netework problem etc...), when the mysql is not accessible I receive in logs the message :

09:42:30,015 ERROR [org.jboss.seam.async.AsynchronousExceptionHandler] (4pm_services_QuartzScheduler_Worker-4) Exception thrown whilst executing asynchronous call: java.
lang.RuntimeException: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Cannot open connection

this is expected as thy mysql is not available at that moment, the porblem is that when the mysql server comes online again the error still persists, and the jboss server is unable to get a valid from the connection pool.

I have: wildfly 8.1 mysql 5.5 use JPA for comunicating with database

here is the datasource configuration:

<datasource jta="true" jndi-name="java:jboss/services_DS" pool-name="fourPmDSServices" enabled="true" use-java-context="true">
                    <connection-url>jdbc:mysql://192.168.10.110:3306/FOUR_PM__SERVICES?useUnicode=true&amp;characterEncoding=UTF-8</connection-url>
                    <driver>mysql</driver>
                    <new-connection-sql>select 1</new-connection-sql>
                    <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
                    <pool>
                        <min-pool-size>1</min-pool-size>
                        <max-pool-size>5</max-pool-size>
                        <prefill>true</prefill>
                        <use-strict-min>false</use-strict-min>
                    </pool>
                    <security>
                        <user-name>*********</user-name>
                        <password>*********</password>
                    </security>
                    <validation>
                        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
                        <check-valid-connection-sql>select 1</check-valid-connection-sql>
                        <stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLStaleConnectionChecker"/>
                        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
                    </validation>
                    <statement>
                        <prepared-statement-cache-size>10</prepared-statement-cache-size>
                        <share-prepared-statements>true</share-prepared-statements>
                    </statement>
                </datasource>

And here is the complete stack trace of the error:

00:00:00,017 WARN  [org.hibernate.util.JDBCExceptionReporter] (4pm_services_QuartzScheduler_Worker-2) SQL Error: 0, SQLState: null
00:00:00,018 ERROR [org.hibernate.util.JDBCExceptionReporter] (4pm_services_QuartzScheduler_Worker-2) javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:jboss/services_DS
00:00:00,018 ERROR [org.jboss.seam.async.AsynchronousExceptionHandler] (4pm_services_QuartzScheduler_Worker-2) Exception thrown whilst executing asynchronous call: java.lang.RuntimeException: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Cannot open connection
    at si.arctur.fourPmServices.scheduleJobs.QuartzJobBase.executeInternal(QuartzJobBase.java:91) [4pm_services-ejb.jar:]
    at si.arctur.fourPmServices.scheduleJobs.QuartzJobBase.schedule(QuartzJobBase.java:76) [4pm_services-ejb.jar:]
    at sun.reflect.GeneratedMethodAccessor98.invoke(Unknown Source) [:1.6.0_32]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.6.0_32]
    at java.lang.reflect.Method.invoke(Method.java:622) [rt.jar:1.6.0_32]
    at org.jboss.seam.util.Reflections.invoke(Reflections.java:22) [jboss-seam.jar:2.2.2.Final]
    at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:32) [jboss-seam.jar:2.2.2.Final]
    at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56) [jboss-seam.jar:2.2.2.Final]
    at org.jboss.seam.transaction.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:28) [jboss-seam.jar:2.2.2.Final]
    at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68) [jboss-seam.jar:2.2.2.Final]
    at org.jboss.seam.core.BijectionInterceptor.aroundInvoke(BijectionInterceptor.java:77) [jboss-seam.jar:2.2.2.Final]
    at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68) [jboss-seam.jar:2.2.2.Final]
    at org.jboss.seam.core.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:44) [jboss-seam.jar:2.2.2.Final]
    at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68) [jboss-seam.jar:2.2.2.Final]
    at org.jboss.seam.async.AsynchronousInterceptor.aroundInvoke(AsynchronousInterceptor.java:52) [jboss-seam.jar:2.2.2.Final]
    at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68) [jboss-seam.jar:2.2.2.Final]
    at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:107) [jboss-seam.jar:2.2.2.Final]
    at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:185) [jboss-seam.jar:2.2.2.Final]
    at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:103) [jboss-seam.jar:2.2.2.Final]
    at si.arctur.fourPmServices.scheduleJobs.emailSender.MailChecker_$$_javassist_seam_2.schedule(MailChecker_$$_javassist_seam_2.java) [4pm_services-ejb.jar:]
    at sun.reflect.GeneratedMethodAccessor104.invoke(Unknown Source) [:1.6.0_32]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.6.0_32]
    at java.lang.reflect.Method.invoke(Method.java:622) [rt.jar:1.6.0_32]
    at org.jboss.seam.util.Reflections.invoke(Reflections.java:22) [jboss-seam.jar:2.2.2.Final]
    at org.jboss.seam.util.Reflections.invokeAndWrap(Reflections.java:144) [jboss-seam.jar:2.2.2.Final]
    at org.jboss.seam.async.AsynchronousInvocation$1.process(AsynchronousInvocation.java:62) [jboss-seam.jar:2.2.2.Final]
    at org.jboss.seam.async.Asynchronous$ContextualAsynchronousRequest.run(Asynchronous.java:80) [jboss-seam.jar:2.2.2.Final]
    at org.jboss.seam.async.AsynchronousInvocation.execute(AsynchronousInvocation.java:44) [jboss-seam.jar:2.2.2.Final]
    at org.jboss.seam.async.QuartzDispatcher$QuartzJob.execute(QuartzDispatcher.java:243) [jboss-seam.jar:2.2.2.Final]
    at org.quartz.core.JobRunShell.run(JobRunShell.java:202) [quartz.jar:1.6.0]
    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:529) [quartz.jar:1.6.0]
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Cannot open connection
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1389) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1317) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:255) [hibernate-core.jar:3.6.10.Final]
    at si.arctur.fourPmServices.scheduleJobs.emailSender.MailChecker.checkQueue(MailChecker.java:37) [4pm_services-ejb.jar:]
    at si.arctur.fourPmServices.scheduleJobs.emailSender.MailChecker.execute(MailChecker.java:26) [4pm_services-ejb.jar:]
    at si.arctur.fourPmServices.scheduleJobs.QuartzJobBase$TransactionalWork.work(QuartzJobBase.java:144) [4pm_services-ejb.jar:]
    at org.jboss.seam.util.Work.workInTransaction(Work.java:61) [jboss-seam.jar:2.2.2.Final]
    at si.arctur.fourPmServices.scheduleJobs.QuartzJobBase.executeInternal(QuartzJobBase.java:89) [4pm_services-ejb.jar:]
    ... 30 more
Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1700) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:801) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2542) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.loader.Loader.list(Loader.java:2271) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:459) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:365) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:246) [hibernate-core.jar:3.6.10.Final]
    ... 35 more
Caused by: java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:jboss/services_DS
    at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:137)
    at org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider.getConnection(InjectedDataSourceConnectionProvider.java:71) [hibernate-core.jar:3.6.10.Final]
    at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446) [hibernate-core.jar:3.6.10.Final]
    ... 49 more
Caused by: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:jboss/services_DS
    at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:390)
    at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnectionManagerImpl.java:368)
    at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:464)
    at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:129)
    ... 51 more
Caused by: javax.resource.ResourceException: IJ000655: No managed connections available within configured blocking timeout (30000 [ms])
    at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:377)
    at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getTransactionNewConnection(AbstractPool.java:495)
    at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:374)
    at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:329)
    ... 54 more

Answer 1

What Does The Message No Managed Connection Available mean?

It means that all possible connections to the db are in use and/or that a thread has timed-out waiting for a connection to open up.

Possible solutions:

Increase your connection pool size

Add

<max-pool-size>whatever</max-pool-size>

to your -ds.xml file to increase

the number of connections available. The default is 20

There is an attribute

MaxConnectionsInUse

viewable on the jmx-console against the

ManagedConnectionPool

that can help you understand how many connections you really need.

Tell your threads to wait longer for a connection to open up

Add

<blocking-timeout-millis>whatever</blocking-timeout-millis>

to your -ds.xml file to increase the length of time to wait. The default is 30000 (5000 before JBoss-3.2.4) milli-seconds.

Make sure you are properly closing open connections

Answer 2

I encountered this issue as well. However, my connection worked briefly and then stopped working after a number of requests. It turned out to be an unclosed connection to the database, which caused connections to be consumed and eventually exhausting the connection pool. Simply increasing the connection pool size will only delay the eventual error. You'll need to identify where you not correctly closing your connection in order to resolve this issue.

Answer 3

It looks like you need to define a flush strategy of you failed connections. check the "flush-strategy" param.

A good point to start is here: https://docs.jboss.org/author/display/WFLY8/DataSource+configuration

They reference this XSD: http://www.ironjacamar.org/doc/schema/datasources_1_1.xsd

And the XSD decription is here: http://www.ironjacamar.org/doc/userguide/1.2/en-US/html_single/index.html#deployingds

In your case I would try to figure out the network issues. Independently of this. I would in your case (with bad connection issues) flush my complete connection pool: flush-strategy => EntirePool

good luck on fixing such an odd issue