You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Pavel Bortnovskiy <pb...@jefferies.com> on 2012/12/20 16:22:53 UTC

what do errors like these mean?

Once in a while, I see the following errors. What may cause them?

java.sql.SQLException: Container 1,329 not found.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedResultSet.closeOnTransactionError(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedResultSet.movePosition(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedResultSet.next(Unknown Source)

Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.

RE: what do errors like these mean?

Posted by Pavel Bortnovskiy <pb...@jefferies.com>.
Knut, I'm running 10.9.1.0 and I can switch to the debug version of the binaries, so that when such error occurs again (it's intermittent, rare, but it does happen), I could give you a more full stack trace. I doubt, though, that I would be able to create a standalone test program which reliably reproduces the error.

No, I don't ever run SYSCS_COMPRESS_TABLE. Never thought there is any need. The tables are all in-memory and once an application exits, there is no need to preserve the data.

Thank you for your help,
Pavel.

-----Original Message-----
From: Knut Anders Hatlen [mailto:knut.hatlen@oracle.com]
Sent: Friday, December 21, 2012 1:47 PM
To: Derby Discussion
Subject: Re: what do errors like these mean?

Pavel Bortnovskiy <pb...@jefferies.com> writes:

> Thank you, Knut, for your prompt response.
>
> It seems that my caching of Prepared Statements is causing some problems.
> In some previous responses, it was indicated that Derby is caching
> them internally anyway, so maybe a better approach for me is not to
> cache them on my side and create them anew? Most of my inserts/updates
> are done in batches, so I could create a PrepStmt before the batch and
> remove a reference to it at the end of the batch's execution.

That sounds like a case where the internal cache could do a good job. If the exact same query (identical SQL string) has been executed before, and it's still in the cache (default cache size is 100, see [1]), preparing the statement should essentially be just a lookup in a hash table. I'd expect that cost to be negligible for most batches.

[1] https://db.apache.org/derby/docs/10.9/ref/rrefproperstatementcachesize.html

> If the
> performance penalty for compilation of PrepStmt is not that great,
> then such approach may be more desirable to avoid errors in the
> production environment.

Not sure if it will avoid the errors, as Derby should invalidate both the active PreparedStatements and those in the internal cache. But there may be bugs, of course, preventing one or the other from being invalidated. Relying on the internal cache for the batches might help simplifying the application code, though.

By the way, do you have the full stack trace for the exception you saw?
The original post only included the stack where the top-level SQLException was created. There should be a StandardException nested in the SQLException telling exactly where it fell over.

And do you regularly run SYSCS_COMPRESS_TABLE on the database? That has been the most common source for these errors.

Which Derby version are you running?

Thanks,

--
Knut Anders

Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.

Re: what do errors like these mean?

Posted by Knut Anders Hatlen <kn...@oracle.com>.
Pavel Bortnovskiy <pb...@jefferies.com> writes:

> Thank you, Knut, for your prompt response.
>
> It seems that my caching of Prepared Statements is causing some problems.
> In some previous responses, it was indicated that Derby is caching
> them internally anyway, so maybe a better approach for me is not to
> cache them on my side and create them anew? Most of my inserts/updates
> are done in batches, so I could create a PrepStmt before the batch and
> remove a reference to it at the end of the batch's execution.

That sounds like a case where the internal cache could do a good job. If
the exact same query (identical SQL string) has been executed before,
and it's still in the cache (default cache size is 100, see [1]),
preparing the statement should essentially be just a lookup in a hash
table. I'd expect that cost to be negligible for most batches.

[1] https://db.apache.org/derby/docs/10.9/ref/rrefproperstatementcachesize.html

> If the
> performance penalty for compilation of PrepStmt is not that great,
> then such approach may be more desirable to avoid errors in the
> production environment.

Not sure if it will avoid the errors, as Derby should invalidate both
the active PreparedStatements and those in the internal cache. But there
may be bugs, of course, preventing one or the other from being
invalidated. Relying on the internal cache for the batches might help
simplifying the application code, though.

By the way, do you have the full stack trace for the exception you saw?
The original post only included the stack where the top-level
SQLException was created. There should be a StandardException nested in
the SQLException telling exactly where it fell over.

And do you regularly run SYSCS_COMPRESS_TABLE on the database? That has
been the most common source for these errors.

Which Derby version are you running?

Thanks,

-- 
Knut Anders

RE: what do errors like these mean?

Posted by Pavel Bortnovskiy <pb...@jefferies.com>.
I'm using an embedded driver with Derby in the in-memory only mode.
My application creates in-memory tables in Derby during its run, which are then accessed by various components through JDBC.

-----Original Message-----
From: Kristian Waagan [mailto:kristian.waagan@oracle.com]
Sent: Friday, December 21, 2012 11:38 AM
To: derby-user@db.apache.org
Subject: Re: what do errors like these mean?

On 21.12.2012 16:18, Pavel Bortnovskiy wrote:
> Thank you, Knut, for your prompt response.
>
> It seems that my caching of Prepared Statements is causing some problems.
> In some previous responses, it was indicated that Derby is caching them internally anyway, so maybe a better approach for me is not to cache them on my side and create them anew? Most of my inserts/updates are done in batches, so I could create a PrepStmt before the batch and remove a reference to it at the end of the batch's execution. If the performance penalty for compilation of PrepStmt is not that great, then such approach may be more desirable to avoid errors in the production environment.

Hi Pavel,

Are you using the embedded driver or the client driver?
The client driver can cache statements on the client side if you use the ConnectionPoolDataSource. This may save you some round-trips, but note that this caching is in addition to the caching that happens on the server side.


--
Kristian

>
> Thanks,
> P.
>
> -----Original Message-----
> From: Knut Anders Hatlen [mailto:knut.hatlen@oracle.com]
> Sent: Friday, December 21, 2012 8:59 AM
> To: Derby Discussion
> Subject: Re: what do errors like these mean?
>
> Pavel Bortnovskiy <pb...@jefferies.com> writes:
>
>> Once in a while, I see the following errors. What may cause them?
>>
>> java.sql.SQLException: Container 1,329 not found.
> The error means that one of the database files (table or index) cannot be found. It typically happens because some DDL operation (for example DROP INDEX, TRUNCATE TABLE or SYSCS_COMPRESS_TABLE) has removed the file, and an already compiled statement still references it.
>
> The error indicates a bug in Derby, so if you find a way to reproduce it, or some pattern that seems to increase the likelihood of the error, please file a bug report.
>
> Derby should invalidate already compiled statement referencing the
> table when DDL is performed on it, and that should make the statement
> recompile automatically the next time it is executed. There have been
> bugs in that area, though. (We fixed some of them in 10.9.1.0, in case
> you haven't already upgraded.)
>
> One possible workaround in that case is to call the SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE procedure to remove the stale query plans from the statement cache.
>
> --
> Knut Anders
>
> Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.


Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.

Re: what do errors like these mean?

Posted by Kristian Waagan <kr...@oracle.com>.
On 21.12.2012 16:18, Pavel Bortnovskiy wrote:
> Thank you, Knut, for your prompt response.
>
> It seems that my caching of Prepared Statements is causing some problems.
> In some previous responses, it was indicated that Derby is caching them internally anyway, so maybe a better approach for me is not to cache them on my side and create them anew? Most of my inserts/updates are done in batches, so I could create a PrepStmt before the batch and remove a reference to it at the end of the batch's execution. If the performance penalty for compilation of PrepStmt is not that great, then such approach may be more desirable to avoid errors in the production environment.

Hi Pavel,

Are you using the embedded driver or the client driver?
The client driver can cache statements on the client side if you use the 
ConnectionPoolDataSource. This may save you some round-trips, but note 
that this caching is in addition to the caching that happens on the 
server side.


-- 
Kristian

>
> Thanks,
> P.
>
> -----Original Message-----
> From: Knut Anders Hatlen [mailto:knut.hatlen@oracle.com]
> Sent: Friday, December 21, 2012 8:59 AM
> To: Derby Discussion
> Subject: Re: what do errors like these mean?
>
> Pavel Bortnovskiy <pb...@jefferies.com> writes:
>
>> Once in a while, I see the following errors. What may cause them?
>>
>> java.sql.SQLException: Container 1,329 not found.
> The error means that one of the database files (table or index) cannot be found. It typically happens because some DDL operation (for example DROP INDEX, TRUNCATE TABLE or SYSCS_COMPRESS_TABLE) has removed the file, and an already compiled statement still references it.
>
> The error indicates a bug in Derby, so if you find a way to reproduce it, or some pattern that seems to increase the likelihood of the error, please file a bug report.
>
> Derby should invalidate already compiled statement referencing the table when DDL is performed on it, and that should make the statement recompile automatically the next time it is executed. There have been bugs in that area, though. (We fixed some of them in 10.9.1.0, in case you haven't already upgraded.)
>
> One possible workaround in that case is to call the SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE procedure to remove the stale query plans from the statement cache.
>
> --
> Knut Anders
>
> Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.

RE: what do errors like these mean?

Posted by Pavel Bortnovskiy <pb...@jefferies.com>.
Thank you, Knut, for your prompt response.

It seems that my caching of Prepared Statements is causing some problems.
In some previous responses, it was indicated that Derby is caching them internally anyway, so maybe a better approach for me is not to cache them on my side and create them anew? Most of my inserts/updates are done in batches, so I could create a PrepStmt before the batch and remove a reference to it at the end of the batch's execution. If the performance penalty for compilation of PrepStmt is not that great, then such approach may be more desirable to avoid errors in the production environment.

Thanks,
P.

-----Original Message-----
From: Knut Anders Hatlen [mailto:knut.hatlen@oracle.com]
Sent: Friday, December 21, 2012 8:59 AM
To: Derby Discussion
Subject: Re: what do errors like these mean?

Pavel Bortnovskiy <pb...@jefferies.com> writes:

> Once in a while, I see the following errors. What may cause them?
>
> java.sql.SQLException: Container 1,329 not found.

The error means that one of the database files (table or index) cannot be found. It typically happens because some DDL operation (for example DROP INDEX, TRUNCATE TABLE or SYSCS_COMPRESS_TABLE) has removed the file, and an already compiled statement still references it.

The error indicates a bug in Derby, so if you find a way to reproduce it, or some pattern that seems to increase the likelihood of the error, please file a bug report.

Derby should invalidate already compiled statement referencing the table when DDL is performed on it, and that should make the statement recompile automatically the next time it is executed. There have been bugs in that area, though. (We fixed some of them in 10.9.1.0, in case you haven't already upgraded.)

One possible workaround in that case is to call the SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE procedure to remove the stale query plans from the statement cache.

--
Knut Anders

Jefferies archives and monitors outgoing and incoming e-mail. The contents of this email, including any attachments, are confidential to the ordinary user of the email address to which it was addressed. If you are not the addressee of this email you may not copy, forward, disclose or otherwise use it or any part of it in any form whatsoever. This email may be produced at the request of regulators or in connection with civil litigation. Jefferies accepts no liability for any errors or omissions arising as a result of transmission. Use by other than intended recipients is prohibited. In the United Kingdom, Jefferies operates as Jefferies International Limited; registered in England: no. 1978621; registered office: Vintners Place, 68 Upper Thames Street, London EC4V 3BJ. Jefferies International Limited is authorised and regulated by the Financial Services Authority.

Re: what do errors like these mean?

Posted by Knut Anders Hatlen <kn...@oracle.com>.
Pavel Bortnovskiy <pb...@jefferies.com> writes:

> Once in a while, I see the following errors. What may cause them?
>
> java.sql.SQLException: Container 1,329 not found.

The error means that one of the database files (table or index) cannot
be found. It typically happens because some DDL operation (for example
DROP INDEX, TRUNCATE TABLE or SYSCS_COMPRESS_TABLE) has removed the
file, and an already compiled statement still references it.

The error indicates a bug in Derby, so if you find a way to reproduce
it, or some pattern that seems to increase the likelihood of the error,
please file a bug report.

Derby should invalidate already compiled statement referencing the table
when DDL is performed on it, and that should make the statement
recompile automatically the next time it is executed. There have been
bugs in that area, though. (We fixed some of them in 10.9.1.0, in case
you haven't already upgraded.)

One possible workaround in that case is to call the
SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE procedure to remove the stale
query plans from the statement cache.

-- 
Knut Anders