Derby
  1. Derby
  2. DERBY-4256

allow alter table to increase the maximum size of a blob and a clob.

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 10.5.1.1
    • Fix Version/s: 10.6.1.0
    • Component/s: SQL
    • Labels:
      None
    • Issue & fix info:
      Newcomer

      Description

      Allow new syntax that allows user to alter the maximum length of a blob and/or clob column:
      ALTER TABLE binarydata ALTER COLUMN data SET DATA TYPE blob(128M)

      The syntax should match up with the existing functionality to increase the size of varchar fields.

      1. ASF.LICENSE.NOT.GRANTED--A.txt
        19 kB
        Eranda Sooriyabandara
      2. ASF.LICENSE.NOT.GRANTED--Clob.java
        2 kB
        Eranda Sooriyabandara
      3. ASF.LICENSE.NOT.GRANTED--derby4256.diff
        5 kB
        Eranda Sooriyabandara
      4. ASF.LICENSE.NOT.GRANTED--derby4256.diff
        4 kB
        Eranda Sooriyabandara
      5. ASF.LICENSE.NOT.GRANTED--derby4256.diff
        0.8 kB
        Eranda Sooriyabandara
      6. ASF.LICENSE.NOT.GRANTED--derby4256.diff
        0.7 kB
        Eranda Sooriyabandara
      7. ten_5_codeOnly.diff
        1 kB
        Bryan Pendleton

        Issue Links

          Activity

          Hide
          Evan Leonard added a comment -

          Thank you Bryan for taking the time to create the 10.5 patch. However I'm not sure this will suit my environment. We ship a product with many dependencies, derby being one of them, and its not advisable to start up custom branches of dependencies since we then have to track the changes in every subsequent release.

          Any chance this patch could be included in 10.5.3.1 or 10.5.4? What is the proper channel to request this?

          Thanks again!

          Show
          Evan Leonard added a comment - Thank you Bryan for taking the time to create the 10.5 patch. However I'm not sure this will suit my environment. We ship a product with many dependencies, derby being one of them, and its not advisable to start up custom branches of dependencies since we then have to track the changes in every subsequent release. Any chance this patch could be included in 10.5.3.1 or 10.5.4? What is the proper channel to request this? Thanks again!
          Hide
          Bryan Pendleton added a comment -

          Hi Evan,

          I think that the code changes are straightforward, and should apply to 10.5 without trouble.

          The test changes are trickier, because the relevant test (AlterTableTest.java) doesn't exist in 10.5.

          Attached is 10_5_codeOnly.diff, which is a patch that you should be able to
          apply to the 10.5 branch in your environment. I created this patch by running

          svn merge -r 802984:802985 https://svn.apache.org/repos/asf/db/derby/code/trunk/

          and then reverting modifyColumn.out. (I didn't need to revert modifyColumn.out, but since
          the test portion of the patch was mostly in AlterTableTest, I thought it would be
          clearer if this diff only contained code changes, not any partial test changes)

          Depending on what your needs are, hopefully the attached patch is useful to
          you (e.g., if you want to build your own modified version of 10.5 and use it
          to alter the column definitions in your database).

          Show
          Bryan Pendleton added a comment - Hi Evan, I think that the code changes are straightforward, and should apply to 10.5 without trouble. The test changes are trickier, because the relevant test (AlterTableTest.java) doesn't exist in 10.5. Attached is 10_5_codeOnly.diff, which is a patch that you should be able to apply to the 10.5 branch in your environment. I created this patch by running svn merge -r 802984:802985 https://svn.apache.org/repos/asf/db/derby/code/trunk/ and then reverting modifyColumn.out. (I didn't need to revert modifyColumn.out, but since the test portion of the patch was mostly in AlterTableTest, I thought it would be clearer if this diff only contained code changes, not any partial test changes) Depending on what your needs are, hopefully the attached patch is useful to you (e.g., if you want to build your own modified version of 10.5 and use it to alter the column definitions in your database).
          Hide
          Evan Leonard added a comment -

          Any chance this could be merged to 10.5? I'd quite like to make use of this fix, but am not excited about having to wait for 10.6 next year. From this attached diffs, it looks like it is a small localized fix that could safely be applied to 10.5.

          Show
          Evan Leonard added a comment - Any chance this could be merged to 10.5? I'd quite like to make use of this fix, but am not excited about having to wait for 10.6 next year. From this attached diffs, it looks like it is a small localized fix that could safely be applied to 10.5.
          Hide
          Eranda Sooriyabandara added a comment -

          Hi Bryan,I am sorry for the mistake I have done and thanks
          for committing the patch.
          I am closing this issue.

          Show
          Eranda Sooriyabandara added a comment - Hi Bryan,I am sorry for the mistake I have done and thanks for committing the patch. I am closing this issue.
          Hide
          Bryan Pendleton added a comment -

          Patch committed to the trunk as svn revision 802985.

          Thanks for the contribution to Derby, Eranda!

          Show
          Bryan Pendleton added a comment - Patch committed to the trunk as svn revision 802985. Thanks for the contribution to Derby, Eranda!
          Hide
          Bryan Pendleton added a comment -

          Hi Eranda, I think maybe you meant to attach these latest patches and comments to DERBY-4282, not DERBY-4256?

          Show
          Bryan Pendleton added a comment - Hi Eranda, I think maybe you meant to attach these latest patches and comments to DERBY-4282 , not DERBY-4256 ?
          Hide
          Bryan Pendleton added a comment -

          Thanks Eranda for the updated patch. I am running the full regression suite and will
          commit this patch soon.

          Show
          Bryan Pendleton added a comment - Thanks Eranda for the updated patch. I am running the full regression suite and will commit this patch soon.
          Hide
          Eranda Sooriyabandara added a comment -

          Hi Bryan,
          Here I change the error message and I am attaching the changed patch.

          Show
          Eranda Sooriyabandara added a comment - Hi Bryan, Here I change the error message and I am attaching the changed patch.
          Hide
          Bryan Pendleton added a comment -

          Hi Eranda, this looks like a very good test, thanks for putting it together.

          I believe that you can alter the message by editing the file

          java/engine/org/apache/derby/loc/messages.xml

          If you want to investigate modifying the message, then after that I'll
          look into committing this fix.

          Show
          Bryan Pendleton added a comment - Hi Eranda, this looks like a very good test, thanks for putting it together. I believe that you can alter the message by editing the file java/engine/org/apache/derby/loc/messages.xml If you want to investigate modifying the message, then after that I'll look into committing this fix.
          Hide
          Eranda Sooriyabandara added a comment -

          Hi Bryan,
          I created updated the AlterTableTest according to the new issue and add the
          method testJira4256().
          I think this will be fine as a test.(It takes my full day).What you think
          about the test?
          And the exception give as "Only columns of type VARCHAR may have their
          length altered".So we must add CLOB and BLOB as data types that can alter
          its length.
          Thanks
          Eranda

          Show
          Eranda Sooriyabandara added a comment - Hi Bryan, I created updated the AlterTableTest according to the new issue and add the method testJira4256(). I think this will be fine as a test.(It takes my full day).What you think about the test? And the exception give as "Only columns of type VARCHAR may have their length altered".So we must add CLOB and BLOB as data types that can alter its length. Thanks Eranda
          Hide
          Bryan Pendleton added a comment -

          Hi Eranda,

          This code seems like a good approach.

          Clob.java reproduces the bug for me, and I also confirmed that if I apply your change to ModifyColumnNode.java,
          Clob.java then works correctly (it can insert the 20K clob into the column which has been altered from 10K).

          This is good progress, thanks for working on the test program.

          I think that we could avoid the need for a separate text file by constructing a string in memory ,
          and then constructing an input stream from the string by using ByteArrayInputStream and String.getBytes.

          That might make the test program easier to integrate into one of our existing classes

          For example, here's a simple method to construct a string in memory of an arbitrary length:

          private static String makeString( int count )

          { char[] raw = new char[ count ]; Arrays.fill( raw, 'a' ); return new String( raw ); }
          Show
          Bryan Pendleton added a comment - Hi Eranda, This code seems like a good approach. Clob.java reproduces the bug for me, and I also confirmed that if I apply your change to ModifyColumnNode.java, Clob.java then works correctly (it can insert the 20K clob into the column which has been altered from 10K). This is good progress, thanks for working on the test program. I think that we could avoid the need for a separate text file by constructing a string in memory , and then constructing an input stream from the string by using ByteArrayInputStream and String.getBytes. That might make the test program easier to integrate into one of our existing classes For example, here's a simple method to construct a string in memory of an arbitrary length: private static String makeString( int count ) { char[] raw = new char[ count ]; Arrays.fill( raw, 'a' ); return new String( raw ); }
          Hide
          Eranda Sooriyabandara added a comment -

          Hi Bryan,Here is the java file I reproduce the bug. I find this code in a
          IBM help site and edited my self.
          I crated a clob column of 10K and alter that column to 20K.Also I use a
          A.txt file about 19K with this to enter to the clob column and to show that
          it works.
          Thanks
          Eranda

          Show
          Eranda Sooriyabandara added a comment - Hi Bryan,Here is the java file I reproduce the bug. I find this code in a IBM help site and edited my self. I crated a clob column of 10K and alter that column to 20K.Also I use a A.txt file about 19K with this to enter to the clob column and to show that it works. Thanks Eranda
          Hide
          Mamta A. Satoor added a comment -

          Eranda, I quickly went through your patch and it looks good to me. Would it be possible to try to create smaller blob/clob columns, increase their size and then insert data which is bigger than the original size but smaller than the new size to see if we are able to store and retrieve bigger data. Once we know this works fine on a standalone case, we should add a junit test to put it into our regression suite for this change.

          Show
          Mamta A. Satoor added a comment - Eranda, I quickly went through your patch and it looks good to me. Would it be possible to try to create smaller blob/clob columns, increase their size and then insert data which is bigger than the original size but smaller than the new size to see if we are able to store and retrieve bigger data. Once we know this works fine on a standalone case, we should add a junit test to put it into our regression suite for this change.
          Hide
          Eranda Sooriyabandara added a comment -

          Hi Bryan,My last patch I was not think about the clob and I think to include
          a little coding to enable the clob to be increase its size.
          Here I attach the new patch with this.

          Show
          Eranda Sooriyabandara added a comment - Hi Bryan,My last patch I was not think about the clob and I think to include a little coding to enable the clob to be increase its size. Here I attach the new patch with this.
          Hide
          Eranda Sooriyabandara added a comment -

          Hi Bryan,
          I made some changes to the code as,
          if (!(typeName.equals(TypeId.VARCHAR_NAME)) &&
          Unable to render embedded object: File ((typeName.equals(TypeId.VARBIT_NAME))&&) not found.(typeName.equals(TypeId.BLOB_NAME)))

          { throw StandardException.newException( SQLState.LANG_MODIFY_COLUMN_INVALID_TYPE); }

          and this succeed the test

          ij version 10.6

          ij> connect 'jdbc:derby:testdb;create=true';

          ij> create table binarydata(c1 int,data blob(64));

          0 rows inserted/updated/deleted

          ij> ALTER TABLE binarydata ALTER COLUMN data SET DATA TYPE blob(256M);

          0 rows inserted/updated/deleted
          Here I am attaching the patch file with this.

          Show
          Eranda Sooriyabandara added a comment - Hi Bryan, I made some changes to the code as, if (!(typeName.equals(TypeId.VARCHAR_NAME)) && Unable to render embedded object: File ((typeName.equals(TypeId.VARBIT_NAME))&&) not found. (typeName.equals(TypeId.BLOB_NAME))) { throw StandardException.newException( SQLState.LANG_MODIFY_COLUMN_INVALID_TYPE); } and this succeed the test ij version 10.6 ij> connect 'jdbc:derby:testdb;create=true'; ij> create table binarydata(c1 int,data blob(64)); 0 rows inserted/updated/deleted ij> ALTER TABLE binarydata ALTER COLUMN data SET DATA TYPE blob(256M); 0 rows inserted/updated/deleted Here I am attaching the patch file with this.
          Hide
          Knut Anders Hatlen added a comment -

          This looks like a duplicate of DERBY-3920.

          Show
          Knut Anders Hatlen added a comment - This looks like a duplicate of DERBY-3920 .
          Hide
          Mike Matrigali added a comment -

          go ahead and ignore my comments about parser, it was not based on reading the code. I am sure about the
          store part of my comments that it is safe to just go ahead and change the catalogs and not have to do anything
          to the data.

          . Bryan has the
          experience in this area. I was just thinking that it was going to add syntax and did not realize that datatype
          part of the syntax would not need parser changes, but it looks like the part that
          handles different datatypes does not really need parser changes. The called routines can handle many
          different datatypes.

          Show
          Mike Matrigali added a comment - go ahead and ignore my comments about parser, it was not based on reading the code. I am sure about the store part of my comments that it is safe to just go ahead and change the catalogs and not have to do anything to the data. . Bryan has the experience in this area. I was just thinking that it was going to add syntax and did not realize that datatype part of the syntax would not need parser changes, but it looks like the part that handles different datatypes does not really need parser changes. The called routines can handle many different datatypes.
          Hide
          Bryan Pendleton added a comment -

          I'm cautiously optimistic that no additional changes will be needed. I'm eager to see what
          results you find by simply modifying ModifyColumnNode.java.

          The relevant portion of the grammar is around line 12740 in the columnAlterClause()
          method of impl/sql/compile/sqlgrammar.jj

          The relevant execution code is the modifyColumnType() method in
          o.a.d.impl.sql.execute.AlterTableConstantAction

          I took a quick peek at both places and it seemed like they should not require changes
          for the extension to blob/clob. But it was a very quick peek...

          Show
          Bryan Pendleton added a comment - I'm cautiously optimistic that no additional changes will be needed. I'm eager to see what results you find by simply modifying ModifyColumnNode.java. The relevant portion of the grammar is around line 12740 in the columnAlterClause() method of impl/sql/compile/sqlgrammar.jj The relevant execution code is the modifyColumnType() method in o.a.d.impl.sql.execute.AlterTableConstantAction I took a quick peek at both places and it seemed like they should not require changes for the extension to blob/clob. But it was a very quick peek...
          Hide
          Evan Leonard added a comment -

          The changes there in ModifyColumnNode are straight forward enough. When Mike was referring to "parser changes and some execution
          time changes" do you have a sense of where he's referring?

          Show
          Evan Leonard added a comment - The changes there in ModifyColumnNode are straight forward enough. When Mike was referring to "parser changes and some execution time changes" do you have a sense of where he's referring?
          Hide
          Bryan Pendleton added a comment -

          Hi Evan, thanks for having a look at this.

          I think the first place to start is with the 'checkUserType()' method in
          o.a.d.impl.sql.compile.ModifyColumnNode. Look around line 118.

          Show
          Bryan Pendleton added a comment - Hi Evan, thanks for having a look at this. I think the first place to start is with the 'checkUserType()' method in o.a.d.impl.sql.compile.ModifyColumnNode. Look around line 118.
          Hide
          Evan Leonard added a comment -

          I'll try and take a look. Can you point me toward the VARCHAR work?

          Show
          Evan Leonard added a comment - I'll try and take a look. Can you point me toward the VARCHAR work?
          Hide
          Mike Matrigali added a comment -

          I believe that this project would be a good one for a new comer. I don't think it would be too hard.

          I would suggest finding the code path that allows alter table to increase the size of a varchar field and
          mimic the code to apply to blob and clob fields. It will require some parser changes and some execution
          time changes. The files can be pointed out if necessary.

          What makes this easy is to only allow INCREASE of size. For blob and clob the size field is really only used
          for error checking at the higher levels of the code, there is no storage difference between a small one and
          a large one. No format difference. Also no space difference as we only use space to store whatever the
          actual size is. By only allowing increase then there is no need to go and check the length of every value, and
          figure out what to do if there exists a value that exceeds the new proposed maximum.

          Show
          Mike Matrigali added a comment - I believe that this project would be a good one for a new comer. I don't think it would be too hard. I would suggest finding the code path that allows alter table to increase the size of a varchar field and mimic the code to apply to blob and clob fields. It will require some parser changes and some execution time changes. The files can be pointed out if necessary. What makes this easy is to only allow INCREASE of size. For blob and clob the size field is really only used for error checking at the higher levels of the code, there is no storage difference between a small one and a large one. No format difference. Also no space difference as we only use space to store whatever the actual size is. By only allowing increase then there is no need to go and check the length of every value, and figure out what to do if there exists a value that exceeds the new proposed maximum.
          Hide
          Mike Matrigali added a comment -

          I logged this issue based on the following discussion list posting:

          Hello again,

          As a corollary to my upgrade question, I have another issue I would like to get some input on.

          Several old databases in production were created with blob columns at the then default blob size of 1mb. How can I go about upgrading these columns to be longer?

          This is the best approach I've come up with so far:

          ALTER TABLE binarydata ADD COLUMN data2 blob(128M)
          UPDATE binarydata SET data2 = data;
          ALTER TABLE binarydata DROP COLUMN data RESTRICT;
          RENAME COLUMN binarydata.data2 TO data;

          The issue with this approach is that some deployed databases are nearly 1GB in size with a large portion of that being in this table I'm trying to adjust. When I ran this query as a test on such a database, the size of the db on disk balloon to over twice its starting size, and then failed because I was running it on a temp drive without enough storage to complete, so I haven't been able to fully test even if this will work.

          Is there a good reason why blob columns can't have their size adjusted directly? I've tried:

          ALTER TABLE binarydata ALTER COLUMN data SET DATA TYPE blob(128M)

          but this failed with an error.

          Any ideas?

          Evan

          Show
          Mike Matrigali added a comment - I logged this issue based on the following discussion list posting: Hello again, As a corollary to my upgrade question, I have another issue I would like to get some input on. Several old databases in production were created with blob columns at the then default blob size of 1mb. How can I go about upgrading these columns to be longer? This is the best approach I've come up with so far: ALTER TABLE binarydata ADD COLUMN data2 blob(128M) UPDATE binarydata SET data2 = data; ALTER TABLE binarydata DROP COLUMN data RESTRICT; RENAME COLUMN binarydata.data2 TO data; The issue with this approach is that some deployed databases are nearly 1GB in size with a large portion of that being in this table I'm trying to adjust. When I ran this query as a test on such a database, the size of the db on disk balloon to over twice its starting size, and then failed because I was running it on a temp drive without enough storage to complete, so I haven't been able to fully test even if this will work. Is there a good reason why blob columns can't have their size adjusted directly? I've tried: ALTER TABLE binarydata ALTER COLUMN data SET DATA TYPE blob(128M) but this failed with an error. Any ideas? Evan

            People

            • Assignee:
              Eranda Sooriyabandara
              Reporter:
              Mike Matrigali
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development