db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-1107) For existing databases JDBC metadata queries do not get updated properly between maintenance versions.
Date Fri, 31 Mar 2006 01:51:34 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1107?page=comments#action_12372617 ] 

Dag H. Wanvik commented on DERBY-1107:
--------------------------------------

> 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.

> For existing databases JDBC metadata queries do not get updated properly  between maintenance
versions.
> -------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-1107
>          URL: http://issues.apache.org/jira/browse/DERBY-1107
>      Project: Derby
>         Type: Bug
>   Components: JDBC
>     Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.2.0.0, 10.1.2.0, 10.1.1.1, 10.1.1.2, 10.1.2.1,
10.1.3.0, 10.1.2.2, 10.1.2.3, 10.1.2.4
>     Reporter: 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.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Mime
View raw message