Overview

SymmetricDS Pro 3.14 release includes 15 features, 50 improvements, and 14 bug fixes.

Security Fixes

Issue Summary Severity

5351

Use PKCS12 for keystore by default

Medium

5376

Parameter to disable logging of application data from a batch error

Medium

Performance Fixes

Issue Summary Severity

5210

Routing reader may use multiple queries for a large number of gaps

Medium

What’s New

Snowflake Change Data Capture

Snowflake support was expanded to include change data capture using Snowflake’s table streams. New configuration items were added for converting relational data into JSON documents suitable for Snowflake. Table groups identify which tables to send together in the JSON document, so that a change in one table will bring related rows into the document. Table group hierarchies arrange the tables into a parent-child hierarchy using relational join information.

SingleStore Replication

SingleStore database was added as a bi-directional replication platform. Change data capture is based on log mining of SingleStore’s audit log.

MySQL Log-based Replication

MySQL support was expanded to include log mining in additional to trigger-based change capture. It is based on parsing statements from the binlog provided by MySQL 5.0 and newer.

Amazon S3 Integration

Amazon S3 can now be used as a target endpoint for receiving change data as comma-separated value (CSV) files. Amazon Web Services (AWS), such as Relational Database Services (RDS), Lambda, and Cloud Watch have integration with S3 to use data for loading databases, driving business logic, and monitoring data events.

SQL Server BCP Bulk Loader

The bulk loader for SQL-Server is now based on the BCP (bulk copy program) command line utility. The BCP automatically handles transfer of data files to a remote server, which supports both SQL Server and Azure SQL Server.

UI Improvements

The web console was enhanced to improve the user’s flow of connecting to databases, enabling change capture, and sending initial loads. Notification messages for data replication events can be customized with a screen that edits a notification template. Screens now support renaming the ID of an item being edited or using a “Save As Copy” button to copy it as a new item. Screens for table triggers and table routing were modified to use JDBC batch mode for faster save performance. New user settings include display mode, preferred date and time format, and timezone.

Operational Improvements

For large deployments, new endpoints can be scheduled to register automatically. Registration works across push links, in addition to pull links. Initial loads can now be monitored at the endpoint with the same level of detail that was previously only available at the source. Creation of tables now includes fractional second precision of time and timestamp columns. Routing query performance was improved when a large number of gaps is present. SQL-Server and Sybase trigger procedures were optimized for the case of a primary key changing, and a parameter of trigger.use.insert.delete.for.primary.key.changes was added to revert to the old behavior.

Issues

New Features

3.14.0 (Pro)
5163 - Add Test Database button and Database Performance dialog to Manage Nodes screen
5172 - Add push registration screens
5174 - Add TLS certificate expiration reminder
5176 - Add monitor types for license expiration, certificate expiration and license usage
5188 - Settings button with display options
5197 - Add warning and dialog for tables that lack a primary key
5202 - Add Manage Incoming Loads screen
5206 - Add SQL Server bcp bulk loader
5212 - Improve how node setup wizard handles load only nodes and bulk loaders
5218 - Add console events for viewing batches
5229 - Log miner for MySQL database
5295 - Relational to JSON writer with nesting using group of tables
5374 - Add more information and an option to take ownership when a node fails to claim exclusive ownership of a database

3.14.0
5178 - Push registration to nodes when group links indicate push
5180 - Schedule registration of a node for a window of time

Improvements

