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_diff.txt
        6 kB
        Kathey Marsden
      2. derby-1107_diff2.txt
        9 kB
        Kathey Marsden
      3. derby-1107_noproc_diff.txt
        6 kB
        Kathey Marsden
      4. derby-1107-proposal1.diff
        1 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Kathey Marsden created issue -
          Knut Anders Hatlen made changes -
          Field Original Value New Value
          Attachment derby-1107-proposal1.diff [ 12324725 ]
          Kathey Marsden made changes -
          Link This issue is related to DERBY-3000 [ DERBY-3000 ]
          Kathey Marsden made changes -
          Derby Categories [High Value Fix]
          Kathey Marsden made changes -
          Assignee Kathey Marsden [ kmarsden ]
          Kathey Marsden made changes -
          Attachment derby-1107_diff.txt [ 12392875 ]
          Kathey Marsden made changes -
          Attachment derby-1107_diff2.txt [ 12392888 ]
          Kathey Marsden made changes -
          Derby Info [Patch Available]
          Kim Haase made changes -
          Link This issue blocks DERBY-3933 [ DERBY-3933 ]
          Kathey Marsden made changes -
          Attachment derby-1107_noproc_diff.txt [ 12393397 ]
          Kathey Marsden made changes -
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]
          Fix Version/s 10.5.0.0 [ 12313010 ]
          Myrna van Lunteren made changes -
          Fix Version/s 10.5.1.1 [ 12313771 ]
          Fix Version/s 10.5.0.0 [ 12313010 ]
          Dag H. Wanvik made changes -
          Issue & fix info [Patch Available] [High Value Fix]
          Gavin made changes -
          Link This issue blocks DERBY-3933 [ DERBY-3933 ]
          Gavin made changes -
          Link This issue is depended upon by DERBY-3933 [ DERBY-3933 ]
          Gavin made changes -
          Workflow jira [ 12351984 ] Default workflow, editable Closed status [ 12802352 ]

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development