[Solved - sort of] Split database to avoid corruption

[MSPhobe]

I try to Keep It Simple... but a recent major hassle with data corruption makes me wonder... should I go to "split database" mode?

I "migrate" between two locations. And I thought that just carefully closing down Base, and THEN copying my .odb file to a thumb-drive would be sufficient to migrate my database between two machines. I must admit: I forgot to see if the Quickstarter was closed.

Didn't work. (Has done in the past, other Base dbs... but not this time.)

Didn't work TWICE.... When I moved from computer A to computer B, the database was mostly okay... but one (simple) form was displaying zeros where there should have been fixed length text data... in several columns. (Other forms worked okay. All the forms simply directly accessed a single table. Nothing "fancy". Simple.)

... and, after rebuilding the form (the data was still in the tables), when I moved back from B to A... the same thing happened again! Once I can live with. Two out of two... not good.

So... any ideas what is wrong with my migration procedure? Yes, I save the database before closing Base, and I close Base before using ordinary Windows tools to move the .odb from my hard drive, where I work on the database, to the thumbdrive, which is just for sneaker-netting the file. Used different directories, so my thumbdrive now has two copies of my .odb... one for database's trip from A to B, other with the .odb's state after work was done on B, this second copy for bringing the database back to A... so I don't think a faulty thumbdrive is the issue?

All "work" on the database done to/from hard drive. Thumbdrive just for transport.

===
An earlier enquiry on this subject gave rise to the suggestion that I move to using a "split database" configuration.

As I said... I like Simple. Enough things go wrong under that scheme. But I'll go to split, if I need too. But still...

Simple: It will only be me using the database. I will stick with the basic, comes-with-Base 1.8.x HSQLDB engine. I will keep paths consistent between the two machines I work with. Are there other things I can do, to deny Mr. Murphy opportunities?

If I've done all of that, do you think I will be better off, in the new "split database" environment? Are there other terms for that? When I searched the forum for prior discussions (!), I didn't find much.

By "split database", I mean I am eschewing the OpenOffice attempt to "help" by creating an archive in one .odb file, said archive holding "everything": Tables, forms, etc, etc.

By "split", I mean I am willing to operate in a less-easy-to-set-up, less easy to maintain, but... what do you think?... more robust? world where my tables, forms, etc are in separate files. As if I were using Base as a front end for, say, a MySQL database.... I just happen to be using the same engine as Base would use if I "went with" the simple, native, not-split .odb system.

===
So... and thank you for reading thus far...

Three questions:

a) Any idea why my .odb file went peculiar TWICE?
b) What do you think about the pros/ cons of going to "split database" operation?
c) How many of you use the "split database" approach, with the "comes with Base" HSQLB engine?

Thanks!

===
PS... after many, many hours working on the above issues, and bringing in things I've learned from the wider 'net...

Re- a) No one seems to know why my .odb did quite what it did.
Re- **c**)... I don't get the impression that many people do this
Re- b) .... so there doesn't seem to be a body of opinion to comment on the pros/ cons.

Some who DO do it, are enthusiastic, it must be said. And they seem knowlegeable.

[Villeroy]

http://forum.openoffice.org/en/forum/vi ... 49#p214049

[MSPhobe]

Villeroy's helpful link will take you to guidance on replacing the database engine your Base uses.

I explored that, but chickened out... it would have had ramifications I didn't want to face.

For the moment, I will carry on using the standard embedded engine for much of my work, but for critical databases, I have spent days learning how to have the tables held in a MySQL server (running in the same PC as my OO, in the same PC I am interacting with directly), and using OO Base as my front end, to access the data in those tables. My forms and reports are in my .odb file, but they access tables in the more secure(?) MySQL.

It has not been a trivial exercise... but "do-able". And everything I've learned to accomplish that has sent me a long way down the road to being able to have the tables on an internet accessible server, and access them remotely, which might be fun one day! Also possible to have multi-user access to a MySQL hosted database, I'm pretty sure.

[DACM]

Some comments and observations:

...replacing the database engine your Base uses...but I chickened out... it would have had ramifications I didn't want to face.

Replacing the bundled HSQLDB 1.8.0.10 engine with HSQLDB 2.2.8 will disable support for the Base default 'embedded database' file [EDIT: you can employ any version of HSQLDB without disabling 'embedded database' support by adopting a macro-enhanced Base template or extension]. Although this limitation will be eliminated in a few days when HSQLDB 2.3.x is released with a special build for Base that allows both to coexist. But the leap to HSQLDB 2.x is not necessary for data-reliability. Simply add the existing HSQLDB 1.8.0.10 to the 'Class Path' in Base, while downloading this file (mydb.file.odb) in order to achieve immediate data-reliability with new/future Base databases.

