Derby
  1. Derby
  2. DERBY-4835

Trigger plan does not recompile with upgrade from 10.5.3.0 to 10.6.1.0 causing java.lang.NoSuchMethodError

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.6.1.0
    • Fix Version/s: 10.5.3.2, 10.6.2.4, 10.7.1.1
    • Component/s: SQL
    • Labels:
      None
    • Issue & fix info:
      High Value Fix, Repro attached, Workaround attached
    • Bug behavior facts:
      Regression

      Description

      Trigger plan does not recompile on upgrade from 10.5.3.0 to 10.6.1.0 causing the following exception the first time the trigger is fired after upgrade.

      ATABASE = wombat), (DRDAID = null), Failed Statement is: INSERT INTO tidlggls(blt_number,create_date,update_date,propagation_date,glossary_status,
      time_stamp,min_max_size )
      VALUES ( (select max(blt_number) from tidlrblt), CURRENT_DATE,
      CURRENT_DATE, CURRENT_DATE, '00' , CURRENT_TIMESTAMP, (select min_max_size from tidlrblt where blt_number = (select max(blt_number) from tidlrblt)))

      java.lang.NoSuchMethodError: org/apache/derby/iapi/sql/execute/ResultSetFactory.getProjectRestrictResultSet(Lorg/apache/derby/iapi/sql/execute/NoPutResultSet;Lorg/apache/derby/iapi/services/loader/GeneratedMethod;Lorg/apache/derby/iapi/services/loader/GeneratedMethod;ILorg/apache/derby/iapi/services/loader/GeneratedMethod;IZZDD)Lorg/apache/derby/iapi/sql/execute/NoPutResultSet;

      at org.apache.derby.exe.acf81e0010x012bx823cxd0d3x00000026c4a00.g0(Unknown Source)

      at org.apache.derby.exe.acf81e0010x012bx823cxd0d3x00000026c4a00.execute(Unknown Source)

      at org.apache.derby.impl.sql.GenericActivationHolder.execute(Unknown Source)

      at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)

      at org.apache.derby.impl.sql.GenericPreparedStatement.executeSubStatement(Unknown Source)

      at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)

      at org.apache.derby.impl.sql.execute.StatementTriggerExecutor.fireTrigger(Unknown Source)

      at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)

      at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(Unknown Source)

      at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown Source)

      at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)

      at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)

      at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)

      at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)

      at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)

      at org.apache.derby.impl.tools.ij.ij.executeImmediate(Unknown Source)

      at org.apache.derby.impl.tools.ij.utilMain.doCatch(Unknown Source)

      at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(Unknown Source)

      at org.apache.derby.impl.tools.ij.utilMain.go(Unknown Source)

      at org.apache.derby.impl.tools.ij.Main.go(Unknown Source)

      at org.apache.derby.impl.tools.ij.Main.mainCore(Unknown Source)

      at org.apache.derby.impl.tools.ij.Main.main(Unknown Source)

      at org.apache.derby.tools.ij.main(Unknown Source)

      Cleanup action completed

      To reproduce, run the attached script 10_5_3_work.sql with the 10.5.3.0 release and then connect with 10.6.1.0 and insert into the table with the trigger:

      connect 'jdbc:derby:wombat;upgrade=true';

      INSERT INTO tidlrblt(BLT,BLT_SIZE,MIN_MAX_SIZE) VALUES('Mamatha Testing2', 15, 20);
      ERROR XJ001: Java exception: 'org/apache/derby/iapi/sql/execute/ResultSetFactory
      .getProjectRestrictResultSet(Lorg/apache/derby/iapi/sql/execute/NoPutResultSet;L
      org/apache/derby/iapi/services/loader/GeneratedMethod;Lorg/apache/derby/iapi/ser
      vices/loader/GeneratedMethod;ILorg/apache/derby/iapi/services/loader/GeneratedMe
      thod;IZZDD)Lorg/apache/derby/iapi/sql/execute/NoPutResultSet;: java.lang.NoSuchM
      ethodError'.

      I think this may be related to the DERBY-1107 change in handleMinorRevisionChange which has the code:
      if (fromVersion.majorVersionNumber >= DataDictionary.DD_VERSION_DERBY_10_5)
      bootingDictionary.updateMetadataSPSes(tc);
      else
      bootingDictionary.clearSPSPlans();

      Likely, clearSPSPlans() should not be in the else clause but rather executed unconditionally.

      To work around the issue, after connecting with 10.6.1, drop and recreate the trigger as in workaround.sql

        Issue Links

          Activity

          Hide
          Mamta A. Satoor added a comment -

          Thanks to Mamatha for providing a stand alone test case.

          Also, credit for the jira really goes to Kathey since she pointed out exactly what part of the code needed to be fixed.

          The fix now is in all the affected releases so I will go ahead and resolve the issue.

          Show
          Mamta A. Satoor added a comment - Thanks to Mamatha for providing a stand alone test case. Also, credit for the jira really goes to Kathey since she pointed out exactly what part of the code needed to be fixed. The fix now is in all the affected releases so I will go ahead and resolve the issue.
          Hide
          Kathey Marsden added a comment -

          I removed the scripts from the user case and am replacing with a link to the email thread.
          http://old.nabble.com/Problem!-tt29737134.html#a29894020

          Mamta did not need the user case to put a test in the regression suite, so no Apache grant is needed.

          Show
          Kathey Marsden added a comment - I removed the scripts from the user case and am replacing with a link to the email thread. http://old.nabble.com/Problem!-tt29737134.html#a29894020 Mamta did not need the user case to put a test in the regression suite, so no Apache grant is needed.
          Hide
          Mamta A. Satoor added a comment -

          Yes, if the user upgraded from 10.6.2.1->10.6.3.x, that will fix the trigger problem because they will get invalidated during the upgrade. I double checked that by trying it here in my codeline. ie I created the db with 10.5.x, upgraded it to 10.6.2.1 and saw the trigger fail. Backported the change to my 10.6 codeline and bumped the release number and then upgraded the db again with those changes and the trigger worked fine.

          Show
          Mamta A. Satoor added a comment - Yes, if the user upgraded from 10.6.2.1->10.6.3.x, that will fix the trigger problem because they will get invalidated during the upgrade. I double checked that by trying it here in my codeline. ie I created the db with 10.5.x, upgraded it to 10.6.2.1 and saw the trigger fail. Backported the change to my 10.6 codeline and bumped the release number and then upgraded the db again with those changes and the trigger worked fine.
          Hide
          Knut Anders Hatlen added a comment -

          Thanks Mamta. My question was a bit unclear. Here's what I meant to ask:

          After this fix has been backported to 10.6 and goes into a release (let's say 10.6.3.x), will it also fix the upgrade path 10.5.x -> 10.6.2.1 -> 10.6.3.x? Or will it only fix upgrades directly from 10.5.x to 10.6.3.x?

          Show
          Knut Anders Hatlen added a comment - Thanks Mamta. My question was a bit unclear. Here's what I meant to ask: After this fix has been backported to 10.6 and goes into a release (let's say 10.6.3.x), will it also fix the upgrade path 10.5.x -> 10.6.2.1 -> 10.6.3.x? Or will it only fix upgrades directly from 10.5.x to 10.6.3.x?
          Hide
          Mamta A. Satoor added a comment -

          Knut, I plan to backport this change to 10.6 this upcoming week. If the user upgrades to that release, then they don't need to drop and recreate the triggers.

          Show
          Mamta A. Satoor added a comment - Knut, I plan to backport this change to 10.6 this upcoming week. If the user upgrades to that release, then they don't need to drop and recreate the triggers.
          Hide
          Knut Anders Hatlen added a comment -

          Thanks for writing the fix and the test case, Mamta.

          Does the patch also fix the case where a database is first upgraded to 10.6.[12] and then to 10.6.3? Or would the users still need to drop and recreate the triggers in that case?

          Show
          Knut Anders Hatlen added a comment - Thanks for writing the fix and the test case, Mamta. Does the patch also fix the case where a database is first upgraded to 10.6. [12] and then to 10.6.3? Or would the users still need to drop and recreate the triggers in that case?
          Hide
          Mamta A. Satoor added a comment -

          Wanted to mention that I have verified that the new test for trigger will fail as part of the upgrade suite if the accompanying code changes are not made. So, the patch has the needed test coverage for the code changes.

          Show
          Mamta A. Satoor added a comment - Wanted to mention that I have verified that the new test for trigger will fail as part of the upgrade suite if the accompanying code changes are not made. So, the patch has the needed test coverage for the code changes.
          Hide
          Mamta A. Satoor added a comment -

          Attaching a patch to fix the problem.

          The problem was that the clearing of stored statements during the upgrade was conditional. The clearing should happen
          unconditionally during upgrade. Unconditional clearing of SPSes has fixed the problem with this jira.

          Also, added a test in upgrade suite for triggers. The test has been added to BasicSetup.java which will ensure that this trigger
          test will automatically get run with even future releases.

          If there are no objections, I will go ahead and commit this early next week and backport it to 10.6 and 10.5 releases(10.5 is when the SPS clearing became conditional at the uprgrade time.)

          Show
          Mamta A. Satoor added a comment - Attaching a patch to fix the problem. The problem was that the clearing of stored statements during the upgrade was conditional. The clearing should happen unconditionally during upgrade. Unconditional clearing of SPSes has fixed the problem with this jira. Also, added a test in upgrade suite for triggers. The test has been added to BasicSetup.java which will ensure that this trigger test will automatically get run with even future releases. If there are no objections, I will go ahead and commit this early next week and backport it to 10.6 and 10.5 releases(10.5 is when the SPS clearing became conditional at the uprgrade time.)
          Hide
          Kathey Marsden added a comment -

          Yes Mike, you are right. The last digit should be bumped on the branches when this is backported so that upgrade gets triggered.

          Show
          Kathey Marsden added a comment - Yes Mike, you are right. The last digit should be bumped on the branches when this is backported so that upgrade gets triggered.
          Hide
          Mike Matrigali added a comment -

          will it be necessary to bump the version number once this fix is applied to catch cases where users have already passed through the "bad" upgrade path in order to force it through the minor upgrade path code again?

          Show
          Mike Matrigali added a comment - will it be necessary to bump the version number once this fix is applied to catch cases where users have already passed through the "bad" upgrade path in order to force it through the minor upgrade path code again?
          Hide
          Kathey Marsden added a comment -

          scripts to recreate the issue.
          Run 10_5_3_work.sql with 10.5.3.0 release
          Run 10_6_1_work.sql with 10.6.1.0 release

          workaround.sql contains the commands to drop and recreate the trigger with 10.6 which will work around the problem.

          Show
          Kathey Marsden added a comment - scripts to recreate the issue. Run 10_5_3_work.sql with 10.5.3.0 release Run 10_6_1_work.sql with 10.6.1.0 release workaround.sql contains the commands to drop and recreate the trigger with 10.6 which will work around the problem.

            People

            • Assignee:
              Mamta A. Satoor
              Reporter:
              Kathey Marsden
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development