ESF integrates a Java SQL database named H2. The main features of this SQL Database are:

• Very fast, open source, JDBC API
• Embedded and server modes; in-memory databases
• Browser-based Console application
• Small footprint

Supported Features

ESF supports the following H2 database features:

• Persistence modes: The H2 implementation currently supports in-memory and file-based database instances. See the Persistence Modes section for more details.

• Multiple database instances: It is possible to create and configure multiple database instances from the ESF Administration UI, these instances can be selectively consumed by applications. A default database instance is created automatically.

• TCP Server: The current implementation allows external processes to access the database instances managed by ESF using TCP. This enables the integration of external applications that can share data with ESF components using the database.

• Web-based console: It is possible to start the H2 Web console directly from the ESF Administration UI. The console can be used to inspect the database contents and perform arbitrary queries for debug purposes.

• Basic credential management: The current implementation allows to change the password of the admin DB user from the ESF Administration UI. This allows the access restriction to the existing database instances.

By default, the DataService in ESF uses the H2 database to persist the messages.

Limitations

• Private in-memory instances: Only named in-memory instances are supported (e.g. jdbc:h2:mem:<dbname>, where <dbname> is not the empty string), private instances represented by the jdbc:h2:mem: URL are currently not supported.

• Remote connections: The current implementation only supports embedded database instances. Connecting to remote instances using the jdbc:h2:tcp:* and jdbc:h2:ssl:* connector URLs is not supported.

🚧

Changes to database related components

The new DbWireRecordFilter and DbWireRecordStore Wire components have been added. These components provide the same functionalities offered by the old H2DbWireRecordFilter and H2DbWireRecordStore components, but they can be used for connectiong to a generic relational database (i.e. H2DB, MySQL or MariaDB). The legacy components will continue to be available in order to keep backward compatibility, but will be deprecated since ESF 7.2.0 and should not be used for new installations.

Usage

Creating a new H2 database instance

To create a new H2 database instance, use the following procedure:

1. Open the ESF Administrative UI and press the + button in the side menu, under the Services section. A pop-up dialog should appear.
2. Select org.eclipse.kura.core.db.H2DbService from the Factory drop-down list, enter an arbitrary name for the new instance and click Apply.

3. An entry for the newly created instance should appear in the side menu under Services, click on it to review its configuration:

Configuration Parameters

The H2DbService provides the following configuration parameters:

• Connector URL: JDBC connector URL of the database instance. Passing the USER and PASSWORD parameters in the connector URL is not supported, these paramters will be ignored if present. Please use the db.user and db.password fields to provide the credentials.

❗️

If the database is created in persisted mode, please make sure that the Linux user running ESF (esfd) has the permissions required to create the database file.
If the permissions are not ok, ESF may be able to create the database (by default it runs with the CAP_DAC_OVERRIDE capability) but it may not be able to perform the periodic defragmentation process, this may cause the database file size to grow especially if the write rate is high.

Executing the following commands as root can be useful to detect potential issues, replace database_parent_directory with the parent directory of the database file.

export TARGET="$(readlink -f database_parent_directory)"
sudo -u esfd sh -c "touch '${TARGET}/.testfile' && rm '${TARGET}/.testfile'"

If command fails it may be necessary to change the database directory or adjust the permissions.

• User: Specifies the user for the database connection. Furthermore

• Password: Specifies the password. The default password is the empty string.

• Checkpoint interval (seconds): H2DbService instances support running periodic checkpoints to ensure data consistency. This parameter specifies the interval in seconds between two successive checkpoints. This setting has no effect for in-memory database instances.

• Defrag interval (minutes): H2DbService instances support running periodic defragmentation (compaction). This parameter specifies the interval in minutes between two successive checkpoints, set to zero to disable. This setting has no effect for in-memory database instances. Existing database connections will be closed during the defragmentation process and need to be reopened by the applications.

• Connection pool max size: The H2DbService manages connections using a connection pool. This parameter defines the maximum number of connections for the pool

It is not possible to create different DB instances that manage the same DB URL. When creating a new instance please make sure that the URL specified in the field db.connector.url is not managed by another instance.

Selecting a Database Instance for Existing ESF Components

A database instance is identified by its Kura service PID. The PID for the default instance is org.eclipse.kura.db.H2DbService while the PID for instances created using the Web UI is the string entered in the Name field at step 2 of the previous section.

The built-in ESF components that use database functionalities allow to specify which instance to use in their configuration. These components are the DataService component of the cloud stack, the DbWireRecordFilter and DbWireRecordStore wire components.
The configuration of each component contains a property that allows to specify the service PID of the desired instance.

Enabling the TCP Server

❗️

Development-only feature

