JBoss 6 CachedConnectionManager] Closing a connection for you

[gerry.matte]

I am attempting to implement a simple jsp application as a war in a jboss 6.0.0.Final server.  I have defined a jboss mysql datasource in the deploy folder.

In my jsp application, I've created a startup servlet which which does a JNDI lookup for the datasource and saves it as a static property. 

Later, a jsp page uses a session scoped javabean which retrieves the datasource from the startup servlet, opens a connection, and executes a jdbc query returning a valid resultset. 

The jsp page displays correctly and I see a valid result indicating the jdbc query succeeded without errors.

However, the jboss log contains an unexpected Exception that I've never seen before:

17:19:43,781 INFO  [org.jboss.resource.connectionmanager.CachedConnectionManager] Closing a connection for you.  Please close them yourself: org.jboss.resource.adapter.jdbc.jdk6.WrappedConnectionJDK6@65c3402b: java.lang.Throwable: STACKTRACE

          at org.jboss.resource.connectionmanager.CachedConnectionManager.registerConnection(CachedConnectionManager.java:278) [:6.0.0.Final]

          at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:524) [:6.0.0.Final]

          at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:941) [:6.0.0.Final]

          at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:89) [:6.0.0.Final]

          at ca.gerrymatte.webutils.mailserver.MySessionBean.<init>(MySessionBean.java:39)

I am not using transactions so I configured the datasource type to be  <no-tx-datasource> but that seems to be irrelevant.

I see that this particular Exception has cropped up many times in these forums but there does not seem to be a resolution.

Does anyone have any suggestions ?

I've attached the datasource definition, my session bean, and the startup servlet for reference.

Thanks in advance

Gerry

[wdfink]

The friendly JBoss avoid a connectio leak for you.

You get a connection in your <init> (constructor) method but you did not close it!

After the containter get back control there is a check which log such stacktrace and close the connection for you (it is not really closed, but put back to pool).

You should close ResultSet, Statement and Connection in a finally block of your code to avoid problems.

[gerry.matte]

Thanks for the suggestion.  I appreciate that you looked over the code.

At Line 105 of the init() method, there is already a close for the ResultSet and the connection and they presumably execute because the log statements at line 107 and 108 generate the expected output:

09:09:57,702 INFO  [TomcatDeployment] deploy, ctxPath=/webUtils

09:09:57,726 INFO  [[/webUtils]] DataSourceServlet: Initialising DataSourceServlet.

09:09:58,056 INFO  [[/webUtils]] DataSourceServlet: A JDBC Connection was established from DataSource java:jdbc/mailenableDatasource.

09:09:58,056 INFO  [[/webUtils]] DataSourceServlet: 7 post offices were found in the mail server database.

09:09:58,105 INFO  [[/webUtils]] DataSourceServlet: A JavaMail Session was obtained from JNDI name java:Mail.

09:09:58,186 INFO  [BeanInstantiatorDeployerBase] Installed org.jboss.ejb3.instantiator.impl.Ejb31SpecBeanInstantiator@5650b841 into MC at org.jboss.ejb.bean.instantiator/inv-ear/jboss-seam/EjbSynchronizations

It may well be that I've made an elementary mistake like that.  It's been a while since I've gone back to the basics like this.

[wdfink]

You are wrong

Not the DataSourceServlet, but you should close the Statement as well and use a finally block (best practice) because you might add code that throw a RuntimeException

If you look at the stacktrace

>>>> at ca.gerrymatte.webutils.mailserver.MySessionBean.<init>(MySessionBean.java:39)

and now check your SessionBean at line 39

[gerry.matte]

Ah ....  I understand what you are saying now.

The exception is being thrown at line 39 of the MySessionBean.

That is second time that a connection is opened using the Datasource - the first time is in the DataSourceServlet at line 94 in DataSourceServlet and that connection is closed line 105.

I agree that connections should be normally closed as part of a finally clause (Best Practice).

However, the reason I created a MySessionBean is to be able to use a session scoped bean to cache the JDBC connection, JDBC statement, etc over multiple JSP pages.  I deliberately do not close the open connection until the MySessionBean is unbound from the session (Line 115).

[gerry.matte]

To prove that the JBoss DataSource is responsible for this Exception being thrown, I decided to replace the statement at Line 78 of DataSourceServlet with new code that would create a DataSource directly:

//                              MAIL_DATASOURCE =  (DataSource) ctx.lookup(JNDI_MAIL_DATASOURCE_NAME);

                        com.mysql.jdbc.jdbc2.optional.MysqlDataSource ds = new com.mysql.jdbc.jdbc2.optional.MysqlDataSource();

                              ds.setUrl("jdbc:mysql://localhost/mailenable");

                              ds.setUser("xxx");

                              ds.setPassword("yyyy");

                              MAIL_DATASOURCE = ds;

The top (commented line) causes an exception to be thrown by the CachedConnectionManager when the second connection is obtained at Line 39 of MySessionBean.

When I instead use the lines shown above to create the DataSource using the MySql driver, no Exception is thrown.

I have therefore concluded that the problem is either a JBoss 6 bug or there is some oddity in how I configured the DataSource using mailenable-ds.xml

[wdfink]

I disaggree, this will not be a bug.

Because if you get a connection from the container pool with a stateless method (servlet or EJB stateless bean) and you leave the context the connection must be closed to avoid leaking.

Hold a connection is not stateless.

The connection pool do the work for you, if you open a transaction the connection providing will give you always the connection related to the Tx.

Also the connection is not really closed if you call close(), the pool decide how to handle e.g. in case of Tx.

A good practice is to use the connection out of the pool, I measure that there is normally no benefit if you open by your self, but some drawbacks like leaking, additional hidden connections to the db (resorce problems) ...

[gerry.matte]

Thanks for staying with this thread.  Further testing has convinced me that you are correct. 

JBoss is enforcing the best practice of always closing JDBC artifacts immediately after using them.

And since the JBoss DataSource provides pooled connections there's not a great performance penalty to follow the best practice.  Just more statements in my code ..... an acceptable price for better logic.

Thanks.

Gerry