Issues with Hibernate 4.3.5 c3p0-0.9.1

Question

I am using Hibernate 4.3.5 I am trying to connect to Database (Oracle) And I have tried connection pooling with c3p0-0.9.1.

I have set the following Properties to enable Connection pooling with c3p0-0.9.1.

  <property  name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>         
  <property name="hibernate.c3p0.min_size">0</property>
  <property name="hibernate.c3p0.max_size">5</property>
  <property name="hibernate.c3p0.timeout">300</property>
  <property name="hibernate.c3p0.max_statements">0</property>
  <property name="hibernate.c3p0.idle_test_period">300</property>

After setting these properties, Below Information is getting logged and I am unable to connect to the database.

     09:58:39,573  INFO MLog:80 - MLog clients using log4j logging.
     09:58:39,698  INFO C3P0Registry:204 - Initializing c3p0-0.9.1 [built 16-January-2007 14:46:42; debug? true; trace: 10]
     09:58:39,854  INFO AbstractPoolBackedDataSource:462 - Initializing c3p0 pool...  com.mchange.v2.c3p0.PoolBackedDataSource@7eaf7e [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@6e3303f6 [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, debugUnreturnedConnectionStackTraces -> false, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, 
   identityToken -> 2xstr79341a29wl20dow|45ef76, idleConnectionTestPeriod -> 300,  initialPoolSize -> 5, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 100, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 5, nestedDataSource ->  com.mchange.v2.c3p0.DriverManagerDataSource@f55211aa [ description -> null, 
     driverClass -> null,  factoryClassLocation -> null, identityToken ->  2xstr79341a29wl20dow|16d0064, jdbcUrl ->  `**Driver URL**`, properties -> {user=******, password=******} ],  preferredTestQuery -> null, propertyCycle -> 0, testConnectionOnCheckin -> false,  testConnectionOnCheckout -> false,   unreturnedConnectionTimeout -> 0,  usesTraditionalReflectiveProxies -> false; userOverrides: {} ], dataSourceName -> null,     factoryClassLocation -> null, identityToken -> 2xstr79341a29wl20dow|11400c8, numHelperThreads -> 3 ]
         09:58:59,854  WARN ThreadPoolAsynchronousRunner:608 -  com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@13780ae -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!
         09:58:59,854  WARN ThreadPoolAsynchronousRunner:624 -    com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@13780ae -- APPARENT   DEADLOCK!!!     
    Complete Status: 
    Managed Threads: 3
    Active Threads: 3
    Active Tasks: 
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1e105a1 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0)
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@389632 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2)
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@10cd3b6 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1)
Pending Tasks: 
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@11f5d8c
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@36b8db
       Pool thread stack traces:
          Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0,5,main]
          java.lang.Thread.sleep(Native Method)
        com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
    com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
     Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2,5,main]
     java.lang.Thread.sleep(Native Method)
     com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
    com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
        Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1,5,main]
        java.lang.Thread.sleep(Native Method)
       com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
    com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

After This scary logging I am getting following Exception

     09:59:18,135  WARN BasicResourcePool:1841 - com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@10cd3b6 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: 
                java.sql.SQLException: ORA-01017: invalid username/password; logon denied

                       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
                       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
                       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:283)
                       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:278)
                       at oracle.jdbc.driver.T4CTTIoauthenticate.receiveOauth(T4CTTIoauthenticate.java:785)
                       at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:362)
                       at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:414)
                       at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
                       at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
                        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
                       at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:135)
                       at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
                       at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
                       at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
                       at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
                       at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
                       at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
                       at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
        HibernateLog --> 09:59:18 WARN  org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL  Error: 0, SQLState: null
       09:59:18,135  WARN SqlExceptionHelper:144 - SQL Error: 0, SQLState: null
       HibernateLog --> 09:59:18 ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Connections could not be acquired from the underlying database!
       09:59:18,135 ERROR SqlExceptionHelper:146 - Connections could not be acquired from the underlying database!
       09:59:18,135 ERROR LoginDAO:111 - GetHostAddrCould not open connection
      org.hibernate.exception.GenericJDBCException: Could not open connection
           at   org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
           at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
           at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
           at  org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:235)
           at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:171)
           at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.connection(StatementPreparerImpl.java:63)
            at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:162)
            at org .hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186)
            at  org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:160)
           at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1884)
           at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1861)
           at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838)
           at org.hibernate.loader.Loader.doQuery(Loader.java:909)
           at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
           at org.hibernate.loader.Loader.doList(Loader.java:2553)
           at org.hibernate.loader.Loader.doList(Loader.java:2539)
            at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2369)
           at org.hibernate.loader.Loader.list(Loader.java:2364)
           at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:126)
           at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1682)
           at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:380)

