How to migrate a Derby database?

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

How to migrate a Derby database?

ajax
It would be nice to have a way to convert a database to a form that is
independent of the software used to support the database. For example,
when using phpMyAdmin on a MySQL database there is an option referred to
as "Export".  This option causes the creation of a text file that
contains all of the SQL statements required to recreate the database at
least in MySQL or Maria DB.  In theory, such a file should also be
useful for migrating the same database to other kinds of relational
databases that support SQL.

At least in the case of MySQL/MariaDB this capability provides an easy
way to transfer databases from one server to another which may be
desirable when making transition to a new release.  If nothing else it
preserves the ability to restore to current release.  Given how many
versions of Derby presently exist it seems like this should be
especially important.

There is a present need to do such a migration simply to upgrade to a
new version of Derby.  In the case where this migration is from a very
old version of Derby the idea of a version independent file format would
seem to be very desirable.  Is that possible with Derby?  If so, some
reference to appropriate technique would be appreciated.  If NOT, is
there a prescribed method for doing such that is safe and effective?

Ajax ...

Reply | Threaded
Open this post in threaded view
|

Re: How to migrate a Derby database?

Rick Hillegas-3
Hi Ajax,

Here are a couple points to consider:

0) Derby supports two kinds of upgrade: soft and hard. Soft-upgraded
databases can be downgraded to previous releases of Derby (but not to a
release earlier than the original version of the database).
Hard-upgraded databases can not be downgraded. Please see the section on
upgrades in the Derby Developer's Guide:
http://db.apache.org/derby/docs/10.15/devguide/cdevupgrades.html

1) Consider using operating system commands to simply copy your old
database to a new location and then hard-upgrade the new copy in place.
You will need to quiesce the source database first, that is, gracefully
shut it down. The Derby database format has not changed since the code
was open-sourced in the early noughties. It is a platform-agnostic
format, so you can simply tar up the whole directory tree holding the
old database and then un-tar it in your target installation--even if the
target machine has a different architecture. Hard-upgrade is something
which we test whenever we vet a new Derby release. The test involves
creating a database from each of the releases which we have produced
over the past fifteen years and then hard-upgrading those databases to
the latest version, that is, to the release which we are vetting. Very
few bugs have been logged against hard-upgrade.

2) If you want to make this as difficult as MySQL, you can, of course,
dump the schema-creating DDL from the old database and then replay it in
a fresh database. Use the dblook tool for this purpose. It is somewhat
analogous to the MySQL Export tool mentioned below. Please see its
documentation in the Tools Guide:
http://db.apache.org/derby/docs/10.15/tools/ctoolsdblook.html Once you
have a schema shell, you will need to populate the tables with data from
the original database. There are two ways to do this:

i) Use the SYSCS_UTIL.SYSCS_EXPORT_TABLE system procedure to dump data
from the original database into flat files and then use the
SYSCS_UTIL.SYSCS_IMPORT_TABLE system procedure to load those files into
the new target database. Please see the sections on these procedures in
the Reference Manual:
http://db.apache.org/derby/docs/10.15/ref/rrefexportproc.html and
http://db.apache.org/derby/docs/10.15/ref/rrefimportproc.html

ii) You can avoid indirecting through flat files and, instead, transfer
your data quickly and directly by using the foreignViews tool documented
in the Tools Guide. Please see
http://db.apache.org/derby/docs/10.15/tools/rtoolsoptforeignviews.html 
In case, you're interested, this tool can also be used to quickly
migrate data out of non-Derby databases.

I would rank these approaches in ascending complexity and time needed,
as follows:

   1
   2ii
   2i

I would avoid over-thinking this problem. Derby does not suffer from the
kinds of version-specific format incompatibilities which plague other
databases. Option 1 is your best choice.

Please feel free to ask more questions.

Hope this helps,
-Rick


On 6/28/20 10:17 AM, [hidden email] wrote:

> It would be nice to have a way to convert a database to a form that is
> independent of the software used to support the database. For example,
> when using phpMyAdmin on a MySQL database there is an option referred
> to as "Export".  This option causes the creation of a text file that
> contains all of the SQL statements required to recreate the database
> at least in MySQL or Maria DB.  In theory, such a file should also be
> useful for migrating the same database to other kinds of relational
> databases that support SQL.
>
> At least in the case of MySQL/MariaDB this capability provides an easy
> way to transfer databases from one server to another which may be
> desirable when making transition to a new release.  If nothing else it
> preserves the ability to restore to current release.  Given how many
> versions of Derby presently exist it seems like this should be
> especially important.
>
> There is a present need to do such a migration simply to upgrade to a
> new version of Derby.  In the case where this migration is from a very
> old version of Derby the idea of a version independent file format
> would seem to be very desirable.  Is that possible with Derby?  If so,
> some reference to appropriate technique would be appreciated.  If NOT,
> is there a prescribed method for doing such that is safe and effective?
>
> Ajax ...
>

Reply | Threaded
Open this post in threaded view
|

Re: How to migrate a Derby database?

Rick Hillegas-3
In reply to this post by ajax
Hi Ajax,

I don't know why you are not receiving email which I posted to the
derby-user list. In any event, you can try posting your messages both to
me and to derby-user. I will respond to all so that you and derby-user
should be copied on the whole conversation.

Thanks,
-Rick

On 6/29/20 7:39 AM, [hidden email] wrote:

> <quote author='Rick Hillegas-3'>
> Hi Ajax,
>
> Here are a couple points to consider:
>
> ...
> </quote>
> Quoted from:
> http://apache-database.10148.n7.nabble.com/How-to-migrate-a-Derby-database-tp151268p151269.html
>
> I'm NOT completely sure what I'm doing right now.  I think this message should end up being an email addressed to Rick Hillegas.  I'm able to do this because of his reply to my original post which was done by sending an email.  It seems that I'm not able to respond to that reply via the website being used to send this message.  I sort of thought your reply might have shown up in my inbox as a result of having subscribed to the mailing list.  However, even after checking SPAM boxes I can find nothing.
>
> While I do have a question related to your excellent reply to my original post, the question for now is "How am I supposed to submit such questions?  In that, how to respond to your reply?".
>
>
> _____________________________________
> Sent from http://apache-database.10148.n7.nabble.com
>

Reply | Threaded
Open this post in threaded view
|

Re: How to migrate a Derby database?

ajax
There were a couple of factors related to this initial post. First, was
that I'd encountered a somewhat minor problem with an application that
I've been using for a bit more than 15 years without any prior problem. 
Second, I do some work with relational databases and thought I should
know how to do such things with Derby.

The minor problem was a failure to allow insertion of a specific new row
into a table.  Based on knowledge of what was being shown to be in the
database this should have worked fine.  Other insertions were also
working as expected.  Therefore, I deduced that a plausible explanation
would be some kind of corruption that may have crept into the underlying
files used to store the data. This could have happened anytime but based
on the specific elements involved I thought it likely to be something
that happened long ago.

Therefore, my idea was to want to recover as much of the data as
possible and then create a new database using that data.  I determined
that when it came to your suggestions 2i was the only one that fit this
criteria.  In that, this produces new database files that are completely
independent from the original ones. Fortunately, my archives did have a
text file that contained the SQL for creating the tables.  I haven't
used the resulting database much yet but I was able to insert the row
that previously failed and have no reason to think there is any problem.

When compared to the MySQL/phpMyAdmin export/import technique this one
is NOT quite as simple.  On the other hand ending up with the data in
.csv format could be considered more desirable for generalized
compatibility reasons than the SQL format used for MySQL.

Would be grateful to learn about any flaws in my assessment of this
situation and many thanks for the help.

ajax ...

On 6/29/2020 6:40 PM, Rick Hillegas wrote:

> Hi Ajax,
>
> I don't know why you are not receiving email which I posted to the
> derby-user list. In any event, you can try posting your messages both
> to me and to derby-user. I will respond to all so that you and
> derby-user should be copied on the whole conversation.
>
> Thanks,
> -Rick
>
> On 6/29/20 7:39 AM, [hidden email] wrote:
>> <quote author='Rick Hillegas-3'>
>> Hi Ajax,
>>
>> Here are a couple points to consider:
>>
>> ...
>> </quote>
>> Quoted from:
>> http://apache-database.10148.n7.nabble.com/How-to-migrate-a-Derby-database-tp151268p151269.html 
>>
>>
>> I'm NOT completely sure what I'm doing right now.  I think this
>> message should end up being an email addressed to Rick Hillegas.  I'm
>> able to do this because of his reply to my original post which was
>> done by sending an email.  It seems that I'm not able to respond to
>> that reply via the website being used to send this message.  I sort
>> of thought your reply might have shown up in my inbox as a result of
>> having subscribed to the mailing list.  However, even after checking
>> SPAM boxes I can find nothing.
>>
>> While I do have a question related to your excellent reply to my
>> original post, the question for now is "How am I supposed to submit
>> such questions?  In that, how to respond to your reply?".
>>
>>
>> _____________________________________
>> Sent from http://apache-database.10148.n7.nabble.com
>>
>
Reply | Threaded
Open this post in threaded view
|

Re: How to migrate a Derby database?

Rick Hillegas-3
I don't have enough information about the original database corruption
to speculate about the aptness of your solution. Maybe the corruption
could have been repaired in place without the need to create a new
database. For my money, 2ii is a faster solution than 2i, but your
mileage may vary. Glad to hear that you fixed your problem.

Cheers,
-Rick

On 6/30/20 8:37 AM, David Gowdy wrote:

> There were a couple of factors related to this initial post. First,
> was that I'd encountered a somewhat minor problem with an application
> that I've been using for a bit more than 15 years without any prior
> problem.  Second, I do some work with relational databases and thought
> I should know how to do such things with Derby.
>
> The minor problem was a failure to allow insertion of a specific new
> row into a table.  Based on knowledge of what was being shown to be in
> the database this should have worked fine.  Other insertions were also
> working as expected.  Therefore, I deduced that a plausible
> explanation would be some kind of corruption that may have crept into
> the underlying files used to store the data. This could have happened
> anytime but based on the specific elements involved I thought it
> likely to be something that happened long ago.
>
> Therefore, my idea was to want to recover as much of the data as
> possible and then create a new database using that data.  I determined
> that when it came to your suggestions 2i was the only one that fit
> this criteria.  In that, this produces new database files that are
> completely independent from the original ones. Fortunately, my
> archives did have a text file that contained the SQL for creating the
> tables.  I haven't used the resulting database much yet but I was able
> to insert the row that previously failed and have no reason to think
> there is any problem.
>
> When compared to the MySQL/phpMyAdmin export/import technique this one
> is NOT quite as simple.  On the other hand ending up with the data in
> .csv format could be considered more desirable for generalized
> compatibility reasons than the SQL format used for MySQL.
>
> Would be grateful to learn about any flaws in my assessment of this
> situation and many thanks for the help.
>
> ajax ...
>
> On 6/29/2020 6:40 PM, Rick Hillegas wrote:
>> Hi Ajax,
>>
>> I don't know why you are not receiving email which I posted to the
>> derby-user list. In any event, you can try posting your messages both
>> to me and to derby-user. I will respond to all so that you and
>> derby-user should be copied on the whole conversation.
>>
>> Thanks,
>> -Rick
>>
>> On 6/29/20 7:39 AM, [hidden email] wrote:
>>> <quote author='Rick Hillegas-3'>
>>> Hi Ajax,
>>>
>>> Here are a couple points to consider:
>>>
>>> ...
>>> </quote>
>>> Quoted from:
>>> http://apache-database.10148.n7.nabble.com/How-to-migrate-a-Derby-database-tp151268p151269.html 
>>>
>>>
>>> I'm NOT completely sure what I'm doing right now.  I think this
>>> message should end up being an email addressed to Rick Hillegas. 
>>> I'm able to do this because of his reply to my original post which
>>> was done by sending an email.  It seems that I'm not able to respond
>>> to that reply via the website being used to send this message.  I
>>> sort of thought your reply might have shown up in my inbox as a
>>> result of having subscribed to the mailing list.  However, even
>>> after checking SPAM boxes I can find nothing.
>>>
>>> While I do have a question related to your excellent reply to my
>>> original post, the question for now is "How am I supposed to submit
>>> such questions?  In that, how to respond to your reply?".
>>>
>>>
>>> _____________________________________
>>> Sent from http://apache-database.10148.n7.nabble.com
>>>
>>