I haven't been following all this, and perhaps I am missing something,
but isn't a large part of the problem due to the fact that we are
invoking compiled procedures? Wouldn't one way to solve the "metadata
problem" be to just invoke the SQL directly from the client rather than
storing it in the server?
David
Knut Anders Hatlen (JIRA) wrote:
[ http://issues.apache.org/jira/browse/DERBY-1107?page=all ]
Knut Anders Hatlen updated DERBY-1107:
--------------------------------------
Attachment: derby-1107-proposal1.diff
I tried running a test like the one Kathey did, and I can verify that
the queries returned the old results even after the upgrade. Adding
code for dropping and regenerating the SPSs in
handleMinorRevisionChange() made it work.
Since handleMinorRevisionChange() is called on all types of version
changes, I suggest that we move the code for dropping/regenerating
SPSs from doFullUpgrade() to handleMinorRevisionChange(). The attached
patch (derby-1107-proposal1.diff) shows this change. I have not tested
the patch very much, it is only meant as a starting point for a
discussion on how to solve the problem.
I can't see that dropping the SPSs between maintenance releases should
cause any problems.
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?
