Appendix C. Database Notes

Each database management system has its own characteristics that results in feature coverage in SymmetricDS. The following table shows which features are available by database.

DatabaseVersions supportedTransaction IdentifierData CaptureConditional SyncUpdate Loop PreventionBLOB SyncCLOB Sync
Oracle10g and aboveYYYYYY
MySQL5.0.2 and aboveYYYYYY
MariaDB5.1 and aboveYYYYYY
PostgreSQL8.2.5 and aboveY (8.3 and above only)YYYYY
Greenplum8.2.15 and aboveNNNYNN
SQL Server2005 and aboveYYYYYY
SQL Server AzureTested on 11.00.2065YYYYYN
HSQLDB1.8YYYYYY
HSQLDB2.0NYYYYY
H21.xYYYYYY
Apache Derby10.3.2.1YYYYYY
IBM DB29.5NYYYYY
Firebird2.0YYYYYY
Informix11NYYYNN
Interbase9.0NYYYYY
SQLite3.xNYYYYY
Active Server Enterprise12.5YYYYYY
SQL Anywhere9YYYYYY
Redshift1.0NNNYNN

Table C.1. Support by Database


C.1. Oracle

SymmetricDS has bulk loading capability available for Oracle. SymmetricDS specifies data loader types on a channel by channel basis. To utilize Oracle Bulk loading versus straight JDBC insert, specify the Oracle Bulk Loader ("oracle_bulk") in the data_loader_type column of sym_channel.

While BLOBs are supported on Oracle, the LONG data type is not. LONG columns cannot be accessed from triggers.

Note that while Oracle supports multiple triggers of the same type to be defined, the order in which the triggers occur appears to be arbitrary.

The SymmetricDS user generally needs privileges for connecting and creating tables (including indexes), triggers, sequences, and procedures (including packages and functions). The following is an example of the needed grant statements:

GRANT CONNECT TO SYMMETRIC;
GRANT RESOURCE TO SYMMETRIC;
GRANT CREATE ANY TRIGGER TO SYMMETRIC;
GRANT EXECUTE ON UTL_RAW TO SYMMETRIC;

Partitioning the DATA table by channel can help insert, routing and extraction performance on concurrent, high throughput systems. TRIGGERs should be organized to put data that is expected to be inserted concurrently on separate CHANNELs. The following is an example of partitioning. Note that both the table and the index should be partitioned. The default value allows for more channels to be added without having to modify the partitions.

CREATE TABLE SYM_DATA
(
    data_id INTEGER NOT NULL ,
    table_name VARCHAR2(50) NOT NULL,
    event_type CHAR(1) NOT NULL,
    row_data CLOB,
    pk_data CLOB,
    old_data CLOB,
    trigger_hist_id INTEGER NOT NULL,
    channel_id VARCHAR2(20),
    transaction_id VARCHAR2(1000),
    source_node_id VARCHAR2(50),
    external_data VARCHAR2(50),
    create_time TIMESTAMP
) PARTITION BY LIST (channel_id) (
PARTITION P_CONFIG VALUES ('config'),
PARTITION P_CHANNEL_ONE VALUES ('channel_one'),
PARTITION P_CHANNEL_TWO VALUES ('channel_two'),
...
PARTITION P_CHANNEL_N VALUES ('channel_n'),
PARTITION P_DEFAULT VALUES (DEFAULT));
            

CREATE UNIQUE INDEX IDX_D_CHANNEL_ID ON SYM_DATA (DATA_ID, CHANNEL_ID)  LOCAL
(
 PARTITION I_CONFIG,
 PARTITION I_CHANNEL_ONE,
 PARTITION I_CHANNEL_TWO,
 ...
 PARTITION I_CHANNEL_N,
 PARTITION I_DEFAULT
);
            

Note also that, for Oracle, you can control the amount of precision used by the Oracle triggers with the parameter oracle.template.precision, which defaults to a precision of 30,10.

If the following Oracle error 'ORA-01489: result of string concatenation is too long' is encountered you might need to set use_capture_lobs to 1 on in the TRIGGER table and resync the triggers. The error can happen when the captured data in a row exceeds 4k and lob columns do not exist in the table. By enabling use_capture_lobs the concatanated varchar string is cast to a clob which allows a length of more than 4k.

C.2. MySQL

MySQL supports several storage engines for different table types. SymmetricDS requires a storage engine that handles transaction-safe tables. The recommended storage engine is InnoDB, which is included by default in MySQL 5.0 distributions. Either select the InnoDB engine during installation or modify your server configuration. To make InnoDB the default storage engine, modify your MySQL server configuration file (my.ini on Windows, my.cnf on Unix):

