"Syntax error" on some derby metadata calls

[Bob Durie <bo...@thirdbrigade.com>]

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.

 

I do not yet have a working test case as this issue only happens when
our database is in some bizarre and not easily reproducible state, but I
thought I'd toss it here to see if anyone has seen it:

 

ERROR 42X01: Syntax error:
org.apache.derby.catalog.IndexDescriptor.getKeyColumnPosition.
    at
org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
    at
org.apache.derby.impl.sql.compile.MethodCallNode.resolveMethodCall(Unkno
wn Source)
    at
org.apache.derby.impl.sql.compile.NonStaticMethodCallNode.bindExpression
(Unknown Source)
    at
org.apache.derby.impl.sql.compile.JavaToSQLValueNode.bindExpression(Unkn
own Source)
    at
org.apache.derby.impl.sql.compile.ResultColumn.bindExpression(Unknown
Source)
    at
org.apache.derby.impl.sql.compile.ResultColumnList.bindExpressions(Unkno
wn Source)
    at
org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown
Source)
    at
org.apache.derby.impl.sql.compile.FromSubquery.bindExpressions(Unknown
Source)
    at
org.apache.derby.impl.sql.compile.FromList.bindExpressions(Unknown
Source)
    at
org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown
Source)
    at
org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unkno
wn Source)
    at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown
Source)
    at org.apache.derby.impl.sql.compile.CursorNode.bind(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown
Source)
    at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown
Source)
    at
org.apache.derby.impl.sql.GenericPreparedStatement.makeValid(Unknown
Source)
    at
org.apache.derby.impl.sql.GenericPreparedStatement.rePrepare(Unknown
Source)
    at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown
Source)
    at
org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown
Source)
    at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unkno
wn Source)
    at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeQuery(Unknown
Source)
    at
org.apache.derby.impl.jdbc.EmbedDatabaseMetaData.getImportedKeys(Unknown
Source)

 

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.

 

Thanks,

Bob

userName legal characters specification?

[Dave Been <be...@us.ibm.com>]

I cannot find anywhere in the Derby documentation what are legal 
characters in a userName.
>From the web i find thing alluding to alphnumeric + _, someone offering a 
patch to change this.

Maybe this should be documented somewhere?   thanks

> If I try to connect, the error thrown is
>     ij> connect
> 'jdbc:derby:testdb;create=true;user=skambha@xyz.com;password=a';
>     ERROR 28502: The user name 'skambha@xyz.com' is not valid.
>
> The first error is being thrown from this method in
> 
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext#getAuthorizationId(String
> username) This method calls the IdUtil.parseId() which will for an
> unqualified id,  check if the string has only characters from a-z, A-Z
> and _ and digits.
> I want to remove this restriction for the username case.

Dave Been

RE: "Syntax error" on some derby metadata calls

[Bob Durie <bo...@thirdbrigade.com>]

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.

Re: "Syntax error" on some derby metadata calls

[Daniel John Debrunner <dj...@apache.org>]

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.