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