DatasourceRealm connection pool exhausting

[craig a chapman]

I have DatasourceRealm configured in Tomcat 5.5.7. The user roles, passwords etc are in my database and I am using Basic authentication. So, a user tries to access a resource, prompted for credentials, log on etc. This works fine but eventually the number of maxActive connections are reached, 15 in my case when it shouldn't be. The problem seems to be that the connections are not being closed. How do the authentication connections get closed in tomcat? Is it related to the session expiry time, or is there a parameter that can be added to the datasource declaration to do this?

[eve agostini]

sorry..i cannot answer your question.. :roll:
but can i ask you how did you exactly configure your DatasourceRealm...i'm getting crazy with mine!

[Ulf Dittmer]

@Craig: Yes, that's part of the DataSource declaration. Here's an excerpt of a declaration using the Jakarta DBCP pool:

    <parameter>
      <name>factory</name>
      <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
    </parameter>
    <parameter>
      <name>connectionCacheProperties</name>
      <value>MaxLimit=5,InactivityTimeout=300</value>
    </parameter>
    <parameter>

@Eve: Are you aware of the documentation?
[ November 03, 2005: Message edited by: Ulf Dittmer ]

[craig a chapman]

Thanks Ulf, I wasn't aware of that parameter.

Eve, I have mine set up this way...

Within the Engine element in server.xml I have the realm configured like so: (These are the table/column info definitions in my database)


<Realm className="org.apache.catalina.realm.DataSourceRealm" debug="99"
dataSourceName="jdbc/tomcatusers"
userTable="users" userNameCol="user_name" userCredCol="user_pass"
userRoleTable="user_roles" roleNameCol="role_name"/>


In the GlobalNamingResources element I have my datasource properties like so: (Obviously your user name and password in the blank fields)

<Resource name="jdbc/tomcatusers"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory"
maxWait="5000"
maxActive="15"
maxIdle="2"
inactivityTimeout="300"
removeAbandoned="true"
removeAbandonedTimeout="60"
username=""
password=""
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/tomcatusers"/>

Then I have a resource link in my apps context.xml file in tomcat_home/catalina/localhost, like so:

<ResourceLink name="jdbc/tomcatusers"
global="jdbc/tomcatusers"
type="javax.sql.DataSource"/>


HTH

[Ben Souther]

Are you closing your connection from your Java code?
If not, you should be.
Closing them returns them to the pool for re-use.

[craig a chapman]

But I don't create the connections within my code. It's going on behind the scenes in Tomcat. It authenticates users based on the user roles etc within my database and obtains a connection to the database to do that, from the data source declaration.

[craig a chapman]

The "InactivityTimeout" parameter doesn't appear to work. I'm monitoring the connections through MySQL administrator. I would expect the number of connections to decrease with inactivity but they all remain open. I've only seen that parameter mentioned when setting up DataSource pools with Orion, with a quick search in google.

[Ben Souther]

connection.close() is what returns them to the pool.

Have a look at the dbcp examples.
In each of them the resultSets, statements, and connections are explicitly closed.
http://cvs.apache.org/viewcvs.cgi/jakarta-commons/dbcp/doc/BasicDataSourceExample.java?rev=1.2&view=markup

Not doing so is one of the more common causes of memory leaks in servlet apps.
[ November 03, 2005: Message edited by: Ben Souther ]

[eve agostini]

thank you craig!!
now it works!
merci beacoup!!grazie grazie!

[craig a chapman]

I know that closing db connections returns them to the pool and anywhere in my code that I am finished with an open connection, I do so. I am aware of the consequences of not closing connections, result sets etc.

But, the authentication is performed by Tomcat. It uses the DataSourceRealm settings to obtain a connection to the database to authenticate users. I don't explicitly open a connection anywhere in my code to do this, so I cannot explicitly close the connection. Surely theres a parameter, such as the one Ulf mentioned that tells the pool to close any connections that have not been utilised in a specified time, thus returning them to the pool. If you can configure Tomcat to open a connection to the database to authenticate users, there must be a corresponding close option, I would have thought.