[prescott.travis]

I am using the struts tools with myEclipse. I have been having a persistent problem with my connections being used when they are no longer valid. My pool seems to be set up logically and I’ve been looking at the deploy directory. It seems that when I deploy, I end up with two c3po jar files:

c3p0-0.8.5.2.jar
c3p0-0.9.0.2.jar

I don’t think that they should both be there. The problem happens after the db has been inactive for a long period of time. When it comes back, there’s a “broken pipe error”. I use hibernate and I’m not even writing any SQL so I’d be really surprised if it was in the code. I’ve tried everything I can think of with the configuration of c3po. In an earlier iteration of the site, before I implemented connection pooling and was just connecting through hibernate, I had the problem too so I may be barking up the wrong tree. At any rate, it seems like there should only be one of these jar files, right?

Travis

[Haris Peco]

Travis,

What database do you use ? It is possible that your database disconnect session after some time.

Regards,

[prescott.travis]

I’m using MYSQL 5.0. I know that the wait_timeout is 8 hours. I’ll post the config for you:

<hibernate-configuration>

    <session-factory>
        <property name="connection.url">jdbc:mysql://localhost:3306/bluecanoe?autoReconnect=true</property>
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>

        <property name="hibernate.c3p0.acquire_increment">1</property>
        <property name="hibernate.c3p0.idle_test_period">300</property> <!-- seconds (300=5min) -->
        <property name="hibernate.c3p0.max_size">20</property>
        <property name="hibernate.c3p0.max_statements">0</property>
        <property name="hibernate.c3p0.min_size">5</property>
        <property name="hibernate.c3p0.max_statements">100</property> <!-- off -->
        <property name="hibernate.c3p0.timeout">1800</property> <!-- seconds -->
        <property name="hibernate.c3p0.validate">true</property>

        <property name="connection.username">web_user</property>
        <property name="connection.password">XXXXX</property>

        <mapping resource="com/label/dao/hibernate/Artist.hbm.xml" />
        <mapping resource="com/label/dao/hibernate/Album.hbm.xml" />
        <mapping resource="com/label/dao/hibernate/Song.hbm.xml" />
        <mapping resource="com/label/dao/hibernate/ReleaseDetail.hbm.xml" />
        <mapping resource="com/label/dao/hibernate/ArtistDetail.hbm.xml" />
        <mapping resource="com/label/dao/hibernate/FeatureDetail.hbm.xml" />
        <mapping resource="com/label/dao/hibernate/PerformanceCredit.hbm.xml" />
        <mapping resource="com/label/dao/hibernate/Contact.hbm.xml" />
        <mapping resource="com/label/dao/hibernate/ContactType.hbm.xml" />
        <mapping resource="com/label/dao/hibernate/FeaturedArtist.hbm.xml" />
        <mapping resource="com/label/dao/hibernate/LatestNews.hbm.xml" />
        <mapping resource="com/label/dao/hibernate/CampaignResponse.hbm.xml" />

    </session-factory>

</hibernate-configuration>

There’s also some additional stuff I put in c3p0 for good measure. Some of the items are overridden in the hibernate-cfg

c3p0.automaticTestTable=connection_test_table
#will be overriddent by 'idle_test_period' in hib.cfg - 5 minutes is less than timeout (30 min)
c3p0.idleConnectionTestPeriod=300
#used in conjunction with idleConnectionTestPeriod (idle_test_period in hib.cfg)
c3p0.testConnectionOnCheckin=true
#will be overridden by 'timeout' in hib.cfg but just to be sure
c3p0.maxIdleTime=1800

I believe I was having the problem before ever implementing a the db pooler.

I notice that the DAO objects generated by the Hibernate plugin loaded with myEclipse is using the sessionFactory.openSession() method for all sessions. Yet, in the DAO themselves, there’s never a call to session.close() or session.flush(). Here’s a quote from hibernate’s website:

Only the Session that you obtained with sf.getCurrentSession() is flushed and closed automatically. If you decide to use sf.openSession() and manage the Session yourself, you have to flush() and close() it.

Peco, what do you think? Maybe I should try to replace the openSession() with a getCurrentSession() and see what happens.

Travis

[prescott.travis]

ouch. i tried that and it won’t execute queries on the database.

org.hibernate.HibernateException: No TransactionManagerLookup specified