Besides, few Base users require the additional function offered by HSQLDB 2.x (i.e. Date math, advanced LOB support, Group Concat, etc.). And there's no rush because the upgrade to HSQLDB 2.x is relatively seamless and automatic should it become necessary.

...but for critical databases, I have spent days learning how to have the tables held in a MySQL server...[to] access tables in the more secure(?) MySQL.

That seems to imply that MySQL is more reliable or "secure" than HSQLDB. That's not necessarily the case. All modern database engines are relatively reliable. But if you actually mean "secure" then HSQLDB 2.x offers much better security than MySQL out-of-the-box due to it's full-database encryption option available in HSQLDB 2.x. MySQL only offers individual string encryption/decryption through SQL commands -- while third-party add-ons are required to provide full-database encryption. Until early 2010, HSQLDB also offered better data reliability than the default MyISAM engine in MySQL since MyISAM is not a transactional engine. In 2010, MySQL 5.5 switched to a transactional engine (InnoDB) as the default primarily to address this issue of data reliability. If you're planning for 50+ users then MySQL is the better choice due to more robust connection pooling. If you're referring to internet security, then MySQL is more tweaked (lower latency, etc.) than HSQLDB on the Web. And while it's possible to host a secure HSQLDB server on the Web for encrypted access by multiple users -- even from your home computer hosting Apache Server with Apache Tomcat using a rented domain or static IP address -- the Web [database space] realistically belongs to MySQL (see AMP).

It has not been a trivial exercise...[eventually] being able to have the tables on an internet accessible server, and access them remotely, which might be fun one day!

Your experience is precisely why I don't promote mainstream database servers for new Base projects. Full-scale database servers require a much steeper learning curve and more daily administration than simply utilizing a seamless SQL engine such as HSQLDB or H2 with Base.

So it's worth considering the advantages (and disadvantages) of HSQLDB or perhaps H2 with Base. These two engines are relatively unique in allowing you to create an SQL database using Base from start to finish. And you don't have to manage a database server when using them in single-user 'file' mode with Base. And the database is bit-compatible and relatively easy to move among all platforms that run Base and Java. And they scale easily in support of dozens of users. And they're easier to learn since they use ANSI-standard SQL. And they're faster (except on Linux using JRE 7 where Base+HSQLDB is very slow). And they support LOB's out-of-the-box better than any mainstream database (the older HSQLDB 1.8 engine notwithstanding). And they offer better database security out-of-the-box with full-database encryption. And...perhaps more. So the vast majority of Base users will never outgrow HSQLDB 1.8.0.10, much less HSQLDB 2.x or H2. And if they do outgrow them, they're in for an education in SQL dialects and database server administration, just as you're encountering.

Suffice to say, novice Base users should consider all options on their relative merits with Base. But a Sheepdog Guide for MySQL would be a blessing for those who require/prefer MySQL, or simply don't have access to Java.

Don't miss: http://wiki.openoffice.org/wiki/Connect_MySQL_and_Base
http://forum.openoffice.org/en/forum/vi ... 40&t=41292
http://forum.openoffice.org/en/forum/vi ... 36#p261736
...

[Villeroy]

Villeroy's helpful link will take you to guidance on replacing the database engine your Base uses.
I explored that, but chickened out... it would have had ramifications I didn't want to face.

I can switch between the 2 HSQLDB engines and restart the office. This wouldn't be too painful because I don't switch between "productive mode" and "toy mode" very often. A simple script could do this switch between 2 Java configurations and restart the office.
Actually, I have two office suites installed so I could even use both types of databases in parallel.

Surely, you will never regret to learn about MySQL. Even if you go back to MS Access some day. Some people say that Access is the best desktop frontend for MySQL databases.

[MSPhobe]

DACM is absolutely right... I was (am!) willing to do the extra work for better data reliability. Security, in the proper use of the term, was not my concern.

Delighted to hear, I think, that there will soon be a way to use the better HSQLB engine without confusing ongoing legacy stuff. But it seems like one of those little "things" the system will be delighted to trip over. And the set up is more subtle... if less work... than the route I've gone down. And thus easier(?) to screw up? (I did look for a more elegant phrase, but that seemed most clear!)

Re...

DACM wrote:
That seems to imply that MySQL is more reliable or "secure" than HSQLDB. That's not necessarily the case. All modern database engines are relatively reliable.

Hmm. Maybe the relative reliability of MySQL and HSQLB are comparable, but using Base with an embedded engine has... only once, admittedly... let me down. I have "used" (played with) the standard (use embedded engine) OO Base for years, no regrets, no problems which didn't trace back to user error. I don't know what I've done that is so "special" in the big (ish) database I am building at the moment... but it did NOT play happily in an embedded engine environment.