default-storage_engine = innodb

Alternatively, you can convert tables to the InnoDB storage engine with the following command:

alter table t engine = innodb;

On MySQL 5.0, the SymmetricDS user needs the SUPER privilege in order to create triggers.

grant super on *.* to symmetric;

On MySQL 5.1, the SymmetricDS user needs the TRIGGER and CREATE ROUTINE privileges in order to create triggers and functions.

grant trigger on *.* to symmetric;

grant create routine on *.* to symmetric;

MySQL allows '0000-00-00 00:00:00' to be entered as a value for datetime and timestamp columns. JDBC cannot deal with a date value with a year of 0. In order to work around this SymmetricDS can be configured to treat date and time columns as varchar columns for data capture and data load. To enable this feature set the db.treat.date.time.as.varchar.enabled property to true.

If you are using UTF-8 encoding in the database, you might consider using the characterEncoding parameter in the JDBC URL.

jdbc:mysql://hostname/databasename?tinyInt1isBit=false&characterEncoding=utf8

C.3. MariaDB

See MySQL notes. In addition, you will need to use a MySQL driver for this dialect.

C.4. PostgreSQL

SymmetricDS has bulk loading capability available for Postgres. SymmetricDS specifies data loader types on a channel by channel basis. To utilize Postgres Bulk loading versus straight JDBC insert, specify the Postgres Bulk Loader ("postgres_bulk") in the data_loader_type column of sym_channel.

Starting with PostgreSQL 8.3, SymmetricDS supports the transaction identifier. Binary Large Object (BLOB) replication is supported for both byte array (BYTEA) and object ID (OID) data types.

In order to function properly, SymmetricDS needs to use session variables. On PostgreSQL, session variables are enabled using a custom variable class. Add the following line to the postgresql.conf file of PostgreSQL server:

custom_variable_classes = 'symmetric'

This setting is required, and SymmetricDS will log an error and exit if it is not present.

Before database triggers can be created by in PostgreSQL, the plpgsql language handler must be installed on the database. The following statements should be run by the administrator on the database:

CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS
    '$libdir/plpgsql' LANGUAGE C;

CREATE FUNCTION plpgsql_validator(oid) RETURNS void AS
    '$libdir/plpgsql' LANGUAGE C;

CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
    HANDLER plpgsql_call_handler
    VALIDATOR plpgsql_validator;

If you want SymmetricDS to install into a schema other than public you should alter the database user to set the default schema.

        alter user {user name} set search_path to {schema name};

In addition, you will likely need the follow privelegdes as well:

        GRANT USAGE ON SCHEMA {schema name} TO {user name};
        GRANT CREATE ON SCHEMA {schema name} TO {user name};

C.5. Greenplum

Greenplum is a data warehouse based on PostgreSQL. It is supported as a target platform in SymmetricDS.

SymmetricDS has bulk loading capability available for Greenplum. SymmetricDS specifies data loader types on a channel by channel basis. To utilize Greenplum Bulk loading versus straight JDBC insert, specify the Postgres Bulk Loader ("postgres_bulk") in the data_loader_type column of sym_channel.

C.6. MS SQL Server

SQL Server was tested using the jTDS JDBC driver.

SQL Server allows the update of primary key fields via the SQL update statement. If your application allows updating of the primary key field(s) for a table, and you want those updates synchronized, you will need to set the "Handle Key Updates" field on the trigger record for that specific table. The default for Handle Key Updates is false.

C.7. HSQLDB

HSQLDB was implemented with the intention that the database be run embedded in the same JVM process as SymmetricDS. Instead of dynamically generating static SQL-based triggers like the other databases, HSQLDB triggers are Java classes that re-use existing SymmetricDS services to read the configuration and insert data events accordingly.

The transaction identifier support is based on SQL events that happen in a 'window' of time. The trigger(s) track when the last trigger fired. If a trigger fired within X milliseconds of the previous firing, then the current event gets the same transaction identifier as the last. If the time window has passed, then a new transaction identifier is generated.

C.8. H2

The H2 database allows only Java-based triggers. Therefore the H2 dialect requires that the SymmetricDS jar file be in the database's classpath.

C.9. Apache Derby

The Derby database can be run as an embedded database that is accessed by an application or a standalone server that can be accessed from the network. This dialect implementation creates database triggers that make method calls into Java classes. This means that the supporting JAR files need to be in the classpath when running Derby as a standalone database, which includes symmetric-ds.jar and commons-lang.jar.

