I'm using hibernate with struts, on Jetty6, with Mysql 4.1.7. For some reason, even though I'm calling session.flush(), session.close() in my logic after any SQL or HSQL, hibernate does not seem to be releasing the connection. After 10 or so queries, this is what my linux box looks like when I do a ps -eaf:

mysql 9824 6184 0 10:19 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 9843 6184 0 10:19 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 9853 6184 0 10:20 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 9859 6184 0 10:20 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 9865 6184 0 10:20 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 9872 6184 0 10:20 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 9878 6184 0 10:20 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 9884 6184 0 10:20 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 9891 6184 0 10:20 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 9901 6184 0 10:20 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 9908 6184 0 10:20 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 9914 6184 0 10:20 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 9921 6184 0 10:20 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 9929 6184 0 10:20 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 9935 6184 0 10:20 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 9966 6184 0 10:21 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 9973 6184 0 10:21 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 9980 6184 0 10:21 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10012 6184 0 10:23 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10018 6184 0 10:23 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10027 6184 0 10:23 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10033 6184 0 10:23 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10040 6184 0 10:23 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10046 6184 0 10:23 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10066 6184 0 10:24 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10075 6184 0 10:25 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10082 6184 0 10:25 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10095 6184 0 10:25 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10101 6184 0 10:25 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10110 6184 0 10:25 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10116 6184 0 10:25 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10123 6184 0 10:25 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10129 6184 0 10:25 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10139 6184 0 10:25 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10145 6184 0 10:25 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10151 6184 0 10:25 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10159 6184 0 10:25 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10166 6184 0 10:26 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10172 6184 0 10:26 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10178 6184 0 10:26 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10185 6184 0 10:26 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10195 6184 0 10:26 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
mysql 10207 6184 0 10:26 pts/2 00:00:00 /main/mysql/bin/mysqld --defaults-extra-file=/main/mysql/data/my.cnf --basedir=/main/mysql --datadir=/main/mysql/data --user
root 10210 6723 0 10:26 pts/3 00:00:00 ps -eaf



This is the simple logic in my struts app:


public static List getCallQ() {

Session session = null;
List callerList = null;

try{
// This step will read hibernate.cfg.xml and prepare hibernate for use
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
session =sessionFactory.openSession();


//Using from Clause
String SQL_QUERY = DemoSQL.callQ;
Query query = session.createQuery(SQL_QUERY);
callerList = query.list();

session.flush();
session.close();

}catch(Exception e){
//System.out.println(e.getMessage());
}finally{
try {
if (session != null) {
session.flush();
session.close();
}
} catch (Exception e) {}
}

return callerList;

}


Any suggestion would be appreciated. Thank you.

Please mention the following informations:

Verson of Hibernate
Version of MySQL driver
Connection Pooler name, properties (min/max Connection)

I once faced similar problem that my pooler holds on to the connections. It happened for plain simple reason, that I did not close my connection/session. Firstly you have used finally block but put the try/catch for flush and close separately. Because flush might cause exception in that case the close will be skipped. Please also add the any exception you get regarding this. Another way you can find the connections open to the database is using

show processlist;

You probably know it.
BTW also if possible try to upgrade your MySQL server to version 5.
Any how waiting for your reply.

I'm using Mysql 5,
c3p0 Connection pooling with 1 min, 10 max,
Hibernae 3.1.3,
MySql driver MySQL Connector/J 5.0

So everything I beleive is up to date. I think I know what the problem might be from googling. It looks like I have to do a tx.commit, even after select statements/HSQL. I can't test that theory until tomorrow, but I hope it works. This is from hibernate's tutorial and it shows a commit, even after a query:


public List listAllBlogNamesAndItemCounts(int max)
throws HibernateException {

Session session = _sessions.openSession();
Transaction tx = null;
List result = null;
try {
tx = session.beginTransaction();
Query q = session.createQuery(
"select blog.id, blog.name, count(blogItem) " +
"from Blog as blog " +
"left outer join blog.items as blogItem " +
"group by blog.name, blog.id " +
"order by max(blogItem.datetime)"
);
q.setMaxResults(max);
result = q.list();
tx.commit();
}
catch (HibernateException he) {
if (tx!=null) tx.rollback();
throw he;
}
finally {
session.close();
}
return result;
}