3.14.0 (Pro)
3805 - Allow Notification Email Template to be Modified Through User Interface
3946 - Update Load Data wizard to be like the Auto Create Table Triggers wizard
4795 - Improve router expression editor
5112 - Kafka Advanced Settings to include ability to specify kafkaclient properties
5142 - Delete router from Configure → Router screen is slow
5148 - Faster save performance for Configure → Table Triggers and Routing screens
5149 - Improve and add filters on Configure Table Triggers and Configure Table Routing screens
5161 - Labels on Health panel should link to the relevant screens
5182 - Improve grid on Manage Nodes screen
5183 - Improve node registration panel
5184 - Include tables in side-by-side documentation
5186 - Add Spotless plugin to SymmetricDS Pro
5187 - Add Overrides column to Configure Parameters grid
5189 - Improve TriggerRouterScreen in quick config wizard
5222 - Improve quick config wizard when opened from Configure Table Triggers screen
5226 - Improve how the router form handles Target Table, Target Catalog, and Target Schema fields
5228 - Make snapshot age more apparent on the Help Support screen
5241 - Log license validation error when license error occurs
5244 - Hide "Setup bulk loading" checkbox if not on registration server
5270 - Better descriptions of DB import options
5276 - Add option to Data Load Wizard to both create/alter tables and truncate tables
5281 - Snowflake Log Mining Functionality
5283 - Add tables from console-schema.xml to pro user guide
5284 - Check the hostname and the local_infile variable when setting up a MySQL node with bulk loading enabled
5288 - Make it possible to ignore a row from an outgoing batch that is part of a load
5292 - Make error messages more obvious on Manage Installed Triggers screen
5293 - Improve how metadata is fetched for engines that are on the same instance
5302 - Improve first-time setup experience
5307 - Add a warning to the Manage Outgoing Loads screen when a channel in use is disabled
5328 - Add filters to Auto Create Table Routing and Auto Create File Routing dialogs
5363 - Database test runs on controller node instead of node selected
5369 - Make failure message easier to read on failed engines screen

3.14.0
2972 - Registration order of nodes should not matter
4723 - Send column with Oracle DATE type to PostgreSQL
4724 - Send column with timestamp datatype from PostgreSQL to PostgreSQL
5138 - Build a cache manager to centralize the handling of data caches
5157 - Sybase and SQL Server update trigger should be made more efficient generating sym_data records when primary keys are changed
5198 - Clean up the consistent use of table constants for registration, trigger installation, extract, and export
5203 - Sync incoming load details to target node (table_reload_status, extract_request)
5210 - Routing reader may use multiple queries for a large number of gaps
5238 - Bulk loaders should report missing table
5265 - Update drivers, modules, and libraries
5269 - Variables of source catalog/schema names for use in target catalog/schema fields in routing
5278 - Alphabetize list of tables in user guide
5280 - Table DDL with timestamp and fractional second precision
5309 - Prepare for Gradle upgrade to version 8
5351 - Use PKCS12 for keystore by default
5353 - Add gpg code signing to jar files
5364 - Always execute shutdown hooks, even for embedded and war deployments
5376 - Parameter to disable logging of application data from a batch error

Bug Fixes

3.14.0 (Pro)
4665 - On configure screens, add "Save as Copy" button and allow editing of ID
5139 - Reconnecting node missing registration URL
5143 - On refresh of nodes panel, expand/collapse indicator is wrong
5237 - SQL Explorer queries on SQLite get IllegalStateException
5247 - SQL Server Date columns are being converted to DateTime

3.14.0
2261 - Schema from Postgres outputs TIMESTAMPTZ that is not compatible with other databases
3778 - Send table fails when sending H2 Timestamp to MySQL
3962 - Oracle’s TIMESTAMP with Time Zone doesn’t map to MariaDB
3963 - char(n) and nchar(n) where n>255 is valid in oracle but maps to invalid types in MariaDB
4298 - Sycing schemas from MSSQL with column type CHAR(400) to MySQL fails
5122 - Wrong conversion from mssql datetime(7) to mysql/mariadb
5239 - Conflict resolution doesn’t work if timestamp is in PK and different fractional second precision between databases
5243 - Change documentation on outgoing batch errors to set the status to 'IG' instead of 'OK' so target will get notified
5304 - Snapshot Fails to Find SingleStore Enum

Tables

The following changes were made to the definition of configuration and runtime tables. Table changes are applied to the database automatically using data definition language (DDL) during startup.

New Tables

Table Name Description

sym_table_group (Pro)

Group of tables to sync together as a dependent unit

sym_table_group_hier (Pro)

Relationships between tables for finding dependent data

New Columns

SYM_EXTRACT_REQUEST
Column Name Description

source_node_id

Unique identifier for the node that will be the source of the extract.

SYM_INCOMING_BATCH
Column Name Description

bulk_loader_flag

A flag that indicates that this batch did or did not use the bulk loader.

SYM_NODE_SECURITY
Column Name Description

registration_not_before

Allow registration beginning at this timestamp.

registration_not_after

Allow registration until this timestamp.

SYM_OUTGOING_BATCH
Column Name Description

