[
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:10 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 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 datable 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
