View Issue Details

IDProjectCategoryView StatusLast Update
0003308SymmetricDSBugpublic2022-09-09 20:47
Reporteranthony.vitale@sonymusic.com Assigned To 
Prioritynormal 
Status closedResolutionunable to reproduce 
Product Version3.7.20 
Summary0003308: Performing Full Upgrade of SymmetricDS lost configuration
DescriptionDear Sirs

We Currently Successfully Run SymmetricDS 3.7.2 between a PostgreSql Master to Sybase Target and it works very Well.

Recently my developers attempted to Upgrade to SymmetricDS 3.8.29 and it failed.

They Followed The Brief Instructions found in the Manual Under F.1 Full Upgrade.

However, I would like to understand if there are Any Steps in the Initial Startup of a Upgraded SymmetricDS.

They Did exactly the Steps 1 thru 7,

However when SymmetricDS Restarted (or was Started as we have normally Start it) We Ran into an issue because the version of Postgresql jdbc driver we used referred to a system level column that was removed in the Database System Table in Postgres v9, So we got all these Errors

[PostgreSqlDdlReader] [symmetric-engine-startup-0] Failed to read table: sym_node_identity. Error: ERROR: column t1.tgisconstraint does not exist

We figured out the issue, and upgraded the JDBC Driver and Re-Started again.

 I can See in the Logs that SymmetricDS attempts to alter the Sym_ Tables and Drop Some ..etc.

However I saw These Messages

2017-09-21 16:07:48,835 WARN [gras-001] [AseDdlBuilder] [symmetric-engine-startup-1] Data cannot be retained in table sym_node_communication because of the addition of the required column queue . The data is backed up in Table [name=sym_node_communication_; 12 columns], consider manually migrating the data back or dropping this temp table.
2017-09-21 16:07:48,849 INFO [gras-001] [AseSymmetricDialect] [symmetric-engine-startup-1] There are SymmetricDS tables that needed altered
2017-09-21 16:07:48,850 INFO [gras-001] [AseSymmetricDialect] [symmetric-engine-startup-1] DDL applied: SET quoted_identifier on
2017-09-21 16:07:48,850 INFO [gras-001] [AseSymmetricDialect] [symmetric-engine-startup-1] DDL applied: SET quoted_identifier on
2017-09-21 16:07:48,874 INFO [gras-001] [AseSymmetricDialect] [symmetric-engine-startup-1] DDL applied: IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE type = 'U' AND name = 'sym_node_')
BEGIN
    DROP TABLE DAOA001.dbo.sym_node_ END
2017-09-21 16:07:48,874 INFO [gras-001] [AseSymmetricDialect] [symmetric-engine-startup-1] DDL applied: SET quoted_identifier on
2017-09-21 16:07:49,137 INFO [gras-001] [AseSymmetricDialect] [symmetric-engine-startup-1] DDL applied: CREATE TABLE DAOA001.dbo.sym_node_(
    node_id VARCHAR(50) NOT NULL,
    node_group_id VARCHAR(50) NOT NULL,
    external_id VARCHAR(50) NOT NULL,
    sync_enabled SMALLINT DEFAULT 0 NULL,
    sync_url VARCHAR(255) NULL,
    schema_version VARCHAR(50) NULL,
    symmetric_version VARCHAR(50) NULL,
    database_type VARCHAR(50) NULL,
    database_version VARCHAR(50) NULL,
    heartbeat_time DATETIME NULL,
    timezone_offset VARCHAR(6) NULL,
    batch_to_send_count NUMERIC(12,0) DEFAULT 0 NULL,
    batch_in_error_count NUMERIC(12,0) DEFAULT 0 NULL,
    created_at_node_id VARCHAR(50) NULL,
    deployment_type VARCHAR(50) NULL,
    PRIMARY KEY (node_id)
)
2017-09-21 16:07:49,140 INFO [gras-001] [AseSymmetricDialect] [symmetric-engine-startup-1] DDL applied: INSERT INTO DAOA001.dbo.sym_node_ (node_id,node_group_id,external_id,sync_enabled,sync_url,schema_version,symmetric_version,database_type,data
base_version,heartbeat_time,timezone_offset,batch_to_send_count,batch_in_error_count,created_at_node_id,deployment_type) SELECT node_id,node_group_id,external_id,sync_enabled,sync_url,schema_version,symmetric_version,database_type,database_versio
n,heartbeat_time,timezone_offset,batch_to_send_count,batch_in_error_count,created_at_node_id,deployment_type FROM DAOA001.dbo.sym_node


And Symmds would not start and work

So We Restored the 8.7.2 Software, and I dropped all sym_ Tables in Source/Target and BAsically Restarted from Scratch the installation

We Want to Try this Again (with the Proper JDBC Driver)

However, I would like to know what the Appropriate steps for Restart should be in order to Upgrade the Software/Sym schema without having to lose my existing configuration.


Please let me know

Thank you
Anthony
Steps To ReproduceInstall any Inferior version of SymmetricDS and have it working operationally between 1 or more tables in a Source/Target Replication configuration

And then Perform the Steps listed within the Manual

When SymmetricDS is started after a major or minor software update, it will alter its database tables with any changes needed for that release.
F.1. Full Upgrade
For major releases and clean upgrades, copy old settings into a new installation using the following steps:

1.Stop the old SymmetricDS.
2.Backup the old SymmetricDS folder by renaming it.
3.Unzip the SymmetricDS distribution.
4.Copy old files from "engines" folder. (These files contain database connection information and engine settings.)
5.Copy old files from "conf" folder. (These files contain settings for ports, wrapper, and logging.) Check to see if any new changes need merged.
6.Copy old files from "security" folder. (These files contain keys for encryption.)
7.Restart SymmetricDS.

And Explain what needs to occur within the Database From a Restart Standpoint in order to maintain the Existing Configuration within the DAtabase (routers, channels, transform_tables, transform_columns, node_group_link, node_group, node, conflict) and have it Startup correctly in the Upgraded Version.

Thanks
Anthony V
Tagsdialect: postgresql, upgrade

Activities

elong

2019-05-14 13:09

developer   ~0001480

It is supposed to preserve your configuration in the database by altering tables. Did it drop tables and recreate them? It's supposed to migrate data to a temporary table with an underscore on the end, drop the old table, create the new table, and migrate the data into the new table. In issue #0001349 it was reported that tables got dropped during a failed upgrade. Maybe that happened to you because of the wrong JDBC driver version? Did you ever successfully upgrade?

Issue History

Date Modified Username Field Change
2017-11-09 19:44 anthony.vitale@sonymusic.com New Issue
2019-04-24 16:46 elong Tag Attached: dialect: postgresql
2019-04-24 16:46 elong Tag Attached: upgrade
2019-05-14 13:09 elong Note Added: 0001480
2019-05-14 13:10 elong Status new => feedback
2019-05-14 13:10 elong Category Task => Bug
2019-05-14 13:10 elong Summary Performing Full Upgrade of SymmetricDS Steps => Performing Full Upgrade of SymmetricDS lost configuration
2020-07-22 17:51 elong Status feedback => closed
2020-07-22 17:51 elong Resolution open => unable to reproduce