db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Oystein.Grov...@sun.com (Øystein Grøvlen)
Subject Re: Schema issues?
Date Fri, 15 Apr 2005 13:19:40 GMT
>>>>> "DJD" == Daniel John Debrunner <djd@debrunners.com> writes:

    DJD> Øystein Grøvlen wrote:
    >> I have a few questions to the following ij-session:
    >> ----------------------------------------------------------------------
    >> ij version 10.1
    ij> connect 'jdbc:derby:TestDB;create=true;user="user1";password="user1"';
    ij> create table testschema.testtab(num int, addr varchar(40));
    >> 0 rows inserted/updated/deleted
    ij> insert into testtab2 values(7000, 'TRONDHEIM');
    >> ERROR 42Y07: Schema 'user1' does not exist
    >> ----------------------------------------------------------------------
    >> - Derby seems to automatically create schemas that are referred by
    >> create table.  Is this according to the standard?  I think this
    >> behavior is problematic since mis-spellings of schema names will
    >> not be caught.

    DJD> Not sure that mis-spellings is something that Derby has to be concerned
    DJD> with, you could mis-spell many items within a SQL statement, e.g. in
    DJD> your examples you could mis-spell the column names, table names,
    DJD> 'TRONDHEIM', mis-enter the varchar length, the value 7000.

You are right.  I was thinking this was similar to FORTRAN's implicit
declaration of variables which can create a lot of problems, but in
this case you will get an error when one later try to use the created

    DJD> I couldn't see anything in the standard about this either way, <table
    DJD> definition> doesn't seem to refer much to the schema name.

    >> - I would expect "Table 'testtab2' does not exist" for the insert
    >> statement.  I guess what happens here is that Derby tries to find
    >> the table in the default schema which is 'user1' since I am
    >> connected as this user.  It then discovers that this schema does
    >> not exists and flag this error.  In my opinion, the error message
    >> is confusing and should be considered a bug.  I can create a Jira
    >> issue for this, but I would like to hear what other people think.

    DJD> A table not found error might be more useful, I think it would be good
    DJD> to keep the schema does not exist exception chained to the table not
    DJD> found exception. This behaviour most likely occurs for all CREATE
    DJD> statements.

I can file a Jira issue for this.

    >> - In my opinion, a default schema should be created by default.  Is
    >> there any particular reason for not doing so?

    DJD> Depending on when the schema was created you could end up with many
    DJD> schema definitions that are never used, wasting space. E.g. if the
    DJD> application has many different users connecting and the schema is
    DJD> created at connection time, then there will be many schemas. But if the
    DJD> first action of the application is to perform a SET SCHEMA to a known
    DJD> schema, then all of those default schemas are created for no reason.

    DJD> The default schema for a user is created automatically when
    DJD> some CREATE statement is issued for an object without a
    DJD> qualified name. This is the same mechanism, implicit schema
    DJD> creation, you discuss in your first point.

I see, combined with the automatic creation of schema on first create
this will not be an usability issue.

The reason I started looking at this is that in an application where
several threads create tables in parallell, a thread get the following
exception when creating a table in schema WIS without first creating
the schema:

ERROR X0Y68: Schema 'WIS' already exists.
        at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:322)
        at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.duplicateDescriptorException(DataDictionaryImpl.java:1512)
        at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.addDescriptorNow(DataDictionaryImpl.java:1498)
        at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.addDescriptor(DataDictionaryImpl.java:1479)
        at org.apache.derby.impl.sql.execute.CreateSchemaConstantAction.executeConstantAction(CreateSchemaConstantAction.java:148)
        at org.apache.derby.impl.sql.execute.DDLConstantAction.getSchemaDescriptorForCreate(DDLConstantAction.java:142)
        at org.apache.derby.impl.sql.execute.CreateTableConstantAction.executeConstantAction(CreateTableConstantAction.java:218)
        at org.apache.derby.impl.sql.execute.MiscResultSet.open(MiscResultSet.java:57)
        at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:367)
        at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1109)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:519)
        at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:475)
        at myapp.WiscInsertClient.run(WiscInsertClient.java:67)
        at java.lang.Thread.run(Thread.java:534)

It suspect there is a race condition where one thread detects that the
schema does not exists, but when it tries to create it, the scema has
already been created by another thread.


View raw message