Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <354659157.1259672540672.JavaMail.jira@brutus>
Date: Tue, 1 Dec 2009 13:02:20 +0000 (UTC)
From: "Kristian Waagan (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-4455) Prepared statement failure with
 CLOB: Stream has already been read and end-of-file reached and cannot be
 re-used.
In-Reply-To: <527960134.1259589320629.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/DERBY-4455?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12784214#action_12784214 ] 

Kristian Waagan commented on DERBY-4455:
----------------------------------------

Hi Brett,

Answering your questions requires some more investigation, and I don't have the cycles right now. I'll try to get back to them later.

I can give some information right away though.
Brett> ... [a] Does this involve a "real" preparation of a statement? [b] And therefore does this behavior defeat the purpose of a PreparedStatement cache (either external or internal)? 
a) In a way it does on the server side, yes. However, the server has a statement cache of its own, across connections.
b) Depends on what you mean. It helps a lot when using the client driver, because you don't have to go across the network as much. My experience comes from using a ClientConnectionPoolDataSource with the internal cache. I don't know how the internal cache compares to an external cache in terms of performance.
   On the server side, you still benefit from the cache (on the client side), because the "connection change" I mentioned doesn't happen for every operation (I'll need to investigate to figure out what causes this perceived change of state). If the server has to re-prepare the statement all the time, it will put unnecessary strain on the server side statement cache - and it may become a bottleneck. I don't remember having seen this as a problem earlier, but then I haven't been running benchmarks in an XA environment. We now also have session state piggy-backing (schema name and isolation level), which was required to implement the client side JDBC prepared statement cache.

Brett> ... Given that the statement cache is per-connection, and that the connection is re-used (in this case), why does Derby require a "re-prepare" and copy of arguments?
I can't answer this properly, but my theory is that the embedded connection on the server side changes. Again, I'll have to confirm this, and figure out why it happens if the theory is correct. There might be an XA specific reason for this, some kind of mismatch between the client and embedded connection, or perhaps due to a side-effect of some internal mechanism.


Two questions for you:
 1) Have you explicitly turned on connection validation using VALUES 1, or is this something Bitronix does by default?
 2) Are you having performance issues with your application?

And thanks for providing the runnable repro and for taking the patch for a spin.

