You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by �ystein Gr�vlen <Oy...@sun.com> on 2005/04/13 14:14:51 UTC
Schema issues?
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.
- 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.
- In my opinion, a default schema should be created by default. Is
there any particular reason for not doing so?
--
�ystein Gr�vlen, Senior Staff Engineer
Sun Microsystems, Database Technology Group
Trondheim, Norway
Re: Schema issues?
Posted by Øystein Grøvlen <Oy...@sun.com>.
>>>>> "DJD" == Daniel John Debrunner <dj...@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
Re: Schema issues?
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Ø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.
Not sure that mis-spellings is something that Derby has to be concerned
with, you could mis-spell many items within a SQL statement, e.g. in
your examples you could mis-spell the column names, table names,
'TRONDHEIM', mis-enter the varchar length, the value 7000.
I couldn't see anything in the standard about this either way, <table
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.
A table not found error might be more useful, I think it would be good
to keep the schema does not exist exception chained to the table not
found exception. This behaviour most likely occurs for all CREATE
statements.
> - In my opinion, a default schema should be created by default. Is
> there any particular reason for not doing so?
Depending on when the schema was created you could end up with many
schema definitions that are never used, wasting space. E.g. if the
application has many different users connecting and the schema is
created at connection time, then there will be many schemas. But if the
first action of the application is to perform a SET SCHEMA to a known
schema, then all of those default schemas are created for no reason.
The default schema for a user is created automatically when some CREATE
statement is issued for an object without a qualified name. This is the
same mechanism, implicit schema creation, you discuss in your first point.
Dan.