[Tutorial] Avoiding data loss with built in HSQLDB

Forum rules
No question in this forum please
For any question related to a topic, create a new thread in the relevant section.

[Tutorial] Avoiding data loss with built in HSQLDB

Postby r4zoli » Mon Apr 13, 2009 9:18 pm

For .odb files with embedded HSQLDB database.

When you use Base as front end to real database (MySQL, postgreSQL, Firebird etc.) then database server keeps data.

From usual posts on data loss issue:
My problem is that Base keeps crashing on me and, although I frequently save manually and have it set to autosave every 15 minutes, the auto-recover facility "recovers" my database back to a stage I had it at many hours ago. Yesterday I lost a full days work and tonight the same thing has happened again.


OOo Base works when opens odb file with built in HSQLDB, first unpack it into memory, start HSQLDB server load data file into database server, when you run a query it runs always in memory, data not saved until you save it manually, and if you saved into disks, your data in memory if you change again or crash happens, may be auto recovery helps. Auto save, backup not working with odb files.

Low on memory


It is a real problem, OOo Base open all data into memory and if it is low, crash can happens when database size reach memory size, and it cannot control.
When you add new items as queries, forms or reports all things require space in memory, and in disk accordingly.

To lower risks on data loss it is good to reduce the size of a Base database when you invested enough work into.
To do this, choose Tools -> SQL, execute the SHUTDOWN COMPACT command, close file and open back.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

[Tutorial] Avoid data loss by avoiding "Embedded databases"

Postby DACM » Mon Nov 01, 2010 9:57 pm

The user-community revisited this problem with data corruption in the Fall of 2010. The results are very encouraging. To begin, I should clarify that the data loss issue is not attributed to the built-in HyperSQL database engine (HSQLDB). We find no fault or instability associated with this third-party database engine. The fault lies entirely with Base, and is limited to the all-in-one "embedded database" file created by the "New Database" wizard. This wizard 'embeds' the HSQL-database files inside the Base container file (.odb) where they become susceptible to corruption by Base. And unfortunately, a similar propensity for file corruption can impact all OOo/AOO/LibO users so consider saving all documents and databases to a protected folder utilizing the latest version history features.

There is a fix, however, for Base...

The data loss problem is easily overcome because Base also supports a traditional "split database" configuration using the 'Connect to an existing database' wizard. In this configuration, the back-end database files (Tables & Data) are maintained separately & exclusively by the database engine. So the database data is inherently safe from corruption by Base. Problem solved. And despite the wizard's name, a new database is generated, as needed, by the HSQLDB engine. The wizard is a bit daunting, so simply download a preset Base template which automatically creates a split HSQL 2.3.x database within a portable folder.

It remains prudent to protect this folder using automated backup/file-history options as mentioned previously, because several front-end components remain inside the Base (.odb) file including your Forms, Queries, Reports and Macros. And while you're not likely to encounter back-end database corruption in this configuration, no system is fully immune...enough said. So for peace-of-mind, simply move (drag-&-drop) the split-database folder to your cloud backup folder (recommend SpiderOak for personal encryption and unlimited file-history features) where you can run the database without fear of losing critical data.

This 'split-database' configuration also supports simultaneous database sharing among networked users (see: server mode and the related daily CHECKPOINT considerations). Some users also export their Forms which effectively hides the Base environment at runtime. Legacy "embedded databases" can be converted to this "split database" configuration while maintaining seamless database access with Base or other front-ends.

You'll find additional resources including an automated wizard under the "Related posts" links below. But suffice to say that we encourage the use of HSQLDB with Base in a proper configuration, even over alternative engines, due to the tight-integration including seamless engine startup. Additional benefits include unsurpassed: ease-of-setup, Base-community support, cross-platform and portability options. In fact, when paired with the latest HSQLDB 2.x engine there's little reason to supplant HSQLDB for small-to-medium size projects including encrypted, multi-user databases on a network (LAN) with role-based security. This is not to overlook the merits of other good options with Base such as H2, PostgreSQL, MySQL/MariaDB, Firebird, SQL Server Express, Oracle Database XE, etc. If you have significant experience with these or need features not available with HSQLDB 2.x then your choice is clear. But rest assured, these are not necessary to achieve a reliable, even full-featured, desktop-database with Base. And I think you'll find that the user-community is best primed for Base+HSQLDB support.


Here's a glossary of terms associated with this topic.

'file mode' runs HSQLDB "in-process" with *Office for seamless database access by a single-user. The user-experience is similar to the default "embedded database" configuration with no need to start/stop the engine manually.

'server mode' employs HSQLDB in the standalone server/listener configuration requiring separate engine start-up/shutdown. This mode enables concurrent, read/write access with user-security over a network. It also contributes to data durability through additional layers of protection.

