db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Inns, Jeff" <ji...@extol.com>
Subject RE: Intermittent PreparedStatement Error
Date Fri, 15 Sep 2006 18:58:50 GMT
Daniel,
	Setting the property
"derby.language.stalePlanCheckInterval=10000000" appears to have fixed
the problem.  

To address your questions:

Q. Is this your prepared statement cache or JBOSS's? Might be
interesting
to see if the bug occurs with that removed.

A. This is our Connection cache.  We actually get other errors, if we
don't use our cache (because of the sheer number of times we open and
close connections).

Q. Is this the com.extol.util.jdbc.CachePreparedStatement code above?
I assume from the naming it is caching statement objects as well.

A.  Our cache only cache's Connections, not PreparedStatements.  The
purpose of our connection cache is to leave the DB Connections open by
intercepting the Connection.close() method to prevent the close() method
from being called on the Derby Connection object.  We have a pool of
open connections that are used clients in our application - connections
are swapped in and out of the pool.

Is setting this property as you have suggested a viable long term
solution?

Regards,
Jeff


-----Original Message-----
From: Daniel John Debrunner [mailto:djd@apache.org] 
Sent: Thursday, September 14, 2006 3:04 PM
To: Derby Discussion
Subject: Re: Intermittent PreparedStatement Error

Inns, Jeff wrote:

> 2006-09-14 12:45:03,127 INFO  [STDOUT]    at
>
com.extol.util.jdbc.CachePreparedStatement.setString(CachePreparedStatem
> ent.java:469)

Is this your prepared statement cache or JBOSS's? Might be interesting
to see if the bug occurs with that removed.


> We have found a work around.  If we set the system property
> "derby.language.statementCacheSize" to a value of "1", we never get
the
> exception.  We have tried setting the property to "1000", and it helps
> in that we don't get the error as frequently, but it still happens
> eventually.

This does make it seem like a Derby bug. But I'd still be interested in
a run without the com.extol prepared statement cache.

> A couple of interesting things about our application:
> 
>  
> 
> 1. We don't use Hibernate, CMP or JDO.
> 
> 2. We have an in house framework for caching SQL connections - at a
> maximum we could have 16 open connections to the database per JVM.
> Practically, we don't usually see more than 10 or 15 (1 remote JVM and
1
> embedded JVM). Theoretically we could have 32.

Is this the com.extol.util.jdbc.CachePreparedStatement code above?
I assume from the naming it is caching statement objects as well.


> I can provide SQL for the table definitions if that is necessary.
> 
>  
> 
> Could this problem stem from unclosed statements or result sets?

I don't see how, but who knows. :-)

Can you try with derby.language.statementCacheSize unset (ie default)
but set this property?

derby.language.stalePlanCheckInterval=10000000

This stops the compiled plan within Derby checking itself to see if it
is out of date so frequently. Just another possibility that could come
into play with the statement cache.

Thanks,
Dan.


Mime
View raw message