[ http://issues.apache.org/jira/browse/DERBY-1107?page=comments#action_12372034 ]
Kathey Marsden commented on DERBY-1107: --------------------------------------- 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. > 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 > > 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
