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,database_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_version,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 3.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 V
Last edit: Anthony Vitale 2017-11-21
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The INFO statements above are normal logging messages and the WARNING is letting you know that its going to clear any node communication information while upgrading. What errors are you receiving? We have had a great deal of groups upgrade from 3.7 to the 3.8.
Thanks
Josh
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
When We Tried given the scenerio above (Migrate from 3.7 to 3.8) We had a cascade of 2 errors and then we rolled back and just continued with 3.7
The 1st Error was in the Postgres JDBC Driver that was being used,
In Version 3.9 of Postgres they updated the system tables and removed the column t1.tgisconstraint from what I forget was the system table, And During the Symmds upgrade the Driver that was being used by our installation was a old Postgres JDBC Driver which contained and Some Native Method that still referenced the column t1.tgisconstraint
As Such the 1st attempt was failing on the Postgresql Side with this error.
Anyway We Figured out the JDBC issue and replaced the driver with the Current Driver.
Then We We Started up again we Recieved Issues from the Sybase Side of the Replication (we Replicate Postgresql to Sybase)
There Where Repeated attempts at this, so I am not sure if I have all the Errors but below was the last errors I have
2017-09-21 16:12:49,071 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_nod
e_communication_; 12 columns], consider manually migrating the data back or dropping this temp table.
2017-09-21 16:12:49,083 INFO [gras-001][AseSymmetricDialect][symmetric-engine-startup-1] There are SymmetricDS tables that needed altered
2017-09-21 16:12:49,084 INFO [gras-001][AseSymmetricDialect][symmetric-engine-startup-1] DDL applied: SET quoted_identifier on
2017-09-21 16:12:49,084 INFO [gras-001][AseSymmetricDialect][symmetric-engine-startup-1] DDL applied: SET quoted_identifier on
2017-09-21 16:12:49,120 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:12:49,120 INFO [gras-001][AseSymmetricDialect][symmetric-engine-startup-1] DDL applied: SET quoted_identifier on
2017-09-21 16:12:49,351 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:12:49,356 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_typ
e,database_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,dat
abase_version,heartbeat_time,timezone_offset,batch_to_send_count,batch_in_error_count,created_at_node_id,deployment_type FROM DAOA001.dbo.sym_node
2017-09-21 16:12:49,357 INFO [gras-001][AseSymmetricDialect][symmetric-engine-startup-1] DDL applied: SET quoted_identifier on
2017-09-21 16:12:49,360 WARN [gras-001][AseSymmetricDialect][symmetric-engine-startup-1] DDL failed: 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:12:49,360 WARN [gras-001][JdbcSqlTemplate][symmetric-engine-startup-1] Cannot drop table 'sym_node' because it still has referential integrity constraints.
. Failed to execute: 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:12:49,365 ERROR [gras-001][AbstractSymmetricEngine][symmetric-engine-startup-1] An error occurred while starting SymmetricDS
org.jumpmind.db.sql.SqlException: Cannot drop table 'sym_node' because it still has referential integrity constraints.
at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:300)
at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:291)
at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:493)
at org.jumpmind.db.sql.JdbcSqlTemplate.update(JdbcSqlTemplate.java:401)
at org.jumpmind.db.sql.SqlScript.execute(SqlScript.java:107)
at org.jumpmind.symmetric.db.AbstractSymmetricDialect.createOrAlterTablesIfNecessary(AbstractSymmetricDialect.java:526)
at org.jumpmind.symmetric.db.AbstractSymmetricDialect.initTablesAndDatabaseObjects(AbstractSymmetricDialect.java:157)
at org.jumpmind.symmetric.AbstractSymmetricEngine.setupDatabase(AbstractSymmetricEngine.java:449)
at org.jumpmind.symmetric.AbstractSymmetricEngine.setup(AbstractSymmetricEngine.java:423)
at org.jumpmind.symmetric.AbstractSymmetricEngine.start(AbstractSymmetricEngine.java:580)
at org.jumpmind.symmetric.AbstractSymmetricEngine.start(AbstractSymmetricEngine.java:571)
at org.jumpmind.symmetric.ClientSymmetricEngine.start(ClientSymmetricEngine.java:244)
at org.jumpmind.symmetric.web.SymmetricEngineHolder$EngineStarter.run(SymmetricEngineHolder.java:530)
Caused by: java.sql.SQLException: Cannot drop table 'sym_node' because it still has referential integrity constraints.
at com.sybase.jdbc4.jdbc.SybConnection.getAllExceptions(Unknown Source)
at com.sybase.jdbc4.jdbc.SybStatement.handleSQLE(Unknown Source)
at com.sybase.jdbc4.jdbc.SybStatement.nextResult(Unknown Source)
at com.sybase.jdbc4.jdbc.SybStatement.nextResult(Unknown Source)
at com.sybase.jdbc4.jdbc.SybStatement.executeLoop(Unknown Source)
at com.sybase.jdbc4.jdbc.SybStatement.execute(Unknown Source)
at com.sybase.jdbc4.jdbc.SybStatement.execute(Unknown Source)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:416)
at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:401)
at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:491)
... 10 more
2017-09-21 16:12:49,490 INFO [gras-001][AbstractSymmetricEngine][symmetric-engine-startup-1] Stopping SymmetricDS externalId=001 version=3.8.29 database=Adaptive Server Enterprise
As You Can see from above,
1) The Table sym_node_communication was backed up to sym_node_communication_ and the message about maybe migrating manually.
I am not sure how this relates to Migration as I checked and there is data in my sym_node_communication on the sybase side, So if the Table is empty on startup then I assume that I should need to Migrate the Data from sym_node_communication_ to sym_node_communication prior to startup. But I am not sure.
2) The Table sym_node is being backed up to sym_node_ and then the Table sym_node is attempted to be dropped And Fails because It is the Parent to other Tables.
sym_node is the Parent to sym_node_security and sym_node_identity
And Sybase does not have a drop cascade delete (nor did I see one in the delete attempted).
And So the Nodes would not startup and Symmds Failed.
Anyway, Given that we had never done this before,
I ask if you or anyone has performed an Upgrade of Symmds and if so should it have or has it been as easy as listed in the Manual. As Below.
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.*
Or Are there Steps within the DB's that I should do like
Drop RI constraints on sym tables prior to upgrade in both Source and Target nodes.
Add some code to do the data migration from backed up sym tables to new tables
We Are holding off on trying the upgrade again until 3.9 as the main reason for the upgrade is to get the Jetty upgrade which we see identified in the 3.9 release which I hope is comming out soon.
Thanks
Anthony Vitale
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
We'll test upgrade on Sybase ASE and see if we can reproduce. It looks like we need a default value for the "queue" column to avoid it migrating the table. The 3.9 release should happen soon, maybe this week. I think your idea of dropping foreign key constraints on the SYM tables might work. It will add the FKs again during the upgrade.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Given my Read of the Comments, then I can Safely Assume that there is not a step I did not know about regarding the Upgrade,
The Software should handle itself with regards to the Tables which was my main concern (that I was missing a step)
Do you think then that the issue with the Queue column will be addressed before 3.9 is released
And I will Drop All FK Constraints within the sym tables within source and target db's prior to attempting to startup the upgraded software which should remedy the issue with drop table.
Thanks Again
Anthony Vitale
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Dear 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
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
And Symmds would not start and work
So We Restored the 3.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 V
Last edit: Anthony Vitale 2017-11-21
Anthony,
The INFO statements above are normal logging messages and the WARNING is letting you know that its going to clear any node communication information while upgrading. What errors are you receiving? We have had a great deal of groups upgrade from 3.7 to the 3.8.
Thanks
Josh
Hi Josh.
Thank you For Your Reply.
When We Tried given the scenerio above (Migrate from 3.7 to 3.8) We had a cascade of 2 errors and then we rolled back and just continued with 3.7
The 1st Error was in the Postgres JDBC Driver that was being used,
In Version 3.9 of Postgres they updated the system tables and removed the column t1.tgisconstraint from what I forget was the system table, And During the Symmds upgrade the Driver that was being used by our installation was a old Postgres JDBC Driver which contained and Some Native Method that still referenced the column t1.tgisconstraint
As Such the 1st attempt was failing on the Postgresql Side with this error.
Anyway We Figured out the JDBC issue and replaced the driver with the Current Driver.
Then We We Started up again we Recieved Issues from the Sybase Side of the Replication (we Replicate Postgresql to Sybase)
There Where Repeated attempts at this, so I am not sure if I have all the Errors but below was the last errors I have
2017-09-21 16:12:49,071 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_nod
e_communication_; 12 columns], consider manually migrating the data back or dropping this temp table.
2017-09-21 16:12:49,083 INFO [gras-001] [AseSymmetricDialect] [symmetric-engine-startup-1] There are SymmetricDS tables that needed altered
2017-09-21 16:12:49,084 INFO [gras-001] [AseSymmetricDialect] [symmetric-engine-startup-1] DDL applied: SET quoted_identifier on
2017-09-21 16:12:49,084 INFO [gras-001] [AseSymmetricDialect] [symmetric-engine-startup-1] DDL applied: SET quoted_identifier on
2017-09-21 16:12:49,120 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:12:49,120 INFO [gras-001] [AseSymmetricDialect] [symmetric-engine-startup-1] DDL applied: SET quoted_identifier on
2017-09-21 16:12:49,351 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:12:49,356 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_typ
e,database_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,dat
abase_version,heartbeat_time,timezone_offset,batch_to_send_count,batch_in_error_count,created_at_node_id,deployment_type FROM DAOA001.dbo.sym_node
2017-09-21 16:12:49,357 INFO [gras-001] [AseSymmetricDialect] [symmetric-engine-startup-1] DDL applied: SET quoted_identifier on
2017-09-21 16:12:49,360 WARN [gras-001] [AseSymmetricDialect] [symmetric-engine-startup-1] DDL failed: 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:12:49,360 WARN [gras-001] [JdbcSqlTemplate] [symmetric-engine-startup-1] Cannot drop table 'sym_node' because it still has referential integrity constraints.
. Failed to execute: 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:12:49,365 ERROR [gras-001] [AbstractSymmetricEngine] [symmetric-engine-startup-1] An error occurred while starting SymmetricDS
org.jumpmind.db.sql.SqlException: Cannot drop table 'sym_node' because it still has referential integrity constraints.
Caused by: java.sql.SQLException: Cannot drop table 'sym_node' because it still has referential integrity constraints.
2017-09-21 16:12:49,490 INFO [gras-001] [AbstractSymmetricEngine] [symmetric-engine-startup-1] Stopping SymmetricDS externalId=001 version=3.8.29 database=Adaptive Server Enterprise
As You Can see from above,
1) The Table sym_node_communication was backed up to sym_node_communication_ and the message about maybe migrating manually.
I am not sure how this relates to Migration as I checked and there is data in my sym_node_communication on the sybase side, So if the Table is empty on startup then I assume that I should need to Migrate the Data from sym_node_communication_ to sym_node_communication prior to startup. But I am not sure.
2) The Table sym_node is being backed up to sym_node_ and then the Table sym_node is attempted to be dropped And Fails because It is the Parent to other Tables.
sym_node is the Parent to sym_node_security and sym_node_identity
And Sybase does not have a drop cascade delete (nor did I see one in the delete attempted).
And So the Nodes would not startup and Symmds Failed.
Anyway, Given that we had never done this before,
I ask if you or anyone has performed an Upgrade of Symmds and if so should it have or has it been as easy as listed in the Manual. As Below.
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.*
Or Are there Steps within the DB's that I should do like
We Are holding off on trying the upgrade again until 3.9 as the main reason for the upgrade is to get the Jetty upgrade which we see identified in the 3.9 release which I hope is comming out soon.
Thanks
Anthony Vitale
We'll test upgrade on Sybase ASE and see if we can reproduce. It looks like we need a default value for the "queue" column to avoid it migrating the table. The 3.9 release should happen soon, maybe this week. I think your idea of dropping foreign key constraints on the SYM tables might work. It will add the FKs again during the upgrade.
Hi Eric, Thanks and I look foward to seeing 3.9
Given my Read of the Comments, then I can Safely Assume that there is not a step I did not know about regarding the Upgrade,
The Software should handle itself with regards to the Tables which was my main concern (that I was missing a step)
Do you think then that the issue with the Queue column will be addressed before 3.9 is released
And I will Drop All FK Constraints within the sym tables within source and target db's prior to attempting to startup the upgraded software which should remedy the issue with drop table.
Thanks Again
Anthony Vitale
Hi,
Do you know if there is any more info on the release date of version 3.9
Thanks
Anthony Vitale