This feature is intended to be used only for debugging/development purposes. The server created by H2 is not running on a secure protocol. Only enable the server for a limited time and make sure to properly secure the firewall ports on which it is running.

The TCP server can be used by creating a H2DbServer instance:

1. Open the ESF Web UI and press the + button in the side menu, under the Services section. A pop-up dialog should appear.
2. Select org.eclipse.kura.core.db.H2DbServer from the Factory drop-down​ list, enter an arbitrary name for the new instance and click Apply.

3. Clicking on the name of the new server instance on the left side of the Web UI​. The configuration of the server component will appear:

4. Set the db.server.type field to TCP
5. Review the server options under db.server.commandline, check the official documentation for more information about the available options.
6. Set the db.server.enabled to true.

The server, with the default configuration, will be listening on port 9123.

🚧

Make sure to review the firewall configuration in order to ensure that the server is reachable from an external process.

Enabling the Web Console

❗️

Development-only feature

This feature is intended to be used only for debugging/development purposes. The server created by H2 is not running on a secure protocol. Only enable the server for a limited time and make sure to properly secure the firewall ports on which it is running.

In order to enable the H2 Web console, proceed as follows:

1. Create a new H2DbServer instance.
2. Set the db.server.type field to WEB
3. Enter appropriate parameters for the Web server in the db.server.commandline field. An example of valid settings can be -webPort 9123 -webAllowOthers -ifExists -webExternalNames <device-ip>.
4. Set the db.server.enabled to true.

The server is now listening on the specified port.

🚧

Make sure to review the firewall configuration in order to ensure that the server is reachable from an external process.

Use a browser to access the console. Open the http://: URL, where is the IP address of the ESF device and is the port specified at step 3.

Enter the DB URL as specified in the Kura configuration in the JDBC URL field and the credentials. Click on Connect, you should be able to access the console.

Change the Database Password

To change the database password the System Administrator needs to:

1. Open the configuration of the desired database instance in the ESF Web UI.
2. Enter the new password in the db.password field.
3. Click Apply.

🚧

If the H2DbServer instance fails to open a database, it will delete and recreate all database files. This behavior​ is aimed at preventing potential issues caused by incorrect credentials in the configuration snapshots. It is highly recommended to perform a backup of an existing database before trying to open it using a H2DbService instance and before changing the password.

Persistence Modes

The H2 database supports several persistence modes.

In Memory

🚧

Abnormal in-memory mode memory usage with H2 2.1.214

Due a excessive memory usage issue found in H2 database 2.1.214, the users with ESF 7.2.0 and 7.2.1 which want to use the in-memory mode, should adopt the following solution:
Use a file-based database using a file path mounted on an in-memory filesystem.

Examples:

• jdbc:h2:file:/tmp/mydb

This solution doesn't affect the eMMC lifecycle because the /tmp is mounted in the memory.
Eurotech strongly advises such users to upgrade to ESF 7.2.2 to leverage the latest bugs and security fixes.

An in-memory database instance can be created using the following URL structure: jdbc:h2:mem:, where is a non-empty string that represents the database name. This configuration is suggested for database instances that are frequently updated. Examples:

• jdbc:h2:mem:kuradb
• jdbc:h2:mem:mydb

The default database instance is in-memory by default and uses the jdbc:h2:mem:kuradb URL.

Persistent

A persistent database instance can be created using the jdbc:h2:file:, where is a non-empty string that represents the database path.

If no URL parameters are supplied the database will enable the transaction log by default. The transaction log is used to restore the database to a consistent state after a crash or power failure. This provides good protection against data losses but causes a lot of writes to the storage device, reducing both performance and the lifetime of flash-based storage devices.

Examples:

• jdbc:h2:file:/opt/db/mydb

Make sure to use absolute paths in the DB URL since H2 does not support DB paths relative to the working directory.

Filesystem Tuning

The H2 file-backed database might become corrupted in case of power failure.
The following mount options can improve database reliability, reducing the database corruption probability at the expense of reduced performance and lifetime of the storage device:

• data=journal: From EXT4(5) Linux MAN Page:

  All data is committed into the journal prior to being written into the main filesystem.

• sync: From mount(8) Linux MAN Page:

  All I/O to the filesystem should be done synchronously. In case of media with limited number of write cycles (e.g. some flash drives) "sync" may cause life-cycle shortening.

After some long running tests, it appears that the data=journal option alone is enough to reduce corruption probability (no corruption observed during the test).

The currently recommended mount in /etc/fstab is as follows for the data partition of the ReliaGATE 10-12, if Docker is not installed:

/dev/mmcblk0p7 /mnt/data auto defaults,data=journal,x-systemd.device-timeout=30,x-systemd.mount-timeout=30,nofail 0 2

