Hi,
I'm developing web application which is running on Tomcat 4.1.24.
Web application developed with Struts, Hibernate.
Database: MySQL 4.0.12
Operating system : Linux 9

While running the application some times it will throws "org.hibernate.exception.GenericJDBCException: Cannot open connection" and "Caused by: java.sql.SQLException: Server configuration denies access to data source" exceptions.

Then I looked at the number of mysql processes running in the MySQL machne ( ps -ef |grep mysql|wc -l) It shows 113. After restating the tomcat, number of processes become 13. (Maximum connections in MySQL have default value of 100).

After restarting the tomcat I can do the operations of the web app untill it reaches 113. After that it will throws the above exceptions.

Appreciate your any help.
Thank you.
Thilina.

In trawling the Search facility on the forum I have a very similar problem too. I suspect your problem is the max limit on MySQL as you say. My problem is the DBCP limit I setup.

The underlying question I would like to ask is what causes so many active connections to be need ? In my situation I am developing and my application needs at most 2 active connections for the testing i am doing.

So I am expecting the connections to be returned to the DBCP pool and new connections to only be opened when the pool is empty and the maxActive limit says more can be allocated.

I am using an "Open Session In View" pattern with a Servlet Filter that is calling Session.close().


I am using:

Hibernate 3.0.5
Tomcat 5.5.9
MySQL 4.0.13 server and mysql-connector-java-3.1.10


I have my DBCP setup on JNDI under tomcat with the following context:

<Resource name="jdbc/label" auth="Container" scope="Shareable"
type="javax.sql.DataSource"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
url="jdbc:mysql://127.0.0.1:3306/labe"
driverClassName="com.mysql.jdbc.Driver" username="generic"
password="generic" maxWait="3000" maxIdle="180" maxActive="10" />

Notice the maxActive="10", this should be fine for my usage.

Okay I have gotten to the bottom of my problem, in the filter I have code like:



It seems that when I call Session.commit() this sets the private boolean Session.closed=true but does not actually do a real session close.

What does isOpen() mean, documentation suggests my usage is correct, it relates to the state of the session not transaction.

So my filter does not call session.close() and the DB handle is not returned to the pool.



As a second question is it valid to issue read only data operations, and lazy loading after I call commit(). The commit() is called at the end of the controller part of MVC, this is before the view is rendered. I'm using open session in view. I could defer the commit until the filter, but then I couldn't indicate a commit() error to the user for his operation.

So it it valid to do:

open();

get();
saveOrUpdate()
commit();

get();
# Lazy loading here... no write operations
close();

Back to top, bad form I know, but if no comment within 24 hours I'll raise a JIRA ticket on the issue, before my vacation time :) hooray.


"Transaction.commit() incorrectly causes Session.closed=true"


More notes on my setup:

* Using default transaction strategy (direct JDBC transactions)
* No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
* Second-level cache: disabled

No you won't.

JIRA is for bugs in Hibernate, not your personal support portal.

Thats an intertesting point of view there gavin, your comment implies there is no bug in hibernate on this issue; but does not back it up with documentation or insight.

Maybe you can point out how this is a support issue not a bug ?


From what I read of the documentation:




I did not call Session.close(); And whatever did call close did not properly call Session.close() so the database handle does not get released.

This is not a bug ?


I raise this issue here for the wider audience to contribute useful reasoning to the problem. If there is anyone with something useful to contribute please fire away.

Okay update.. Traced through Hibernate source to find that by default under my environment Session.autoCloseSession is true. I have now inhibited this so my Servlet Filter can do this work.

hibernate.transaction.auto_close_session=false


Now I'm still getting DBCP exhaustion problems. But too soon to say if its, application not releasing, or hibernate not giving them back to dbcp, or tomcat dbcp issues.

I've traced execution back into Tomcat's DBCP.

I had originally thought that DBCP maxIdle="180" was the amount of time to keep a pooled connection open, but its the maximum number of idle connections to keep around at any one time.

By simply using as many default DBCP settings as I can (by removing those I've added), now yeilds a 4 connection limit before exhaustion.

Off to find out which project stands up to for that DBCP implementation, it appears Tomcat re-package Apache Commons DBCP internally, and the Tomcat website does not seem to support a source distribution of their (TC's) version nor can I spot any build process to transform an original Apache Commons DBCP package into the TC one.


It would be nice to be able to dump out all properties values (including implicit defaults) from a given runtime config so I can thwart next time Hibernate tries to second guess what I'm doing.


So it appears Gavin's hint at no problem within Hibernate was both useful and well founded.