C.10. IBM DB2

The DB2 Dialect uses global variables to enable and disable node and trigger synchronization. These variables are created automatically during the first startup. The DB2 JDBC driver should be placed in the "lib" folder.

Currently, the DB2 Dialect for SymmetricDS does not provide support for transactional synchronization. Large objects (LOB) are supported, but are limited to 16,336 bytes in size. The current features in the DB2 Dialect have been tested using DB2 9.5 on Linux and Windows operating systems.

There is currently a bug with the retrieval of auto increment columns with the DB2 9.5 JDBC drivers that causes some of the SymmetricDS configuration tables to be rebuilt when auto.config.database=true. The DB2 9.7 JDBC drivers seem to have fixed the issue. They may be used with the 9.5 database.

A system temporary tablespace with too small of a page size may cause the following trigger build errors:

SQL1424N Too many references to transition variables and transition table
columns or the row length for these references is too long. Reason
code="2". LINE NUMBER=1. SQLSTATE=54040
            

Simply create a system temporary tablespace that has a bigger page size. A page size of 8k will probably suffice.

C.11. Firebird

The Firebird Dialect requires the installation of a User Defined Function (UDF) library in order to provide functionality needed by the database triggers. SymmetricDS includes the required UDF library, called SYM_UDF, in both source form (as a C program) and as pre-compiled libraries for both Windows and Linux. The SYM_UDF library is copied into the UDF folder within the Firebird installation directory.

For Linux users:

cp databases/firebird/sym_udf.so /opt/firebird/UDF

For Windows users:

copy databases\firebird\sym_udf.dll C:\Program Files\Firebird\Firebird_2_0\UDF

The following limitations currently exist for this dialect:

  • The outgoing batch does not honor the channel size, and all outstanding data events are included in a batch.

  • Syncing of Binary Large Object (BLOB) is limited to 16K bytes per column.

  • Syncing of character data is limited to 32K bytes per column.

C.12. Informix

The Informix Dialect was tested against Informix Dynamic Server 11.50, but older versions may also work. You need to download the Informix JDBC Driver (from the IBM Download Site) and put the ifxjdbc.jar and ifxlang.jar files in the SymmetricDS lib folder.

Make sure your database has logging enabled, which enables transaction support. Enable logging when creating the database, like this:

CREATE DATABASE MYDB WITH LOG;
            

Or enable logging on an existing database, like this:

ondblog mydb unbuf log
ontape -s -L 0
            

The following features are not yet implemented:

  • Syncing of Binary and Character Large Objects (LOB) is disabled.

  • There is no transaction ID recorded on data captured, so it is possible for data to be committed within different transactions on the target database. If transaction synchronization is required, either specify a custom transaction ID or configure the synchronization so data is always sent in a single batch. A custom transaction ID can be specified with the tx_id_expression on TRIGGER. The batch size is controlled with the max_batch_size on CHANNEL. The pull and push jobs have runtime properties to control their interval.

C.13. Interbase

The Interbase Dialect requires the installation of a User Defined Function (UDF) library in order to provide functionality needed by the database triggers. SymmetricDS includes the required UDF library, called SYM_UDF, in both source form (as a C program) and as pre-compiled libraries for both Windows and Linux. The SYM_UDF library is copied into the UDF folder within the Interbase installation directory.

For Linux users:

cp databases/interbase/sym_udf.so /opt/interbase/UDF

For Windows users:

copy databases\interbase\sym_udf.dll C:\CodeGear\InterBase\UDF

The Interbase dialect currently has the following limitations:

  • Data capture is limited to 4 KB per row, including large objects (LOB).

  • There is no transaction ID recorded on data captured. Either specify a tx_id_expression on the TRIGGER table, or set a max_batch_size on the CHANNEL table that will accommodate your transactional data.

C.14. SQLite

For SQLite, the implementation of sync-on-incoming back and the population of a source node if in the sym data rows relies on use of a context table (by default, called sym_context) to hold a boolean and node id in place of the more common methods of using temp tables (which are inaccessible from triggers) or functions (which are not available). The context table assumes there's a single thread updating the database at any onetime. If that is not the case in the future, the current implementation of sync on incoming batch will be unreliable.

Nodes using SQLite should have the jobs.synchronized.enable parameter set to true. This parameter causes the jobs and push/pull threads to all run in a synchronized fashion, which is needed in the case of SQLite.

