Derby
  1. Derby
  2. DERBY-438

Update triggers on tables with BLOB and CLOB columns fail at execution time if the triggered-SQL-statement references the LOB column(s).

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.2, 10.1.1.0, 10.2.1.6
    • Fix Version/s: 10.2.1.6
    • Component/s: SQL
    • Labels:
      None

      Description

      Suppose I have 1) a table "t1" with blob data in it, and 2) an UPDATE trigger "tr1" defined on that table, where the triggered-SQL-action for "tr1" references the blob column from the updated ("new") row. Ex:

      create table t1 (id int, updated smallint, bl blob(32000));
      create trigger tr1 after update on t1 referencing new as n_row for each row mode db2sql values length(n_row.bl);

      Assuming that t1 has been populated with some data, then attempts to update t1 will fire the trigger, but the result will be one of the two following errors:

      1) If blob data is < 32K...

      If the actual data in the table is less than 32K in length, the result will be:

      ERROR XCL12: An attempt was made to put a data value of type 'org.apache.derby.impl.jdbc.EmbedBlob' into a data value of type 'BLOB'.

      2) If blob data is > 32K...

      If at least one row in the table has blob data that is longer than 32K (which means that Derby will stream it, so far as I can tell), then the error will be:

      ERROR XCL30: An IOException was thrown when reading a 'BLOB' from an InputStream.
      ERROR XJ001: Java exception: ': java.io.EOFException'.

      Note that for data larger than 32K, this error will occur regardless of whether or not the triggered-SQL-statement
      references the blob column.

      Surprisingly, it doesn't (appear to) matter what the trigger statement is actually doing--so long as it references the blob column at least once, one of these two errors will occur, depending on the length of the data. And if the data is greater than 32k, then the error will happen regardless of what the trigger does or whether or not it references the blob column.

      I looked at the documentation for UPDATE statements and TRIGGER statements, but nowhere did I see anything saying that either of these will not work with blobs. So as far as I can tell, both of the above scenarios should succeed...

      1. derby438_draft_statement_LOB_patch.txt
        27 kB
        Daniel John Debrunner
      2. go.java
        2 kB
        A B

        Activity

        Hide
        A B added a comment -

        Attaching a repro for the problem. To run, just do "java go".

        Show
        A B added a comment - Attaching a repro for the problem. To run, just do "java go".
        Hide
        A B added a comment -

        There was some discussion on this issue on the derby-dev list; I'm including the results of that discussion here for ease-of-tracking:

        It was noted that Derby as it currently stands does not support "accessing LOB columns of the modified table by the action statement" within a triggered-SQL-action, "because within the database engine, there is no mechanism to create a java.sql.Blob object (or Clob) from a SQL BLOB (CLOB) datatype" (quoting from the derby-dev thread). That said, there are really four 'to-do's with this issue:

        1 - document the current restrictions
        2 - with the current restriction produce a better error message
        3 - implement LOB support in the referenced tables.
        4 - Fix triggers so that they work in cases where the triggered-SQL-statement does not reference a blob column (currently, a trigger will fail with an IOException if the target table has blob data larger than 32K, even if that column isn't actually referenced by the trigger action).

        For the full thread, see:

        http://article.gmane.org/gmane.comp.apache.db.derby.devel/6272

        I should have posted this comment at the time the derby-dev discussion was concluded; apologies for any inconvenience...

        Show
        A B added a comment - There was some discussion on this issue on the derby-dev list; I'm including the results of that discussion here for ease-of-tracking: It was noted that Derby as it currently stands does not support "accessing LOB columns of the modified table by the action statement" within a triggered-SQL-action, "because within the database engine, there is no mechanism to create a java.sql.Blob object (or Clob) from a SQL BLOB (CLOB) datatype" (quoting from the derby-dev thread). That said, there are really four 'to-do's with this issue: 1 - document the current restrictions 2 - with the current restriction produce a better error message 3 - implement LOB support in the referenced tables. 4 - Fix triggers so that they work in cases where the triggered-SQL-statement does not reference a blob column (currently, a trigger will fail with an IOException if the target table has blob data larger than 32K, even if that column isn't actually referenced by the trigger action). For the full thread, see: http://article.gmane.org/gmane.comp.apache.db.derby.devel/6272 I should have posted this comment at the time the derby-dev discussion was concluded; apologies for any inconvenience...
        Hide
        Manish Khettry added a comment -

        Even if the blob column is not referenced, an io exception is thrown. The Exception is caused by the SQL layer trying to read the same Stream twice. The test case is pretty much the same as the attached go.java, reproduced here for convenience)

        create table t1 (id int, updated smallint, bl blob(64k));
        create trigger tr1 after update on t1 referencing
        new as n_row for each row mode db2sql values length(n_row.updated);
        update t1 set updated = 1 where id = ?;

        The first call to load the stream is in NormalizeResultSet; i.e.

        at org.apache.derby.iapi.types.SQLBlob.normalize(SQLBlob.java:110)
        at org.apache.derby.iapi.types.DataTypeDescriptor.normalize(DataTypeDescriptor.java:432)
        at org.apache.derby.impl.sql.execute.NormalizeResultSet.normalizeRow(NormalizeResultSet.java:351)
        at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(NormalizeResultSet.java:207)
        at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:124)

        The second call is

        at org.apache.derby.iapi.types.SQLBinary.getValue(SQLBinary.java:198)
        at org.apache.derby.iapi.types.SQLBinary.loadStream(SQLBinary.java:542)
        at org.apache.derby.impl.sql.execute.DMLWriteResultSet.objectifyStreams(DMLWriteResultSet.java:151)
        at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:132)
        at org.apache.derby.impl.sql.execute.UpdateResultSet.collectAffectedRows(UpdateResultSet.java:453)

        If Derby does not support accessing LOB columns in triggers, there does seem to becode that seems to think otherwise! In particular look at the constructor for DMLWriteResultStream (the comments at the end, the variable 'needToObjectifyStream'). It looks to me, that for any DML on a table which has triggers defined, we'll end up materializing stream columns even if the DML and the trigger do not reference any stream storable columns.

        So apart from the 4 "todo's" in the previous comment, I have a couple of questions for those more familiar with this code.

        1. If we don't support referencing blob columns in triggers, do we need the logic for objectifyStream in DMLWriteResultSet? The comment in the constructor refers to several bug numbers which predate Jira-- 2432, 3383, 4896. Is it possible to get information about these?

        2. Does NormalizeResultSet need to normalize a column that is not referred to in the update?

        Show
        Manish Khettry added a comment - Even if the blob column is not referenced, an io exception is thrown. The Exception is caused by the SQL layer trying to read the same Stream twice. The test case is pretty much the same as the attached go.java, reproduced here for convenience) create table t1 (id int, updated smallint, bl blob(64k)); create trigger tr1 after update on t1 referencing new as n_row for each row mode db2sql values length(n_row.updated); update t1 set updated = 1 where id = ?; The first call to load the stream is in NormalizeResultSet; i.e. at org.apache.derby.iapi.types.SQLBlob.normalize(SQLBlob.java:110) at org.apache.derby.iapi.types.DataTypeDescriptor.normalize(DataTypeDescriptor.java:432) at org.apache.derby.impl.sql.execute.NormalizeResultSet.normalizeRow(NormalizeResultSet.java:351) at org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(NormalizeResultSet.java:207) at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:124) The second call is at org.apache.derby.iapi.types.SQLBinary.getValue(SQLBinary.java:198) at org.apache.derby.iapi.types.SQLBinary.loadStream(SQLBinary.java:542) at org.apache.derby.impl.sql.execute.DMLWriteResultSet.objectifyStreams(DMLWriteResultSet.java:151) at org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWriteResultSet.java:132) at org.apache.derby.impl.sql.execute.UpdateResultSet.collectAffectedRows(UpdateResultSet.java:453) If Derby does not support accessing LOB columns in triggers, there does seem to becode that seems to think otherwise! In particular look at the constructor for DMLWriteResultStream (the comments at the end, the variable 'needToObjectifyStream'). It looks to me, that for any DML on a table which has triggers defined, we'll end up materializing stream columns even if the DML and the trigger do not reference any stream storable columns. So apart from the 4 "todo's" in the previous comment, I have a couple of questions for those more familiar with this code. 1. If we don't support referencing blob columns in triggers, do we need the logic for objectifyStream in DMLWriteResultSet? The comment in the constructor refers to several bug numbers which predate Jira-- 2432, 3383, 4896. Is it possible to get information about these? 2. Does NormalizeResultSet need to normalize a column that is not referred to in the update?
        Hide
        Daniel John Debrunner added a comment -

        Several fixes have been made (seen the Subversion commits) that support blob/clob in row triggers including the case when the LOB value is streamed from the store.
        Writing a test for statement triggers showed an issue where the implementation of the statement trigger depended on a VTI (ResultSet implementation) that was still at JDBC 1.2 level, thus not supporting BLOB/CLOB. Some description of how triggers are executed has been documented on the wiki at:

        http://wiki.apache.org/db-derby/TriggerImplementation

        The proposed change to fix this is to change the VTIs used in triggers from implementations of java.sql.ResultSet that wrap a embedded java.sql.ResultSet that wraps a Derby language ResultSet, to implementations of java.sql.PreparedStatement that simply return the embedded java.sql.ResultSet that wraps a Derby language ResultSet.

        PreparedStatement VTIs are more flexible that ResultSet VTIs in that they can be updatable (not required in this case) and (for this case) the implementation of the ResultSet can be hidden by the class, allowing different implementations in different situations.

        This fixes the situation for statement triggers as the ResultSet now returned to the re-written trigger action statement will support BLOB/CLOB as it will be JDBC ResultSet as returned from the embedded driver. Since the api of the VTI does not change this fix should work on soft upgrade for old triggers from 10.1/10.0 and triggers created in 10.2 soft upgrade that will then run in 10.1/10.0

        Show
        Daniel John Debrunner added a comment - Several fixes have been made (seen the Subversion commits) that support blob/clob in row triggers including the case when the LOB value is streamed from the store. Writing a test for statement triggers showed an issue where the implementation of the statement trigger depended on a VTI (ResultSet implementation) that was still at JDBC 1.2 level, thus not supporting BLOB/CLOB. Some description of how triggers are executed has been documented on the wiki at: http://wiki.apache.org/db-derby/TriggerImplementation The proposed change to fix this is to change the VTIs used in triggers from implementations of java.sql.ResultSet that wrap a embedded java.sql.ResultSet that wraps a Derby language ResultSet, to implementations of java.sql.PreparedStatement that simply return the embedded java.sql.ResultSet that wraps a Derby language ResultSet. PreparedStatement VTIs are more flexible that ResultSet VTIs in that they can be updatable (not required in this case) and (for this case) the implementation of the ResultSet can be hidden by the class, allowing different implementations in different situations. This fixes the situation for statement triggers as the ResultSet now returned to the re-written trigger action statement will support BLOB/CLOB as it will be JDBC ResultSet as returned from the embedded driver. Since the api of the VTI does not change this fix should work on soft upgrade for old triggers from 10.1/10.0 and triggers created in 10.2 soft upgrade that will then run in 10.1/10.0
        Hide
        Daniel John Debrunner added a comment -

        Draft patch that shows the change from ResultSet to PreparedStatement for the trigger VTIs.

        M java\engine\org\apache\derby\impl\sql\compile\FromVTI.java
        M java\engine\org\apache\derby\vti\UpdatableVTITemplate.java
        M java\engine\org\apache\derby\catalog\TriggerNewTransitionRows.java
        M java\engine\org\apache\derby\catalog\TriggerOldTransitionRows.java
        M java\testing\org\apache\derbyTesting\functionTests\tests\jdbcapi\blobclob4BLOB.java

        Majority of change is removing the ResultSet methods from the transition VTIs.
        Only includes the test changes, not the modified output. Patch is draft.

        Show
        Daniel John Debrunner added a comment - Draft patch that shows the change from ResultSet to PreparedStatement for the trigger VTIs. M java\engine\org\apache\derby\impl\sql\compile\FromVTI.java M java\engine\org\apache\derby\vti\UpdatableVTITemplate.java M java\engine\org\apache\derby\catalog\TriggerNewTransitionRows.java M java\engine\org\apache\derby\catalog\TriggerOldTransitionRows.java M java\testing\org\apache\derbyTesting\functionTests\tests\jdbcapi\blobclob4BLOB.java Majority of change is removing the ResultSet methods from the transition VTIs. Only includes the test changes, not the modified output. Patch is draft.
        Hide
        Daniel John Debrunner added a comment -

        Number of commits marked with DERBY-438 have been applied. Tests are in triggerGeneral.sql and blobclob4BLOB.java for both BLOB and CLOB. Includes row & statement, with small and large LOBs.

        I do not believe DERBY-442 is a sub-task of this bug description. DERBY-442 is a separate (but related) issue. I don't see a way in Juru to move it though.

        Show
        Daniel John Debrunner added a comment - Number of commits marked with DERBY-438 have been applied. Tests are in triggerGeneral.sql and blobclob4BLOB.java for both BLOB and CLOB. Includes row & statement, with small and large LOBs. I do not believe DERBY-442 is a sub-task of this bug description. DERBY-442 is a separate (but related) issue. I don't see a way in Juru to move it though.
        Hide
        A B added a comment -

        Many patches contributed and committed by Dan, the last of which has been in the codeline for over a month now. So I'm closing this issue.

        Show
        A B added a comment - Many patches contributed and committed by Dan, the last of which has been in the codeline for over a month now. So I'm closing this issue.
        Hide
        Daniel John Debrunner added a comment -

        Kathey Mardsen (in DERBY-1482) has indicated a need to fix this issue in 10.1, possibly by backporting these changes.

        One possible concern to backporting is the changes made in revision 409422 which modifed the altered form of the trigger's action statement.
        It might work ok in a branch (ie. 10.1) as long as the commit that includes that specific change also changes the last digit of the version number.
        This will trigger automatic re-compile for all stored plans which will include the trigger's action statement. This would need to be confirmed though.

        Show
        Daniel John Debrunner added a comment - Kathey Mardsen (in DERBY-1482 ) has indicated a need to fix this issue in 10.1, possibly by backporting these changes. One possible concern to backporting is the changes made in revision 409422 which modifed the altered form of the trigger's action statement. It might work ok in a branch (ie. 10.1) as long as the commit that includes that specific change also changes the last digit of the version number. This will trigger automatic re-compile for all stored plans which will include the trigger's action statement. This would need to be confirmed though.
        Hide
        Daniel John Debrunner added a comment -

        In back-porting this (which I think is the best approach to fixing this in 10.1) back-porting DERBY-1258 might be required.

        Show
        Daniel John Debrunner added a comment - In back-porting this (which I think is the best approach to fixing this in 10.1) back-porting DERBY-1258 might be required.
        Hide
        Kathey Marsden added a comment -

        reopen to attempt backport to 10.1

        Show
        Kathey Marsden added a comment - reopen to attempt backport to 10.1

          People

          • Assignee:
            Daniel John Debrunner
            Reporter:
            A B
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development