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:[EMAIL PROTECTED] 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.
