[
https://issues.apache.org/jira/browse/DERBY-1107?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kathey Marsden reassigned DERBY-1107:
-------------------------------------
Assignee: Kathey Marsden
> For existing databases JDBC metadata queries do not get updated properly
> between maintenance versions.
> -------------------------------------------------------------------------------------------------------
>
> Key: DERBY-1107
> URL: https://issues.apache.org/jira/browse/DERBY-1107
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1,
> 10.2.1.6
> Reporter: Kathey Marsden
> Assignee: Kathey Marsden
> Attachments: derby-1107-proposal1.diff
>
>
> 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?
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
