Derby
  1. Derby
  2. DERBY-5280

Large batch of DDL in a database procedure dies on a transaction severity error.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.8.1.2, 10.9.1.0
    • Fix Version/s: 10.8.2.2, 10.9.1.0
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Regression

      Description

      The batch of DDL run by the procedure which registers database metadata functions now dies with the following error:

      ERROR 40XC0: Dead statement. This may be caused by catching a transaction severity error inside this statement.

      A process of binary search shows that this problem was introduced by revision 1086920 as part of the work on DERBY-5161.

      The bug can be reproduced by compiling the DBMDWrapper class attached to DERBY-3973 and then running the following script:

      connect 'jdbc:derby:memory:db;create=true';

      create procedure registerPublicStaticMethods( in connectionURL varchar( 200 ), in printSQL boolean )
      language java parameter style java modifies sql data
      external name 'DBMDWrapper.registerPublicStaticMethods';

      call registerPublicStaticMethods( 'jdbc:default:connection', false );

      If you change the second argument to registerPublicStaticMethods to true, then you will see all of the DDL being issued by the database procedure. The procedure runs fine in 10.7 but fails with this error in 10.8.

      1. junit-repro.diff
        2 kB
        Knut Anders Hatlen
      2. d5280.diff
        3 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Hide
          Rick Hillegas added a comment -

          Thanks for fixing this, Knut. I have verified that the DDMDWrapper registration procedure now runs cleanly. Closing this issue.

          Show
          Rick Hillegas added a comment - Thanks for fixing this, Knut. I have verified that the DDMDWrapper registration procedure now runs cleanly. Closing this issue.
          Hide
          Knut Anders Hatlen added a comment - - edited

          Merged fix to 10.8.
          Committed revision 1138856.

          Show
          Knut Anders Hatlen added a comment - - edited Merged fix to 10.8. Committed revision 1138856.
          Hide
          Knut Anders Hatlen added a comment -

          All regression tests ran cleanly with DERBY-5161 backed out. Committed to trunk with revision 1138787.

          Show
          Knut Anders Hatlen added a comment - All regression tests ran cleanly with DERBY-5161 backed out. Committed to trunk with revision 1138787.
          Hide
          Rick Hillegas added a comment -

          Thanks for looking into this, Knut. +1 to backing out the previous improvement and not over-rotating on a more involved solution at this time. Thanks.

          Show
          Rick Hillegas added a comment - Thanks for looking into this, Knut. +1 to backing out the previous improvement and not over-rotating on a more involved solution at this time. Thanks.
          Hide
          Knut Anders Hatlen added a comment -

          The cleanup in DERBY-5161 always pops the right statement context, since it specifies explicitly which context to pop. However, there's a higher-level cleanup that detects statement severity errors and pops the first statement context it finds if there is one. In this case, where we have nested statement contexts, that causes the parent statement context to be popped. I think it's this higher-level cleanup that's missing when preparing an internal statement fails and that caused the problem seen in DERBY-5161.

          A proper fix for DERBY-5161 might be to get this higher-level cleanup to kick in somehow also for errors when preparing internal statements, or keeping the low-level cleanup added in DERBY-5161 and making the higher-level detect that cleanup has already happened. However, since DERBY-5161 is believed not to be possible after DERBY-5157, it's probably safer to just back out the fix for DERBY-5161 than trying to devise an even more complex fix for something that's most likely not broken.

          Show
          Knut Anders Hatlen added a comment - The cleanup in DERBY-5161 always pops the right statement context, since it specifies explicitly which context to pop. However, there's a higher-level cleanup that detects statement severity errors and pops the first statement context it finds if there is one. In this case, where we have nested statement contexts, that causes the parent statement context to be popped. I think it's this higher-level cleanup that's missing when preparing an internal statement fails and that caused the problem seen in DERBY-5161 . A proper fix for DERBY-5161 might be to get this higher-level cleanup to kick in somehow also for errors when preparing internal statements, or keeping the low-level cleanup added in DERBY-5161 and making the higher-level detect that cleanup has already happened. However, since DERBY-5161 is believed not to be possible after DERBY-5157 , it's probably safer to just back out the fix for DERBY-5161 than trying to devise an even more complex fix for something that's most likely not broken.
          Hide
          Knut Anders Hatlen added a comment -

          Attached is a JUnit test case that reproduces the bug.

          Show
          Knut Anders Hatlen added a comment - Attached is a JUnit test case that reproduces the bug.
          Hide
          Knut Anders Hatlen added a comment -

          If we don't find a better solution, it would probably not do much harm to back out the fix for DERBY-5161. The problem it's supposed to fix shouldn't happen after DERBY-5157.

          The stored procedure that gets called in the repro ignores many SQLExceptions that happen during normal operation (typically DROP FUNCTION statements that fail because the function doesn't exist). It looks like the extra cleanup on error that was added in DERBY-5161 somehow kills the parent statement and not only the statement running inside the stored procedure.

          Show
          Knut Anders Hatlen added a comment - If we don't find a better solution, it would probably not do much harm to back out the fix for DERBY-5161 . The problem it's supposed to fix shouldn't happen after DERBY-5157 . The stored procedure that gets called in the repro ignores many SQLExceptions that happen during normal operation (typically DROP FUNCTION statements that fail because the function doesn't exist). It looks like the extra cleanup on error that was added in DERBY-5161 somehow kills the parent statement and not only the statement running inside the stored procedure.

            People

            • Assignee:
              Knut Anders Hatlen
              Reporter:
              Rick Hillegas
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development