View Issue Details

IDProjectCategoryView StatusLast Update
0005067SymmetricDSBugpublic2021-08-10 15:00
Reporterjkarvanen Assigned Torudiejd  
Prioritynormal 
Status resolvedResolutionfixed 
Product Version3.12.11 
Target Version3.12.12Fixed in Version3.12.12 
Summary0005067: Failing to sync stored procedures containing semi-colons
DescriptionWhen 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 ReproduceSet
 trigger.capture.ddl.changes=true

Create procedure containing semicolon.

The batch with the procedure creation failing in target db.
Additional InformationManaged 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");
TagsNo tags attached.

Activities

jkarvanen

2021-08-03 13:21

reporter   ~0001973

Sorry Summary is partly missing.
Should be:
Failing to sync stored procedures containing semi-colons.

rudiejd

2021-08-09 18:51

developer   ~0001975

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.

Related Changesets

SymmetricDS: 3.12 72fc6da8

2021-08-10 14:45:47

JD


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

Issue History

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