> Prepared statement failure with CLOB: Stream has already been read and end-of-file reached and cannot be re-used.
> -----------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4455
>                 URL: https://issues.apache.org/jira/browse/DERBY-4455
>             Project: Derby
>          Issue Type: Bug
>          Components: Network Server
>    Affects Versions: 10.5.3.0
>         Environment: Mac OS X 10.6.2, Java 6, Bitronix JTA
>            Reporter: Brett Wooldridge
>         Attachments: derby-4455-1a.diff, derby-4455-1b.diff, DerbyFailure.zip
>
>
> Possibly related to #4332?
> We have encountered an error when using Prepared Statements and CLOBs.  I have read:
> http://db.apache.org/derby/papers/JDBCImplementation.html#setAsciiStream%2CsetBinaryStream%2CsetCharacterStream
> But it does not seem applicable, as we are not re-using a stream.
> The environment is this:
> 1. Java 6
> 2. Derby 10.5.3.0
> 3. Bitronix JTA 1.3.3
> We're actually using Hibernate, but I eliminated it from the equation (and the problem persists).
> A summary of the failure flow is this:
> 1. Start a transaction
> 2. Obtain a connection from a pool of connections (for this test, the pool size is pinned at 1)
> 3. Prepare a statement that inserts a CLOB.
> 4. Set the parameters
> 5. Add the prepared statement to a batch (but we only batch 1 -- this is to emulate what hibernate is doing as closely as possible).
> 6. Execute the batch.
> Everything up to this point works.
> 7. Repeat steps 1-6.  But this time, the connection will be reused from the pool, and the statement will be gotten from a prepared statement cache (maintained by bitronix).  I.e. the prepared statement is re-used.
> 8. Observe the following failure:
> org.apache.derby.client.am.BatchUpdateException: Non-atomic batch failure.  The batch was submitted, but at least one exception occurred on an individual member of the batch. Use getNextException() to retrieve the exceptions for specific batched elements.
> 	at org.apache.derby.client.am.Agent.endBatchedReadChain(Unknown Source)
> 	at org.apache.derby.client.am.PreparedStatement.executeBatchRequestX(Unknown Source)
> 	at org.apache.derby.client.am.PreparedStatement.executeBatchX(Unknown Source)
> 	at org.apache.derby.client.am.PreparedStatement.executeBatch(Unknown Source)
> 	at bitronix.tm.resource.jdbc.JdbcPreparedStatementHandle.executeBatch(JdbcPreparedStatementHandle.java:248)
> 	at org.dancernetworks.TestFailure.doInsert(TestFailure.java:134)
> 	at org.dancernetworks.TestFailure.doPrepared(TestFailure.java:110)
> 	at org.dancernetworks.TestFailure.main(TestFailure.java:55)
> Nov 30, 2009 10:29:31 PM bitronix.tm.BitronixTransactionManager shutdown
> INFO: shutting down Bitronix Transaction Manager
> An IOException was thrown when reading a 'java.sql.String' from an InputStream.
> java.sql.SQLException: An IOException was thrown when reading a 'java.sql.String' from an InputStream.
> 	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.Util.seeNextException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedResultSet.noStateChangeException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.EmbedPreparedStatement.transferParameters(Unknown Source)
> 	at org.apache.derby.jdbc.XAStatementControl.getRealPreparedStatement(Unknown Source)
> 	at org.apache.derby.iapi.jdbc.BrokeredPreparedStatement.getPreparedStatement(Unknown Source)
> 	at org.apache.derby.iapi.jdbc.BrokeredPreparedStatement.getStatement(Unknown Source)
> 	at org.apache.derby.iapi.jdbc.BrokeredStatement.close(Unknown Source)
> 	at org.apache.derby.impl.drda.DRDAStatement.close(Unknown Source)
> 	at org.apache.derby.impl.drda.Database.close(Unknown Source)
> 	at org.apache.derby.impl.drda.Session.close(Unknown Source)
> 	at org.apache.derby.impl.drda.DRDAConnThread.closeSession(Unknown Source)
> 	at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)
> Caused by: java.sql.SQLException: An IOException was thrown when reading a 'java.sql.String' from an InputStream.
> 	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> 	... 15 more
> Caused by: java.sql.SQLException: Java exception: 'Stream has already been read and end-of-file reached and cannot be re-used.: java.io.EOFException'.
> 	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> 	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source)
> 	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> 	... 12 more
> Caused by: java.io.EOFException: Stream has already been read and end-of-file reached and cannot be re-used.
> 	at org.apache.derby.iapi.types.ReaderToUTF8Stream.read(Unknown Source)
> 	at java.io.DataInputStream.readUnsignedShort(DataInputStream.java:320)
> 	at org.apache.derby.iapi.types.SQLChar.readExternal(Unknown Source)
> 	at org.apache.derby.iapi.types.SQLChar.getString(Unknown Source)
> 	at org.apache.derby.iapi.types.SQLChar.setFrom(Unknown Source)
> 	at org.apache.derby.iapi.types.DataType.setValue(Unknown Source)
> 	at org.apache.derby.impl.sql.GenericParameterValueSet.transferDataValues(Unknown Source)
> 	at org.apache.derby.impl.sql.execute.BaseActivation.setParameters(Unknown Source)
> 	at org.apache.derby.impl.sql.GenericActivationHolder.setParameters(Unknown Source)
> 	... 10 more
> Attached is an archived Eclipse project of a self-contained reproduction.  It includes everything needed to run, including the Bitronix 1.3.3 jar.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.