'split database' terminology is adopted to provide distinction from the Base default "embedded database" configuration. A split database refers to the separation of files on disk; separate front-end (Base) files and back-end (database) files. So a portable split-database will consist of multiple files in a single folder rather than 'embedded' within a single [zip-archive] file. The status bar in Base will reflect "JDBC" when running a split database configuration based on the bundled Java engine (HSQLDB).

    Note: A split database can be run in 'file' or 'server' mode interchangeably. Server-mode supports multiple, concurrent users but is more difficult to setup and manage. File-mode is preferred in single-user environments due to the relative ease, including seamless engine management and database portability when using an available Base template (.odb).

    Note: Microsoft has also adopted this 'split database' terminology as they recognized the need to separate MS Access databases into the respective components for a variety of reasons, including data-durability.

    Note: The current templates are a boon for split-database portability. But to take portability to the next level, consider installing LibreOffice Portable & JavaPortable to a cloud folder or perhaps a fast portable drive. This allows you to run your database application entirely from the portable folder without installation on the host computer.

    Note: Cloud storage has come-of-age through synchronized folders such as we find with Dropbox, Google Drive, SkyDrive, Box, Wuala, SpiderOak and many more. Cloud storage does not effect performance since everything is mirrored on your local disk. These automated backup solutions feature 'file history/versioning.' This is an invaluable feature that's proves essential with databases and other critical documents since you can recover previous versions of each file in the event of file-corruption. And while all of these services encrypt your files in-transit and while stored on the cloud servers, only 'client-side encryption' can guarantee your data is secure from prying-eyes at the cloud server-farm or in response to government-level court orders. In response, we are now seeing the rapid introduction of cloud backup services featuring client-side encryption. This ensures that nobody can access the contents of your cloud-based files without access to your computer(s). Wuala and SpiderOak have implemented client-side encryption, while Boxcrypt is an add-on used to add this critical layer of encryption to most other popular cloud storage solutions.

    I should emphasize that a cloud-sync'd folder is not appropriate for simultaneous read/write database sharing among multiple users, although coordinated (serial time-based) access is possible. Otherwise, concurrent internet access to your database involves running the HSQL engine in 'web server mode' and the appropriate 'https' protocol in the HSQLDB startup string. This is different than the 'hsql' protocol used for LAN database access. For web access, you'll generally need admin access to the web-server in order to setup HSQLDB and the necessary Java support. Many web hosting services already offer Java support in the form of Tomcat, Jetty, GlassFish, etc. Once setup, running HSQLDB in web-server mode allows concurrent internet access from Base or other front-ends. HSQLDB 2.x supports connection pooling and MVCC (multi-version concurrency control) so in theory, HSQLDB running within an adequate JVM (Java heap set to 1GB RAM or more; -Xmx1024m) provides a robust online database capable of supporting hundreds of simultaneous read/write users -- seamlessly using Base (*Office) for remote access. Realistically, web-server database deployment is the domain of IT professionals using MySQL, PostgreSQL, Firebird, SQL Server, Oracle, etc. combined with browser-based front-ends hosting Rich Internet Apps.
'Class path' refers to the location of critical program components in Java environments. In database parlance the 'class path' refers to the location (folder path) of a JDBC driver. Drivers are necessary to connect with any RDBMS engine including the built-in HSQLDB engine, SQLite, or otherwise.

    Note: The user-community has traditionally relied on the 'global' class path settings in support of 'split HSQL databases.' This 'global' solution worked but it disabled support for legacy 'embedded databases' while risking inadvertent database upgrade and compatibility headaches -- effectively limiting the Base installation to a specific HSQLDB version while killing database portability. Recent user-community developments have eliminated use of the global class path setting, thereby eliminating the drawbacks. We now leverage 'session' class path settings accessible only through macros. The resulting macro-enhanced 'split HSQL database' templates or add-ins have eased split-database adoption, while largely supplanting the 'New database' wizard and even eliminating the need for a default database in Base. Simply download a macro-enhanced template and you'll instantly be using new 'split HSQL database' leveraging the latest HSQLDB engine in a fully-portable database package (folder).

    Given the advantages, we now strongly discourage manual/global class path setup, in favor of adopting macro-enhanced templates for all single-user HSQL database needs with Base.

    Note: Multi-user support is also possible through future templates, but for now it is necessary to revert to the manual global class path setup process on each computer when supporting multiple users accessing a 'split HSQL database' running in 'server-mode.' This requirement will be relatively easy to eliminate as template development continues.

'Data-source URL' refers to the database connection settings. These settings are stored within the Base (.odb) file. We can setup the data-source URL with a macro or manually in Base (Edit > Database > Properties). This somewhat-cryptic string of characters determines the database-access mode: file or server mode.

    'File mode' is seamless, allowing us to click directly on a properly-configured Base file (.odb) for startup.

    But 'server mode' requires separate HSQLDB startup for Table access. So it becomes a two-step process: (1) start HSQLDB in 'server mode' (2) and click mydb.server.odb to open the front-end in Base. This process is typically automated using a script/batch file. For example, the provided Windows script file ('START.vbs') automates all aspects of 'server mode' through a single mouse click. The associated Windows batch files (server.start.bat and server.stop.bat) will require some tweaking to reflect your actual folder-paths. This setup process is consolidated in a nice Windows system-tray app created by Greengiant224 found here.


Related links (must reads) (in particular the linked post)
Split HSQL database setup instructions:
Legacy database recovery and migration:
Base limitations as a database manager:
Slow database remedies:
Multi-user setup links:
User avatar
DACM
Volunteer
 
Posts: 1070
Joined: Tue Nov 03, 2009 7:24 am


Return to Base

Who is online

Users browsing this forum: No registered users and 3 guests