curious problem dropping triggers and tables

[gmbradford <gm...@alcatel-lucent.com>]

I have a java application that gets a copy of a Derby database that is
transferred to it over a network. (The sending end freezes the DB and then
sends the files over a socket.) The receiving jvm closes the socket and
streams when the database transfer is complete, and then connects to it to
do some sql. The sql includes delete statements, drop trigger statements,
and drop table statements. The delete statements consistently work fine, but
the drop trigger and drop table statements don't always work (sometimes they
do). When they don't, we see SqlSyntaxErrorException, with the complaint
that the trigger or table does not exist. We can't look at the DB while this
jvm is running because it's using embedded mode, but when it's stopped, ij
shows that the triggers and tables exist and from ij the drops work fine.

I'm wondering if anyone has an idea why this might be happening.
-- 
View this message in context: http://old.nabble.com/curious-problem-dropping-triggers-and-tables-tp28235584p28235584.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: curious problem dropping triggers and tables

[Brett Wooldridge <br...@gmail.com>]

Are you sure all of the file streams are flushed and closed after the
transfer?  Can you check with some tool like 'lsof' on Linux or some tool
from SysInternals if Windows?  After shutdown of the embedding VM, all
handles would be closed, which might be why ij works after that.  Just a
thought.

-Brett

On Wed, Apr 14, 2010 at 6:03 AM, gmbradford
<gm...@alcatel-lucent.com>wrote:

>
> I have a java application that gets a copy of a Derby database that is
> transferred to it over a network. (The sending end freezes the DB and then
> sends the files over a socket.) The receiving jvm closes the socket and
> streams when the database transfer is complete, and then connects to it to
> do some sql. The sql includes delete statements, drop trigger statements,
> and drop table statements. The delete statements consistently work fine,
> but
> the drop trigger and drop table statements don't always work (sometimes
> they
> do). When they don't, we see SqlSyntaxErrorException, with the complaint
> that the trigger or table does not exist. We can't look at the DB while
> this
> jvm is running because it's using embedded mode, but when it's stopped, ij
> shows that the triggers and tables exist and from ij the drops work fine.
>
> I'm wondering if anyone has an idea why this might be happening.
> --
> View this message in context:
> http://old.nabble.com/curious-problem-dropping-triggers-and-tables-tp28235584p28235584.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>
>

Re: curious problem dropping triggers and tables

[gmbradford <gm...@alcatel-lucent.com>]

I don't think the problem is the wrong schema, because it uses the same
connection to do both the deletes and the drops, and the deletes always
work. And even the drops work sometimes.

I thought there might be a race condition of some sort, so I tried sleeping
awhile before doing the sql. I also tried connecting to the database,
immediately followed by shutting down the database, and then connecting
again before doing the sql. Neither helped.
-- 
View this message in context: http://old.nabble.com/curious-problem-dropping-triggers-and-tables-tp28235584p28242001.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: curious problem dropping triggers and tables

[Knut Anders Hatlen <Kn...@Sun.COM>]

On 04/13/10 11:03 PM, gmbradford wrote:
> I have a java application that gets a copy of a Derby database that is
> transferred to it over a network. (The sending end freezes the DB and then
> sends the files over a socket.) The receiving jvm closes the socket and
> streams when the database transfer is complete, and then connects to it to
> do some sql. The sql includes delete statements, drop trigger statements,
> and drop table statements. The delete statements consistently work fine, but
> the drop trigger and drop table statements don't always work (sometimes they
> do). When they don't, we see SqlSyntaxErrorException, with the complaint
> that the trigger or table does not exist. We can't look at the DB while this
> jvm is running because it's using embedded mode, but when it's stopped, ij
> shows that the triggers and tables exist and from ij the drops work fine.
>
> I'm wondering if anyone has an idea why this might be happening.
>   

Perhaps it's executing the DROP statements in the wrong schema? The
default schema of a Derby session is the same as the user name. You can
change that by executing SET SCHEMA <schemaName>, or you can qualify the
table/trigger name with the schema name (e.g., DROP TRIGGER
MYSCHEMA.MYTRIGGER).

-- 
Knut Anders