If Docker is installed /etc/fstab the data partition is already mounted as follows:

/dev/mmcblk0p7 /var/lib/docker auto defaults 0 0

To use this partition also for the H2 database, the line should be changed to:

/dev/mmcblk0p7 /var/lib/docker auto defaults,data=journal,x-systemd.device-timeout=30,x-systemd.mount-timeout=30,nofail 0 2

Please note that the change would be reverted if the Docker package is re-installed or updated.

Alternatively, the configuration partition can be dedicated to the H2 database:

/dev/mmcblk0p5 /mnt/data auto defaults,data=journal,x-systemd.device-timeout=30,x-systemd.mount-timeout=30,nofail 0 2

The BoltGATE 20-25 can leverage an SSD (factory option) for the H2 database:

UUID=<partition UUID> /mnt/data auto defaults,data=journal,x-systemd.device-timeout=30,x-systemd.mount-timeout=30,nofail 0 2

where the actual <partition UUID> of the SSD can be found executing the command blkid.

In order to reduce disk writes to a minimum noatime can be added to the mount options:

UUID=<partition UUID> /mnt/data auto defaults,data=journal,noatime,x-systemd.device-timeout=30,x-systemd.mount-timeout=30,nofail 0 2

Upgrade from ESF 7.0.x

ESF 7.0.x uses H2 v.1.4.199, whereas ESF 7.1.x uses H2 v.2.1.210. When upgrading, persisted databases are migrated to the new version. Out of the box, ESF 7.1.x is adapted to be compatible with the new specifications of H2 v2. However, there are some important changes in H2 v2.1.210 that might break custom-made bundles and need to be considered before upgrading:

• The maximum length of CHARACTER and CHARACTER VARYING data types is 1048576 characters. For larger values use CHARACTER LARGE OBJECT
• BINARY and BINARY VARYING are now different data types. BINARY means fixed-length data type and its default length is 1. The maximum length of binary strings is 1048576 bytes. For larger values use BINARY LARGE OBJECT
• NUMERIC / DECIMAL / DEC without parameters now have scale 0. For a variable-scale data type see DECFLOAT. Negative scale isn't allowed for these data types any more. The maximum precision is now 100,000
• ENUM values now have 1-based ordinal numbers
• Arrays are now typed. Arrays with mixed types of elements aren't supported. In some cases they can be replaced with a new ROW data type
• All non-standard data types, with exception for TINYINT, JAVA_OBJECT, ENUM, GEOMETRY, JSON, and UUID are deprecated
• Various legacy vendor-specific declarations and expressions are deprecated and may not work at all depending on compatibility mode
• Identity columns should be normally declared with GENERATED BY DEFAULT AS IDENTITY or GENERATED ALWAYS AS IDENTITY clauses, options may also be specified. GENERATED ALWAYS AS IDENTITY columns cannot be assigned to a user-provided value unless OVERRIDING SYSTEM VALUE is specified
• NULL cannot be specified as a value for IDENTITY column to force identity generation (with exception for some compatibility modes). Use DEFAULT or simply exclude this column from insert column list
• IDENTITY() and SCOPE_IDENTITY() aren't available in Regular mode. If you need to get a generated value, you need to use data change delta tables or Statement.getGeneratedKeys()
• Undocumented Oracle-style .NEXTVAL and .CURRVAL expressions are restricted to Oracle compatibility mode. Other functions are deprecated for Regular mode. Use sequence value expression instead
• INFORMATION_SCHEMA in H2 is now compliant with the SQL Standard and other database systems, but it isn't compliant with previous versions of H2. You may need to update your queries
• There are a lot more SQL keywords now. Many SQL statements feature far better support of SQL-Standard behaviour. There is a NON_KEYWORDS setting that can be used as a temporary workaround if your application uses them as unquoted identifiers
• Numeric and boolean values aren't comparable. It means you need to use TRUE, FALSE, or UNKNOWN as boolean literals. 1 and 0 don't work any more (with exception for some compatibility modes)
• Oracle-style units were never supported officially without being in Oracle compatibility mode, although some worked before. For example, the length of the VARCHAR datatype cannot be more specified using CHAR but CHARACTERS or OCTETS. CHAR and BYTE need to be used in Oracle compatibility mode
• IDENTITY syntax changed when type is specified: if the type for IDENTITY is specified, then the clause needs to be expanded as INTEGER GENERATED ALWAYS AS IDENTITY. Using just INTEGER IDENTITY is no more working
• LOG connection setting removed: PageStore was removed from H2 so the "LOG=0" setting at the end of the URL (like "jdbc:h2:file:/tmp/test;LOG=0") is no longer available

We advise to look for the breaking changes in the official H2 migration to v2 documentation.