I am setting up file synchronisation between a server (let's call it omni) and a client (let's call it website) using SymmetricDS (version 3.12.3). I have been using SymmetricDS for a while now to do database synchronisation between the said client and server. However, SymmetricDS throws and error as soon as the FileSyncTrackerJob runs and detects that a file has been created/modified/deleted in the source directory on the server. It throws the following error:
The terminal log output:
[omni-000] - FileTriggerTracker - Tracked 2 files in 0 seconds. Found 2 files changed.
[] - DataGapRouteReader - org.jumpmind.db.sql.SqlException: The result set is closed
at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:306)
at org.jumpmind.db.platform.firebird.FirebirdJdbcSqlTemplate.translate(FirebirdJdbcSqlTemplate.java:62)
at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:295)
at org.jumpmind.db.sql.JdbcSqlReadCursor.next(JdbcSqlReadCursor.java:146)
at org.jumpmind.symmetric.route.DataGapRouteReader.fillPeekAheadQueue(DataGapRouteReader.java:478)
at org.jumpmind.symmetric.route.DataGapRouteReader.executeNonTransactional(DataGapRouteReader.java:250)
at org.jumpmind.symmetric.route.DataGapRouteReader.execute(DataGapRouteReader.java:155)
at org.jumpmind.symmetric.route.DataGapRouteReader.run(DataGapRouteReader.java:130)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630)
at java.base/java.lang.Thread.run(Thread.java:832)
Caused by: java.sql.SQLException: The result set is closed
at org.firebirdsql.jdbc.AbstractResultSet.checkOpen(AbstractResultSet.java:297)
at org.firebirdsql.jdbc.AbstractResultSet.checkCursorMove(AbstractResultSet.java:285)
at org.firebirdsql.jdbc.AbstractResultSet.next(AbstractResultSet.java:360)
at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)
at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)
at org.jumpmind.db.sql.JdbcSqlReadCursor.next(JdbcSqlReadCursor.java:132)
... 7 more
The log file output which is a bit more verbose:
2020-09-02 18:25:01,427 DEBUG [] [JdbcSqlTemplate] [omni-000-router-reader-2] (20ms.) select d.data_id, d.table_name, d.event_type, cast(d.row_data as varchar(20000)) as row_data, cast(d.pk_data as varchar(1000)) as pk_data, cast(d.old_data as varchar(20000)) as old_data, d.create_time, d.trigger_hist_id, d.channel_id, d.transaction_id, d.source_node_id, d.external_data, d.node_list, d.is_prerouted from sym_data d where d.channel_id='filesync' and ((d.data_id between 15398 and 50015397)) order by d.data_id asc
2020-09-02 18:25:01,429 ERROR [] [DataGapRouteReader] [omni-000-router-reader-2] StackTraceKey.init [SqlException:994639622] org.jumpmind.db.sql.SqlException: The result set is closed
at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:306)
at org.jumpmind.db.platform.firebird.FirebirdJdbcSqlTemplate.translate(FirebirdJdbcSqlTemplate.java:62)
at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:295)
at org.jumpmind.db.sql.JdbcSqlReadCursor.next(JdbcSqlReadCursor.java:146)
at org.jumpmind.symmetric.route.DataGapRouteReader.fillPeekAheadQueue(DataGapRouteReader.java:478)
at org.jumpmind.symmetric.route.DataGapRouteReader.executeNonTransactional(DataGapRouteReader.java:250)
at org.jumpmind.symmetric.route.DataGapRouteReader.execute(DataGapRouteReader.java:155)
at org.jumpmind.symmetric.route.DataGapRouteReader.run(DataGapRouteReader.java:130)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630)
at java.base/java.lang.Thread.run(Thread.java:832)
Caused by: java.sql.SQLException: The result set is closed
at org.firebirdsql.jdbc.AbstractResultSet.checkOpen(AbstractResultSet.java:297)
at org.firebirdsql.jdbc.AbstractResultSet.checkCursorMove(AbstractResultSet.java:285)
at org.firebirdsql.jdbc.AbstractResultSet.next(AbstractResultSet.java:360)
at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)
at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)
at org.jumpmind.db.sql.JdbcSqlReadCursor.next(JdbcSqlReadCursor.java:132)
... 7 more
Here is my sql for for setting up the appropriate sym tables for file synchronisation:
SYM_FILE_TRIGGER table
INSERT INTO SYM_FILE_TRIGGER
( TRIGGER_ID,
CHANNEL_ID,
RELOAD_CHANNEL_ID,
BASE_DIR,
RECURSE,
INCLUDES_FILES,
EXCLUDES_FILES,
SYNC_ON_CREATE,
SYNC_ON_MODIFIED,
SYNC_ON_DELETE,
SYNC_ON_CTL_FILE,
DELETE_AFTER_SYNC,
BEFORE_COPY_SCRIPT,
AFTER_COPY_SCRIPT,
CREATE_TIME,
LAST_UPDATE_BY,
LAST_UPDATE_TIME,
DESCRIPTION
)
VALUES
( 'file_trigger',
'filesync',
'filesync_reload',
'c:\website_images\',
1,
'*.jpg',
NULL,
1,
1,
1,
0,
0,
NULL,
NULL,
current_timestamp,
'Admin',
current_timestamp,
'file trigger'
);
sym_router table
insert into sym_router
( router_id,
source_node_group_id,
target_node_group_id,
router_type,
sync_on_update,
sync_on_insert,
sync_on_delete,
create_time,
last_update_by,
last_update_time,
description
)
VALUES
( 'filerouter_omniserver_2_website',
'omni',
'website',
'default',
1,
1,
1,
current_timestamp,
'Admin',
current_timestamp,
'file router'
);
SYM_FILE_TRIGGER_ROUTER table
INSERT INTO SYM_FILE_TRIGGER_ROUTER
( TRIGGER_ID,
ROUTER_ID,
ENABLED,
INITIAL_LOAD_ENABLED,
TARGET_BASE_DIR,
CONFLICT_STRATEGY,
CREATE_TIME,
LAST_UPDATE_BY,
LAST_UPDATE_TIME,
DESCRIPTION
)
VALUES
( 'file_trigger',
'filerouter_omniserver_2_website',
1,
1,
'\home\filesync',
'source_wins',
current_timestamp,
'Admin',
current_timestamp,
'file trigger router'
);
I have 2 sym node groups, with node_group_id's as omni and website, respectively.
The omni server is a Windows machine, while the client is a Linux (Ubuntu) machine. FYI, the database synchronisation is between a firebird database on the server and a mysql database on the client.
This looks like a possible bug with SymmetricDS on Firebird when using the "nontransactional" batch algorithm. The batch algorithm is set to "nontransactional" for the filesync channel, while it's set to "default" for change data capture channels. Try switching filesync to use the "default" batch algorithm, like this:
update sym_channel set batch_algorithm = 'default' where channel_id = 'filesync'