Derby
  1. Derby
  2. DERBY-1107

For existing databases JDBC metadata queries do not get updated properly between maintenance versions.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6
    • Fix Version/s: 10.5.1.1
    • Component/s: JDBC
    • Labels:
      None
    • Issue & fix info:
      High Value Fix

      Description

      The JDBC DatabaseMetaData queries are stored as stored prepared statements in the database. If a bug is fixed for any of the metadata calls it can require that these queries be changed. Currently existing databases will not get updated properly if a bug is fixed. Ideally the metadata queries should match the derby version that is running. That way we avoid situations where the query is not compatible with the Derby version running.

      To confirm I :
      1) created a database with 10.1.1.0
      2) Made a metadata change in my 10.1.2.4 client.
      3) Connected to the 10.1.1.0 database with 10.1.2.4 and saw that there was no change to the stored prepared statements in SYS.SYSSTATEMENTS

      I also confirmed that a database created with 10.1.2.4 does not get changed when reverting to 10.1.1.0.

      Below this line is some history and reference that might be helful to someone fixing this issue:
      ------------------------------------------------------------------------------------------------------------------------------------------------
      In discussing DERBY-970, the subject of the metadata stored prepared statements
      came up.
      The general questions are:

      1) Why do we use stored prepared statements for metadata queries?
      2) What issues might there be related to upgrade/downgrade with the
      metadata stored prepared statements?
      3) How do we address potential upgrade/downgrade issues?

      GENERAL HISTORY:

      • Cloudscape 5.x had stored prepared statements, a way to store precompiled
        statements in the database. This is no longer exposed externally.
      • Metadata stored prepared statements were a performance optimization that
        predated the statement cache.
      • In the past, this performance optimization has been of particular importance
        to gui database browsers that execute all the metadata methods on connection to
        the database. This would still probably be an issue with embedded even with the
        statement cache.
      • All stored prepared statements get recompiled on the first connection to the
        database if the version changes.

      UPGRADE HISTORY

      • In Cloudscape 5.1, the metadata stored prepared statements have traditionally
        been a source of trouble for even minor version changes as queries change or
        they refer to methods/stored procedures that may or may not exist in the target
        version and cannot recompile or execute.
      • The solution to the problem in Cloudscape v5.1.60 was to automatically
        always call DD_Version.dropJDBCMetadataSPSes() whenever the version changed up
        or down in upgradeIfNeeded().
      • The workaround before this change to do this automatically was to call this
        method manually:
        CALL Factory.getDatabaseOfConnection().
        dropAllJDBCMetaDataSPSes()

      HOW DERBY WORKS TODAY:

      • In Derby we now only call dropJDBCMetadataSPSes() on fullUpgrade and it has
        been this way since contribution.
      • I think the problems of upgrade/downgrade for metadata stored prepared
        statements may exist in Derby.
      • I don't know a workaround to drop the metadata stored prepared statements if
        we need to deliver a bug fix or how the ugprade/downgrade is handled currently.
      • I seem to recall some special handling in Derby for soft upgrade for optimizer directives, but don't know the details.

      RECENT DISCUSSIONS:
      In discussing DERBY-970, the subject of the metadata stored prepared statements
      came up.
      The general questions are:

      1) Why do we use stored prepared statements for metadata queries?
      2) What issues might there be related to upgrade/downgrade with the
      metadata stored prepared statements?
      3) How do we address potential upgrade/downgrade issues?

      MY QUESTIONS
      Anyone know when/why the dropJDBCMetadataSPSes() on all version changes was
      removed between Cloudcape 5.1.60 and contribution?
      How do we deliver bug fixes for metadata queries or handle changes in the
      metadata queries in Derby?

      1. derby-1107_noproc_diff.txt
        6 kB
        Kathey Marsden
      2. derby-1107_diff2.txt
        9 kB
        Kathey Marsden
      3. derby-1107_diff.txt
        6 kB
        Kathey Marsden
      4. derby-1107-proposal1.diff
        1 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          I have had a look at the code, and I'm not sure there is a problem
          with SPSs and upgrade.

          In hard/full upgrade, dropJDBCMetadataSPSes() is called, hence the
          SPSs are dropped and regenerated.

          In soft upgrade mode, SPSs aren't used. Instead,
          EmbedDatabaseMetaData.getPreparedQuery() will read the query directly
          from metadata.properties.

          When you did the upgrade/downgrade test, did you confirm that the
          metadata calls returned incorrect/old results, or did you only look at
          the contents of SYS.SYSSTATEMENTS?

          Show
          Knut Anders Hatlen added a comment - I have had a look at the code, and I'm not sure there is a problem with SPSs and upgrade. In hard/full upgrade, dropJDBCMetadataSPSes() is called, hence the SPSs are dropped and regenerated. In soft upgrade mode, SPSs aren't used. Instead, EmbedDatabaseMetaData.getPreparedQuery() will read the query directly from metadata.properties. When you did the upgrade/downgrade test, did you confirm that the metadata calls returned incorrect/old results, or did you only look at the contents of SYS.SYSSTATEMENTS?
          Hide
          Kathey Marsden added a comment -

          This issue is not related to upgrade but to maintenance version (3 and 4th digit ) changes.
          If a bug is fixed in the metadata on a new maintenence version the bug fix is not picked up if you change to a newer version. e.g. 10.1.2.1 to 10.1.2.4. Nor does it go back to the old query version when you go back to 10.1.2.1.

          I tend to think handleMinorRevisionChange needs to drop the JDBC SPS's in addition to clearing the SPS plans in this case, but am not totally sure.

          At the time I tested I just checked the SPS's but I think that a metadata call would not act like soft upgrade for this case.

          Show
          Kathey Marsden added a comment - This issue is not related to upgrade but to maintenance version (3 and 4th digit ) changes. If a bug is fixed in the metadata on a new maintenence version the bug fix is not picked up if you change to a newer version. e.g. 10.1.2.1 to 10.1.2.4. Nor does it go back to the old query version when you go back to 10.1.2.1. I tend to think handleMinorRevisionChange needs to drop the JDBC SPS's in addition to clearing the SPS plans in this case, but am not totally sure. At the time I tested I just checked the SPS's but I think that a metadata call would not act like soft upgrade for this case.
          Hide
          Knut Anders Hatlen added a comment -

          I tried running a test like the one Kathey did, and I can verify that
          the queries returned the old results even after the upgrade. Adding
          code for dropping and regenerating the SPSs in
          handleMinorRevisionChange() made it work.

          Since handleMinorRevisionChange() is called on all types of version
          changes, I suggest that we move the code for dropping/regenerating
          SPSs from doFullUpgrade() to handleMinorRevisionChange(). The attached
          patch (derby-1107-proposal1.diff) shows this change. I have not tested
          the patch very much, it is only meant as a starting point for a
          discussion on how to solve the problem.

          I can't see that dropping the SPSs between maintenance releases should
          cause any problems.

          Show
          Knut Anders Hatlen added a comment - I tried running a test like the one Kathey did, and I can verify that the queries returned the old results even after the upgrade. Adding code for dropping and regenerating the SPSs in handleMinorRevisionChange() made it work. Since handleMinorRevisionChange() is called on all types of version changes, I suggest that we move the code for dropping/regenerating SPSs from doFullUpgrade() to handleMinorRevisionChange(). The attached patch (derby-1107-proposal1.diff) shows this change. I have not tested the patch very much, it is only meant as a starting point for a discussion on how to solve the problem. I can't see that dropping the SPSs between maintenance releases should cause any problems.
          Hide
          Kathey Marsden added a comment -

          Thanks Knut for looking at this. I have always known there were issues with this but never understood them well enough to dive in myself and think I could fix it so bear with me while I ask some questions about your fix and also the preexisting code.

          BEFORE YOUR CHANGE.

          1) There is one statement in the SYSIBM schema, METADATA but it does not get created here. There is a method in DataDictionaryImple createSystemSps that creates both but that is not called here. It does seem to get created when a database is created via a call DataDictionaryImpl.createSystemSps. Should it be created and what is the impact of not creating it?

          AFTER YOUR CHANGE

          2) I think even if this goes into 10.1 right away, the bug still exists when reverting to versions earlier than the fix. This means that we would have to be careful to make sure any changes to the metadata queries worked with the old version. For example we could not add a new function call to the metadata queries that get loaded with 10.1, because then reverting to the old version would fail because the java class would not be there. Maybe that is not so bad, because it is only changes on 10.1 itself we would have to watch out for. For 10.2 and higher it would get loaded from metadata.properties on soft upgrade. Is that correct?

          1) It seems to me that assuming your fix went into 10.1 right away, we still have and will always have an issue with the metadata statements getting updated properly when you go back to a 10.1 version prior to the fix for DERBY-1107.
          This would mean that none of the existing statements could have new stored procedure calls added. This is because those stored procedures could not be used when you go back to a previous versions. Is that correct?

          Bear with me while I

          Then the only issue we have is one that cannot be solved.
          That problem is that metadata queries will not get dropped when reverting to 10.1 releases before

          The issue that we will always have to be careful of is that the metadata queries that get accessed with the metadata queries that get loaded with 10.1 will not be able to reference any new stored procedures, because. Addition of

          Show
          Kathey Marsden added a comment - Thanks Knut for looking at this. I have always known there were issues with this but never understood them well enough to dive in myself and think I could fix it so bear with me while I ask some questions about your fix and also the preexisting code. BEFORE YOUR CHANGE. 1) There is one statement in the SYSIBM schema, METADATA but it does not get created here. There is a method in DataDictionaryImple createSystemSps that creates both but that is not called here. It does seem to get created when a database is created via a call DataDictionaryImpl.createSystemSps. Should it be created and what is the impact of not creating it? AFTER YOUR CHANGE 2) I think even if this goes into 10.1 right away, the bug still exists when reverting to versions earlier than the fix. This means that we would have to be careful to make sure any changes to the metadata queries worked with the old version. For example we could not add a new function call to the metadata queries that get loaded with 10.1, because then reverting to the old version would fail because the java class would not be there. Maybe that is not so bad, because it is only changes on 10.1 itself we would have to watch out for. For 10.2 and higher it would get loaded from metadata.properties on soft upgrade. Is that correct? 1) It seems to me that assuming your fix went into 10.1 right away, we still have and will always have an issue with the metadata statements getting updated properly when you go back to a 10.1 version prior to the fix for DERBY-1107 . This would mean that none of the existing statements could have new stored procedure calls added. This is because those stored procedures could not be used when you go back to a previous versions. Is that correct? Bear with me while I Then the only issue we have is one that cannot be solved. That problem is that metadata queries will not get dropped when reverting to 10.1 releases before The issue that we will always have to be careful of is that the metadata queries that get accessed with the metadata queries that get loaded with 10.1 will not be able to reference any new stored procedures, because. Addition of
          Hide
          Kathey Marsden added a comment -

          Sorry there was some cruft that printed with my previous comment.
          Can't delete anymore. Comment should have been just the first part:

          Thanks Knut for looking at this. I have always known there were issues with this but never understood them well enough to dive in myself and think I could fix it so bear with me while I ask some questions about your fix and also the preexisting code.

          BEFORE YOUR CHANGE.

          1) There is one statement in the SYSIBM schema, METADATA but it does not get created here. There is a method in DataDictionaryImple createSystemSps that creates both but that is not called here. It does seem to get created when a database is created via a call DataDictionaryImpl.createSystemSps. Should it be created and what is the impact of not creating it?

          AFTER YOUR CHANGE

          2) I think even if this goes into 10.1 right away, the bug still exists when reverting to versions earlier than the fix. This means that we would have to be careful to make sure any changes to the metadata queries worked with the old version. For example we could not add a new function call to the metadata queries that get loaded with 10.1, because then reverting to the old version would fail because the java class would not be there. Maybe that is not so bad, because it is only changes on 10.1 itself we would have to watch out for. For 10.2 and higher it would get loaded from metadata.properties on soft upgrade. Is that correct?

          Show
          Kathey Marsden added a comment - Sorry there was some cruft that printed with my previous comment. Can't delete anymore. Comment should have been just the first part: Thanks Knut for looking at this. I have always known there were issues with this but never understood them well enough to dive in myself and think I could fix it so bear with me while I ask some questions about your fix and also the preexisting code. BEFORE YOUR CHANGE. 1) There is one statement in the SYSIBM schema, METADATA but it does not get created here. There is a method in DataDictionaryImple createSystemSps that creates both but that is not called here. It does seem to get created when a database is created via a call DataDictionaryImpl.createSystemSps. Should it be created and what is the impact of not creating it? AFTER YOUR CHANGE 2) I think even if this goes into 10.1 right away, the bug still exists when reverting to versions earlier than the fix. This means that we would have to be careful to make sure any changes to the metadata queries worked with the old version. For example we could not add a new function call to the metadata queries that get loaded with 10.1, because then reverting to the old version would fail because the java class would not be there. Maybe that is not so bad, because it is only changes on 10.1 itself we would have to watch out for. For 10.2 and higher it would get loaded from metadata.properties on soft upgrade. Is that correct?
          Hide
          Knut Anders Hatlen added a comment -

          Kathey wrote:

          > BEFORE YOUR CHANGE.
          >
          > 1) There is one statement in the SYSIBM schema, METADATA but it does
          > not get created here. There is a method in DataDictionaryImple
          > createSystemSps that creates both but that is not called here. It
          > does seem to get created when a database is created via a call
          > DataDictionaryImpl.createSystemSps. Should it be created and what
          > is the impact of not creating it?

          This is interesting! Only statements in the SYS schema are dropped and
          regenerated. To check what happens on upgrade, I created a database
          with 10.1.2.1 and looked at the values returned by "execute statement
          SYSIBM.METADATA" in 10.2 with soft and hard upgrade. The values were
          not the same as when I created the database with 10.2. The changes
          are probably caused by DERBY-965 which modified the METADATA
          statement.

          I think we should drop the SPSs in both SYSIBM and SYS when performing
          a hard upgrade and use createSystemSps() to regenerate them. This
          would however not solve the soft upgrade case for SYSIBM.METADATA. We
          need to make SystemProcedures.METADATA() read metadata_net.properties
          when running in soft upgrade mode.

          > AFTER YOUR CHANGE
          >
          > 2) I think even if this goes into 10.1 right away, the bug still
          > exists when reverting to versions earlier than the fix. This
          > means that we would have to be careful to make sure any changes
          > to the metadata queries worked with the old version. For example
          > we could not add a new function call to the metadata queries that
          > get loaded with 10.1, because then reverting to the old version
          > would fail because the java class would not be there. Maybe that
          > is not so bad, because it is only changes on 10.1 itself we would
          > have to watch out for. For 10.2 and higher it would get loaded
          > from metadata.properties on soft upgrade. Is that correct?

          All of what you said above is correct.

          Show
          Knut Anders Hatlen added a comment - Kathey wrote: > BEFORE YOUR CHANGE. > > 1) There is one statement in the SYSIBM schema, METADATA but it does > not get created here. There is a method in DataDictionaryImple > createSystemSps that creates both but that is not called here. It > does seem to get created when a database is created via a call > DataDictionaryImpl.createSystemSps. Should it be created and what > is the impact of not creating it? This is interesting! Only statements in the SYS schema are dropped and regenerated. To check what happens on upgrade, I created a database with 10.1.2.1 and looked at the values returned by "execute statement SYSIBM.METADATA" in 10.2 with soft and hard upgrade. The values were not the same as when I created the database with 10.2. The changes are probably caused by DERBY-965 which modified the METADATA statement. I think we should drop the SPSs in both SYSIBM and SYS when performing a hard upgrade and use createSystemSps() to regenerate them. This would however not solve the soft upgrade case for SYSIBM.METADATA. We need to make SystemProcedures.METADATA() read metadata_net.properties when running in soft upgrade mode. > AFTER YOUR CHANGE > > 2) I think even if this goes into 10.1 right away, the bug still > exists when reverting to versions earlier than the fix. This > means that we would have to be careful to make sure any changes > to the metadata queries worked with the old version. For example > we could not add a new function call to the metadata queries that > get loaded with 10.1, because then reverting to the old version > would fail because the java class would not be there. Maybe that > is not so bad, because it is only changes on 10.1 itself we would > have to watch out for. For 10.2 and higher it would get loaded > from metadata.properties on soft upgrade. Is that correct? All of what you said above is correct.
          Hide
          Dag H. Wanvik added a comment -

          > I think we should drop the SPSs in both SYSIBM and SYS when performing
          > a hard upgrade and use createSystemSps() to regenerate them. This
          > would however not solve the soft upgrade case for SYSIBM.METADATA. We
          > need to make SystemProcedures.METADATA() read metadata_net.properties
          > when running in soft upgrade mode.

          Good find!. This would need to get fixed for SUR metadata changes to
          work correctly under both soft and hard upgrade when running with the
          client! (it would not be a regression though, due to DERBY-965).
          I think your suggestion makes good sense.

          Show
          Dag H. Wanvik added a comment - > I think we should drop the SPSs in both SYSIBM and SYS when performing > a hard upgrade and use createSystemSps() to regenerate them. This > would however not solve the soft upgrade case for SYSIBM.METADATA. We > need to make SystemProcedures.METADATA() read metadata_net.properties > when running in soft upgrade mode. Good find!. This would need to get fixed for SUR metadata changes to work correctly under both soft and hard upgrade when running with the client! (it would not be a regression though, due to DERBY-965 ). I think your suggestion makes good sense.
          Hide
          Dag H. Wanvik added a comment -

          > I think we should drop the SPSs in both SYSIBM and SYS when performing
          > a hard upgrade and use createSystemSps() to regenerate them. This
          > would however not solve the soft upgrade case for SYSIBM.METADATA. We
          > need to make SystemProcedures.METADATA() read metadata_net.properties
          > when running in soft upgrade mode.

          Good find!. This would need to get fixed for SUR metadata changes to
          work correctly under both soft and hard upgrade when running with the
          client! (it would not be a regression though, due to DERBY-965).
          I think your suggestion makes good sense.

          Show
          Dag H. Wanvik added a comment - > I think we should drop the SPSs in both SYSIBM and SYS when performing > a hard upgrade and use createSystemSps() to regenerate them. This > would however not solve the soft upgrade case for SYSIBM.METADATA. We > need to make SystemProcedures.METADATA() read metadata_net.properties > when running in soft upgrade mode. Good find!. This would need to get fixed for SUR metadata changes to work correctly under both soft and hard upgrade when running with the client! (it would not be a regression though, due to DERBY-965 ). I think your suggestion makes good sense.
          Hide
          Kathey Marsden added a comment -

          I wonder if the best way to resolve this is to introduce a new stored procedure that can drop all the metadata SPS;s similar to the old Cloudscape procedure
          http://publibfi.boulder.ibm.com/epubs/html/cloud51/javadoc/com/ibm/db2j/database/Database.html#dropAllJDBCMetaDataSPSes()

          This could be run, if someone needed an update to a metadata statement on upgrade.

          Show
          Kathey Marsden added a comment - I wonder if the best way to resolve this is to introduce a new stored procedure that can drop all the metadata SPS;s similar to the old Cloudscape procedure http://publibfi.boulder.ibm.com/epubs/html/cloud51/javadoc/com/ibm/db2j/database/Database.html#dropAllJDBCMetaDataSPSes( ) This could be run, if someone needed an update to a metadata statement on upgrade.
          Hide
          Kathey Marsden added a comment -

          I think I will approach this by creating a stored procedure
          SYSCS_UTIL.SYSCS_UPDATE_METADATA_QUERIES()

          I think trying to do something automatic is going to be problematic because if we automatically update the metadata queries on upgrade, they may not be valid if the user downgrades their version. Please let me know if you have any concerns.

          Silly question...

          Is it ok/possible to add a system stored procedure in a maintenance branch or would this change have to be limited to the trunk.

          Show
          Kathey Marsden added a comment - I think I will approach this by creating a stored procedure SYSCS_UTIL.SYSCS_UPDATE_METADATA_QUERIES() I think trying to do something automatic is going to be problematic because if we automatically update the metadata queries on upgrade, they may not be valid if the user downgrades their version. Please let me know if you have any concerns. Silly question... Is it ok/possible to add a system stored procedure in a maintenance branch or would this change have to be limited to the trunk.
          Hide
          Knut Anders Hatlen added a comment -

          > Is it ok/possible to add a system stored procedure in a maintenance branch or would this change have to be limited to the trunk.

          Possible: Not sure. I thought the system stored procedures were only created when the database was created or when the database was upgraded (new minor version). So I think you would need (a) a way to create the procedure when no database upgrade has taken place, and (b) a way to tell the upgrade code to only create this procedure if it hasn't already been created. Those mechanisms haven't been needed before since the set of system procedures was uniquely identified by the major.minor version.

          Show
          Knut Anders Hatlen added a comment - > Is it ok/possible to add a system stored procedure in a maintenance branch or would this change have to be limited to the trunk. Possible: Not sure. I thought the system stored procedures were only created when the database was created or when the database was upgraded (new minor version). So I think you would need (a) a way to create the procedure when no database upgrade has taken place, and (b) a way to tell the upgrade code to only create this procedure if it hasn't already been created. Those mechanisms haven't been needed before since the set of system procedures was uniquely identified by the major.minor version.
          Hide
          Kathey Marsden added a comment -

          Attached is a patch to add the SYSCS_UTIL.SYSCS_UPDATE_METADATA_QUERIES()

          system stored procedure. I am running tests now. I manually tested the change by backing out the change for DERBY-876 and creating a database and then calling the stored procedure once the correct metadata.properties was restored.

          The patch doesn't include regression tests. I am not quite sure how to automate regression tests for this. Any advice is welcome.

          Show
          Kathey Marsden added a comment - Attached is a patch to add the SYSCS_UTIL.SYSCS_UPDATE_METADATA_QUERIES() system stored procedure. I am running tests now. I manually tested the change by backing out the change for DERBY-876 and creating a database and then calling the stored procedure once the correct metadata.properties was restored. The patch doesn't include regression tests. I am not quite sure how to automate regression tests for this. Any advice is welcome.
          Hide
          Kathey Marsden added a comment -

          There were a couple of tests that needed to be updated because of the extra system procedure. Those are included in the new patch derby-1107_diff2.txt

          Show
          Kathey Marsden added a comment - There were a couple of tests that needed to be updated because of the extra system procedure. Those are included in the new patch derby-1107_diff2.txt
          Hide
          Myrna van Lunteren added a comment -

          I had a look at the patch and it all seems reasonable to me.

          Too bad we can't do something automatic. Maybe it's something to keep in mind for a possible version 11...(then we're talking migration rather than upgrade, and we could make the change to always drop & recreate).

          I do think the new procedure needs to be documented in such a way that customers doing upgrades can find it.

          Show
          Myrna van Lunteren added a comment - I had a look at the patch and it all seems reasonable to me. Too bad we can't do something automatic. Maybe it's something to keep in mind for a possible version 11...(then we're talking migration rather than upgrade, and we could make the change to always drop & recreate). I do think the new procedure needs to be documented in such a way that customers doing upgrades can find it.
          Hide
          Kim Haase added a comment -

          I created DERBY-3933 to add documentation for this procedure to the Reference Manual.

          Show
          Kim Haase added a comment - I created DERBY-3933 to add documentation for this procedure to the Reference Manual.
          Hide
          Knut Anders Hatlen added a comment -

          Could the procedure be tested by checking the columns LASTCOMPILED and VALID in SYS.SYSSTATEMENTS? VALID is true when the statement has been compiled and no recompilation is needed, and false otherwise. LASTCOMPILED should contain a time stamp telling the last time the statement was compiled. So after the procedure has been called, all meta-data statements should have VALID=false and LASTCOMPILED=NULL.

          Show
          Knut Anders Hatlen added a comment - Could the procedure be tested by checking the columns LASTCOMPILED and VALID in SYS.SYSSTATEMENTS? VALID is true when the statement has been compiled and no recompilation is needed, and false otherwise. LASTCOMPILED should contain a time stamp telling the last time the statement was compiled. So after the procedure has been called, all meta-data statements should have VALID=false and LASTCOMPILED=NULL.
          Hide
          Kathey Marsden added a comment -

          I attempted to add the test described by Knut Anders to DatabaseMetaDataTest and found failures in the upgrade tests because the procedure didn't exist in old versions. When I disabled the test for versions less than 10.5 and I got assertion failures checking the SYSSTATEMENTS columns, which I don't fully understand. But all of this got me thinking. Firstly I think that users shouldn't be able to update metadata queries if the database version less than 10.5 because there will be no procedure available to restore the metadata queries if they go back. Secondly I think maybe we can automate this after all. If we make the logic:

          If the database version >=10.5 update the metadata queries on any version change (up or down).

          But I want to make sure that this will really work. For some reason I can't remember I thought it might not in the past. Maybe something related to soft upgrade handling of metadata queries? Does anyone have thoughts on this?

          Show
          Kathey Marsden added a comment - I attempted to add the test described by Knut Anders to DatabaseMetaDataTest and found failures in the upgrade tests because the procedure didn't exist in old versions. When I disabled the test for versions less than 10.5 and I got assertion failures checking the SYSSTATEMENTS columns, which I don't fully understand. But all of this got me thinking. Firstly I think that users shouldn't be able to update metadata queries if the database version less than 10.5 because there will be no procedure available to restore the metadata queries if they go back. Secondly I think maybe we can automate this after all. If we make the logic: If the database version >=10.5 update the metadata queries on any version change (up or down). But I want to make sure that this will really work. For some reason I can't remember I thought it might not in the past. Maybe something related to soft upgrade handling of metadata queries? Does anyone have thoughts on this?
          Hide
          Kathey Marsden added a comment -

          Attached is an alternate automatic fix for this issue. We will drop and recreate the SPSes with any version change up or down, as long as the database version is >= 10.5. This should prevent any problems going back after soft upgrade and keep the spses in sync with the version moving forward without the need for a new stored procedure.

          The only impact I can imagine is that it may take slightly longer to boot the database on version change. Upgrade tests passed. Running all tests now.

          Thanks

          Kathey

          Show
          Kathey Marsden added a comment - Attached is an alternate automatic fix for this issue. We will drop and recreate the SPSes with any version change up or down, as long as the database version is >= 10.5. This should prevent any problems going back after soft upgrade and keep the spses in sync with the version moving forward without the need for a new stored procedure. The only impact I can imagine is that it may take slightly longer to boot the database on version change. Upgrade tests passed. Running all tests now. Thanks Kathey
          Hide
          Kathey Marsden added a comment -

          tests passed patch derby-1107_noproc_diff.txt.

          Show
          Kathey Marsden added a comment - tests passed patch derby-1107_noproc_diff.txt.
          Hide
          Knut Anders Hatlen added a comment -

          It sounds safe to me too, as long as we only do this for 10.5 and higher. I don't think the time to boot on version change is a big issue, and the code will be more robust if we recreate the SPSs on version change.

          With this patch, will they be recreated on every version change, or only when one of the first three digits in the version number changes? That is, will they get recreated when we move from 10.5.1.0 to 10.5.1.1? I'm getting a bit confused by the naming in DD_Version, since it seems to use major/minor version in a different way than we normally do.

          Show
          Knut Anders Hatlen added a comment - It sounds safe to me too, as long as we only do this for 10.5 and higher. I don't think the time to boot on version change is a big issue, and the code will be more robust if we recreate the SPSs on version change. With this patch, will they be recreated on every version change, or only when one of the first three digits in the version number changes? That is, will they get recreated when we move from 10.5.1.0 to 10.5.1.1? I'm getting a bit confused by the naming in DD_Version, since it seems to use major/minor version in a different way than we normally do.
          Hide
          Kathey Marsden added a comment -

          It will get changed on any version change. I tested by bumping to 10.5.0.1 and the SPSes got recreated. The minor version as referenced in DD_Version takes into account all digits. I'll go ahead and check this in later today.

          Show
          Kathey Marsden added a comment - It will get changed on any version change. I tested by bumping to 10.5.0.1 and the SPSes got recreated. The minor version as referenced in DD_Version takes into account all digits. I'll go ahead and check this in later today.
          Hide
          Knut Anders Hatlen added a comment -

          Thanks for verifying. And +1 to commit.

          This still leaves the problem if we make a change that requires recompilation and don't bump the version on the branch. You mentioned one solution is to use the svn revision number when we compare the versions. If we had a way to detect that recompilation was needed, it would probably be simpler just to bump the revision when needed. Could we use the upgrade tests to detect it? Currently, the upgrade tests don't test upgrades from previous releases on the same branch, but I don't think there's anything preventing us from changing that. And the upgrade tests should run all meta-data queries, so that should be sufficient to detect these problems, right?

          Show
          Knut Anders Hatlen added a comment - Thanks for verifying. And +1 to commit. This still leaves the problem if we make a change that requires recompilation and don't bump the version on the branch. You mentioned one solution is to use the svn revision number when we compare the versions. If we had a way to detect that recompilation was needed, it would probably be simpler just to bump the revision when needed. Could we use the upgrade tests to detect it? Currently, the upgrade tests don't test upgrades from previous releases on the same branch, but I don't think there's anything preventing us from changing that. And the upgrade tests should run all meta-data queries, so that should be sufficient to detect these problems, right?
          Hide
          Kathey Marsden added a comment -

          Resolving this issue as the fix is checked into trunk. Note: This fix should not be backported as the metadata update can only happen for versions 10.5 and higher.

          Show
          Kathey Marsden added a comment - Resolving this issue as the fix is checked into trunk. Note: This fix should not be backported as the metadata update can only happen for versions 10.5 and higher.
          Hide
          Kathey Marsden added a comment -

          Thanks Knut for looking at the patch. Perhaps you are right that just manually bumping the version when needed is the best solution. As for the upgrade tests, I will open a Jira to add upgrade tests for third and fourth digit upgrades and put it on my TODO list to add that in when we have our first maintenance release on the 10.5 branch.

          Show
          Kathey Marsden added a comment - Thanks Knut for looking at the patch. Perhaps you are right that just manually bumping the version when needed is the best solution. As for the upgrade tests, I will open a Jira to add upgrade tests for third and fourth digit upgrades and put it on my TODO list to add that in when we have our first maintenance release on the 10.5 branch.

            People

            • Assignee:
              Kathey Marsden
              Reporter:
              Kathey Marsden
            • Votes:
              1 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development