db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bob Durie" <bob.du...@thirdbrigade.com>
Subject RE: "Syntax error" on some derby metadata calls
Date Thu, 22 Mar 2007 20:53:29 GMT
Thanks for the response Daniel.

There is certainly some DDL involved, but no rollbacks (unless failures
occur, which isn't part of the normal code path).  I just tried a
variant based on your suggestion, and the transaction seems irrelevant -
without it, the problem still happens, albeit on different tables.

The DDL we do is just a whole bunch of alter table statements, and
sometimes they even have no effect.  We get the meta data, get the key
info, run alter table statements based on discrepancies we think we've
found with the meta data and our application, and then proceed to the
next table to repeat.

I will attempt to generate a test case, but as I said this only happens
on some data sets, making it extremely hard to reproduce.  Any further
suggestions would be appreciated!

Thanks,
Bob

-----Original Message-----
From: Daniel John Debrunner [mailto:djd@apache.org] 
Sent: Thursday, March 22, 2007 1:26 PM
To: Derby Discussion
Subject: Re: "Syntax error" on some derby metadata calls

Bob Durie wrote:
> Hi,
> 
>  
> 
> I've searched for this issue on jira with no luck, posting here in
hopes 
> someone has seen it or knows where it might be coming from.
> 
>  
> 
> What I'm seeing is that "sometimes" during a schema upgrade of our 
> database, the getImportedKeys or getPrimaryKeys methods of 
> EmbedDatabaseMetaData fail with syntax errors (see the stack trace 
> below).  If we rollback the transaction, try again, it is ALWAYS 
> successful, but may fail later on when we do a similar operation with 
> another table.  Hence, we have a workaround (try again), but it
doesn't 
> leave me feeling very confident.
[snip]
> One thing to note is that the process in our application calls the 
> "connection.getMetaData" many many times, makes updates to the schema,

> and calls it again many times on the same transaction.  While not 
> necessarily efficient, I'm reluctant to change it as I want to know
the 
> source of the problem first.

I think it's a bug in Derby. The metadata queries use some internal SQL 
constructs and are compiled in "internal SQL" mode to allow these 
constructs. It looks like something is triggering a re-compile of the 
metadata statement at runtime and during this re-compile the internal 
SQL flag is not set.

I'm not sure what you could be doing that results in the recompile, one 
possibility is a rollback in a transaction that performed some DDL. 
Somthing like:
    begin tran
    ddl
    execute meta data for first time
    rollback

Derby at the moment is fairly liberal in its statement invalidation when

a rollback happens in a transaction that performed DDL, assuming that 
this is a infrequent and non-performance critical event.

Dan.


Mime
View raw message