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
table.

    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.

-- 
Øystein


Mime
View raw message