db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tony Brusseau (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (DERBY-5881) Limitation: Record of a btree secondary index cannot be updated or inserted due to lack of space on the page.
Date Fri, 27 Jul 2012 20:35:33 GMT

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

Tony Brusseau edited comment on DERBY-5881 at 7/27/12 8:33 PM:
---------------------------------------------------------------

Lowering the priority and urgency since this turned out to be a problem due to inappropriate
key size for an index. However, I still think a number of improvements to help avoid this
issue in the future could be made.

1. The comment:
   "Exception: java.sql.BatchUpdateException: Limitation: Record of a btree secondary index
cannot be updated or inserted due to
    lack of space on the page. Use the parameters derby.storage.pageSize and/or derby.storage.pageReservedSpace
to 
    work around this limitation.
    Error Code: 20000"
   should be updated to:
   a indicate that the problem could be due to a key which is too large
   b.indicate that the page size be *increased* and reserved page size be *decreased*.
   c.indicate the index, table and columns involved

2. The system should error (or possibly warn) when an index is *created* such that the key
could possibly be too large to fit for the given page size and reserved page size (ie more
that .5x the size of page size - reserved size). It is vastly preferable to get an error at
database design time, rather than let the user encounter an error later on because they entered
a string which is one character over the limit of some internal database magic number.

3. The documentation for sized datatypes like char and varchar should be updated to warn about
indexing limitations (which would most likely be encounterd with such datatypes).




                
      was (Author: apb):
    Lowering the priority and urgency since this turned out to be a problem due to inappropriate
key size for an index. However, I still think a number of improvements to help avoid this
issue in the future could be made.

1. The comment:
   "Exception: java.sql.BatchUpdateException: Limitation: Record of a btree secondary index
cannot be updated or inserted due to
    lack of space on the page. Use the parameters derby.storage.pageSize and/or derby.storage.pageReservedSpace
to 
    work around this limitation.
    Error Code: 20000"
   should be updated to:
   a indicate that the problem could be due to a key which is too large
   b.indicate that the page size be *increased* and reserved page size be *decreased*.
   c.indicate the index, table and columns involved

2. The system should error (or possibly warn) when an index is *created* such that the key
could possibly be too large to fit for the given page size and reserved page size (ie more
that .5x the size of page size - reserved size). It is vastly preferable to get an error at
database design time, rather than let the user encounter an error later on because they entered
a string which is one character over the limit of some internal database magic number.

3. The documentation for sized datatypes like varchar should be updated to warn about indexing
limitations (which would most likely be encounterd with such datatypes).




                  
> Limitation: Record of a btree secondary index cannot be updated or inserted due to lack
of space on the page.
> -------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-5881
>                 URL: https://issues.apache.org/jira/browse/DERBY-5881
>             Project: Derby
>          Issue Type: Bug
>          Components: Store
>    Affects Versions: 10.8.2.2, 10.9.1.0
>         Environment: Red Hat Enterprise Linux Server release 6.1 (Santiago) -x86_64
> and
> OpenSuse 11.3 -x86_64
>            Reporter: Tony Brusseau
>
> I'm trying to load a large database. After about 18 million inserts I always get the
following error:
>  Exception: java.sql.BatchUpdateException: Limitation: Record of a btree secondary index
cannot be updated or inserted due to lack of space on the page.  Use the parameters derby.storage.pageSize
and/or derby.storage.pageReservedSpace to work around this limitation.
> Error Code: 20000
> I'm already using the maximum pageSize as stated in the documentation (32768) and I've
bumped up the reserved space to 25%. I've also gotten rid of every non-essential index for
loading so this is probably related to a primary key index (I had to leave most of them around).
Our loading process is very non-trivial and batched so I'm not even sure which insert/index
is causing the problem.
> Problems:
> 1. The error message doesn't give any information about which tables/columns/indexes
are causing this problem so I have no way of knowing if i can work around this problem somehow.
> 2.The database shouldn't die saying to adjust some magic numbers with no guidance on
how to adjust them and by how much. Even if I do adjust them, what is to guarantee I won't
get the same error at a later date...maybe even sometime during production which would be
extremely bad. I believe an automated correction to this limitation needs to be created.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message