Derby
  1. Derby
  2. DERBY-1718

creating an after insert trigger with trigger action involving xml datatype throws java.io.NottSerializableException

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.2.1.6, 10.3.1.4
    • Fix Version/s: 10.2.1.6, 10.3.1.4
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Java Version: 1.4.2
      Java Vendor: IBM Corporation

      Description

      creating an after insert trigger with trigger action involving xml datatype throws following error :
      ij> create trigger trigxml after insert on t1 for each statement mode db2sql
      insert into t2 values (1,
      xmlparse(document '<name> ram </name>' preserve whitespace));
      ERROR XSDAJ: Exception during write of a serializable or SQLData object
      ERROR XJ001: Java exception: 'org.apache.derby.iapi.types.SqlXmlUtil: java.io.No
      ton'.SerializableExcepti
      ij>

      repro:
      connect 'jdbc:derby:wombat;create=true';
      create table t1 (i int, x xml);
      create table t2 (i int, x xml);

      insert into t2 values (1,
      xmlparse(document '<name> suresh </name>' preserve whitespace));
      — following trigger creation is failing ,.
      create trigger trigxml after insert on t1 for each statement mode db2sql
      insert into t2 values (1,
      xmlparse(document '<name> ram </name>' preserve whitespace));

      1. derby1718-trunk-diff02.txt
        16 kB
        Yip Ng
      2. derby1718-trunk-stat02.txt
        0.7 kB
        Yip Ng
      3. derby1718-trunk-diff01.txt
        11 kB
        Yip Ng
      4. derby1718-trunk-stat01.txt
        0.5 kB
        Yip Ng
      5. ASF.LICENSE.NOT.GRANTED--stk.txt
        7 kB
        Suresh Thalamati

        Activity

        Hide
        Yip Ng added a comment -

        From looking at the stack trace, it appears that the SqlXmlUtil class which is used as a "saved object" didn't implement the org.apache.derby.iapi.services.io.Formatable interface; thus, causing the java.io.NonSerializableException when the system is externalizing the SPS object during execution of create trigger statement.

        Show
        Yip Ng added a comment - From looking at the stack trace, it appears that the SqlXmlUtil class which is used as a "saved object" didn't implement the org.apache.derby.iapi.services.io.Formatable interface; thus, causing the java.io.NonSerializableException when the system is externalizing the SPS object during execution of create trigger statement.
        Hide
        Yip Ng added a comment -

        Attaching initial patch derby1718-trunk-diff01.txt for DERBY-1718. The fix basically implements the Formatable interface for SqlXmlUtil class. Currently, it writes out the query expression string instead of the XPath object(its serializable I think), and then later recompiles the query once at evaluation time. The reason behind this is that I don't want the stored form to be tied to a particular XML implementation. But I am open if anyone feels that the Xalan XPath object should be serialized instead.

        To run the lang/xml_general.sql and lang/xmlBinding.java, please refer to the instructions in this thread:

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

        derbylang and the above xml tests passes. Running derbyall now. Appreciate if someone can review this patch. Thanks.

        Show
        Yip Ng added a comment - Attaching initial patch derby1718-trunk-diff01.txt for DERBY-1718 . The fix basically implements the Formatable interface for SqlXmlUtil class. Currently, it writes out the query expression string instead of the XPath object(its serializable I think), and then later recompiles the query once at evaluation time. The reason behind this is that I don't want the stored form to be tied to a particular XML implementation. But I am open if anyone feels that the Xalan XPath object should be serialized instead. To run the lang/xml_general.sql and lang/xmlBinding.java, please refer to the instructions in this thread: http://article.gmane.org/gmane.comp.apache.db.derby.devel/25793 derbylang and the above xml tests passes. Running derbyall now. Appreciate if someone can review this patch. Thanks.
        Hide
        A B added a comment -

        Thanks for picking this up, figuring out the problem, and providing a fix, Yip!

        I reviewed the patch and I agree that it's best to write/read the query expression itself instead of reading/writing a Xalan-specific object-so I think you did this the right way. I applied the patch and did a full build with no problems. I also ran the new test case with and without your changes and it behaved as expected-i.e. failed without the patch and passed with it.

        I then ran the xmlSuite (using ibm142) with these changes applied and I noticed that xml_general.sql fails with DerbyNet and DerbyNet client. Based on your stat file, it looks like only the embedded master file was updated. Can you run the new test with DerbyNet and DerbyNetClient, as well, and update the master files accordingly?

        One other very minor comment is the age-old comment of whitespace: prior to this patch all lines in SqlXmlUtil.java used 4 spaces, but I see that the diff01 patch introduces tabs. I think it'd be best if you could do a find/replace of tabs with spaces in just the SqlXmlUtil file--since the spacing is uniform in that file already, it'd be nice to keep it that way (even if the whole tab/space issue is still far from resolved for the codeline as a whole).

        The whitespace isssue is a nit and should not block the patch; I do, however, think that the master updates for DerbyNet and DerbyNetClient should be made before the patch is committed...

        Note: assuming you have the required classes in your classpath, you can run all of the XML tests against embedded, JCC, and Derby client by running the "xmlSuite" suite.
        Thanks again for your work on this!

        Show
        A B added a comment - Thanks for picking this up, figuring out the problem, and providing a fix, Yip! I reviewed the patch and I agree that it's best to write/read the query expression itself instead of reading/writing a Xalan-specific object- so I think you did this the right way. I applied the patch and did a full build with no problems. I also ran the new test case with and without your changes and it behaved as expected -i.e. failed without the patch and passed with it. I then ran the xmlSuite (using ibm142) with these changes applied and I noticed that xml_general.sql fails with DerbyNet and DerbyNet client. Based on your stat file, it looks like only the embedded master file was updated. Can you run the new test with DerbyNet and DerbyNetClient, as well, and update the master files accordingly? One other very minor comment is the age-old comment of whitespace: prior to this patch all lines in SqlXmlUtil.java used 4 spaces, but I see that the diff01 patch introduces tabs. I think it'd be best if you could do a find/replace of tabs with spaces in just the SqlXmlUtil file--since the spacing is uniform in that file already, it'd be nice to keep it that way (even if the whole tab/space issue is still far from resolved for the codeline as a whole). The whitespace isssue is a nit and should not block the patch; I do, however, think that the master updates for DerbyNet and DerbyNetClient should be made before the patch is committed... Note: assuming you have the required classes in your classpath, you can run all of the XML tests against embedded, JCC, and Derby client by running the "xmlSuite" suite. Thanks again for your work on this!
        Hide
        Yip Ng added a comment -

        Attaching derby1718-trunk-diff02.txt. This patch fixes the whitespace issue + the missing master files from derbynet and derbynetclient. Thanks for reviewing the patch, Army. derbyall + xmlSuite passes.

        Show
        Yip Ng added a comment - Attaching derby1718-trunk-diff02.txt. This patch fixes the whitespace issue + the missing master files from derbynet and derbynetclient. Thanks for reviewing the patch, Army. derbyall + xmlSuite passes.
        Hide
        A B added a comment -

        Thank you for the quick follow-up, Yip. I ran xmlSuite with ibm142 and all tests passed. I also verified that most of the whitespace in SqlXmlUtil.java has been fixed (there are 3 tabs remaining, but who's counting?

        +1 to commit derby1718-trunk-diff02.txt

        Thanks again.

        Show
        A B added a comment - Thank you for the quick follow-up, Yip. I ran xmlSuite with ibm142 and all tests passed. I also verified that most of the whitespace in SqlXmlUtil.java has been fixed (there are 3 tabs remaining, but who's counting? +1 to commit derby1718-trunk-diff02.txt Thanks again.
        Hide
        Mike Matrigali added a comment -

        patch is available, marking it a candidate for 10.2

        Show
        Mike Matrigali added a comment - patch is available, marking it a candidate for 10.2
        Hide
        Suresh Thalamati added a comment -

        Thanks for working on this issue Yip. I will run derbyall and commit the patch. Thanks a lot for taking time to review the patch , Army.

        /suresh

        Show
        Suresh Thalamati added a comment - Thanks for working on this issue Yip. I will run derbyall and commit the patch. Thanks a lot for taking time to review the patch , Army. /suresh
        Hide
        Suresh Thalamati added a comment -

        I did a quick review of this patch. One change that caught my attention is the new
        format id added in this patch , I am wondering if this will cause any upgrade
        issues, if some one does soft-upgrade to 10.2, creates the trigger and then
        reverts back to 10.1 I think it should not cause any problems during recovery,
        because it is written as part of another higher level object (Storable Prepared Statement).

        Any one know if the new format id will cause any errors when building the stored
        prepared statement descriptors list or when the trigger gets executed on 10.1 ?

        Thanks
        -suresh

        Show
        Suresh Thalamati added a comment - I did a quick review of this patch. One change that caught my attention is the new format id added in this patch , I am wondering if this will cause any upgrade issues, if some one does soft-upgrade to 10.2, creates the trigger and then reverts back to 10.1 I think it should not cause any problems during recovery, because it is written as part of another higher level object (Storable Prepared Statement). Any one know if the new format id will cause any errors when building the stored prepared statement descriptors list or when the trigger gets executed on 10.1 ? Thanks -suresh
        Hide
        A B added a comment -

        > Any one know if the new format id will cause any errors when building the stored
        > prepared statement descriptors list or when the trigger gets executed on 10.1 ?

        I don't really know all of the details on how SPS descriptors are "built" or what is/is not written to disk for triggers, but I did run the following scenario and everything appears to be working as expected:

        • Create a 10.1 database
        • Create tables t1 and t2 as shown in the description for this issue
        • Disconnect/shutdown 10.1 database.
        • Connect to 10.1 database with Derby 10.3 codeline, effectively doing a soft upgrade.
        • Create the trigger trigxml as shown in the description for this issue
        • Disconnect/shutdown the database.
        • Reconnect to the database using Derby 10.1 codeline (effectively "soft downgrade")
        • Execute an insert statement that fires the trigger. The insert statement was successful,
          as was the trigger statement action.

        I admit I was a bit surprised to see this work at first. However, I then looked at the SYS.SYSSTATEMENTs table and I noticed that when I first create the trigger in 10.3, the "VALID" column is set to "true". But when I reconnect with 10.1, the "VALID" column becomes "false". My understanding is that the "false" value means that when the trigger is fired, it will first be RE-compiled before getting executed. This would in turn mean that the new format id for the 10.3 SqlXmlUtil class will never actually be read when running in 10.1--instead, the trigger statement will be recompiled using 10.1 and thus we don't see any failures.

        I don't know for sure what the mechanism is, but based on this behavior it appears that any attempts to boot a database from a version that is different from the most-recently-booted version causes all stored prepared statements to be marked invalid and thus forces recompilation. It is this recompilation of the trigger statements which allows the 10.3 trigxml to execute successfully against a 10.1 database after a "soft downgrade" back to 10.1.

        That's my understanding based on the tests I ran; if anyone knows otherwise or would like to add more, please feel free...

        And thanks Suresh for taking the time to review the patch. I wasn't even thinking about upgrade issues when I did my review. Definitely true that more eyes are better...

        Show
        A B added a comment - > Any one know if the new format id will cause any errors when building the stored > prepared statement descriptors list or when the trigger gets executed on 10.1 ? I don't really know all of the details on how SPS descriptors are "built" or what is/is not written to disk for triggers, but I did run the following scenario and everything appears to be working as expected: Create a 10.1 database Create tables t1 and t2 as shown in the description for this issue Disconnect/shutdown 10.1 database. Connect to 10.1 database with Derby 10.3 codeline, effectively doing a soft upgrade. Create the trigger trigxml as shown in the description for this issue Disconnect/shutdown the database. Reconnect to the database using Derby 10.1 codeline (effectively "soft downgrade") Execute an insert statement that fires the trigger. The insert statement was successful, as was the trigger statement action. I admit I was a bit surprised to see this work at first. However, I then looked at the SYS.SYSSTATEMENTs table and I noticed that when I first create the trigger in 10.3, the "VALID" column is set to "true". But when I reconnect with 10.1, the "VALID" column becomes "false". My understanding is that the "false" value means that when the trigger is fired, it will first be RE-compiled before getting executed. This would in turn mean that the new format id for the 10.3 SqlXmlUtil class will never actually be read when running in 10.1--instead, the trigger statement will be recompiled using 10.1 and thus we don't see any failures. I don't know for sure what the mechanism is, but based on this behavior it appears that any attempts to boot a database from a version that is different from the most-recently-booted version causes all stored prepared statements to be marked invalid and thus forces recompilation. It is this recompilation of the trigger statements which allows the 10.3 trigxml to execute successfully against a 10.1 database after a "soft downgrade" back to 10.1. That's my understanding based on the tests I ran; if anyone knows otherwise or would like to add more, please feel free... And thanks Suresh for taking the time to review the patch. I wasn't even thinking about upgrade issues when I did my review. Definitely true that more eyes are better...
        Hide
        Yip Ng added a comment -

        For this type of minor revision upgrade, all the SPSs stored in SYS.SYSSTATEMENTS will get invalidated at database boot time. This logic is handled in the DD_Version class. When the trigger is fired, the SPS will get recompiled.

        Show
        Yip Ng added a comment - For this type of minor revision upgrade, all the SPSs stored in SYS.SYSSTATEMENTS will get invalidated at database boot time. This logic is handled in the DD_Version class. When the trigger is fired, the SPS will get recompiled.
        Hide
        Suresh Thalamati added a comment -

        Committed to trunk on revision 448085..

        Show
        Suresh Thalamati added a comment - Committed to trunk on revision 448085..
        Hide
        Andrew McIntyre added a comment -

        Marking resolved, merged to 10.2 with revision 448085.

        Show
        Andrew McIntyre added a comment - Marking resolved, merged to 10.2 with revision 448085.
        Hide
        Andrew McIntyre added a comment -

        This issue has been resolved for over a year with no further movement. Closing.

        Show
        Andrew McIntyre added a comment - This issue has been resolved for over a year with no further movement. Closing.

          People

          • Assignee:
            Yip Ng
            Reporter:
            Suresh Thalamati
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development