[
https://issues.apache.org/jira/browse/DERBY-5881?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13422453#comment-13422453
]
Mike Matrigali commented on DERBY-5881:
---------------------------------------
do you have the derby.log from when the error happened. Usually this will have
stack traces that are useful, and we can see if there are nested exception that
might
provide more information. This might tell us if this is an expected result of
a long key being inserted or some bug.
Derby does have a hard limitation on the size of an index key with respect to
the page size of the index, it is something like 1/3 of the page size. Is it
likely that one of the rows that you
are loading has index key that is longer than 10k? Note that a well designed
application for
derby should try to have keys much smaller than this limit, as the derby index
is going to be very inefficient as key size approaches 1/3 size of the page.
What is the ddl for the index that you are believe is failing.
Note that it is the page size of the index, not the base table that is
affecting this issue. You can use the space table vti to verify that the base
table and indexes have the page size you expect.
For indexes you should be reducing the reserved space rather than increasing it
to work around this problem. In the current derby implementation entries into
indexes are never updated, so would recommend a reserved space of 0 for
indexes. Updates of key columns are always internally changed to a delete
followed by an insert into the index. Setting bigger reserve space effectively
means less of the page available for insert and so even shorter keys will be
disallowed. It also means less rows per page and about the only reason I can
see to set it to non-zero for indexes is to provide very very slight
concurrency increase as page latch would affect less rows - i would not
recommend it.
> 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
> Priority: Blocker
>
> 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
