hi,
i am able to do sync for changes(insert and update), if i do truncate data on any table(that table is in sync) , that deleted data is not getting sync with other nodes.
is it known issue ?
i am using sql server 2005 for my backend.
Appaji
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
SymmetricDS does not support the propagation of table truncates because SymmetricDS is based on triggers.
For SQL Server 2005 'TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.'
We could provide a special JMX API for table truncation that would propagate the action by inserting a SQL event into the data and data event table (which you could also do via the JMX console).
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Yes, it's a known limitation. We capture changes (insert, update, delete) by installing database triggers. On most systems there is no trigger for truncate. You can run a delete of all rows, and that would be captured by SymmetricDS.
Truncate is more efficient than delete because it bypasses a lot (if not all) of the transaction log overhead of the database. If you want to run truncate instead of delete, you could send out the truncate to other nodes using the sendSQL() method in JMX:
An easy way to access this is to run the Java "jconsole" command and connect to SymmetricDS on the localhost. Under the "MBeans" tab, you go to DefaultDomain->Node->Operations, where you'll be able to execute the sendSQL() method. For example:
It actually inserts rows into sym_data and sym_data_event to send the SQL to the node. You can do this yourself instead of using JMX if you like.
Chris and I have talked about providing a more general mechanism for running scripts that alter the database through SymmetricDS and are automatically sent out to all synchronizing nodes. Let us know if this is a requirement for your project.
Eric
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Linh, the same rules still basically apply. Truncates are not usually considered normal DML and are not captured by the triggers. Whatever code you are using to perform the truncate on the source database could insert a SQL "SEND" ('S') event into Symmetric's sym_data to perform the truncate on the other side.
Mark
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
hi,
i am able to do sync for changes(insert and update), if i do truncate data on any table(that table is in sync) , that deleted data is not getting sync with other nodes.
is it known issue ?
i am using sql server 2005 for my backend.
Appaji
thx for your information
SymmetricDS does not support the propagation of table truncates because SymmetricDS is based on triggers.
For SQL Server 2005 'TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.'
We could provide a special JMX API for table truncation that would propagate the action by inserting a SQL event into the data and data event table (which you could also do via the JMX console).
Yes, it's a known limitation. We capture changes (insert, update, delete) by installing database triggers. On most systems there is no trigger for truncate. You can run a delete of all rows, and that would be captured by SymmetricDS.
Truncate is more efficient than delete because it bypasses a lot (if not all) of the transaction log overhead of the database. If you want to run truncate instead of delete, you could send out the truncate to other nodes using the sendSQL() method in JMX:
sendSQL(String nodeId, String tableName, String sql)
An easy way to access this is to run the Java "jconsole" command and connect to SymmetricDS on the localhost. Under the "MBeans" tab, you go to DefaultDomain->Node->Operations, where you'll be able to execute the sendSQL() method. For example:
sendSQL("00001", "my_table", "truncate table my_table")
It actually inserts rows into sym_data and sym_data_event to send the SQL to the node. You can do this yourself instead of using JMX if you like.
Chris and I have talked about providing a more general mechanism for running scripts that alter the database through SymmetricDS and are automatically sent out to all synchronizing nodes. Let us know if this is a requirement for your project.
Eric
Hi Eric and All,
I want to sync truncate table from source to target automaticaly.
Now, in 2019, Can SymmetricDS do that?
Thank you so much!
Last edit: Linh 2019-01-25
Linh, the same rules still basically apply. Truncates are not usually considered normal DML and are not captured by the triggers. Whatever code you are using to perform the truncate on the source database could insert a SQL "SEND" ('S') event into Symmetric's sym_data to perform the truncate on the other side.
Mark