"Divide and conquer" has always seemed right to me. And by using a server to manage my tables, I've divided that part out.

I do have an "unbroken" copy of my database from before The Big Crash. I spent hours during the rebuild process comparing the records of the old (complete) tables with the being-built new tables. Not a process I want to repeat. Rebuilding a form is a pain... not much more. Rebuilding a table's data? Not something I want to repeat.

Half of me was sorry to hear....

DACM wrote:
the Web realistically belongs to MySQL (see AMP).

... but half was glad. I looked for a long time to see which server I should try to master. And that was the conclusion I came to, too. Fantastic community... not unlike the OO Base community!!... where a newbie can find answers to silly "How do I...?" questions. And a mature product, with momentum. (And yes, I've long liked the LAMP/ WAMP packages.)

As I said in earlier posts: At the moment, I am still "single user", on a single machine (well... one at a time. The "migration" capability remains, and remains a reason for the extra work.) But if the work I am doing to learn to "crawl" in MySQL will start me down the road to being able to add users, or access the data remotely, well that's a good bonus! I've always wanted those things... but for today, I have to get more pressing things done. Sigh.

MSPhobe wrote:
It has not been a trivial exercise...

DACM wrote:
...is why I don't promote mainstream database servers. Full-scale database servers require a much steeper learning curve and more daily administration...

Actually, now that I am beginning to see what I am doing, yes... there is more work, and more to learn to get started. But I LIKE the new environment. Would seriously consider using it (now that it is set up, and I've "got started"!) for "ordinary" work, even though, as I said, I have not entirely given up on "plain" Base for everyday stuff. I like being less vulnerable to the complexities involved in the OO Quickstarter, and the fact that the .odb file is a huge archive of "stuff"... far too many eggs in one basket, and the creation of the "basket" not transparent enough or frequent enough. (To raise your respect for the Base creators, make a copy of an .odb file, rename it .zip, and look at it with your regular unzip/text editor tools!)

DACM wrote:
But a Sheepdog Guide for MySQL would be a blessing...

You are too kind! (And I am, so far, still too ignorant... but material IS being collected. It is just too easy to forget the little Gotchas that even one-eyed kings imagine "everyone knows")

Finally... two tips for anyone thinking of giving MySQL a try:

It doesn't play nicely with case sensitive data. You can MAKE it play with such data, but if you can go around the need for it, you will be better off. (I.e., don't build things so that "F123awx" and "f123awx" could both occur as values in a primary key field... and "need" to be treated as "different".)

It also Does Things (and at other times fails to) to the case of file and table names. So call your tables my_table, not MyTable. (Oh... and it doesn't like spaces in file/ table names. Bad idea anyway... I'm so glad I started in DOS, and still avoid file names like "What My Canary Thought". "wmct" was good enough for my grandfather....

[DACM]

DACM is absolutely right... I was (am!) willing to do the extra work for better data reliability.

Well, my point is, you're barking up the wrong tree. Data-reliability is not a direct-result of switching database engines. It's achieved with Base primarily by switching configurations (or wizards). Any compatible, transactional, ACID-compliant, database engine will do. Conjecture on data-reliability among qualified engines is largely meaningless -- especially relative to the greater impact of supplemental measures such as automated backups with versioning and perhaps a UPS or laptop with battery. And once the engine is running in a proper (split database) configuration, a Base crash alone won't affect data-reliability -- regardless of engine choice. This includes the bundled HSQLDB engine (setup steps). That's not to imply that a database cannot be corrupted, even in a proper configuration. Thus, most engines include built-in recovery mechanisms for that very purpose. A quick Google search on MySQL data corruption generates millions of hits, so you'll apparently have the lion's-share of help in the unlikely event of data corruption. Murphy's Law always applies.

...the big (ish) database I am building at the moment... but it did NOT play happily in an embedded engine environment.

I get it. Burn me once...

But "embedded" is a loaded word among Base users. Which embedded engine environment were you using?

• 1. Default 'embedded database' (.odb) file running HSQLDB in its embedded (in-process) mode?
  2. Non-embedded (split) database files running HSQLDB in file mode (embedded mode)?
  3. Non-embedded (split) database files running HSQLDB in server mode?
  4. And some folks use the term embedded to mean distributed with Base.

The difference is night-and-day in terms of data-reliability. For instance, the default 'embedded database' file (#1 above) configuration is not only unstable, but evidently designed to fail with larger databases due to intentional cache reductions, etc.

"Divide and conquer" has always seemed right to me. And by using a server to manage my tables, I've divided that part out.

I agree. But the extent of the division is exactly the same with MySQL or HSQLDB running in server mode.

I do have an "unbroken" copy of my database from before The Big Crash. I spent hours during the rebuild process comparing the records of the old (complete) tables with the being-built new tables. Not a process I want to repeat.

Did you ask in these forums for recovery help? Did the manual database recovery mechanisms built-into HSQLDB also fail you? Would the free supplemental measures mentioned above have helped?

At the moment, I am still "single user", on a single machine...[but] The "migration" capability remains, and remains a reason for the extra work.)

Granted, if you plan to serve more than few dozen users on the internet, especially without Base in favor of PHP, then skipping HSQLDB for MySQL is a great choice. Now we're talking!

[MySQL] doesn't play nicely with case sensitive data...So call your tables my_table, not MyTable.

Perhaps see these discussion about case-sensitivity:

• http://forum.openoffice.org/en/forum/vi ... 03#p199303
  http://forum.openoffice.org/en/forum/vi ... 09#p254909
  http://forum.openoffice.org/en/forum/vi ... 59#p220259
  ...

[MSPhobe]

ARGH! No "answers" expected... this just to say "Whew, all the work was worth it!"

My database just crashed AGAIN!.... but this time, I was more prepared!!!

I lost no recent entries to tables. (^_^)

Just recent tweaks to forms.

Here's the story... (this is for real, not just a "why I like my new mode of operation")..

Entered some data yesterday evening. Uneventful.
Left maching running overnight, forms, etc, open.
Went to enter data this morning. Glitches, etc, etc... immediately.
Base before long just shut itself down.
Restarted. Told document needed recovery. No big deal, been there, done that before now...

BUT!!!

The forms I'd had open previously came up open again... but unpopulated.
AND I couldn't close them, or the main Base project management window
Eventually resorted to Task Manager to shut down the soffice.bin process.
Tried a few restarts with recovery offered and accepted each time...
.... recovery "successful", but repeatedly the empty, uncloseable forms.
(All possibly to do with timed out connection to MySQL server?)

Eventually gave up. Made a copy of recent(!) backup of .odb
Tried running that. DECLINED "Recover document.
All was well!!! Hurrah!!!

(While writing, brief replies to DACM most recent...

Thank you again for USEFUL links, etc.

I think I was...
1. Default 'embedded database' (.odb) file running HSQLDB in its embedded (in-process) mode?
(I was (and still do, for many simple jobs) just using Base as it comes "out of the box".

Did I ask in these forums for recovery help? Oh yes. And for enlightenment as to why my migration scheme didn't work.

MySQL is a great choice. Now we're talking!

... so, part of my "logic" valid... all the work I do to master making my data safer from the "all eggs in one basket" model of .odb use will ALSO give me a head-start with the next adventure. Good news!

As for there being lots of hits for MySQL corruption, perhaps some arise from operator error? Lots of hits may just mean lots of people using the server? I understand that no platform is perfect, but it was a two minute delight to get back to where I was yesterday this time. The previous time Base blew a fuse, it was two weeks to get my data back to where it had been...

[The_Letter_J]

I do not have experience with HSQL, or with ooBase for that matter. But I have programmed SQLite and msAccess, so I know the general area.

As for MySQL , it is actually *multiple* database engines. The default engine is the fast-but-UN-reliable MyISAM style of tables. Those are more subject to data corruption e.g. from power failure which causes a server crash.

If you want reliability when using MySQL, the main tips are: *only* use the InnoDB style of tables, nothing else, anywhere. (This is talking about tables *you* create and use -- I don't mean you should go and mess with the internal-use-only tables that MySQL creates and configures for itself.) The other big one, which starts to apply as your structures-n-applications get more complicated, is to learn about transactions, and use them. This is the key to building the ACID-compliant systems that were mentioned further up this discussion. However, if you are only doing one-form-which-talks-to-one-table, with no cascading inserts/deletes, and no multirow updates, then transactions are prolly not actually required for your use-case, as yet. There is also the possibility that ooBase automagically uses transactions for you, under the hood, when talking to MySQL? (outside my knowledge)

p.s. You mention using thumbdrives -- make sure you have your OS configured so that they are 'optimized for quick removal' rather than the alternative 'optimized for quick performance'. You want all the bytes to be written to the thumbdrive, before you yank it out of the usb socket and send it across the sneaker-net. But some OSes will *not* actually write your data to the thumbdrive, just cause you told them too... instead, they'll keep that data in ram, deferring the write until later, when it can be batched up for performance reasons, or when it is convenient to the OS, or when the blue moon jumps over the cow with the dish and the spoon (or something like that). Make sure your system is configured to flush data-writes to the thumbdrive, before that thumbdrive is gone.