bulk_loader_flag

A flag that indicates that this batch did or did not use the bulk loader.

SYM_TABLE_RELOAD_STATUS
Column Name Description

batch_bulk_load_count

The number of batches that were loaded with the bulk loader.

SYM_TRIGGER_HIST
Column Name Description

is_missing_pk

Indicates whether the source table is missing a primary key.

Modified Tables

SYM_EXTRACT_REQUEST
  • Added primary keys: source_node_id

  • Added index idx_er_ld_src_nd (load_id, source_node_id)

  • Added index idx_er_src_nd_st (source_node_id, status)

Parameters

The following changes were made to add new parameters, modify their default value, modify their description, or remove them from use.

New Parameters

console.auto.create.table.group.hiers.depth.limit (Pro)

The maximum depth that the Auto Create Table Group Hierarchies dialog will go to when searching for child tables. (Default: 5)

console.auto.create.table.group.hiers.table.limit (Pro)

The maximum number of child tables that the Auto Create Table Group Hierarchies dialog will search for. (Default: 25)

data.flush.jdbc.batch.size

Flush size for JDBC batch mode used by services to save configuration. (Default: 10000)

dataloader.ignore.sql.event.errors

Indicate that the data loader should ignore errors while loading a SQL event and the execution of the statement fails. (Default: false)

dataloader.log.sql.params.on.error

Indicate that the data loader should log SQL parameter values when a batch fails, which can be helpful for debugging. Since SQL parameters will contain application data, some sites may need to turn this off for policy compliance. (Default: true)

log.miner.mysql.directory (Pro)

Specifies the name and path for the directory that contains MySQL’s binary log files. When blank, SymmetricDS will use each log file’s path as it appears in MySQL’s binary log index file. (Default: )

log.miner.mysql.index (Pro)

Specifies the name and path for MySQL’s binary log index file. When blank, SymmetricDS will use MySQL’s log_bin_index system variable. (Default: )

mssql.auto.create.trigger.options (Pro)

When auto-creating table triggers, some options will be detected and applied for the user, such as excluding ROWVERSION columns. (Default: true)

mssql.bulk.load.bcp.cmd

Specifies the path to the SQL Server bulk copy program utility (bcp) executable. (Default: )

mssql.bulk.load.use.bcp

Enables the use of the SQL Server bulk copy program utility (bcp) as the bulk loader. (Default: false)

opensearch.load.aws.access.key

The AWS secret access key (aws_secret_access_key) to use as credentials for uploading to S3 (Default: )

opensearch.load.aws.secret.key

The AWS secret access key (aws_secret_access_key) to use as credentials for uploading to S3 (Default: )

purge.snapshot.file.retention.minutes (Pro)

This is the retention time for how long support snapshot files will be retained (Default: 14400)

registration.push.config.allowed

When group link is configured to push to clients, allow registration to work over push. Useful when client cannot reach server, so server pushes registration to client. (Default: true)

routing.data.reader.use.multiple.queries

Routing reader may run multiple queries for data, with each query including the maximum number of data gaps as specified by the routing.max.gaps.to.qualify.in.sql parameter. This method attempts to use the table’s index for quick results and avoid wasting time on filtering rows that were already routed (as used by the greater.than.query method). (Default: true)

single.store.audit.log.dir (Pro)

The path to the auditlogs dir for SingleStore (Default: /var/lib/memsql/{cluster-id}/auditlogs)

snapshot.max.batches

Max number of batches to write to statistics listing for support snapshot. (Default: 10000)

snapshot.max.files

Max number of files to write in directory listing for support snapshot. (Default: 50000)

snapshot.max.node.channels

Max number of nodes and channels for batch statistics, after which it will group by node only. (Default: 5000)

trigger.use.insert.delete.for.primary.key.changes

For Sybase and SQL Server, if a primary key is changed, this parameter determines if a delete followed by an insert is captured for the row change instead of an update. When set to true, an update will be created if no primary keys are changed or if only one row is changed when a primary key change occurs, otherwise a delete followed by an insert is created for each row updated when a primary key change occurs and more than one row is updated by the SQL statement. If set to false, it will always create an update. This parameter, when changed, requires a restart of the SymmetricDS instance, followed by a rebuild of the triggers. (Default: true)