1 2 Previous Next 18 Replies Latest reply on Jun 28, 2013 7:01 AM by bruno bruno

    org.hibernate.exception.GenericJDBCException: Cannot open connection

    bruno bruno Newbie

      Hi,

       

      >I've been encountering this error for a week now. I've tried to increase the blocking timeout from 5sec to 1min however it didn't work.

        Our application is running with oracle 11g.

        Any suggestion on how to solve this?

        Is there additional configuration that we need to do?

       

       

      root Exception Stack Trace : org.hibernate.exception.GenericJDBCException: Cannot open connection

      org.hibernate.exception.GenericJDBCException: Cannot open connection

                at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)

                at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)

                at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)

                at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)

                at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)

                at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)

                at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:142)

                at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:85)

                at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1354)

                at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:497)

                at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:349)

                at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:255)

                at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:102)

                at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)

                at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)

                at $Proxy73.getRecipientDetails(Unknown Source)

                at aero.champ.fusion.schedular.EmailSchedular.sendEmailsAndMessages(EmailSchedular.java:225)

                at aero.champ.fusion.schedular.EmailSchedular.sendEmailPerCarrier(EmailSchedular.java:170)

                at aero.champ.fusion.schedular.EmailSchedular.run(EmailSchedular.java:77)

                at java.util.TimerThread.mainLoop(Timer.java:534)

                at java.util.TimerThread.run(Timer.java:484)

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

                at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:137)

                at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)

                at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)

                ... 16 more

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

                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)

                ... 18 more

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

                at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:377)

                at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getSimpleConnection(AbstractPool.java:397)

                at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:365)

                at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:329)

                ... 21 more

       

       

      datasource definition:

       

       

          <datasource jndi-name="java:/dataSourcePK" pool-name="dataSourcePK" enabled="true" use-java-context="true">

                          <connection-url>jdbc:oracle:thin:@ddi:2222:webcli</connection-url>

                          <driver>oracle</driver>

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

                          <pool>

                              <min-pool-size>8</min-pool-size>

                              <max-pool-size>32</max-pool-size>

                              <prefill>true</prefill>

                              <use-strict-min>true</use-strict-min>

                              <flush-strategy>FailingConnectionOnly</flush-strategy>

                          </pool>

                          <security>

                              <user-name>PK</user-name>

                              <password>PK</password>

                          </security>

                          <validation>

                              <exception-sorter class-name="org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter"/>

                          </validation>

                          <timeout>

                              <blocking-timeout-millis>60000</blocking-timeout-millis>

                              <idle-timeout-minutes>5</idle-timeout-minutes>

                          </timeout>

                          <statement>

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

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

                          </statement>

                      </datasource>

        • 1. Re: org.hibernate.exception.GenericJDBCException: Cannot open connection
          Stephen Coy Master

          Does this happen under load, or with a single user?

          • 2. Re: org.hibernate.exception.GenericJDBCException: Cannot open connection
            bruno bruno Newbie

            Hi Coy,

             

                 this happen under load not with a single user.

            • 3. Re: org.hibernate.exception.GenericJDBCException: Cannot open connection
              Tomaz Cerar Master

              Hi,

               

              it is telling you that pool is full, aka all 32 connections that you have configured for max-pool-size are used up

              and wait time for getting it (hoping that something gets retured into pool) has expired.

               

              I would recommend you to increse max-pool-size to something bigger, especialy if it only happens under load.

              Otherwise there might be issue with not closing connections.

               

               

              --

              tomaz

              • 4. Re: org.hibernate.exception.GenericJDBCException: Cannot open connection
                Stephen Coy Master

                How many concurrent users?

                 

                Do you have transactions that take a long time?

                • 5. Re: org.hibernate.exception.GenericJDBCException: Cannot open connection
                  bruno bruno Newbie

                  Hi Coy,

                   

                   

                           There are only few users using are application however, it listen from the queue and consume messages as well.

                            messages also consume the connection.

                   

                           Yes there is a transaction that take a long time. it is the processing of the messages.

                  • 6. Re: org.hibernate.exception.GenericJDBCException: Cannot open connection
                    Stephen Coy Master

                    Normally, each message is processed in its own transaction.

                     

                    Does it take a long time to process a single message?

                     

                    Is it possible to optimise the database transaction at all?

                    ie. write a better query.

                    • 7. Re: org.hibernate.exception.GenericJDBCException: Cannot open connection
                      bruno bruno Newbie

                      Yup you're correct each message is processed in its own transaction.

                       

                      the fastest <5secs and the slowest is > 2mins.

                       

                       

                      Actually we are currently migrating from SUN app server 8.2 to jboss AS 7.1 (with the same DB oracle 11g)

                      but we haven't encoutered this error from the old server and we copied the configuration from the old server (e.g min-pool-size, max-poo-size, timeouts and etc.)

                      • 8. Re: org.hibernate.exception.GenericJDBCException: Cannot open connection
                        Stephen Coy Master

                        Are you using the stock ("mdb-strict-max-pool") mdb pool?

                         

                        If you're queue is saturated it will be using 20 of your 32 database connections all by itself.

                         

                        You may want to consider a smaller mdb pool size.

                        • 9. Re: org.hibernate.exception.GenericJDBCException: Cannot open connection
                          bruno bruno Newbie

                          we're not using mdb pool.. we used spring jms (esp. messageListener).

                           

                          if i increase the size of max-pool-size will this solve the issue or it will cause another problem?

                          • 11. Re: org.hibernate.exception.GenericJDBCException: Cannot open connection
                            bruno bruno Newbie

                            Hi Stephen,

                             

                                Here's the jms configuration

                             

                             

                            <bean id="messageExceptionListener" class="integration.core.impl.DefaultExceptionListener"/>

                             

                             

                                      <!--Message Listener -->

                             

                             

                                      <bean id="messageListener"

                                                class="integration.core.impl.DefaultMessageHandler"

                                                init-method="initialize">

                                                <property name="integrationService" ref="integrationService" />

                                                <property name="marshaller" ref="jaxb2Marshaller" />

                                                <property name="emailSenderUtil" ref="emailSenderUtil" />

                                      </bean>

                                      <bean id="listenerContainer"

                                                class="org.springframework.jms.listener.DefaultMessageListenerContainer102">

                                                <property name="concurrentConsumers" value="1" />

                                                <property name="connectionFactory"

                                                          ref="internalJmsQueueConnectionFactory" />

                                                <property name="destination" ref="recieverQueue" />

                                                <property name="messageListener" ref="messageListener" />

                                                <property name="exceptionListener" ref="messageExceptionListener" />

                                                <property name="autoStartup" value="true" />

                                                <property name="sessionTransacted" value="true" />

                                      </bean>

                                      <bean id="listenerContainerForGFX"

                                                class="org.springframework.jms.listener.DefaultMessageListenerContainer102">

                                                <property name="concurrentConsumers" value="5" />

                                                <property name="connectionFactory"

                                                          ref="internalJmsQueueConnectionFactory" />

                                                <property name="destination" ref="receiverGFXQueue" />

                                                <property name="messageListener" ref="messageListener" />

                                                <property name="exceptionListener" ref="messageExceptionListener" />

                                                <property name="autoStartup" value="true" />

                                                <property name="sessionTransacted" value="true" />

                                      </bean>

                             

                             

                                      <!-- JNDI Environment Template -->

                             

                             

                                      <bean id="jndiTemplate"

                                                class="org.springframework.jndi.JndiTemplate">

                                                <property name="environment">

                                                          <props>

                                                                    <prop key="java.naming.factory.initial">

                                                                              com.sun.jndi.fscontext.RefFSContextFactory

                                                                    </prop>

                                                                    <prop key="java.naming.provider.url">

                                                                              file:/opt/SUNWappserver/appserver/fusion/jndi

                                                                    </prop>

                                                          </props>

                                                </property>

                                      </bean>

                             

                             

                                      <!-- JMS Queue Connection Factory -->

                             

                             

                                      <bean id="internalJmsQueueConnectionFactory"

                                                class="org.springframework.jndi.JndiObjectFactoryBean">

                                                <property name="jndiTemplate">

                                                          <ref bean="jndiTemplate" />

                                                </property>

                                                <property name="jndiName">

                                                          <value>JMS_XAQCF</value>

                                                </property>

                                      </bean>

                             

                             

                                      <!-- JMS Destination -->

                             

                             

                                      <bean id="destinationQueue"

                                                class="org.springframework.jndi.JndiObjectFactoryBean">

                                                <property name="jndiTemplate">

                                                          <ref bean="jndiTemplate" />

                                                </property>

                                                <property name="jndiName">

                                                          <value>JMS_FQueue</value>

                                                          <!-- <value>JMS_FQueueDemo</value>-->

                                                </property>

                                      </bean>

                                      <bean id="recieverQueue"

                                                class="org.springframework.jndi.JndiObjectFactoryBean">

                                                <property name="jndiTemplate">

                                                          <ref bean="jndiTemplate" />

                                                </property>

                                                <property name="jndiName">

                                                          <value>JMS_TQueue</value>

                                                          <!-- <value>JMS_TQueueDemo</value>-->

                                                </property>

                                      </bean>

                             

                             

                                      <!-- Priority Destination Queue -->

                             

                             

                                      <bean id="priorityDestinationQueue"

                                                class="org.springframework.jndi.JndiObjectFactoryBean">

                                                <property name="jndiTemplate">

                                                          <ref bean="jndiTemplate" />

                                                </property>

                                                <property name="jndiName">

                                                          <value>JMS_FPriorityQueue</value>

                                                          <!-- <value>JMS_FPriorityQueueDemo</value>-->

                                                </property>

                                      </bean>

                                      <bean id="priorityRecieverQueue"

                                                class="org.springframework.jndi.JndiObjectFactoryBean">

                                                <property name="jndiTemplate">

                                                          <ref bean="jndiTemplate" />

                                                </property>

                                                <property name="jndiName">

                                                          <value>JMS_TPriorityQueue</value>

                                                          <!-- <value>JMS_TPriorityQueueDemo</value>-->

                                                </property>

                                      </bean>

                             

                             

                                      <bean id="receiverGFXQueue"

                                                class="org.springframework.jndi.JndiObjectFactoryBean">

                                                <property name="jndiTemplate">

                                                          <ref bean="jndiTemplate" />

                                                </property>

                                                <property name="jndiName">

                                                          <value>JMS_TGFXQueue</value>

                                                          <!-- <value>JMS_TGFXQueueDemo</value>-->

                                                </property>

                                      </bean>

                             

                             

                                      <!-- JMS Queue Templates -->

                             

                             

                                      <bean id="jmsTemplateProducer"

                                                class="org.springframework.jms.core.JmsTemplate102">

                                                <property name="connectionFactory">

                                                          <ref bean="internalJmsQueueConnectionFactory" />

                                                </property>

                                                <property name="defaultDestination">

                                                          <ref bean="destinationQueue" />

                                                </property>

                                                <property name="receiveTimeout">

                                                          <value>60000</value>

                                                </property>

                                      </bean>

                                      <bean id="recieverJmsTemplateProducer"

                                                class="org.springframework.jms.core.JmsTemplate102">

                                                <property name="connectionFactory">

                                                          <ref bean="internalJmsQueueConnectionFactory" />

                                                </property>

                                                <property name="defaultDestination">

                                                          <ref bean="recieverQueue" />

                                                </property>

                                                <property name="receiveTimeout">

                                                          <value>30000</value>

                                                </property>

                                      </bean>

                                      <!--JaxB Marshaller  -->

                                      <bean id="jaxb2Marshaller"

                                                class="org.springframework.oxm.jaxb.Jaxb2Marshaller">

                                                <property name="classesToBeBound">

                                                          <list>

                                                                    <value>

                                                                              luc.LUCMessage

                                                                    </value>

                                                                    <value>

                                                                              scm.SCMMessage

                                                                    </value>

                                                                    <value>

                                                                              ucm.UCMMessage

                                                                    </value>

                                                                    <value>

                                                                              rej.REJMessage

                                                                    </value>

                                                                    <value>

                                                                              ack.ACKMessage

                                                                    </value>

                                                                    <value>

                                                                              uldfltreq.ULDFltListReq

                                                                    </value>

                                                                    <value>

                                                                              uldfltres.ULDFltListRes

                                                                    </value>

                                                                    <value>

                                                                              gfx.GFXMessage

                                                                    </value>

                                                                    <value>

                                                                              txt.TXTMessage

                                                                    </value>

                                                                    <value>

                                                                              muc.MUCMessage

                                                                    </value>

                                                          </list>

                                                </property>

                                                <property name="marshallerProperties">

                                                          <map>

                                                                    <entry key="jaxb.encoding" value="UTF-8" />

                                                          </map>

                                                </property>

                                                <property name="schema" value="classpath:FusionMessage.xsd" />

                                      </bean>

                                      <bean id="jmsTracer"

                                                class="integration.tracemessages.JMSTracerImpl">

                                                <property name="jmsLoggerFacade" ref="jmsLoggerFacade" />

                                      </bean>

                            • 12. Re: org.hibernate.exception.GenericJDBCException: Cannot open connection
                              Stephen Coy Master

                              As far as I can tell from this, your queue is being processed by at most 5 messageListeners.

                               

                              I think I would a need a thread dump in order to see what is going on.

                               

                              Can you attach a thread dump taken at a time when you know the JDBC connection pool is exhausted?

                              • 13. Re: org.hibernate.exception.GenericJDBCException: Cannot open connection
                                bruno bruno Newbie

                                Hi Stephen,

                                 

                                 

                                     i cannot send you the thread dump anymore because we switched back again to the old server and then stopped the jboss AS (we are using load balancer).

                                 

                                     In case it happens again what we should look?

                                • 14. Re: org.hibernate.exception.GenericJDBCException: Cannot open connection
                                  Stephen Coy Master

                                  Presumably there are 32 threads that each have hold of a database connection.

                                   

                                  It should be easy to pick from this whether it's the messaging system or just slow user transactions that is causing the problem.

                                   

                                  Slow or long running transactions are the nemesis of scalability. Normally I would expect 32 database connections to be able to handle 100 users or more.

                                  1 of 1 people found this helpful
                                  1 2 Previous Next