**Here the username and password for database is absolutely correct **

After this exception I am not able to connect to database but In my log My application is trying to connect to database because I can see same kind of logging in my console.

Following are my configuration.

        c3p0-0.9.1.jar
        Hibernate-c3p0-4.3.5.Final.jar
        Struts2 -fullhibernatecoreplugin-2.2.2-GA.jar

I am using struts2 and Hibernate 4.3.5. Can somebody help me on this. I googles alto but cant find the right and VIVID solution.

Ans I can connect to the Database and my application is running good if I dont use connection pooling with c3p0

Thanks in advance.

EDIT : ** I am configuring hibernate with mixed properties Like I am passing the username and password by the java program. **My dumb guess is that c3p0 is not finding the username and password which are being set by java program, cause I have tried this connection pooling with sample console java program Where I can connect to DB even though I am getting Logon Denied exception

Here I need to set username and password programmatically, is there any way to get rid off this ? .

for a just test can you set username and password through config file? — Amogh, Jul 07 '14 at 05:49
Please consider upgrading to c3p0-0.9.1.x is very old. The current stable version is 0.9.2.1, and that's pretty old. 0.9.5-pre8 is near final. Others comments are good re ensuring that the user and password properties are properly set. Note that you can also set these properties in a file called c3p0.propertes as `c3p0.user` and `c3p0.password`, or as System properties under those same names, which gives you options if you are trying not to check authentication information into version control. — Steve Waldman, Jul 07 '14 at 08:16

Vlad Mihalcea · Answer 1 · 2014-07-07T13:24:20.517

I'd suggest you provide your own C3P0 data source to Hibernate like so:

You create the C3P0 DataSource programatically:

@org.springframework.context.annotation.Configuration
public class C3P0Configuration {

    @Bean
    public ComboPooledDataSource dataSource() {
        ComboPooledDataSource actualDataSource = new ComboPooledDataSource();
        actualDataSource.setJdbcUrl("set-your-url");
        actualDataSource.setUser("set-your-user");
        actualDataSource.setPassword("set-your-user");
        actualDataSource.setMinPoolSize(0);
        actualDataSource.setMaxPoolSize(5);
        return actualDataSource;
    }
}

You pass the dataSource to your Hibernate SessionFactory:

<bean id="mySessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean" dependsOn="dataSource">
    <property name="dataSource" ref="dataSource"/>
    <property name="mappingResources">
        <list>
        <value>your-hibernate-config.hbm.xml</value>
        </list>
    </property>
    <property name="hibernateProperties">
        <value>
            hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
        </value>
    </property>
</bean>

or if you build the session factory programatically too, make sure you supply this property:

properties.put("hibernate.connection.datasource", dataSource);

If you don't use Spring, you would have to have access to the session factory instantiation process, because you need to build the data source prior to the session factory in order to supply it as the aforementioned Hibernate property.

OP is using a full hibernate plugin (s2hibernate) to mange hibernate session, not a spring. — Roman C, Jul 07 '14 at 10:56
The spring example is meant to better explain the solution. It may work with any other container or even with hand-made component instantiation. The only requirement is to build the data source, prior to the session factory. — Vlad Mihalcea, Jul 07 '14 at 13:26
I got your point, but your solution is not related to OP's problem (and probably doesn't solve it), unless he/she remove content related to fullhiberante plugin. — Roman C, Jul 07 '14 at 13:31
@VladMihalcea Unfortunately I am not using Spring can you try to illustrate in Struts 2. — Rookie007, Jul 08 '14 at 11:37
I've never used Struts2 hibernate plugin, so you'll have to check if you can implement this solution and ask yourself if the MVC layer should control your data layer. I personally think that's the job of a middleware layer, be it JEE or spring. Apart from being more flexible, you'll get better support for transactions, caching, dependency injection, batch processing to name a few. — Vlad Mihalcea, Jul 08 '14 at 11:54

Issues with Hibernate 4.3.5 c3p0-0.9.1

1 Answers1