Re: How to migrate a Derby database?
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, recei...@gowdygroup.net wrote: Hi Ajax, Here are a couple points to consider: ... 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
Re: How to migrate a Derby database?
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, recei...@gowdygroup.net wrote: Hi Ajax, Here are a couple points to consider: ... 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
Re: How to migrate a Derby database?
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, recei...@gowdygroup.net wrote: Hi Ajax, Here are a couple points to consider: ... 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
Re: How to migrate a Derby database?
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, recei...@gowdygroup.net 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 ...
How to migrate a Derby database?
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 ...
