View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0003636 | SymmetricDS | Bug | public | 2018-07-18 21:26 | 2018-08-03 14:12 |
| Reporter | mmichalek | Assigned To | mmichalek | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.9.0 | ||||
| Target Version | 3.9.10 | Fixed in Version | 3.9.10 | ||
| Summary | 0003636: 3rd party triggers can affect SymmetricDS data load | ||||
| Description | In 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 Information | This 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; | ||||
| Tags | No tags attached. | ||||
|
SymmetricDS: 3.9 a2b09398 2018-07-18 17:27:38 Details Diff |
0003636: 3rd party triggers can affect SymmetricDS data load |
Affected Issues 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 Details Diff |
0003636: 3rd party triggers can affect SymmetricDS data load |
Affected Issues 0003636 |
|
| mod - symmetric-jdbc/src/main/java/org/jumpmind/db/sql/JdbcSqlTransaction.java | Diff File | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2018-07-18 21:26 | mmichalek | New Issue | |
| 2018-07-18 21:26 | mmichalek | Status | new => assigned |
| 2018-07-18 21:26 | mmichalek | Assigned To | => mmichalek |
| 2018-07-18 21:29 | mmichalek | Status | assigned => resolved |
| 2018-07-18 21:29 | mmichalek | Resolution | open => fixed |
| 2018-07-18 21:29 | mmichalek | Fixed in Version | => 3.9.10 |
| 2018-07-18 22:00 | mmichalek | Changeset attached | => SymmetricDS 3.9 a2b09398 |
| 2018-07-31 18:00 | mmichalek | Changeset attached | => SymmetricDS 3.9 6907d91d |
| 2018-08-03 14:12 | chenson | Status | resolved => closed |