Derby
  1. Derby
  2. DERBY-3947

Cannot insert 994 character long string into indexed column

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.4.2.0
    • Fix Version/s: 10.6.1.0
    • Component/s: Store
    • Labels:
      None
    • Urgency:
      Normal
    • Issue & fix info:
      High Value Fix, Repro attached, Workaround attached
    • Bug behavior facts:
      Seen in production

      Description

      Inserting a 994 character string into a varchar(1000) column with an index fails.

      These steps

      1. "create table t (x varchar(1000) primary key)"
      2. "insert into t values " where ? holds a 994 character string

      produce the following error:

      ERROR XSCB6: 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.
      at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:276)
      at org.apache.derby.impl.store.access.btree.BTreeController.doIns(BTreeController.java:845)
      at org.apache.derby.impl.store.access.btree.BTreeController.insert(BTreeController.java:1264)
      at org.apache.derby.impl.store.access.btree.index.B2IController.insert(B2IController.java:210)
      at org.apache.derby.impl.sql.execute.IndexChanger.insertAndCheckDups(IndexChanger.java:439)
      at org.apache.derby.impl.sql.execute.IndexChanger.doInsert(IndexChanger.java:383)
      at org.apache.derby.impl.sql.execute.IndexChanger.insert(IndexChanger.java:589)
      at org.apache.derby.impl.sql.execute.IndexSetChanger.insert(IndexSetChanger.java:268)
      at org.apache.derby.impl.sql.execute.RowChangerImpl.insertRow(RowChangerImpl.java:453)
      at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(InsertResultSet.java:1011)
      at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:487)
      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:372)
      at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1235)

      The page size should be set sufficiently high at index creation time to hold columns with the specified maximum size.

      1. firstTry.diff
        5 kB
        Bryan Pendleton
      2. moreTests.diff
        6 kB
        Bryan Pendleton
      3. VarcharIndex.java
        0.6 kB
        Knut Anders Hatlen

        Activity

        Gavin made changes -
        Workflow jira [ 12446227 ] Default workflow, editable Closed status [ 12799608 ]
        Knut Anders Hatlen made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Hide
        Knut Anders Hatlen added a comment -

        Verified on trunk (table is still created with page size 4K, but index is created with page size 32K, and the insert does not fail). Closing.

        Show
        Knut Anders Hatlen added a comment - Verified on trunk (table is still created with page size 4K, but index is created with page size 32K, and the insert does not fail). Closing.
        Bryan Pendleton made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Issue & fix info [Workaround attached, Repro attached, Patch Available, High Value Fix] [High Value Fix, Repro attached, Workaround attached]
        Fix Version/s 10.6.0.0 [ 12313727 ]
        Resolution Fixed [ 1 ]
        Hide
        Bryan Pendleton added a comment -

        Thanks for the review, Knut. Committed to the trunk as revision 886162.

        Show
        Bryan Pendleton added a comment - Thanks for the review, Knut. Committed to the trunk as revision 886162.
        Hide
        Knut Anders Hatlen added a comment -

        Thanks for investigating this issue, Bryan. The fix looks correct to me. +1 to commit.

        Show
        Knut Anders Hatlen added a comment - Thanks for investigating this issue, Bryan. The fix looks correct to me. +1 to commit.
        Bryan Pendleton made changes -
        Issue & fix info [Workaround attached, Repro attached, High Value Fix] [High Value Fix, Patch Available, Repro attached, Workaround attached]
        Hide
        Bryan Pendleton added a comment -

        Regression testing was uneventful. I think that 'moreTests.diff' is worthy
        of consideration as a resolution to this, and would appreciate any input.

        Show
        Bryan Pendleton added a comment - Regression testing was uneventful. I think that 'moreTests.diff' is worthy of consideration as a resolution to this, and would appreciate any input.
        Bryan Pendleton made changes -
        Attachment moreTests.diff [ 12426369 ]
        Hide
        Bryan Pendleton added a comment -

        Upon more study, I don't think that ALTER TABLE takes a different code path
        when adding a constraint; i think the flow still goes through TableElementList.

        I did, however, add some more tests, including a test of trying to add a constraint
        which mentions a column which doesn't exist, which caught the fact that the
        patch needs to be careful when looking at the column lengths, because if the
        column doesn't exist, we can't access its length.

        I added more tests to the patch proposal, and am running a full set of regression tests.

        Show
        Bryan Pendleton added a comment - Upon more study, I don't think that ALTER TABLE takes a different code path when adding a constraint; i think the flow still goes through TableElementList. I did, however, add some more tests, including a test of trying to add a constraint which mentions a column which doesn't exist, which caught the fact that the patch needs to be careful when looking at the column lengths, because if the column doesn't exist, we can't access its length. I added more tests to the patch proposal, and am running a full set of regression tests.
        Bryan Pendleton made changes -
        Attachment firstTry.diff [ 12426330 ]
        Hide
        Bryan Pendleton added a comment -

        Attached is my first try at fixing this problem. The patch contains a regression
        test case, based on Knut's repro program, and a change to TableElementList
        to check, when creating the index which backs up a constraint, whether the
        index needs to use a large page size rather than the default page size.

        The change makes the repro case passes, but does NOT handle the similar,
        but different, cases of ALTER TABLE ADD CONSTRAINT, so the patch is
        not ready for commit.

        But I think the basic idea is workable, so I'll see if I can extend it to handle
        the special needs of ALTER TABLE.

        Show
        Bryan Pendleton added a comment - Attached is my first try at fixing this problem. The patch contains a regression test case, based on Knut's repro program, and a change to TableElementList to check, when creating the index which backs up a constraint, whether the index needs to use a large page size rather than the default page size. The change makes the repro case passes, but does NOT handle the similar, but different, cases of ALTER TABLE ADD CONSTRAINT, so the patch is not ready for commit. But I think the basic idea is workable, so I'll see if I can extend it to handle the special needs of ALTER TABLE.
        Hide
        Bryan Pendleton added a comment -

        I fiddled with this problem for a little while, and I think that the
        problem is that the index which is created behind-the-scenes to
        implement the PRIMARY KEY constraint goes through a different
        code path than the normal index which is created by CREATE INDEX.

        In the case of

        create table t (x varchar(1000));
        create index t_ix on t;

        we go through CreateIndexNode.makeConstantAction, where there
        is code that looks at the size of the columns in the index and notices
        that we need a larger page size for this index, and chooses the
        larger page size automatically.

        But in the case from the issue description

        create table t (x varchar(1000) primary key)

        we instead go through TableElementNode.genIndexAction,
        which doesn't have any logic to notice that a larger page size is needed.

        I think that we could fix this problem by arranging for the colInfos data
        from the CreateTableNode to be passed through to TableElementNode.genIndexAction
        so that it can check the size of the columns and see if it needs to create the
        PRIMARY KEY index with a larger page size.

        Ideally, it would be great if we could somehow share this code between
        CreateIndexNode and TableElementNode, but I think that the data
        structures are unfortunately not very similar so we may end up
        with two bits of code instead.

        At least, I can start by writing a second bit of code and demonstrating
        that it fixes the problem, and then we can try to figure out how to share
        the code and only compute the index's needed page size once.

        Show
        Bryan Pendleton added a comment - I fiddled with this problem for a little while, and I think that the problem is that the index which is created behind-the-scenes to implement the PRIMARY KEY constraint goes through a different code path than the normal index which is created by CREATE INDEX. In the case of create table t (x varchar(1000)); create index t_ix on t ; we go through CreateIndexNode.makeConstantAction, where there is code that looks at the size of the columns in the index and notices that we need a larger page size for this index, and chooses the larger page size automatically. But in the case from the issue description create table t (x varchar(1000) primary key) we instead go through TableElementNode.genIndexAction, which doesn't have any logic to notice that a larger page size is needed. I think that we could fix this problem by arranging for the colInfos data from the CreateTableNode to be passed through to TableElementNode.genIndexAction so that it can check the size of the columns and see if it needs to create the PRIMARY KEY index with a larger page size. Ideally, it would be great if we could somehow share this code between CreateIndexNode and TableElementNode, but I think that the data structures are unfortunately not very similar so we may end up with two bits of code instead. At least, I can start by writing a second bit of code and demonstrating that it fixes the problem, and then we can try to figure out how to share the code and only compute the index's needed page size once.
        Bryan Pendleton made changes -
        Assignee Bryan Pendleton [ bryanpendleton ]
        Kristian Waagan made changes -
        Bug behavior facts [Seen in production]
        Urgency Normal
        Issue & fix info [High Value Fix] [High Value Fix, Repro attached, Workaround attached]
        Hide
        Kristian Waagan added a comment -

        Triaged July 3, 2009: Assigned normal urgency, marked as Repro attached, Workaround attached and as Seen in production (even though 'm not 100% sure if the bug was seen in production or in the pre-deployment phase).

        Show
        Kristian Waagan added a comment - Triaged July 3, 2009: Assigned normal urgency, marked as Repro attached, Workaround attached and as Seen in production (even though 'm not 100% sure if the bug was seen in production or in the pre-deployment phase).
        Dag H. Wanvik made changes -
        Issue & fix info [High Value Fix]
        Kathey Marsden made changes -
        Derby Categories [High Value Fix]
        Hide
        Kristian Waagan added a comment -

        Similar problem reported by Alan Burlison on the user list, see http://www.nabble.com/btree-overflow-during-insert-to20460876.html

        Show
        Kristian Waagan added a comment - Similar problem reported by Alan Burlison on the user list, see http://www.nabble.com/btree-overflow-during-insert-to20460876.html
        Knut Anders Hatlen made changes -
        Field Original Value New Value
        Attachment VarcharIndex.java [ 12393870 ]
        Hide
        Knut Anders Hatlen added a comment -

        Attaching test case which reproduces the bug.

        Show
        Knut Anders Hatlen added a comment - Attaching test case which reproduces the bug.
        Knut Anders Hatlen created issue -

          People

          • Assignee:
            Bryan Pendleton
            Reporter:
            Knut Anders Hatlen
          • Votes:
            1 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development