You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@avalon.apache.org by Annette Doyle <an...@calebtech.com> on 2002/02/19 21:16:05 UTC
Bug in AbstractJdbcConnection class - not closing result set from test
After running the excalibur 4.1 pooling for some time with tomcat, Oracle started giving the "ORA-01000: maximum open cursors exceeded" error. I had not changed anything with Oracle or my web application, except for replacing my modified excalibur 4.0 with new excalibur 4.1. After looking at the 4.1 source code, I found that isClosed() method in AbstractJdbcConnection class did not close the result set after executing the test statement. I had fixed this in the my modified version of excalibur 4.0 JdbcConnection class. ( I had fixed this to track down another bug I sent in a while back for class JdbcConnection in excalibur 4.0).
Excalibur 4.1 code:
public boolean isClosed()
throws SQLException
{
if ( m_connection.isClosed())
{
return true;
}
long age = System.currentTimeMillis() - m_lastUsed;
if ( age > 1000*60*60 ) // over an hour?
{
this.dispose();
return true;
}
if (m_testStatement != null && age > (5*1000)) // over 5 seconds ago
{
if (getLogger().isDebugEnabled())
{
getLogger().debug("Pinging database after " + age + "ms of inactivity.");
}
try
{
m_testStatement.executeQuery();
}
catch (final SQLException se)
{
this.dispose();
return true;
}
}
return false;
}
My old modified JdbcConnection class from excalibur 4.0:
public final boolean isClosed()
throws SQLException
{
if ( m_connection.isClosed())
{
System.out.println("connection closed");
return true;
}
if ( this.m_num_uses <= 0 )
{
this.dispose();
System.out.println("num uses=0");
return true;
}
if (m_test_statement != null)
{
try
{
java.sql.ResultSet rs = m_test_statement.executeQuery();
rs.close();
}
catch (final SQLException se)
{
this.dispose();
System.out.println("sql test failed");
return true;
}
}
return false;
}
Annette Doyle
CALEB Technologies Corp.
annette.doyle@calebtech.com
512.617.2212
"The information contained in this message is confidential, and is intended only for the use of the individual(s) named above. If the reader of this message is not the intended recipient, you are hereby notified that any copying, disclosure or distribution to others, or other use of this message in any form is strictly prohibited. If you have received this message in error or are not the intended recipient, please notify the sender and delete this message from your system."
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Bug in AbstractJdbcConnection class - not closing result set from test
Posted by Leif Mortenson <le...@silveregg.co.jp>.
Leif Mortenson wrote:
>
> In the AbstractJdbcConnection a single statement is being opened and
> kept around for the Keep Alive feature. Each time the database is
> pinged, the same statement is reused to execute query. According to
> the Javadocs, this should be closing any previous ResultSets. So
> this shouldn't have been a problem. Is this a known place where
> Oracle does not conform to the JDBC standard? We only use MySQL,
> PostgreSQL and HSQL and they all seem fine.
According to this page:
http://www.cs.umbc.edu/help/oracle8/java.815/a64685/tips3.htm
---
Memory Leaks and Running Out of Cursors
If you receive messages that you are running out of cursors or that you
are running out of memory, make sure that all of your Statement and
ResultSet objects are explicitly closed. The Oracle JDBC drivers do not
have finalizer methods; they perform cleanup routines by using the
close() method of the ResultSet and Statement classes. If you do not
explicitly close your result set and statement objects, serious memory
leaks could occur. You could also run out of cursors in the database.
Closing a result set or statement releases the corresponding cursor in
the database.
Similarly, you must explicitly close Connection objects to avoid leaks
and running out of cursors on the server side. When you close the
connection, the JDBC driver closes any open statement objects associated
with it, thus releasing the cursor objects on the servers side.
---
Oracle JDBC drivers do NOT seem to be following the JDBC spec. So It
looks like all Statements and ResultSets do need to be closed?
:-/
Leif
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Bug in AbstractJdbcConnection class - not closing result set from test
Posted by Peter Royal <pr...@managingpartners.com>.
On Tuesday 19 February 2002 08:54 pm, Leif Mortenson wrote:
> try
> {
> Connection conn = getConnection();
> try
> {
> Statement stmt = conn.createStatement();
> ResultSet rs = stmt.executeQuery(query);
> // Do something with the ResultSet
> }
> finally
> {
> conn.close();
> }
> }
> catch (SQLException e)
> {
> m_logger.error("Error", e);
> }
> ---
I generally do:
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = getConnection();
st = con.createStatement();
rs = st.executeQuery(sql);
//blah blah
} catch (SQLExeption e) {
getLogger().error("Exception: " + sql, e);
} finally {
try { if (rs != null) rs.close } catch (SQLException e) {}
try { if (st != null) st.close } catch (SQLException e) {}
try { if (con != null) con.close } catch (SQLException e) {}
}
Still closing everything but somewhat terse in syntax.
-pete
--
peter royal -> proyal@managingpartners.com
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Bug in AbstractJdbcConnection class - not closing result set from test
Posted by Leif Mortenson <le...@silveregg.co.jp>.
Berin Loritsch wrote:
> Annette Doyle wrote:
>
>> After running the excalibur 4.1 pooling for some time with tomcat,
>> Oracle started giving the "ORA-01000: maximum open cursors exceeded"
>> error. I had not changed anything with Oracle or my web application,
>> except for replacing my modified excalibur 4.0 with new excalibur
>> 4.1. After looking at the 4.1 source code, I found that isClosed()
>> method in AbstractJdbcConnection class did not close the result set
>> after executing the test statement. I had fixed this in the my
>> modified version of excalibur 4.0 JdbcConnection class. ( I had fixed
>> this to track down another bug I sent in a while back for class
>> JdbcConnection in excalibur 4.0).
>
> Thank you for bringing it to our attention, it is now fixed in CVS.
I did this, but according Sun's javadocs this should not have been a
problem:
From the java.sql.ResultSet.close() javadocs:
---
Releases this ResultSet object's database and JDBC resources immediately
instead of waiting for this to happen when it is automatically closed.
Note: A ResultSet object is automatically closed by the Statement object
that generated it when that Statement object is closed, re-executed, or
is used to retrieve the next result from a sequence of multiple results.
A ResultSet object is also automatically closed when it is garbage
collected.
---
In the AbstractJdbcConnection a single statement is being opened and
kept around for the Keep Alive feature. Each time the database is
pinged, the same statement is reused to execute query. According to the
Javadocs, this should be closing any previous ResultSets. So this
shouldn't have been a problem. Is this a known place where Oracle does
not conform to the JDBC standard? We only use MySQL, PostgreSQL and
HSQL and they all seem fine.
Likewise, Statements are supposed to be automatically closed along with
their last ResultSet when they are garbage collected.
For this reason, to keep the code clean, we never close ResultSets or
Statements unless there is some immediate need to save memory or something.
Code that looks like this:
---
try
{
Connection conn = getConnection();
try
{
Statement stmt = conn.createStatement();
try
{
ResultSet rs = stmt.executeQuery(query);
try
{
// Do something with the ResultSet
}
finally
{
rs.close();
}
}
finally
{
stmt.close();
}
}
finally
{
conn.close();
}
}
catch (SQLException e)
{
m_logger.error("Error", e);
}
---
Can be reduced to much simpler code that looks like this:
---
try
{
Connection conn = getConnection();
try
{
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
// Do something with the ResultSet
}
finally
{
conn.close();
}
}
catch (SQLException e)
{
m_logger.error("Error", e);
}
---
What standards do you usually follow when dealing with ResultSets and
Statements? PreparedStatements will cause errors in MySQL drivers if
they are closed. The driver keeps them around to be reused. So we
never close them either.
Thanks,
Leif
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Bug in AbstractJdbcConnection class - not closing result set from test
Posted by Berin Loritsch <bl...@apache.org>.
Annette Doyle wrote:
> After running the excalibur 4.1 pooling for some time with tomcat, Oracle started giving the "ORA-01000: maximum open cursors exceeded" error. I had not changed anything with Oracle or my web application, except for replacing my modified excalibur 4.0 with new excalibur 4.1. After looking at the 4.1 source code, I found that isClosed() method in AbstractJdbcConnection class did not close the result set after executing the test statement. I had fixed this in the my modified version of excalibur 4.0 JdbcConnection class. ( I had fixed this to track down another bug I sent in a while back for class JdbcConnection in excalibur 4.0).
>
Thank you for bringing it to our attention, it is now fixed in CVS.
--
"They that give up essential liberty to obtain a little temporary safety
deserve neither liberty nor safety."
- Benjamin Franklin
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>