db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Inns, Jeff" <ji...@extol.com>
Subject Intermittent PreparedStatement Error
Date Thu, 14 Sep 2006 17:37:42 GMT
Derby version: 10.1.3.1 as of 08/09/2006

Platform: WinTel, RHEL on Intel, FC5 on Intel

 

Our application runs Derby in a JBoss JVM in embedded mode and also
serves remote GUI Clients in Server Mode (1 JVM, embedded and server
mode simultaneously).

 

Occasionally we get the following exception:

 

2006-09-14 12:45:03,127 INFO  [STDOUT] Caused by: SQL Exception: Invalid
character string format for type BIGINT.

2006-09-14 12:45:03,127 INFO  [STDOUT]    at
org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:195)

2006-09-14 12:45:03,127 INFO  [STDOUT]    at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Tr
ansactionResourceImpl.java:381)

2006-09-14 12:45:03,127 INFO  [STDOUT]    at
org.apache.derby.impl.jdbc.EmbedResultSet.noStateChangeException(EmbedRe
sultSet.java:3811)

2006-09-14 12:45:03,127 INFO  [STDOUT]    at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.setString(EmbedPrepare
dStatement.java:478)

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

 

By occasionally, I mean, we might get the error once in 50,000 database
hits (not consistently).  Once we get the error, the only thing that
fixes it is shutting the JVM down and restarting it.

 

I haven't been able to write a simple test case that reproduces the
problem, but I can reproduce it some what consistently inside our
application under certain conditions.

 

The snippet of code that produces the above error is:

 

PreparedStatement stmt = connection.prepareStatement(select "A", "B"
from "SCHEMA"."TABLE_A" where "C" = ? and "D" = ? ORDER BY "E");

stmt.setString(1, "some string");

stmt.setInt(2, an int);

 

I've taken the liberty to instrument the Derby code with some debugging
information and here is what is produced:

 

Exception thrown in :
org.apache.derby.impl.jdbc.EmbedPreparedStatement30

EmbedPreparedStatement.SQLText         = select "A", "B" from
"SCHEMA"."TABLE_A" where "C" = ? and "D" = ? ORDER BY "E"

preparedStatement.getSource            = update "SCHEMA"."TABLE_B" set A
= ? where B = ?

Activation.PreparedStatement.getSource = update "SCHEMA"."TABLE_B" set A
= ? where B = ?

 

DataTypeDescriptor's from preparedStatement : 2

DTD string: BIGINT

DTD string: CHAR(18)

 

I've removed the actual table and column names.  In the error that I get
in our app, there are not any similarities between the column and table
names - the above convention is just for illustrative purposes.

 

A couple of interesting things about this problem:

 

1.    The problem seems to be exacerbated when there is a lot of
simultaneous DB access through the embedded and network interfaces.  

2.    The exception is always thrown at the same point in our code (see
snippet). That exact snippet of code runs in both JVM's.

3.    The SQL in the "preparedStatement.getSource" and
"Activation.PreparedStatement.getSource" objects vary between instances
of the exception. So, for example, the first time we get the exception,
the SQL returned by the "getSource" methods may look as it does above.
The second time we get the exception, the SQL may look like this:
"select * from "SCHEMA"."TABLE_X" where "P" = ?"

 

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.

 

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.

3. All access to Derby in the Server JVM (JBoss) is done via the
embedded interface.

4. All access to Derby in the GUI JVM is done via the network interface.

5. There are about 500 records in the table in question the first time
the error occurs.

 

I can provide SQL for the table definitions if that is necessary.

 

Could this problem stem from unclosed statements or result sets?

 

Regards,

Jeff Inns

 

 


Mime
View raw message