To see how the connection pool is setting up, I’ll post the log statement…slightly reformatted…

Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@5facbd 
    [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@37dde9 
        [ acquireIncrement -> 1, 
          acquireRetryAttempts -> 30, 
          acquireRetryDelay -> 1000, 
          autoCommitOnClose -> false, 
          automaticTestTable -> connection_test_table, 
          breakAfterAcquireFailure -> false, 
          checkoutTimeout -> 0, 
          connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, 
          factoryClassLocation -> null, 
          forceIgnoreUnresolvedTransactions -> false, 
          idleConnectionTestPeriod -> 300, 
          initialPoolSize -> 5, 
          maxIdleTime -> 1800, 
          maxPoolSize -> 20, 
          maxStatements -> 100, 
          maxStatementsPerConnection -> 0, 
          minPoolSize -> 5, 
          nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@3744bc 
            [ description -> null, 
              driverClass -> null, 
              factoryClassLocation -> null, 
              jdbcUrl -> jdbc:mysql://localhost:3306/bluecanoe?autoReconnect=true, 
              properties -> {release_mode=on_close} 
            ] , preferredTestQuery -> null, 
                  propertyCycle -> 300, 
                  testConnectionOnCheckin -> true, 
                  testConnectionOnCheckout -> false, 
                  usesTraditionalReflectiveProxies -> false 
        ] , factoryClassLocation -> null, 
              numHelperThreads -> 3, 
              poolOwnerIdentityToken -> 5facbd 
    ] 
Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@4260ab 
    [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@156c69c 
        [ acquireIncrement -> 1, 
          acquireRetryAttempts -> 30, 
          acquireRetryDelay -> 1000, 
          autoCommitOnClose -> false, 
          automaticTestTable -> connection_test_table, 
          breakAfterAcquireFailure -> false, 
          checkoutTimeout -> 0, 
          connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, 
          factoryClassLocation -> null, 
          forceIgnoreUnresolvedTransactions -> false, 
          idleConnectionTestPeriod -> 300, 
          initialPoolSize -> 5, 
          maxIdleTime -> 1800, 
          maxPoolSize -> 20, 
          maxStatements -> 100, 
          maxStatementsPerConnection -> 0, 
          minPoolSize -> 5, 
          nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@17ad30a 
            [ description -> null, 
              driverClass -> null, 
              factoryClassLocation -> null, 
              jdbcUrl -> jdbc:mysql://localhost:3306/bluecanoe?autoReconnect=true, 
              properties -> {} 
            ] , preferredTestQuery -> null, 
                propertyCycle -> 300, 
                testConnectionOnCheckin -> true, 
                testConnectionOnCheckout -> false, 
                usesTraditionalReflectiveProxies -> false 
        ] , factoryClassLocation -> null, 
            numHelperThreads -> 3, 
            poolOwnerIdentityToken -> 4260ab 
    ] 

[Haris Peco]

Travis,

Hibernate property hibernate.c3p0.timeout is same as c3p0’s property c3p0.maxIdleTime and it mean that your idle Connection to be expired over 1800 seconds (half hour).You can set this property to 0 if you want that your connection never expire

Regards,

[prescott.travis]

I didn’t know that. Still, is that a good idea? None of the examples I’ve seen (even on Hibernate’s website as above) indicate a move like that. I thought since I was using a test table for c3p0 to hit (every 5 minutes with each connection, I think) that it would renew any kind of timeout. I’ll try the ‘zero’ timeout but I’d like to understand what is going on…I thought I understood what I needed to know about poolers…but I guess not 😉

[Haris Peco]

Travis,

It depends from you.If you want that connection never expired then you have to set timeout to 0.However, you can have dead connections if you don’t close them correctly.It is possible that you need just bigger timeout
There is some documentation on c3p page (sf.net), but not too much.

Regards,

[prescott.travis]

With regard to “closing” the connections…

The auto-generated code for the DAO objects do not close the connections. They use the “openSession” and should have to call session.close() but they do not. So, if I call a find method that they provide and I do not close it could this be causing my troubles, in your opinion? Here’s an example of the code generated:

    public Album findById( java.lang.Long id) {
        log.debug("getting Album instance with id: " + id);
        try {
            Album instance = (Album) getSession()
                    .get("com.label.dao.hibernate.Album", id);
            return instance;
        } catch (RuntimeException re) {
            log.error("get failed", re);
            throw re;
        }
    } 

Note that the getSession() calls HibernateSessionFactory.getSession() in a superclass. From what I read (and posted above), should this require a call to close()? It does the same for a save() method. Since it’s provided by the generation, I would think that it wouldn’t leave connections hanging but would like to determine what’s actually happening. I only have theories at this point.

Travis

[Haris Peco]

Travis,

You are correct.However, you can use Spring DAO and declarative transaction in Spring.

Regards,

[prescott.travis]

FYI, setting the timeout to 0 did not fix the issue. It still bombed out…I think when the wait_timeout in MySQL expired. I am rethinking the use of the Struts auto-generated data access stuff. I’ll post the results of my experimentation when I’m done though it may take me a bit of time to get to it. In the interim, if anyone has ideas I’m anxious to hear from you 🙂

Travis

[prescott.travis]

Even though the topic does not still relate to the problem discussed, I”ll post my solution in case anyone else is interested. It turns out that the tool that generates the DAO layer (hibernate plugin) created for me DAOs that used getSession() to get a Session object. I’m not sure why it doesn’t give you an option but the code that was generated for me did. I’m sure I might have told the tool to do it in some way that I don’t understand.

At any rate, when you call the factory.getSession() method you get a session that the developer must manage (session.flush(); session.close(); etc…) If you want a session that closes the session and relinquishes your connection you have to call factory.getCurrentSession(). Thus, since this doesn’t appear to be configurable at the code level, I was forced to manage the session myself (which I did not do). This rendered the DAO objects pretty useless b/c I could no longer call and expect that the call:

MyDAO.save(My obj) 

would ever relinquish control of the connection. I am, pretty sure that this is what would happen to me. My connections would get stale and eventually would allow me to use pooled resources that were dead.

My solution involved using the Spring framework to manage this for me and it no longer has this behavior. I am sure I could have altered the generated code to work properly but my system does not require me to manage the connections/sessions for transactional purposes and I did not want to complicate matters.

Anyway, maybe this is of some use to someone…

Travis

[Author]

Posts