View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0005067 | SymmetricDS | Bug | public | 2021-08-03 13:17 | 2021-11-04 14:58 |
| Reporter | jkarvanen | Assigned To | rudiejd | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.12.11 | ||||
| Target Version | 3.12.12 | Fixed in Version | 3.12.12 | ||
| Summary | 0005067: Failing to sync stored procedures containing semi-colons | ||||
| Description | When trigger.capture.ddl.changes is true, procedure replication is failing if procedure / function contains semi-colon. The procedure creation failing in target db because application split procedure to separate statements before execution. | ||||
| Steps To Reproduce | Set trigger.capture.ddl.changes=true Create procedure containing semicolon. The batch with the procedure creation failing in target db. | ||||
| Additional Information | Managed to get it to work adding delimiter $; command in from of row_data in sym_on_ddl trigger. MsSqlTriggerTemplate.java: " insert into " + defaultCatalog + "$(defaultSchema)$(prefixName)_data\n" + " (table_name, event_type, trigger_hist_id, row_data, channel_id, source_node_id, create_time)\n" + " values ('$(prefixName)_node', '" + DataEventType.SQL.getCode() + "', @histId,\n" + -" '\"' + replace(replace(@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)'),'\\','\\\\'),'\"','\\\"') + '\",ddl',\n" + +" '\"delimiter $;' + CHAR(13) + char(10) + replace(replace(@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)'),'\\','\\\\'),'\"','\\\"') + '\",ddl',\n" + " 'config', dbo.$(prefixName)_node_disabled(), current_timestamp)\n" + "end\n" + "---- go"); | ||||
| Tags | No tags attached. | ||||
|
|
Sorry Summary is partly missing. Should be: Failing to sync stored procedures containing semi-colons. |
|
|
Reproduced on SQL Server 2017 running on Ubuntu with the JTDS driver. Steps to reproduce: Set up replication between two tables. On source table, create stored procedure as follows: CREATE PROCEDURE spTest AS BEGIN SELECT ('foo'); SELECT ('bar'); END GO Note that it doesn't get replicated. Now create another procedure: CREATE PROCEDURE spTest AS BEGIN SELECT ('foo') END GO Note that without semi-colons, this procedure replicates to target fine. |
|
SymmetricDS: 3.12 72fc6da8 2021-08-10 14:45:47 Committer: GitHub Details Diff |
fix: 0005067, mssql stored procedures with parentheses won't replicate (0000165) * fix: resolve issue 0005067, add parameter for the delimiter used in ddl updates on sql server * fix: 0005067 format (unwrap line, remove unnecesary space) * 0005067: rollback to prepareAndExecute for stability |
Affected Issues 0005067 |
|
| mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql/MsSqlTriggerTemplate.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 | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2021-08-03 13:17 | jkarvanen | New Issue | |
| 2021-08-03 13:21 | jkarvanen | Note Added: 0001973 | |
| 2021-08-04 17:50 | pmarzullo | Summary | Failing to => Failing to sync stored procedures containing semi-colons |
| 2021-08-09 15:32 | rudiejd | Assigned To | => rudiejd |
| 2021-08-09 15:32 | rudiejd | Status | new => assigned |
| 2021-08-09 18:51 | rudiejd | Note Added: 0001975 | |
| 2021-08-10 14:46 | elong | Status | assigned => resolved |
| 2021-08-10 14:46 | elong | Resolution | open => fixed |
| 2021-08-10 14:46 | elong | Fixed in Version | => 3.12.12 |
| 2021-08-10 14:46 | elong | Target Version | => 3.12.12 |
| 2021-08-10 15:00 | JD | Changeset attached | => SymmetricDS 3.12 72fc6da8 |
| 2021-11-04 14:58 | admin | Status | resolved => closed |