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 Egil Sørensen <eg...@stud.ntnu.no> on 2007/02/15 10:02:02 UTC
Internal Statements in Derby
Hi!
As a part of my Master Thesis at NTNU, Trondheim I am trying to
implement a Hot-Standby scheme between two Derby databases located on
different hosts. So far the primary derby database creates logical
log records and ships these to the Hot-Standby database. However, I
am having some trouble making the Hot-Standby derby to redo the
statements when they are committed on the primary node.
Is there a way to run internal sql statements from within a Derby
database with no active connections? I have tried to use the
prepareInternalStamement(sql) from the
GenericLanguageConnectionContext, but it needs an active connection,
which Hot-Standby databases do not have by definition.
It is important to do the operation at a high enough abstraction so
that the operation is logged with the physical logger as well.
Thanks!
- Egil Sørensen
Re: Internal Statements in Derby
Posted by Bryan Pendleton <bp...@amberpoint.com>.
> (org.apache.derby.impl.services.net.logreceiver). When logs are received
> here these need to be redone and reflected on the dbms running this code.
This sounds like very interesting work, and I'm looking forward to learning
more about it. Will you be able to share it with the community? Perhaps later,
when you have worked out more of the details and have running code to share?
thanks,
bryan
Re: Internal Statements in Derby
Posted by Egil Sørensen <eg...@stud.ntnu.no>.
Ok, I have been shown how the network server works and a suggestion
was to create a DRDAConnectionThread for the Hot Standby Controller
and then close the clientConnectionThread to create an internal
connection to the database.
What I mean by "from inside the same database" is that the source
code for the log receiver is implemented inside the derby dbms source
code (org.apache.derby.impl.services.net.logreceiver). When logs are
received here these need to be redone and reflected on the dbms
running this code.
Thanks
- Egil Sørensen
On Feb 21, 2007, at 5:03 AM, Bryan Pendleton wrote:
>> I am shipping logical log records from node to node. However these
>> can be converted into sql and in that way be redone by a normal
>> jdbc-statement. However, will it work to open an external jdbc-
>> connection to the database from the inside of the same database?
>
> Yes, you should be able to open multiple connections to the
> same database. Each connection will behave as a separate transaction,
> so be careful to ensure that you don't deadlock upon yourself.
>
> What do you mean "from the inside of the same database"? Does this
> mean that you are running from inside a trigger, or a database
> procedure,
> or some special situation like that?
>
> thanks,
>
> bryan
>
>
Re: Internal Statements in Derby
Posted by Bryan Pendleton <bp...@amberpoint.com>.
> I am shipping logical log records from node to node. However these can
> be converted into sql and in that way be redone by a normal
> jdbc-statement. However, will it work to open an external
> jdbc-connection to the database from the inside of the same database?
Yes, you should be able to open multiple connections to the
same database. Each connection will behave as a separate transaction,
so be careful to ensure that you don't deadlock upon yourself.
What do you mean "from the inside of the same database"? Does this
mean that you are running from inside a trigger, or a database procedure,
or some special situation like that?
thanks,
bryan
Re: Internal Statements in Derby
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Mike Matrigali <mi...@sbcglobal.net> writes:
> Was there a reason to ship "logical" log records - did that just fit
> with some code you had for other dbs?
>
> I believe Derby trunk is a long way there for someone to implement a
> hot standby using the existing "physical" log records. With the most
> recent online backup work, one could put the working db in "online
> backup mode". What this does is it configures the system so that
> all physical log files can be applied a point in time copy of the
> db to bring it up the current state. The missing code is to get the
> existing log records copied over to an online backup on another
> machine. Work to do would
I made a proof-of-concept implementation of this method a while back
by shipping completed log files via shell scripts to the standby,
essentially Mike's option one, and introduced a
"rollForwardContinuousRecoveryFrom" mode which basically is the
ordinary Derby "rollForwardRecoveryFrom", but with the "ever-running"
loop which picks up incoming log files detects failover by way of a
special file. I there is any interest I can post the patch and the
scripts. I did not do any work on the client reconnect part, I was
thinking we could make a url containing both servers, the so the app
would only need to do a reconnect, like Mike says.
Using scripts is not what you would want, of course; it was just a
fast way to try it out a bit..
Dag
> include:
> o some code at the log level to ship data in log.dat files from machine
> to machine. One would have to decide how often to do this. Some
> options would be:
> o log switch time (ie. file at a time) - probably easily done
> inititiated by a callout to a externally implemented system procedure.
> Read can happen from file on disk as data is guaranteed synced.
> o synced log block write (ie. block(s) at a time) - again system
> procedure callout, with data read directly from synced file and
> shipped in a way hidded from log system. Note that nothing that
> hasn't been synced is really needed by the standby.
> o log block write (synced/unsynced) - a little harder as data now
> may not be in the file for external communication. Call to
> system procedure could include it.
> o log record at time.
>
> o On the hot standby side the code wants to look like a "ever-running"
> online backup with some magic code in the redo loop that waits for new
> log records rather than exit when it gets to end.
>
> o Finally some coordination code to turn the hot standby recovered
> online backup into the current db.
>
> o I haven't thought how you move users from the old db to the new db.
> I think could be some sort of smart jdbc url that hides the actual db
> user is connecting to. This is probably easy if it ok to fail a
> connection and then succeed on a retry.
>
> Egil Sørensen wrote:
>> Thank you for your reply.
>> I am shipping logical log records from node to node. However these
>> can be converted into sql and in that way be redone by a normal
>> jdbc-
>> statement. However, will it work to open an external jdbc-connection
>> to the database from the inside of the same database? I would assume
>> there would be a better internal solution, however I have yet to
>> discover one :)
>> Thanks,
>> Egil
>> On Feb 15, 2007, at 4:09 PM, Bryan Pendleton wrote:
>>
>>>> the primary derby database creates logical log records and ships
>>>> these to the Hot-Standby database. However, I am having some
>>>> trouble making the Hot-Standby derby to redo the statements when
>>>> they are committed on the primary node.
>>>
>>>
>>> I'm not quite sure I understand. Are you transferring log records
>>> from node to node? Or are you transferring SQL statements?
>>>
>>> If you are transferring SQL statements, can't the recipient node
>>> open a normal JDBC Statement and call Statement.executeUpdate()?
>>>
>>> thanks,
>>>
>>> bryan
>>>
>>>
>>>
>>
>
--
Dag H. Wanvik
Sun Microsystems, Database Technology Group (DBTG)
Haakon VII gt. 7b, N-7485 Trondheim, Norway
Tel: x43496/+47 73842196, Fax: +47 73842101
Re: Internal Statements in Derby
Posted by Mike Matrigali <mi...@sbcglobal.net>.
Was there a reason to ship "logical" log records - did that just fit
with some code you had for other dbs?
I believe Derby trunk is a long way there for someone to implement a
hot standby using the existing "physical" log records. With the most
recent online backup work, one could put the working db in "online
backup mode". What this does is it configures the system so that
all physical log files can be applied a point in time copy of the
db to bring it up the current state. The missing code is to get the
existing log records copied over to an online backup on another machine.
Work to do would
include:
o some code at the log level to ship data in log.dat files from machine
to machine. One would have to decide how often to do this. Some
options would be:
o log switch time (ie. file at a time) - probably easily done
inititiated by a callout to a externally implemented system procedure.
Read can happen from file on disk as data is guaranteed synced.
o synced log block write (ie. block(s) at a time) - again system
procedure callout, with data read directly from synced file and
shipped in a way hidded from log system. Note that nothing that
hasn't been synced is really needed by the standby.
o log block write (synced/unsynced) - a little harder as data now
may not be in the file for external communication. Call to
system procedure could include it.
o log record at time.
o On the hot standby side the code wants to look like a "ever-running"
online backup with some magic code in the redo loop that waits for new
log records rather than exit when it gets to end.
o Finally some coordination code to turn the hot standby recovered
online backup into the current db.
o I haven't thought how you move users from the old db to the new db. I
think could be some sort of smart jdbc url that hides the actual db
user is connecting to. This is probably easy if it ok to fail a
connection and then succeed on a retry.
Egil Sørensen wrote:
> Thank you for your reply.
>
> I am shipping logical log records from node to node. However these can
> be converted into sql and in that way be redone by a normal jdbc-
> statement. However, will it work to open an external jdbc-connection to
> the database from the inside of the same database? I would assume there
> would be a better internal solution, however I have yet to discover one :)
>
> Thanks,
>
> Egil
>
>
> On Feb 15, 2007, at 4:09 PM, Bryan Pendleton wrote:
>
>>> the primary derby database creates logical log records and ships
>>> these to the Hot-Standby database. However, I am having some trouble
>>> making the Hot-Standby derby to redo the statements when they are
>>> committed on the primary node.
>>
>>
>> I'm not quite sure I understand. Are you transferring log records
>> from node to node? Or are you transferring SQL statements?
>>
>> If you are transferring SQL statements, can't the recipient node
>> open a normal JDBC Statement and call Statement.executeUpdate()?
>>
>> thanks,
>>
>> bryan
>>
>>
>>
>
>
>
Re: Internal Statements in Derby
Posted by Egil Sørensen <eg...@stud.ntnu.no>.
Thank you for your reply.
I am shipping logical log records from node to node. However these
can be converted into sql and in that way be redone by a normal jdbc-
statement. However, will it work to open an external jdbc-connection
to the database from the inside of the same database? I would assume
there would be a better internal solution, however I have yet to
discover one :)
Thanks,
Egil
On Feb 15, 2007, at 4:09 PM, Bryan Pendleton wrote:
>> the primary derby database creates logical log records and ships
>> these to the Hot-Standby database. However, I am having some
>> trouble making the Hot-Standby derby to redo the statements when
>> they are committed on the primary node.
>
> I'm not quite sure I understand. Are you transferring log records
> from node to node? Or are you transferring SQL statements?
>
> If you are transferring SQL statements, can't the recipient node
> open a normal JDBC Statement and call Statement.executeUpdate()?
>
> thanks,
>
> bryan
>
>
>
Re: Internal Statements in Derby
Posted by Bryan Pendleton <bp...@amberpoint.com>.
> the primary derby database creates logical log
> records and ships these to the Hot-Standby database. However, I am
> having some trouble making the Hot-Standby derby to redo the statements
> when they are committed on the primary node.
I'm not quite sure I understand. Are you transferring log records
from node to node? Or are you transferring SQL statements?
If you are transferring SQL statements, can't the recipient node
open a normal JDBC Statement and call Statement.executeUpdate()?
thanks,
bryan