You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Knut Anders Hatlen (JIRA)" <de...@db.apache.org> on 2006/03/29 16:59:47 UTC
[jira] Updated: (DERBY-1107) For existing databases JDBC metadata
queries do not get updated properly between maintenance versions.
[ 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?
--
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
Re: [jira] Updated: (DERBY-1107) For existing databases JDBC metadata
queries do not get updated properly between maintenance versions.
Posted by Kathey Marsden <km...@sbcglobal.net>.
Knut Anders Hatlen wrote:
>"
>
>
>I'll summarize my understanding of these two issues
>
>
[snip very good explanation]
>As you suggested, the client could invoke the SQL directly. This would
>however cause some problems:
>
> - if there is a bug in a metadata query, it is not enough to upgrade
> the server, you would also have to upgrade all the clients.
>
> - if the server changes the name of a system table or a column in a
> system table, old clients will be broken.
>
>
>
Another problem is that the metadata queries currently use
non-supported syntax. Changing these to use supported syntax, such as
Mamta did when adding optimizer directives would mean that the old
non-supported syntax would have to stay in the server.
One more thing not mentioned here was that Satheesh for 1120/1124
added some version dependent metadata queries, which were necessary for
soft upgrade because with the full v10.2 upgrade, system columns
needed to be accessed for the metadata queries that were not available
on soft upgrade. That seemed like a reasonable approach to soft upgrade.
Once all the infrastructure gets firmed up, it would be good to have
some information on the Wiki about how maintain metadata queries. It
is good to see it getting addressed.
Kathey
Re: [jira] Updated: (DERBY-1107) For existing databases JDBC metadata
queries do not get updated properly between maintenance versions.
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Knut Anders Hatlen <Kn...@Sun.COM> writes:
> Kathey Marsden <km...@sbcglobal.net> writes:
>
>> I am a bit fuzzy on this still after I reread the thread for DERBY-1107
>> a few times. I was wondering if someone could summarize? Has an
>> upgrade strategy been defined for metadata?
>
> I don't think we defined a strategy, but we seem to agree on:
>
> 1) It is possible to fix it (without time travel) when the need
> arises.
>
> 2) If we want to fix it before the need arises, it is acceptable to
> drop and recreate stored prepared statements on every maintenance
> release.
>
> 3) Metadata changes should be avoided in point releases.
>
>> Could someone who
>> understands it put it on the Wiki?
>
> I can start on a wiki page tomorrow. If someone wants do to it
> earlier, just go ahead!
Sorry that it took so long. I have put up a wiki page on
http://wiki.apache.org/db-derby/MetadataUpgrade and linked to it from
the front page. Feel free to add, remove or change whatever you would
like.
--
Knut Anders
Re: [jira] Updated: (DERBY-1107) For existing databases JDBC metadata
queries do not get updated properly between maintenance versions.
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Kathey Marsden <km...@sbcglobal.net> writes:
> Satheesh Bandaram wrote:
>
>>>My issue is that, as of mid-March, metadata changes have been blocked
>>>pending resolution of two issues:
>>>
>>>1) Agreement on our upgrade policy vis-a-vis metadata.
>>>
>>>
> I am a bit fuzzy on this still after I reread the thread for DERBY-1107
> a few times. I was wondering if someone could summarize? Has an
> upgrade strategy been defined for metadata?
I don't think we defined a strategy, but we seem to agree on:
1) It is possible to fix it (without time travel) when the need
arises.
2) If we want to fix it before the need arises, it is acceptable to
drop and recreate stored prepared statements on every maintenance
release.
3) Metadata changes should be avoided in point releases.
> Could someone who
> understands it put it on the Wiki?
I can start on a wiki page tomorrow. If someone wants do to it
earlier, just go ahead!
--
Knut Anders
Re: Disussion surrounding holding off metadata changes until certain
issues resolved
Posted by Kathey Marsden <km...@sbcglobal.net>.
Dag H. Wanvik wrote:
>Kathey Marsden <km...@sbcglobal.net> writes:
>
>
>
>>Once confirmed, could you file a bug against Network Server for this? It
>>should negotiate down properly.
>>
>>
>
>Will do.
>
>
>
>>For now it would be good for JCC to take the same path as the 10.1.1.0
>>client takes to correct this issue.
>>
>>
>
>Not sure what you have in mind here..
>
>
>
I don't have any particular solution in mind. All I am saying is that
whatever solution is used to fix the 10.2 Server so that it works with
10.1.1.0 client should also work for JCC.
Kathey
Re: Disussion surrounding holding off metadata changes until certain
issues resolved
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Kathey Marsden <km...@sbcglobal.net> writes:
> Once confirmed, could you file a bug against Network Server for this? It
> should negotiate down properly.
Will do.
> For now it would be good for JCC to take the same path as the 10.1.1.0
> client takes to correct this issue.
Not sure what you have in mind here..
Dag
Re: Disussion surrounding holding off metadata changes until certain
issues resolved
Posted by Kathey Marsden <km...@sbcglobal.net>.
Dag H. Wanvik wrote:
>>>>>>"Kathey" == Kathey Marsden <km...@sbcglobal.net> wrote:
>>>>>>
>>>>>>
>
>Kathey> This is a regression for JCC, but is not a problem for the original
>Kathey> client release with the 10.2 server? I don't understand why JCC and the
>Kathey> original client 10.1.1.0 release would behave differently in this
>Kathey> regard. Could you explain?
>
>I'll try :) With an old (10.1) client and a new (10.2) server, there
>will be a problem for *both JCC and the Derby client* in that these
>four metadata calls (changed for SUR) will show a wrong value, since
>no down-negotiation happens for metadata calls:
>
> deletesAreDetected(TYPE_SCROLL_INSENSITIVE) -> true
> updatedAreDetected(TYPE_SCROLL_INSENSITIVE) -> true
> ownDeletesAreVisible(TYPE_SCROLL_INSENSITIVE) -> true
> ownUpdatesAreVisible(TYPE_SCROLL_INSENSITIVE) -> true
>
>
>
Once confirmed, could you file a bug against Network Server for this? It
should negotiate down properly.
[snip]
>FThat leaves the case of a (potential) new JCC driver and and old
>server. I won't address that.
>
>
>
For now it would be good for JCC to take the same path as the 10.1.1.0
client takes to correct this issue.
Since that potential JCC release does not exist yet.
Kathey
Re: Disussion surrounding holding off metadata changes until certain
issues resolved
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
>>>>> "Kathey" == Kathey Marsden <km...@sbcglobal.net> wrote:
Kathey> This is a regression for JCC, but is not a problem for the original
Kathey> client release with the 10.2 server? I don't understand why JCC and the
Kathey> original client 10.1.1.0 release would behave differently in this
Kathey> regard. Could you explain?
I'll try :) With an old (10.1) client and a new (10.2) server, there
will be a problem for *both JCC and the Derby client* in that these
four metadata calls (changed for SUR) will show a wrong value, since
no down-negotiation happens for metadata calls:
deletesAreDetected(TYPE_SCROLL_INSENSITIVE) -> true
updatedAreDetected(TYPE_SCROLL_INSENSITIVE) -> true
ownDeletesAreVisible(TYPE_SCROLL_INSENSITIVE) -> true
ownUpdatesAreVisible(TYPE_SCROLL_INSENSITIVE) -> true
For the other method affected by SUR, supportsResultSetType, JCC
always returns false for all arguments, with both 10.1 and 10.2
servers, as far as I can see, and thus suffers no regression for this
call. The old Derby client has a bug (DERBY-965) which is only fixed
in 10.2, so the old Derby client would not show a regression with a
new server for supportsResultSetType.
Now, with new clients and new server, the Derby client will show the
SUR values for these calls, which is correct, including for the
soft/hard upgrade scenarios (after DERBY-1176). I don't know if there
will be a 10.2 version of JCC; if there will be, my point is that JCC
would continue to show wrong values for these four (detectability,
visibility) calls unless some special precaution was taken, since the
server don't differentiate between the clients on the metadata
calls. If the JCC client code for supportsResultSetType were corrected
to show the values returned by the server (in stead of always
returning false), it would have the same vulnerability: it would show
that SUR is supported, which I assume it would not be with JCC.
For the last case, new client and old server, for the Derby client the
four detectability and visibility calls are correct (return
false). The supportsResultSetType calls are wrong due to the server
side bugs of DERBY-965 still present in the old server, although now
manifested in a different way than with an old client, since the new
client correctly decodes the values transmitted:
SupportsResultSetConcurrency: TYPE_FORWARD_ONLY,CONCUR_READ_ONLY: false
SupportsResultSetConcurrency: TYPE_FORWARD_ONLY,CONCUR_UPDATABLE: true
SupportsResultSetConcurrency: TYPE_SCROLL_INSENSITIVE,CONCUR_READ_ONLY: false
SupportsResultSetConcurrency: TYPE_SCROLL_INSENSITIVE,CONCUR_UPDATABLE: true
SupportsResultSetConcurrency: TYPE_SCROLL_SENSITIVE,CONCUR_READ_ONLY: true
SupportsResultSetConcurrency: TYPE_SCROLL_SENSITIVE,CONCUR_UPDATABLE: true
Wrong, of course. The old client returned false for all of these.
That leaves the case of a (potential) new JCC driver and and old
server. I won't address that.
I hope this clarified matters in stead of muddling them further, the
details are a bit messy. Don't hesitate to ask if you have further
questions.
Dag
Re: Disussion surrounding holding off metadata changes until certain
issues resolved
Posted by Kathey Marsden <km...@sbcglobal.net>.
Dag H. Wanvik wrote:
>K
>
>
>However, for the four other metadata calls that have changed with SUR:
>
> deletesAreDetected(TYPE_SCROLL_INSENSITIVE) -> true
> updatedAreDetected(TYPE_SCROLL_INSENSITIVE) -> true
> ownDeletesAreVisible(TYPE_SCROLL_INSENSITIVE) -> true
> ownUpdatesAreVisible(TYPE_SCROLL_INSENSITIVE) -> true
>
>we are not so lucky. On trying this for 10.2 trunk I see that JCC
>returns true for these as well (both in soft upgrade and and after
>hard upgrade, thanks to DERBY-1176), which *is* a regression for JCC
>apps:
>
>
>
This is a regression for JCC, but is not a problem for the original
client release with the 10.2 server? I don't understand why JCC and the
original client 10.1.1.0 release would behave differently in this
regard. Could you explain?
Thanks
Kathey
Re: Disussion surrounding holding off metadata changes until certain
issues resolved
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Kathey Marsden <km...@sbcglobal.net> writes:
> Thank you so much for calling out this potential compatibility issue in
> this thread. DERBY-775 was not on my compatibility radar.
>
> I tried the repro for DERBY-965 with JCC and the trunk server and see
> that JCC returns false these values for supportsResultSetConcurrency, so
> I think that we are ok for JCC too. Is that correct?
I have run this test again, adding the JCC client, after DERBY-775 and
DERBY-1176 went it. The JCC client return false for
SupportsResultSetConcurrency in all cases (10.1, 10.2 in soft upgrade
mode and after 10.2 hard upgrade). So I can see no regression for JCC
here. But the answer is wrong, of course ;)
However, for the four other metadata calls that have changed with SUR:
deletesAreDetected(TYPE_SCROLL_INSENSITIVE) -> true
updatedAreDetected(TYPE_SCROLL_INSENSITIVE) -> true
ownDeletesAreVisible(TYPE_SCROLL_INSENSITIVE) -> true
ownUpdatesAreVisible(TYPE_SCROLL_INSENSITIVE) -> true
we are not so lucky. On trying this for 10.2 trunk I see that JCC
returns true for these as well (both in soft upgrade and and after
hard upgrade, thanks to DERBY-1176), which *is* a regression for JCC
apps:
(
com.ibm.db2.jcc.DB2Driver:
SupportsResultSetConcurrency: TYPE_FORWARD_ONLY,CONCUR_READ_ONLY: false
SupportsResultSetConcurrency: TYPE_FORWARD_ONLY,CONCUR_UPDATABLE: false
SupportsResultSetConcurrency: TYPE_SCROLL_INSENSITIVE,CONCUR_READ_ONLY: false
SupportsResultSetConcurrency: TYPE_SCROLL_INSENSITIVE,CONCUR_UPDATABLE: false
SupportsResultSetConcurrency: TYPE_SCROLL_SENSITIVE,CONCUR_READ_ONLY: false
SupportsResultSetConcurrency: TYPE_SCROLL_SENSITIVE,CONCUR_UPDATABLE: false
deletesAreDetected: true
updatedAreDetected: true
ownDeletesAreVisible: true
ownUpdatesAreVisible: true
So I guess the JCC people should be alerted to this fact.
Dag
Re: Disussion surrounding holding off metadata changes until certain
issues resolved
Posted by Kathey Marsden <km...@sbcglobal.net>.
Dag H. Wanvik wrote:
>>Old client, new server scenario: In this case, the downgrade of the
>>result set happens on the client, so applications would not be able
>>to use SUR. Only the new client lifts the restriction.
>>
>>Due to the bug in the 10.1 client code for
>>supportsResultSetConcurrency (DERBY-965), the client would still get
>>a "false" on this query, which is correct. Note that this is be
>>accident rather than by design! Derby has a weakness here in that
>>metadata queries are answered by the server without regard to the
>>client's version (capabilities).
>>
>>
Thank you so much for calling out this potential compatibility issue in
this thread. DERBY-775 was not on my compatibility radar.
I tried the repro for DERBY-965 with JCC and the trunk server and see
that JCC returns false these values for supportsResultSetConcurrency, so
I think that we are ok for JCC too. Is that correct?
com.ibm.db2.jcc.DB2Driver:
SupportsResultSetConcurrency: TYPE_FORWARD_ONLY,CONCUR_READ_ONLY: false
SupportsResultSetConcurrency: TYPE_FORWARD_ONLY,CONCUR_UPDATABLE: false
SupportsResultSetConcurrency: TYPE_SCROLL_INSENSITIVE,CONCUR_READ_ONLY:
false
SupportsResultSetConcurrency: TYPE_SCROLL_INSENSITIVE,CONCUR_UPDATABLE:
false
SupportsResultSetConcurrency: TYPE_SCROLL_SENSITIVE,CONCUR_READ_ONLY: false
SupportsResultSetConcurrency: TYPE_SCROLL_SENSITIVE,CONCUR_UPDATABLE: false
Apache Derby Network Server - 10.2.0.0 alpha shutdown at 2006-04-09
15:38:51.040 GMT
>Generally, it seems results of such metadata calls should potentially
>be "negotiated down" if the client is older and cannot support the new
>feature.. I am not sure of the machinery of NetDatabaseMetaData can be
>used as is for this purpose...(i.e. the serverSupport<FeatureX>
>methods). Or?
>
>
>
Right, I think there is still a hole here in terms of metadata handling
and how to negotiate down. I am guessing that will become incumbent on
someone who finds that they need that negotiation for some new
feature. It looks like you are "off the hook" this time because of
DERBY-965.
Re: Disussion surrounding holding off metadata changes until certain
issues resolved
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Kathey Marsden <km...@sbcglobal.net> writes:
>>As I was working with DERBY-775, it struck me that the JCC client
>>might get problems when metadata_net.properties is updated to reflect
>>Derby client capabilities not necessarily present in the JCC driver.
>>Should there perhaps be different code paths for the two drivers(JCC,
>>DerbyNetClient) wrt SYSIBM metadata? Not my itch, but...
>
> I am not clear on the specific issue you are referring to, but such
The metadata methods in question are supportsResultSetConcurrency,
ownDeletesAreVisible, ownUpdatesAreVisible, deletesAreDetected and
updatesAreDetected. With client SUR (not yet committed), they now
return true for scrollable insensitive result set types.
> compatibility issues would I think also exist when running with the
> original client release, Derby 10.1 and the new server. No ?
Yes and no, cf the explanation from the DERBY-775 writeup:
> Old client, new server scenario: In this case, the downgrade of the
> result set happens on the client, so applications would not be able
> to use SUR. Only the new client lifts the restriction.
>
> Due to the bug in the 10.1 client code for
> supportsResultSetConcurrency (DERBY-965), the client would still get
> a "false" on this query, which is correct. Note that this is be
> accident rather than by design! Derby has a weakness here in that
> metadata queries are answered by the server without regard to the
> client's version (capabilities).
>
> For the detectability metadata calls, the returned values would be
> wrong, but since this is new functionality it should be ok.
The changed value of
supportsResultSetConcurrency(TYPE_SCROLL_INSENSITIVE,
CONCUR_UPDATABLE), now true with the advent of SUR, could in theory
affect JCC client apps, but not 10.1 Derby network driver clients apps
due to the serendipty of DERBY-965: the driver will continue to return
false, as long as the patch for 965 is not back ported to the 10.1
branch.
Generally, it seems results of such metadata calls should potentially
be "negotiated down" if the client is older and cannot support the new
feature.. I am not sure of the machinery of NetDatabaseMetaData can be
used as is for this purpose...(i.e. the serverSupport<FeatureX>
methods). Or?
Back next week. One week easter vacation, have fun, all!
Dag
Re: Disussion surrounding holding off metadata changes until certain
issues resolved
Posted by Kathey Marsden <km...@sbcglobal.net>.
Dag H. Wanvik wrote:
>Knut Anders Hatlen <Kn...@Sun.COM> writes:
>
>
>
>>Kathey Marsden <km...@sbcglobal.net> writes:
>>
>>
>>
>>>Rick Hillegas wrote:
>>>
>>>
>>>
>>>>So, I'm unclear: Are you still blocking metadata changes or are you
>>>>satisfied with the analysis done so far by Knut Anders and Dyre? Can
>>>>we proceed with metadata checkins provided that:
>>>>
>>>>1) Such patches clearly describe acceptable upgrade behavior
>>>>2) Clean upgrade test results accompany the submissions
>>>>
>>>>
>>>>
>>>I think that this would be fine and in addition would hope that any
>>>metadata queries changed/added were covered as part of the ugprade test.
>>>
>>>
>>I think it would be good if the upgrade test also ran metadata tests
>>in the DerbyNetClient framework. The only known upgrade issue in 10.2
>>is DERBY-1176, but it is not exposed by the upgrade test since it is
>>only seen in client/server mode.
>>
>>
>
>It would be nice it that were added.
>
>Client SUR changes some metadata (in metadata_net.properties) and
>relies on DERBY-1176 for correct operation under upgrade for some
>metadata queries, cf. the DERBY-775 write-up.
>
>As I was working with DERBY-775, it struck me that the JCC client
>might get problems when metadata_net.properties is updated to reflect
>Derby client capabilities not necessarily present in the JCC driver.
>Should there perhaps be different code paths for the two drivers(JCC,
>DerbyNetClient) wrt SYSIBM metadata? Not my itch, but...
>
>
>
I am not clear on the specific issue you are referring to, but such
compatibility issues would I think also exist when running with the
original client release, Derby 10.1 and the new server. No ?
Kathey
Re: Disussion surrounding holding off metadata changes until certain
issues resolved
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Knut Anders Hatlen <Kn...@Sun.COM> writes:
> Kathey Marsden <km...@sbcglobal.net> writes:
>
>> Rick Hillegas wrote:
>>
>>> So, I'm unclear: Are you still blocking metadata changes or are you
>>> satisfied with the analysis done so far by Knut Anders and Dyre? Can
>>> we proceed with metadata checkins provided that:
>>>
>>> 1) Such patches clearly describe acceptable upgrade behavior
>>> 2) Clean upgrade test results accompany the submissions
>>>
>> I think that this would be fine and in addition would hope that any
>> metadata queries changed/added were covered as part of the ugprade test.
>
> I think it would be good if the upgrade test also ran metadata tests
> in the DerbyNetClient framework. The only known upgrade issue in 10.2
> is DERBY-1176, but it is not exposed by the upgrade test since it is
> only seen in client/server mode.
It would be nice it that were added.
Client SUR changes some metadata (in metadata_net.properties) and
relies on DERBY-1176 for correct operation under upgrade for some
metadata queries, cf. the DERBY-775 write-up.
As I was working with DERBY-775, it struck me that the JCC client
might get problems when metadata_net.properties is updated to reflect
Derby client capabilities not necessarily present in the JCC driver.
Should there perhaps be different code paths for the two drivers(JCC,
DerbyNetClient) wrt SYSIBM metadata? Not my itch, but...
Dag
Re: Disussion surrounding holding off metadata changes until certain
issues resolved
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Kathey Marsden <km...@sbcglobal.net> writes:
> Rick Hillegas wrote:
>
>> So, I'm unclear: Are you still blocking metadata changes or are you
>> satisfied with the analysis done so far by Knut Anders and Dyre? Can
>> we proceed with metadata checkins provided that:
>>
>> 1) Such patches clearly describe acceptable upgrade behavior
>> 2) Clean upgrade test results accompany the submissions
>>
> I think that this would be fine and in addition would hope that any
> metadata queries changed/added were covered as part of the ugprade test.
I think it would be good if the upgrade test also ran metadata tests
in the DerbyNetClient framework. The only known upgrade issue in 10.2
is DERBY-1176, but it is not exposed by the upgrade test since it is
only seen in client/server mode.
--
Knut Anders
Re: Disussion surrounding holding off metadata changes until certain
issues resolved
Posted by Rick Hillegas <Ri...@Sun.COM>.
Thanks, Kathey. This seems reasonable to me.
Regards,
-Rick
Kathey Marsden wrote:
>Rick Hillegas wrote:
>
>
>
>>So, I'm unclear: Are you still blocking metadata changes or are you
>>satisfied with the analysis done so far by Knut Anders and Dyre? Can
>>we proceed with metadata checkins provided that:
>>
>>1) Such patches clearly describe acceptable upgrade behavior
>>2) Clean upgrade test results accompany the submissions
>>
>>
>>
>I think that this would be fine and in addition would hope that any
>metadata queries changed/added were covered as part of the ugprade test.
>
>Kathey
>
>
>
>
>
>
>
Re: Disussion surrounding holding off metadata changes until certain
issues resolved
Posted by Kathey Marsden <km...@sbcglobal.net>.
Rick Hillegas wrote:
> So, I'm unclear: Are you still blocking metadata changes or are you
> satisfied with the analysis done so far by Knut Anders and Dyre? Can
> we proceed with metadata checkins provided that:
>
> 1) Such patches clearly describe acceptable upgrade behavior
> 2) Clean upgrade test results accompany the submissions
>
I think that this would be fine and in addition would hope that any
metadata queries changed/added were covered as part of the ugprade test.
Kathey
Re: Disussion surrounding holding off metadata changes until certain
issues resolved
Posted by Rick Hillegas <Ri...@Sun.COM>.
>http://www.apache.org/foundation/how-it-works.html
>
>
>
>>>The blockage was based on the fact that we once had an irreconcilable
>>>downgrade bug and seemed to be set up for that again.
>>>
>>>
>>>
>>>
>>What was the code change that caused this problem in 5.1? You have
>>provided symptoms of the problem. It would be good to understand the
>>code change that cause this symptom.
>>
>>
>>
>>
>>
>The code change was that in 5.1.60 there were new classes and methods
>that were added and referred to in the metadata SQL queries. Going
>back to the previous version, the metadata queries were still there but
>the classes and methods were not, so it caused this failure. In the
>10.1 codeline, this means that no new functions or syntax not
>compatible with older versions can be added to the queries. Adding the
>drop on version change except for read only databases seems the safest
>thing here to me. It seems like it will be a long time before we have a
>10.2 release, who knows what will go in the 10.1 codeline.
>
>
>
I don't believe that the blocked patches have this defect. Am I wrong?
>>Again, I am not sure if DERBY-1107 should stop metadata developments.
>>Wasn't this "issue" present from the beginning?
>>
>>
>>
>>
>>
>It is a serious outstanding issue with the handling of metadata
>changes. It would be good to see it resolved soon, because it is again
>one possibly needing time travel to resolve, but isn't stopping any
>metadata changes from going in now.
>
>
>
So, I'm unclear: Are you still blocking metadata changes or are you
satisfied with the analysis done so far by Knut Anders and Dyre? Can we
proceed with metadata checkins provided that:
1) Such patches clearly describe acceptable upgrade behavior
2) Clean upgrade test results accompany the submissions
Regards,
-Rick
Disussion surrounding holding off metadata changes until certain
issues resolved (was: Re: [jira] Updated: (DERBY-1107) For existing databases
JDBC metadata queries do not get updated properly between maintenance versions.)
Posted by Kathey Marsden <km...@sbcglobal.net>.
Satheesh Bandaram wrote:
>Kathey Marsden wrote:
>
>
>
>>Satheesh Bandaram wrote:
>>
>>
>>I think on this one, we just need to get the DERBY-1076 patch
>>committed.
>>
>not sure if adding of new tests should stop development
>that passes current Derby checkin criteria of clean derbyAll.
>
>
>
I think if an area has regressions, significant quality issues, poor
test coverage or is a significant risk area it is appropriate for
anyone in the community to raise objections to new functionality being
added to that area until the core quality issues are addressed.
Upgrade is particularly sensitive because if not working, the resolution
may require time travel to resolve. Since there was no infrastructure in
place to handle upgrade changes of metadata, it had outstanding
regressions, and it has traditionally been an area of serious issue I
asked Knut to wait and he agreed., probably because he agrees with the
six core principals of the "Apache Way" especially the one about
"consistently high quality software".
http://www.apache.org/foundation/how-it-works.html
>>The blockage was based on the fact that we once had an irreconcilable
>>downgrade bug and seemed to be set up for that again.
>>
>>
>What was the code change that caused this problem in 5.1? You have
>provided symptoms of the problem. It would be good to understand the
>code change that cause this symptom.
>
>
>
The code change was that in 5.1.60 there were new classes and methods
that were added and referred to in the metadata SQL queries. Going
back to the previous version, the metadata queries were still there but
the classes and methods were not, so it caused this failure. In the
10.1 codeline, this means that no new functions or syntax not
compatible with older versions can be added to the queries. Adding the
drop on version change except for read only databases seems the safest
thing here to me. It seems like it will be a long time before we have a
10.2 release, who knows what will go in the 10.1 codeline.
>Again, I am not sure if DERBY-1107 should stop metadata developments.
>Wasn't this "issue" present from the beginning?
>
>
>
It is a serious outstanding issue with the handling of metadata
changes. It would be good to see it resolved soon, because it is again
one possibly needing time travel to resolve, but isn't stopping any
metadata changes from going in now.
Kathey
Re: [jira] Updated: (DERBY-1107) For existing databases JDBC metadata
queries do not get updated properly between maintenance versions.
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Kathey Marsden wrote:
>Satheesh Bandaram wrote:
>
>
>I think on this one, we just need to get the DERBY-1076 patch
>committed. I think that Deepa is working to get upgrade tests into
>derbyall so we don't have to .worry so much about regressions in this area.
>
>
Right... It would be great to have the tests done and be part of
derbyAll. But not sure if adding of new tests should stop development
that passes current Derby checkin criteria of clean derbyAll.
>I think that the fact that we still don't yet have upgrade tests in
>derbyall should make metadata checkins code YELLOW.
>I think folks making metadata changes should make sure the ugprade tests
>pass and masters are updated.
>
>
This is a good suggestion... It should be good to ask for this test to
be run as part of metadata changes?
>The blockage was based on the fact that we once had an irreconcilable
>downgrade bug and seemed to be set up for that again. Where an
>incompatible change was made to the metadata statements and that could
>not be fixed. I hope folks interested in modifying metadata will
>continue to make progress on the metadata upgrade strategy and get
>DERBY-1107 resolved in 10.1 and trunk to avoid a similar unfortunate
>situation.
>
>
What was the code change that caused this problem in 5.1? You have
provided symptoms of the problem. It would be good to understand the
code change that cause this symptom.
Again, I am not sure if DERBY-1107 should stop metadata developments.
Wasn't this "issue" present from the beginning?
Satheesh
Re: [jira] Updated: (DERBY-1107) For existing databases JDBC metadata
queries do not get updated properly between maintenance versions.
Posted by Kathey Marsden <km...@sbcglobal.net>.
Satheesh Bandaram wrote:
>>My issue is that, as of mid-March, metadata changes have been blocked
>>pending resolution of two issues:
>>
>>1) Agreement on our upgrade policy vis-a-vis metadata.
>>
>>
I am a bit fuzzy on this still after I reread the thread for DERBY-1107
a few times. I was wondering if someone could summarize? Has an
upgrade strategy been defined for metadata? Could someone who
understands it put it on the Wiki?
>>2) Fixing the upgrade tests.
>>
>>
I think on this one, we just need to get the DERBY-1076 patch
committed. I think that Deepa is working to get upgrade tests into
derbyall so we don't have to .worry so much about regressions in this area.
>>
>>
>It is my personal feeling
>that this "blockage" of metadata work should be lifted.
>
>
I think that the fact that we still don't yet have upgrade tests in
derbyall should make metadata checkins code YELLOW.
I think folks making metadata changes should make sure the ugprade tests
pass and masters are updated.
The blockage was based on the fact that we once had an irreconcilable
downgrade bug and seemed to be set up for that again. Where an
incompatible change was made to the metadata statements and that could
not be fixed. I hope folks interested in modifying metadata will
continue to make progress on the metadata upgrade strategy and get
DERBY-1107 resolved in 10.1 and trunk to avoid a similar unfortunate
situation.
Here is the information from the release notes regarding that bug
provide context:
If you revert to a previous version of Cloudscape Version 5.1 (Versions
5.1.2 through 5.1.59) after upgrading and connecting to a database using
Cloudscape Version 5.1.60, you will receive errors when you first
connect and when you run the ALTER STATEMENT RECOMPILE ALL command.
Example error when connecting to a database after reverting:
ERROR XCW01: Database upgrade succeeded. The upgraded database is now
ready for
use. Revalidating stored prepared statements failed. See next exception
for details of failure.
Correct problem and recompile before executing the prepared statement.
See "ALTER STATEMENT RECOMPILE".
ERROR 42X75: No constructor was found with the signature
com.ibm.db2j.drda.SQLGe
tUDTsVTI(java.sql.Connection, UNTYPED, UNTYPED, UNTYPED, UNTYPED, UNTYPED).
It may be that the parameter types are not method invocation convertible.
Example error when running ALTER STATEMENT RECOMPILE ALL after reverting:
ERROR 42X75: No constructor was found with the signature
com.ibm.db2j.drda.SQLGe
tUDTsVTI(java.sql.Connection, UNTYPED, UNTYPED, UNTYPED, UNTYPED, UNTYPED).
It may be that the parameter types are not method invocation convertible.
To correct this problem, drop the SYSIBM schema using the dropSYSIBM.bat
(Window OS) or dropSYSIBM.ksh (Unix OS) command file found in the
Cloudscape Version 5.1.60 install directories. If you are not running
Network Server, execute the SQL statements in the dropSYSIBM.sql file
rather than using the dropSYSIBM scripts.
Re: [jira] Updated: (DERBY-1107) For existing databases JDBC metadata
queries do not get updated properly between maintenance versions.
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Rick Hillegas wrote:
>
>>
>> Looks like we agree this issue could be solved when the real need
>> arises... I noticed Rick included this issue as one of the reasons
>> for codefreeze delay. Rick, do you think this issue is more serious?
>>
> My issue is that, as of mid-March, metadata changes have been blocked
> pending resolution of two issues:
>
> 1) Agreement on our upgrade policy vis-a-vis metadata.
> 2) Fixing the upgrade tests.
>
Now I understand your concern... As far as I know, upgrade tests now
pass with 10.2 trunk, though need some manual testing steps. Looks like
we are making progress on upgrade policy for metadata. Someone suggested
a Wiki to capture knowledge so far. That is a great idea. With the
original freeze date for 10.2 being changed, it is my personal feeling
that this "blockage" of metadata work should be lifted. In my opinion,
the blockage to solve pending issues made sense when we were real close
to the proposed 10.2 timeframe. Not anymore. Also proposing patches and
solutions would make us understand issues so we could evolve the proposal.
Any one have objections to continuing metadata work now? I agree with
you the blockage is bad...
Satheesh
> We are now almost three weeks into this blockage. A lot of useful work
> is backed up behind these issues. Every week of delay pushes back the
> code freeze.
>
> Regards,
> -Rick
>
>
>
Re: [jira] Updated: (DERBY-1107) For existing databases JDBC metadata
queries do not get updated properly between maintenance versions.
Posted by Rick Hillegas <Ri...@Sun.COM>.
>
> Looks like we agree this issue could be solved when the real need
> arises... I noticed Rick included this issue as one of the reasons for
> codefreeze delay. Rick, do you think this issue is more serious?
>
My issue is that, as of mid-March, metadata changes have been blocked
pending resolution of two issues:
1) Agreement on our upgrade policy vis-a-vis metadata.
2) Fixing the upgrade tests.
We are now almost three weeks into this blockage. A lot of useful work
is backed up behind these issues. Every week of delay pushes back the
code freeze.
Regards,
-Rick
Re: [jira] Updated: (DERBY-1107) For existing databases JDBC metadata queries do not get updated properly between maintenance versions.
Posted by Satheesh Bandaram <ba...@gmail.com>.
On 4/3/06, Knut Anders Hatlen <Kn...@sun.com> wrote:
>
> Satheesh Bandaram <ba...@gmail.com> writes:
>
>
> > This seems like a good problem to have?
>
> Yes, as long as we actually fix a bug and don't introduce a new
> one. But we don't write bugs, do we? :) Adding a version number to the
> query name could help us out of that situation.
Right... In past, things like metadata queries or changes to them are
written and tested well because it is more difficult to change them and
there is no upgrade support for maintenance and point-releases. Similar
issue with system schema changes or changing of system routines. Current
upgrade mechanisms only works between major_minor release versions. Same as
metadata.
If I declared a column to be not null in one of new system tables I added,
there is no way I could change that in next maintenance version and have
upgrade support that change. Adding or changing of system routines also have
this issue. Should we try to "fix" all these in DERBY-1107 or is metadata
case special?
> --
> Knut Anders
>
Thanks for your work on this... I think we should convert your original
write up on metadata handling into a Wiki and try to update as the design
changes.
Satheesh
Re: [jira] Updated: (DERBY-1107) For existing databases JDBC metadata
queries do not get updated properly between maintenance versions.
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Satheesh Bandaram <ba...@gmail.com> writes:
> On 3/30/06, Knut Anders Hatlen <Kn...@sun.com> wrote:
>>
>> Satheesh Bandaram <sa...@Sourcery.Org> writes:
>>
>> I don't think there has been any metadata changes between maintenance
>> releases, and I suppose bug fixes should be the only changes between
>> maintenance releases. And I think it is more a downgrade issue than an
>> upgrade issue. I mean, for upgrade it's no problem if we don't add
>> upgrade code until a metadata change is made. The problem with this
>> approach is that we will still use the new query after a downgrade. As
>> long as the new query doesn't use new functions, new tables or new
>> syntax (not likely that we will add such things in a maintenance
>> release, I guess), the only consequence is that the bug is fixed even
>> after we downgrade.
>
> This seems like a good problem to have?
Yes, as long as we actually fix a bug and don't introduce a new
one. But we don't write bugs, do we? :) Adding a version number to the
query name could help us out of that situation.
>> Is performance a big issue? This will happen once per version
>> change. I agree that it could be solved when the need arises. See my
>> comments above.
>
> Performance could be a small issue for the first time following version
> change. Not sure how long recompiling all system SPSes take, but could be
> several seconds?
On my computer (AMD 3200+) it takes less than one second to drop and
recompile all SPSs (SYS and SYSIBM).
> If we are proposing recompiling between maintenance
> releases only, it may be ok... but this cost could be noticed for
> point-release changes as ODD.
>
> Looks like we agree this issue could be solved when the real need arises...
I'm fine with leaving the current behaviour when moving between
maintenance or point releases as it is. There still is a problem with
upgrade from 10.1 to 10.2, though. I have logged it as DERBY-1176 and
will upload a patch soon.
> I noticed Rick included this issue as one of the reasons for codefreeze
> delay. Rick, do you think this issue is more serious?
>
> While use of SPS for metadata has some serious advantages, it does cause
> complications too, I think. Necessary evil?
As long as we are aware of the complications, I think we can live with
them. Necessary evil? Sure, but I sometimes feel "pure evil" is more
accurate... ;)
--
Knut Anders
Re: [jira] Updated: (DERBY-1107) For existing databases JDBC metadata queries do not get updated properly between maintenance versions.
Posted by Satheesh Bandaram <ba...@gmail.com>.
On 3/30/06, Knut Anders Hatlen <Kn...@sun.com> wrote:
>
> Satheesh Bandaram <sa...@Sourcery.Org> writes:
>
>
> I don't think there has been any metadata changes between maintenance
> releases, and I suppose bug fixes should be the only changes between
> maintenance releases. And I think it is more a downgrade issue than an
> upgrade issue. I mean, for upgrade it's no problem if we don't add
> upgrade code until a metadata change is made. The problem with this
> approach is that we will still use the new query after a downgrade. As
> long as the new query doesn't use new functions, new tables or new
> syntax (not likely that we will add such things in a maintenance
> release, I guess), the only consequence is that the bug is fixed even
> after we downgrade.
This seems like a good problem to have? Like you mentioned, adding of new
tables or new syntax is unlikely between maintenance or point releases. Now
we also have a mechanism to add major_minor version specific queries to
metadata and still have soft upgrade work. As far as I know, Cloudscape or
Derby so far hasn't had a need to change metadata queries in incompatible
way between maintenace versions. May be others know more....?
Since we can't drop and regenerate the statements in a read-only
> database, we have two options
>
> 1) Leave the current behaviour of Derby. That is, metadata bugs
> won't be fixed for read-only databases.
>
> 2) Make EmbedDatabaseMetaData do the same in read-only upgrade mode
> as it does in soft upgrade mode. That is, read metadata queries
> from metadata.properties.
I think either option would work, each having their own advantages... 1) is
probably best for performance... 2) Has the advantage of fixing currently
open bug and an issue having to recompile SPS statements. There is code in
Derby that automatically decides when to recompile SPS and sometimes gets
triggered after large number of executions of a statement. (just to refresh
the plan, in case any schema changes like adding of indexes has happened)
This check is useless for system SPS since changing of schema is not
possible and causes the bug in read-only databases. (while trying to write
the recompiled plan back to disk!)
Option 1 keeps the bugs, option 2 gives lower run-time performance
> even for the unmodified queries.
Hah... I just saw this after I typed everything just above... You think of
everything :)
> 2) Performance considerations. Could we
> > instead make it as need arises? Like if version is going from 10.1.2 to
> > 10.1.3 (just example) Hopefully these are rare cases.
>
> Is performance a big issue? This will happen once per version
> change. I agree that it could be solved when the need arises. See my
> comments above.
Performance could be a small issue for the first time following version
change. Not sure how long recompiling all system SPSes take, but could be
several seconds? If we are proposing recompiling between maintenance
releases only, it may be ok... but this cost could be noticed for
point-release changes as ODD.
Looks like we agree this issue could be solved when the real need arises...
I noticed Rick included this issue as one of the reasons for codefreeze
delay. Rick, do you think this issue is more serious?
While use of SPS for metadata has some serious advantages, it does cause
complications too, I think. Necessary evil?
Satheesh
--
> Knut Anders
>
Re: [jira] Updated: (DERBY-1107) For existing databases JDBC metadata
queries do not get updated properly between maintenance versions.
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Satheesh Bandaram <sa...@Sourcery.Org> writes:
> Knut Anders Hatlen wrote:
>
>> - if you move from one version to a version with the same major and
>> minor version (say, from 10.1.1.0 to 10.1.2.1), the SPSs are
>> invalidated, but not dropped. This means they will be recompiled,
>> but still use the old SQL code. Hence, changes to an existing
>> query in metadata.properties will not be visible to the users.
>>
>>
> May be I missed some previous messages about this... Are there real
> examples of need to change metadata between maintenance releases?
> Probably to fix specific bugs? I looked at DERBY-1107 and it talks
> about general need to have a mechanism. Don't think I saw any specific
> examples.
I don't think there has been any metadata changes between maintenance
releases, and I suppose bug fixes should be the only changes between
maintenance releases. And I think it is more a downgrade issue than an
upgrade issue. I mean, for upgrade it's no problem if we don't add
upgrade code until a metadata change is made. The problem with this
approach is that we will still use the new query after a downgrade. As
long as the new query doesn't use new functions, new tables or new
syntax (not likely that we will add such things in a maintenance
release, I guess), the only consequence is that the bug is fixed even
after we downgrade.
>>To solve the problem for the last case, there are at least three
>>options:
>>
>> 1) Drop and regenerate SPSs on all version changes.
>>
>> 2) Stop using SPSs (use ordinary prepared statements instead).
>>
>> 3) Instead of modifying an existing query, one could create a new
>> one with a new name.
>>
>>To me, option 1 seems like the best choice.
>>
>>
> I think so too... but I would like to see specific reasons to need this.
> Some concerns are 1) How would this affect read-only databases? If we
> decided to drop them even for maintenance versions or point-releases,
> need to handle read-only dbs
Since we can't drop and regenerate the statements in a read-only
database, we have two options
1) Leave the current behaviour of Derby. That is, metadata bugs
won't be fixed for read-only databases.
2) Make EmbedDatabaseMetaData do the same in read-only upgrade mode
as it does in soft upgrade mode. That is, read metadata queries
from metadata.properties.
Option 1 keeps the bugs, option 2 gives lower run-time performance
even for the unmodified queries.
> 2) Performance considerations. Could we
> instead make it as need arises? Like if version is going from 10.1.2 to
> 10.1.3 (just example) Hopefully these are rare cases.
Is performance a big issue? This will happen once per version
change. I agree that it could be solved when the need arises. See my
comments above.
--
Knut Anders
Re: [jira] Updated: (DERBY-1107) For existing databases JDBC metadata
queries do not get updated properly between maintenance versions.
Posted by Satheesh Bandaram <sa...@Sourcery.Org>.
Good write up, describing metadata design well... I think one of the
advantages of using SPS for metadata is performance. SPS statements are
pre-compiled and fast to execute. Compiling these (large) queries slow
down commonly used metadata queries. Statement caching reduces some
future need to recompile, but plans could go out of cache after some time.
Knut Anders Hatlen wrote:
> - if you move from one version to a version with the same major and
> minor version (say, from 10.1.1.0 to 10.1.2.1), the SPSs are
> invalidated, but not dropped. This means they will be recompiled,
> but still use the old SQL code. Hence, changes to an existing
> query in metadata.properties will not be visible to the users.
>
>
May be I missed some previous messages about this... Are there real
examples of need to change metadata between maintenance releases?
Probably to fix specific bugs? I looked at DERBY-1107 and it talks
about general need to have a mechanism. Don't think I saw any specific
examples.
>To solve the problem for the last case, there are at least three
>options:
>
> 1) Drop and regenerate SPSs on all version changes.
>
> 2) Stop using SPSs (use ordinary prepared statements instead).
>
> 3) Instead of modifying an existing query, one could create a new
> one with a new name.
>
>To me, option 1 seems like the best choice.
>
>
I think so too... but I would like to see specific reasons to need this.
Some concerns are 1) How would this affect read-only databases? If we
decided to drop them even for maintenance versions or point-releases,
need to handle read-only dbs 2) Performance considerations. Could we
instead make it as need arises? Like if version is going from 10.1.2 to
10.1.3 (just example) Hopefully these are rare cases.
>As you suggested, the client could invoke the SQL directly. This would
>however cause some problems:
>
> - if there is a bug in a metadata query, it is not enough to upgrade
> the server, you would also have to upgrade all the clients.
>
> - if the server changes the name of a system table or a column in a
> system table, old clients will be broken.
>
>The way it is now, if the bug is fixed on the server, it is also fixed
>on the client.
>
>
Right... I think we should keep SPS for metadata.
Satheesh
Re: [jira] Updated: (DERBY-1107) For existing databases JDBC metadata
queries do not get updated properly between maintenance versions.
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
"David W. Van Couvering" <Da...@Sun.COM> writes:
> 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?
Well, we are not discussing how the client should handle metadata on
upgrade, but how the embedded driver should handle it. These are two
separate issues which I think are orthogonal.
I'll summarize my understanding of these two issues (if someone who
knows these things reads this, please correct me if I'm wrong):
The SQL code for metadata queries is kept in metadata.properties. When
you create a database, Derby will read all the queries, compile them
and store them as SPSs (stored prepared statements). An SPS is not a
stored procedure, but a stored compiled query.
If you just modify a query in metadata.properties, the metadata call
will still use the old query since it is stored as an SPS. To make the
changes visible after a version change, Derby has this approach:
- if you are running in soft upgrade mode, the SPSs will not be
used. Instead, the query is read from metadata.properties. The
users will therefore see results from the new query.
- if you perform a hard upgrade to a new major/minor version, all
the SPSs are dropped and regenerated. The new query will be
visible to the users.
- if you move from one version to a version with the same major and
minor version (say, from 10.1.1.0 to 10.1.2.1), the SPSs are
invalidated, but not dropped. This means they will be recompiled,
but still use the old SQL code. Hence, changes to an existing
query in metadata.properties will not be visible to the users.
To solve the problem for the last case, there are at least three
options:
1) Drop and regenerate SPSs on all version changes.
2) Stop using SPSs (use ordinary prepared statements instead).
3) Instead of modifying an existing query, one could create a new
one with a new name.
To me, option 1 seems like the best choice.
The client, on the other hand, executes a stored procedure on the
server in order to fetch metadata. The stored procedure forwards the
call to the embedded driver, and the embedded driver will use an SPS
to fetch the metadata. This way, the client will always get whatever
the server means is the correct data. If you use an old client against
a new server, you will get results from the new query, and if you use
a new client against an old server, you get results from the old query
(or an error if that query was not implemented in the old version of
the server). As long as the signature of the stored procedure is not
changed, any version of the client should work with any version of the
server.
As you suggested, the client could invoke the SQL directly. This would
however cause some problems:
- if there is a bug in a metadata query, it is not enough to upgrade
the server, you would also have to upgrade all the clients.
- if the server changes the name of a system table or a column in a
system table, old clients will be broken.
The way it is now, if the bug is fixed on the server, it is also fixed
on the client.
--
Knut Anders
Re: [jira] Updated: (DERBY-1107) For existing databases JDBC metadata
queries do not get updated properly between maintenance versions.
Posted by "David W. Van Couvering" <Da...@Sun.COM>.
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?
>
>