View Issue Details

IDProjectCategoryView StatusLast Update
0003636SymmetricDSBugpublic2018-08-03 10:12
ReportermmichalekAssigned Tommichalek 
Prioritynormal 
Status closedResolutionfixed 
Product Version3.9.0 
Target Version3.9.10Fixed in Version3.9.10 
Summary0003636: 3rd party triggers can affect SymmetricDS data load
DescriptionIn some edge cases, 3rd party triggers can create result sets and alter how SymmetricDS interprets the execution results of an insert or update.

Previously, SymDS would basically call:
prepareStatement.execute();
prepareStatement.getUpdateCount();

This works for a simple update or insert. But if a 3rd party trigger creates a result set, that result set is not consumed by this code. This leads to 2 problems:
1) Errors raised by the trigger are not thrown by the driver (until the result set is consumed using preparedStatement.getMoreResults())
2) SymmetricDS may report the wrong update count (-1) leading to conflicts and data not getting loaded.

This is known to effect SQL Server and Sybase, but may affect other platforms as well. The root problem is that JdbcSqlTransaction was not calling preparedStatement.execute() in line with the full spec for this method:
"The execute method returns a boolean to indicate the form of the first result. **You must call either the method getResultSet or getUpdateCount to retrieve the result; **you must call getMoreResults to move to any subsequent result(s)" (emphases added)
https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#execute()

There was some concern about adding additional calls to getUpdateCount and getMoreResults for each and every data load for SymmetricDS. In practical testing, no difference in performance was noted. But it does appear that the Microsoft SQL Server driver at least will do an additional read from teh TDS stream when calling getMoreResults.

Based on these findings, we settled on a compromise:
1) By default, we will call executeUpdate() which seems to give the driver and datbase the best chance of optimizing the call. Plus, this method will throw an error if there are unexpectly one or more ResultSets available.
2) For cases where there are unexpected result sets available, there is a new parameter available: allow.updates.with.results. This defaults to false, but setting to true will instruct SymDS to call PreparedStatement.execute() and scroll through the results and updateCounts which should both properly raise trigger errors and report accurate update counts.
Additional InformationThis is a sample trigger that causes unexpected behavior on Sybase. Both the presence of the SELECT and also the ROLLBACK TRAN were not handled properly.


            CREATE TRIGGER sym_parameter_u_t
                 ON sym_parameter
                 FOR UPDATE
                 AS
                select name from sysobjects where 1 = 2
                   
                 IF EXISTS(SELECT 1 FROM inserted WHERE param_key = 'Reject')
                 BEGIN
                     ROLLBACK TRAN
                     RAISERROR 80000 'test_target_u_t: Update rejected by trigger'
                 END
                   
                 RETURN;

Activities

There are no notes attached to this issue.

Related Changesets

SymmetricDS: 3.9 a2b09398

2018-07-18 17:27:38

mmichalek

Details Diff
0003636: 3rd party triggers can affect SymmetricDS data load
0003636
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/ClientSymmetricEngine.java Diff File
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/common/ParameterConstants.java Diff File
mod - symmetric-core/src/main/resources/symmetric-default.properties Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/sql/SqlTemplateSettings.java Diff File
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/sql/JdbcSqlTransaction.java Diff File

SymmetricDS: 3.9 6907d91d

2018-07-31 13:33:16

mmichalek

Details Diff
0003636: 3rd party triggers can affect SymmetricDS data load
0003636
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/sql/JdbcSqlTransaction.java Diff File

Issue History

Date Modified Username Field Change
2018-07-18 17:26 mmichalek New Issue
2018-07-18 17:26 mmichalek Status new => assigned
2018-07-18 17:26 mmichalek Assigned To => mmichalek
2018-07-18 17:29 mmichalek Status assigned => resolved
2018-07-18 17:29 mmichalek Resolution open => fixed
2018-07-18 17:29 mmichalek Fixed in Version => 3.9.10
2018-07-18 18:00 mmichalek Changeset attached => SymmetricDS 3.9 a2b09398
2018-07-31 14:00 mmichalek Changeset attached => SymmetricDS 3.9 6907d91d
2018-08-03 10:12 chenson Status resolved => closed