I do not see transaction being the reason for the problem. As you know Transaction is used for entirely different purpose. Furthermore i use session all the time without transactions and session flush they work fine. So the only thing I guess is that an exception was occuring before session.close() and that was causing the connection to be left open.

Anyways let me know of the solution.

Thanks

This sucks. Nothing I'm trying is working. Everything I do it just keeps the connection open. Do you notice anything incorrect from the Initial server hibernate startup output?


- Hibernate 3.1.3
- hibernate.properties not found
- using CGLIB reflection optimizer
- using JDK 1.4 java.sql.Timestamp handling
- configuring from resource: /hibernate.cfg.xml
- Configuration resource: /hibernate.cfg.xml
- Reading mappings from resource: resources/callq.hbm.xml
- Mapping class: com.lendingcrowd.hibernate.CallQ -> lendingcrowd.callq
- Reading mappings from resource: resources/customer.hbm.xml
- Mapping class: com.lendingcrowd.hibernate.Customer -> lendingcrowd.customer
- Reading mappings from resource: resources/users.hbm.xml
- Mapping class: com.lendingcrowd.core.Account -> lendingcrowd.users
- Configured SessionFactory: null
- C3P0 using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql://localhost/lendingcrowd
- Connection properties: {user=lendingcrowd, password=****, release_mode=auto}
- autocommit mode: false
- MLog clients using log4j logging.
- Initializing c3p0-0.9.1-pre6 [built 23-March-2006 16:11:59 +0200; debug? true; trace: 10]
- Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@1b60280 [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@c62c8 [ acquireIncrement -> 2, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> c62c8, idleConnectionTestPeriod -> 300, initialPoolSize -> 0, maxIdleTime -> 1800, maxPoolSize -> 5, maxStatements -> 10, maxStatementsPerConnection -> 0, minPoolSize -> 0, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@1fa1bb6 [ description -> null, driverClass -> null, factoryClassLocation -> null, identityToken -> 1fa1bb6, jdbcUrl -> jdbc:mysql://localhost/lendingcrowd, properties -> {user=******, password=******, release_mode=auto} ], preferredTestQuery -> null, propertyCycle -> 300, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, usesTraditionalReflectiveProxies -> false; userOverrides: {} ], dataSourceName -> 1b60280, factoryClassLocation -> null, identityToken -> 1b60280, numHelperThreads -> 3 ]
- RDBMS: MySQL, version: 5.0.22-standard
- JDBC driver: MySQL-AB JDBC Driver, version: mysql-connector-java-5.0.3 ( $Date: 2006-07-26 17:26:47 +0200 (Wed, 26 Jul 2006) $, $Revision: 5553 $ )
- Using dialect: org.hibernate.dialect.MySQLDialect
- Transaction strategy: org.hibernate.transaction.JDBCTransactionFactory
- No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
- Automatic flush during beforeCompletion(): disabled
- Automatic session close at end of transaction: disabled
- JDBC batch size: 15
- JDBC batch updates for versioned data: disabled
- Scrollable result sets: enabled
- JDBC3 getGeneratedKeys(): enabled
- Connection release mode: auto
- Maximum outer join fetch depth: 3
- Default batch fetch size: 1
- Generate SQL with comments: disabled
- Order SQL updates by primary key: disabled
- Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
- Using ASTQueryTranslatorFactory
- Query language substitutions: {}
- Second-level cache: enabled
- Query cache: disabled
- Cache provider: org.hibernate.cache.NoCacheProvider
- Optimize cache for minimal puts: disabled
- Structured second-level cache entries: disabled
- Statistics: disabled
- Deleted entity synthetic identifier rollback: disabled
- Default entity-mode: pojo
- building session factory
- Not binding factory to JNDI, no JNDI name configured

For some reason I can't use any of the hibernate default setting or connection pooling. They just crash mysql and keep the connections open. As soon as I switched back to the apache commons dbcp and put this in hibernate.cfg.xml, it all works:

<!--may have to do this instead of hibernate pooling-->
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="connection.datasource">jdbc/mysqlds</property>
<property name="current_session_context_class">thread</property>
<property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
<property name="show_sql">true</property>
<property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>