You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by si...@insession.com on 2004/11/09 07:56:04 UTC
Documentation questions for admin manual
Questions regarding the section "Differences between running Derby in
embedded mode and using the Network Server":
http://incubator.apache.org/derby/manuals/admin/hubprnt19.html
**Q1** In the section "User authentication" it says "The user's name
specified upon connection is the default schema for the connection, if a
schema with that name exists. See the "User Names and Schemas" section in
the Derby Developer's Guide."
What happens if a schema matching the user's name does not exist? Do I
have to manually create a schema for each defined user?
E.G. I am have defined a user called wkpoint using the BUILTIN
authentication provider in derby.properties
derby.connection.requireAuthentication=true
derby.authentication.provider=BUILTIN
derby.user.wkpoint=wppass
And I am seeing the following error returned from some of my SQL scripts.
ERROR 42Y07: Schema 'WKPOINT' does not exist
**Q2** Is the documentation for the section "Encrypted UserID and
Password" out of date? Do you need the IBM JCE if you are running the Sun
JRE 1.4?
**Q3** Typo 'Dun JDK'. Do you want an issue raised?
Thanks,
John
Re: Documentation questions for admin manual
Posted by si...@insession.com.
Thanks Army for the help regarding the schema error.
I have raised http://nagoya.apache.org/jira/browse/DERBY-65 for the
encryption problem.
Regards,
John
Army <ar...@golux.com> wrote on 11/11/2004 04:44:58 AM:
> sissonj@insession.com wrote:
> > Is this a bug? Should error 42X05 have been returned even on the
first
> > insert attempt?
>
> Technically speaking, no, I don't think is a "bug" per se, because
> the 42Y07 that you're seeing corresponds to the
> expected Derby behavior in the scenario you described. What's the
> expected behavior? Well, it's pretty much exactly
> what you described, which is reiterated in the following post, which
> is copy-pasted from a post to the user's list back
> on 09/29 by Dan Debrunner: (anything I've added/marked up is
> enclosed in square brackets)
>
> [ Begin quote ]
>
> The current schema for any connection defaults to a schema
> corresponding to the user name, in this case [WKPOINT]. If no
> user name is supplied then the user name (and hence current schema)
> defaults to APP. [ NOTE: User name defaults to APP
> for _embedded_ Derby--but for Network Server, one isn't supposed to
> be able to connect without a user name, so there is
> no "default"--schema will always be the user name. ]
>
> However even though the current schema is set to the user name, that
> schema may not exist. A schema is only created by
> CREATE SCHEMA or creating an object (table etc.) in that schema
> (this is implicit schema creation). Thus in your case
> the schema [WKPOINT] does not exist.
>
> The one exception to this is the APP schema, which is always
> created, though applications should not depend on that.
>
> So you will see the schema not exists error if your application
> tries to access the current schema before any objects
> have been created in it.
>
> [ end quote]
>
> So in a word, we _expect_ to see 42Y07 the first time, because the
> it IS in fact the case that the schema does not
> exist. Once you create a table, the schema is created implicitly--
> so subsequent attempts to reference a non-existent
> table will pass the "42Y07" check (because the schema now exists)
> and then fail with 42X05.
>
> I.e. it's a matter of precedence: Derby checks for the schema first,
> and _then_ checks for the table name, which makes
> sense since we can't verify the existence of the table unless we
> know what schema it's in. It's possible (and valid) to
> just return a "table not found" error if the schema doesn't exist,
> but "schema not found" is a more accurate description
> of the problem...
>
> >>**Q2** Is the documentation for the section "Encrypted UserID and
> >>Password" out of date? Do you need the IBM JCE if you are running the
> > Sun JRE 1.4?
>
> While Derby embedded supports the "encryptionProvider" connection
> attribute to specify a non-IBM encryption provider
> (such as Sun's), the Network Server currently does not. A glance at
> the java/drda/org/apache/derby/impl/drda file shows
> that "IBMJCE" is actually hardcoded into the server.
>
> That is something that will need to change at some point; not sure
> how hard/easy it would be to change the Server to
> accept other encryption providers, but it might be worth filing a
> JIRA enhancement request...?
>
> Army
Re: Documentation questions for admin manual
Posted by Army <ar...@golux.com>.
sissonj@insession.com wrote:
> Is this a bug? Should error 42X05 have been returned even on the first
> insert attempt?
Technically speaking, no, I don't think is a "bug" per se, because the 42Y07 that you're seeing corresponds to the
expected Derby behavior in the scenario you described. What's the expected behavior? Well, it's pretty much exactly
what you described, which is reiterated in the following post, which is copy-pasted from a post to the user's list back
on 09/29 by Dan Debrunner: (anything I've added/marked up is enclosed in square brackets)
[ Begin quote ]
The current schema for any connection defaults to a schema corresponding to the user name, in this case [WKPOINT]. If no
user name is supplied then the user name (and hence current schema) defaults to APP. [ NOTE: User name defaults to APP
for _embedded_ Derby--but for Network Server, one isn't supposed to be able to connect without a user name, so there is
no "default"--schema will always be the user name. ]
However even though the current schema is set to the user name, that schema may not exist. A schema is only created by
CREATE SCHEMA or creating an object (table etc.) in that schema (this is implicit schema creation). Thus in your case
the schema [WKPOINT] does not exist.
The one exception to this is the APP schema, which is always created, though applications should not depend on that.
So you will see the schema not exists error if your application tries to access the current schema before any objects
have been created in it.
[ end quote]
So in a word, we _expect_ to see 42Y07 the first time, because the it IS in fact the case that the schema does not
exist. Once you create a table, the schema is created implicitly--so subsequent attempts to reference a non-existent
table will pass the "42Y07" check (because the schema now exists) and then fail with 42X05.
I.e. it's a matter of precedence: Derby checks for the schema first, and _then_ checks for the table name, which makes
sense since we can't verify the existence of the table unless we know what schema it's in. It's possible (and valid) to
just return a "table not found" error if the schema doesn't exist, but "schema not found" is a more accurate description
of the problem...
>>**Q2** Is the documentation for the section "Encrypted UserID and
>>Password" out of date? Do you need the IBM JCE if you are running the
> Sun JRE 1.4?
While Derby embedded supports the "encryptionProvider" connection attribute to specify a non-IBM encryption provider
(such as Sun's), the Network Server currently does not. A glance at the java/drda/org/apache/derby/impl/drda file shows
that "IBMJCE" is actually hardcoded into the server.
That is something that will need to change at some point; not sure how hard/easy it would be to change the Server to
accept other encryption providers, but it might be worth filing a JIRA enhancement request...?
Army
Re: Documentation questions for admin manual
Posted by si...@insession.com.
See comments inline..
sissonj@insession.com wrote on 09/11/2004 05:56:04 PM:
> Questions regarding the section "Differences between running Derby in
> embedded mode and using the Network Server":
>
> http://incubator.apache.org/derby/manuals/admin/hubprnt19.html
>
> **Q1** In the section "User authentication" it says "The user's name
> specified upon connection is the default schema for the connection, if a
> schema with that name exists. See the "User Names and Schemas" section
in
> the Derby Developer's Guide."
>
> What happens if a schema matching the user's name does not exist? Do I
> have to manually create a schema for each defined user?
>
> E.G. I am have defined a user called wkpoint using the BUILTIN
> authentication provider in derby.properties
>
> derby.connection.requireAuthentication=true
> derby.authentication.provider=BUILTIN
> derby.user.wkpoint=wppass
>
> And I am seeing the following error returned from some of my SQL
scripts.
>
> ERROR 42Y07: Schema 'WKPOINT' does not exist
I now know the circumstances where it reports ERROR 42Y07: Schema
'WKPOINT' does not exist.
My script was attempting to do an insert before the table was defined.
Here is the output showing the insert failing with the above error, the
table being defined and then the insert working:
D:\Derby_snapshots\svnversion_46005>java -cp
.;.\lib\derby.jar;.\lib\derbynet.jar;.\lib\derbytools.jar;..\db2jcc\lib\db2jcc.jar;..\db2jcc\lib\db2jcc_license_c.jar
-Dij.driver=com.ibm.db2.jcc.DB2Driver -Dij.user=wkpoint
-Dij.password=wppass -Dij.protocol=jdbc:derb
y:net://localhost:1527/ org.apache.derby.tools.ij
ij version 10.0 (C) Copyright IBM Corp. 1997, 2004.
ij> connect
'testDB3;create=true:retrieveMessagesFromServerOnGetMessage=true;';
ij> insert into MY_TABLE(MY_ID, NAME) values(1, 'blah');
ERROR 42Y07: Schema 'WKPOINT' does not exist
ij> create table MY_TABLE
(
MY_ID smallint not null,
NAME varchar(32) not null
);
0 rows inserted/updated/deleted
ij> insert into MY_TABLE(MY_ID, NAME) values(1, 'blah');
1 row inserted/updated/deleted
ij> insert into MY_OTHER_TABLE(MY_ID, NAME) values(1, 'blah');
ERROR 42X05: Table 'MY_OTHER_TABLE' does not exist.
ij>
Note how the first time I attempted an insert I received the 42Y07 error,
but after one table had been defined an attempt to insert into a non
existent table gave me 42X05, the error I was expecting. So it seems that
when the first table is created, the schema is created for the user
(currently connected via network server).
Is this a bug? Should error 42X05 have been returned even on the first
insert attempt?
Here is my sysinfo.. (other questions still below)
D:\Derby_snapshots\svnversion_46005>java -cp
.;.\lib\derby.jar;.\lib\derbynet.jar;.\lib\derbytools.jar;..\db2jcc\lib\db2jcc.jar;..\d
b2jcc\lib\db2jcc_license_c.jar -Dij.driver=com.ibm.db2.jcc.DB2Driver
org.apache.derby.tools.sysinfo
------------------ Java Information ------------------
Java Version: 1.4.2_05
Java Vendor: Sun Microsystems Inc.
Java home: C:\Program Files\Java\j2re1.4.2_05
Java classpath:
.;.\lib\derby.jar;.\lib\derbynet.jar;.\lib\derbytools.jar;..\db2jcc\lib\db2jcc.jar;..\db2jcc\lib\db2jcc_license_c.j
ar
OS name: Windows XP
OS architecture: x86
OS version: 5.1
Java user name: sissonj
Java user home: C:\Documents and Settings\john
Java user dir: D:\Derby_snapshots\svnversion_46005
--------- Derby Information --------
[D:\Derby_snapshots\svnversion_46005\lib\derby.jar] 10.0.2.0 - (46005)
[D:\Derby_snapshots\svnversion_46005\lib\derbynet.jar] 10.0.2.0 - (46005)
[D:\Derby_snapshots\svnversion_46005\lib\derbytools.jar] 10.0.2.0 -
(46005)
[D:\Derby_snapshots\db2jcc\lib\db2jcc.jar] 2.4 - (17)
[D:\Derby_snapshots\db2jcc\lib\db2jcc_license_c.jar] 2.4 - (17)
------------------------------------------------------
----------------- Locale Information -----------------
------------------------------------------------------
>
> **Q2** Is the documentation for the section "Encrypted UserID and
> Password" out of date? Do you need the IBM JCE if you are running the
Sun
> JRE 1.4?
>
> **Q3** Typo 'Dun JDK'. Do you want an issue raised?
>
> Thanks,
>
> John
>