The SQLite dialect has the following limitations:

  • There is no transaction ID recorded on data captured. Either specify a tx_id_expression on the TRIGGER table, or set a max_batch_size on the CHANNEL table that will accommodate your transactional data.

  • Due to the single threaded access to SQLite, the following parameter should be set to true: jobs.synchronized.enable.

C.15. Sybase Active Server Enterprise

Active Server Enterprise (ASE) was tested using the jConnect JDBC driver. The jConnect JDBC driver should be placed in the "lib" folder.

Columns of type DATETIME are accurate to 1/300th of a second, which means that the last digit of the milliseconds portion will end with 0, 3, or 6. An incoming DATETIME synced from another database will also have its millisconds rounded to one of these digits (0 and 1 become 0; 2, 3, and 4 become 3; 5, 6, 7, and 8 become 6; 9 becomes 10). If DATETIME is used as the primary key or as one of the columns to detect a conflict, then conflict resolution could fail unless the milliseconds are rounded in the same fashion on the source system.

On ASE, each new trigger in a table for the same operation (insert, update, or delete) overwrites the previous one. No warning message displays before the overwrite occurs. When SymmetricDS is installed and configured to synchronize a table, it will install triggers that could overwrite already existing triggers on the database. New triggers created after SymmetricDS is installed will overwrite the SymmetricDS triggers. Custom trigger text can be added to the SymmetricDS triggers by modifying CUSTOM_ON_INSERT_TEXT, CUSTOM_ON_UPDATE_TEXT, and CUSTOM_ON_DELETE_TEXT on the TRIGGER table.

C.16. Sybase SQL Anywhere

SQL Anywhere was tested using the jConnect JDBC driver. The jConnect JDBC driver should be placed in the "lib" folder.

C.17. Redshift

Redshift is a managed data warehouse in the cloud from Amazon. Version 1.0 of Redshift is based on PostgreSQL 8.0, with some features modified or removed. SymmetricDS supports Redshift as a target platform where data can be loaded, but it does not support data capture. However, the initial load and reload functions are implemented, so it is possible to query rows from Redshift tables and send them to another database.

While Redshift started with PostgreSQL 8.0, there are some important differences from PostgreSQL. Redshift does not support constraints, indexes, functions, triggers, or sequences. Primary keys, foreign keys, and unique indexes can be defined on tables, but they are informational metadata that are not enforced by the system. When using the default data loader with SymmetricDS, it will enforce primary keys, either defined in the database or with the sync keys features, by checking if a row exists before attempting an insert. However, the bulk loader does not perform this check. The data types supported are smallint, integer, bigint, decimal, real, double precision, boolean, char, varchar, date, and timestamp.

A data loader named "redshift_bulk" is a bulk loader that can be set for a channel to improve loading performance. Instead of sending individual SQL statements to the database, it creates a comma separated value (CSV) file, uploads the object to Amazon S3, and uses the COPY statement to load it. The COPY command appends the new data to any existing rows in the table. If the target table has any IDENTITY columns, the EXPLICIT_IDS option is enabled to override the auto-generated values and load the incoming values. The following parameters (see Appendix B) can be set for bulk loader:

  • redshift.bulk.load.max.rows.before.flush - When the max rows is reached, the flat file is sent to S3 and loaded into the database. The default is 100,000 rows.
  • redshift.bulk.load.max.bytes.before.flush - When the max bytes is reached, the flat file is sent to S3 and loaded into the database. The default is 1,000,000,000 bytes.
  • redshift.bulk.load.s3.bucket - The S3 bucket name where files are uploaded. This bucket should be created from the AWS console ahead of time.
  • redshift.bulk.load.s3.access.key - The AWS access key ID to use as credentials for uploading to S3 and loading from S3.
  • redshift.bulk.load.s3.secret.key - The AWS secret key to use as credentials for uploading to S3 and loading from S3.

To clean and organize tables after bulk changes, it is recommended to run a "vacuum" against individual tables or the entire database so that consistent query performance is maintained. Deletes and updates mark rows for delete that are not automatically reclaimed. New rows are stored in a separate unsorted region, forcing queries to sort on demand. Consider running a "vacuum" periodically during a maintenance window when there is minimal query activity that will be affected. If large batches are continually loaded from SymmetricDS, the "vacuum" command can be run after committing a batch by using a load filter (see Section 3.9) for the "batch commit" event, like this:

for (String tablename : context.getParsedTables().keySet()) {
    engine.getSqlTemplate().update("vacuum " + tablename, new Object[] { } );
}