[ http://issues.apache.org/jira/browse/DERBY-1107?page=comments#action_12372427 ]
Knut Anders Hatlen commented on DERBY-1107: ------------------------------------------- Kathey wrote: > 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? This is interesting! Only statements in the SYS schema are dropped and regenerated. To check what happens on upgrade, I created a database with 10.1.2.1 and looked at the values returned by "execute statement SYSIBM.METADATA" in 10.2 with soft and hard upgrade. The values were *not* the same as when I created the database with 10.2. The changes are probably caused by DERBY-965 which modified the METADATA statement. 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. > 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? All of what you said above is correct. > 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
