Hibernate - Tomcat - MySQL issue

Question

Dear All,
I'm testing a Struts webApp using hibernate-mysql on tomcat7...

After the 8 hours timeout period my webApp always crashes. I've changed configurations here and there. But no success.

I really appreciate your attention...

Here some lines from hibernate.xml

 property name="hibernate.bytecode.use_reflection_optimizer">false
    property name="hibernate.c3p0.idle_test_period">30
    property name="hibernate.c3p0.max_size">600
    property name="hibernate.c3p0.max_statements">50
    property name="hibernate.c3p0.min_size">5
    property name="hibernate.c3p0.timeout">1800
    property name="hibernate.c3p0.testConnectionOnCheckout">true
    property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider
    property name="hibernate.c3p0.validate">true
    property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver
    property name="hibernate.connection.url">jdbc:mysql://localhost:3306/stockdb?autoReconnect=true

Here are some of lines from my stacktraces:

    com.mchange.v2.c3p0.impl.NewPooledConnection handleThrowable
    WARNING: [c3p0] A PooledConnection that has already signalled a Connection error is still in use!
    ...



WARNING: [c3p0] Another error has occurred [ com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed. ] which will not be reported to listeners!
...

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.



...
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1014)
...



at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.getMaxRows(NewProxyPreparedStatement.java:1200)




at org.hibernate.jdbc.AbstractBatcher.closeQueryStatement(AbstractBatcher.java:212)


...
at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:452)
...

Please help!

Tomcat has [a built-in database connection pool](http://people.apache.org/~fhanik/tomcat/jdbc-pool.html), rendering c3p0 unnecessary. — hd1, Feb 27 '13 at 04:27
awesome. that's totally new for my poor knowledge.... Thank you so much for the hint. — Dan Jay, Feb 27 '13 at 04:32
I'll put it as an answer, along with some configuration details from the page, so you can accept it in a second — hd1, Feb 27 '13 at 04:33
Sorted, good luck and let me know if you have further problems. — hd1, Feb 27 '13 at 04:41

score 2 · Accepted Answer · answered Feb 27 '13 at 04:40

Tomcat has a database connection pool that's included out of the box. To configure MySQL, for example:

<Context>

    <!-- maxActive: Maximum number of database connections in pool. Make sure you
         configure your mysqld max_connections large enough to handle
         all of your db connections. Set to -1 for no limit.
         -->

    <!-- maxIdle: Maximum number of idle database connections to retain in pool.
         Set to -1 for no limit.  See also the DBCP documentation on this
         and the minEvictableIdleTimeMillis configuration parameter.
         -->

    <!-- maxWait: Maximum time to wait for a database connection to become available
         in ms, in this example 10 seconds. An Exception is thrown if
         this timeout is exceeded.  Set to -1 to wait indefinitely.
         -->

    <!-- username and password: MySQL username and password for database connections  -->

    <!-- driverClassName: Class name for the old mm.mysql JDBC driver is
         org.gjt.mm.mysql.Driver - we recommend using Connector/J though.
         Class name for the official MySQL Connector/J driver is com.mysql.jdbc.Driver.
         -->

    <!-- url: The JDBC connection url for connecting to your MySQL database.
         -->

  <Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
               maxActive="100" maxIdle="30" maxWait="10000"
               username="javauser" password="javadude" driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/javatest"/>

</Context>

Then you'll need to configure your connection as follows:

    public static void main(String[] args) throws Exception {
        PoolProperties p = new PoolProperties();
        p.setUrl("jdbc:mysql://localhost:3306/mysql");
        p.setDriverClassName("com.mysql.jdbc.Driver");
        p.setUsername("root");
        p.setPassword("password");
        p.setJmxEnabled(true);
        p.setTestWhileIdle(false);
        p.setTestOnBorrow(true);
        p.setValidationQuery("SELECT 1");
        p.setTestOnReturn(false);
        p.setValidationInterval(30000);
        p.setTimeBetweenEvictionRunsMillis(30000);
        p.setMaxActive(100);
        p.setInitialSize(10);
        p.setMaxWait(10000);
        p.setRemoveAbandonedTimeout(60);
        p.setMinEvictableIdleTimeMillis(30000);
        p.setMinIdle(10);
        p.setLogAbandoned(true);
        p.setRemoveAbandoned(true);
 p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
        DataSource datasource = new DataSource();
        datasource.setPoolProperties(p); 

        Connection con = null;
        con = datasource.getConnection();

Now, proceed as one normally would with your JDBC code.

please tell me this... how can I combine with hibernate and struts... would it be a conflict? — Dan Jay, Feb 27 '13 at 04:50
Given that [one of the most respected Java bloggers](http://www.mkyong.com/struts/struts-hibernate-integration-example/) has a tutorial on just that, I'm guessing it would not be a conflict. — hd1, Feb 27 '13 at 04:52
Sure I'll vote you up... But my reputation is not enough for that yet. But I will vote definitely. — Dan Jay, Feb 28 '13 at 03:58
Hello again... I'm using tomcat connection pool. So far it works fine. But sometimes it gives SQLException "PooledConnection has already been closed." Have any idea why this is happening? — Dan Jay, Mar 06 '13 at 08:26
Play with the maxActive, maxAllowed, and maxWait parameters in the XML, along with the times specified in the Java code. Hope that helps. If you're still having trouble, edit your question with your code and I'll take a look at it. — hd1, Mar 06 '13 at 08:32
no luck my friend... sadly still the same error as when i've been using the c3p0 pooling — Dan Jay, Mar 15 '13 at 02:56
http://stackoverflow.com/questions/15424174/issue-with-tomcat-pooling-with-hibernate-mysql-timeout — Dan Jay, Mar 15 '13 at 03:40
Don't use c3p0 with tomcat, it has built-in database pooling. — hd1, Jul 09 '13 at 05:32

Hibernate - Tomcat - MySQL issue

1 Answers1

Linked