View Revisions: Issue #6674

Back to Issue
Summary 0006674: False conflict change detection on update of Null or Empty values in NVARCHAR(MAX) and VARCHAR(MAX) columns (SQLServer-specific)
Revision 2024-12-11 01:43 by pbelov
Description False-positive conflict change detection on update of Null or Empty values in NVARCHAR(MAX) and VARCHAR(MAX) columns (SQLServer-specific)
Revision 2024-12-11 02:05 by pbelov
Description False-positive conflict change detection on update of Null or Empty values in NVARCHAR(MAX) and VARCHAR(MAX) columns (SQLServer-specific)
Affects SymmetricDS bi-directional replication with conflict resolution mode FALLBACK.
Affects LOB columns of the NVARCHAR(MAX) or VARCHAR(MAX) data types on MS SQL Server version 2005 and up.
Whenever an OLD_DATA for batch contains a blank or null value being replaced by a non-blank value, there is an additional (erroneous) change captures and sent as part of conflict resolution fallback, which sends blank value to the node on the opposite side of the group link.
Testing requires:
* MS SQL Server on both source and target nodes
* Bi-directional replication with conflict resolution of Detection Type=USE_PK_DATA, Resolution Type=FALLBACK, Ping Back=SINGLE_ROW
* Set Push interval for 5 seconds to speed-up testing.
* A table with column of NVARCHAR(MAX) or VARCHAR(MAX) data type, which is NOT a part of primary key.
* One more column, of any data type, which is also NOT a part of primary key.
* Initial value of null of blank string for the column of NVARCHAR(MAX) or VARCHAR(MAX) data type
* Two or more simultaneous and conflicting batches on opposite nodes, with an UPDATE change targeting different columns mentioned above.
Revision 2024-12-11 01:43 by pbelov
Steps To Reproduce Server db=SQL Server (SymQaRoot schema)
Client db=SQL Server (SymQaClient schema)
Revision 2024-12-11 02:05 by pbelov
Steps To Reproduce Server db=SQL Server (SymQaRoot schema)
Client db=SQL Server (SymQaClient schema)

CREATE TABLE SymQaRoot.dbo.test_nvarchar_max(
    id int NOT NULL
    ,col2 int NULL
    ,col3 nvarchar(max) NULL
    ,CONSTRAINT test_nvarchar_max_pk UNIQUE (id)
);


INSERT INTO SymQaRoot.dbo.test_nvarchar_max VALUES (1, 11, NULL); -- Seed null value for col3


-- Create two conflicting updates - on two different nodes:
UPDATE SymQaRoot.dbo.test_nvarchar_max SET col2=33 WHERE id=1;
UPDATE SymQaClient.dbo.test_nvarchar_max SET col3='COMMENT_33' WHERE id=1;


-- Observe three changes to the col3 column, resulting in
-- (correct) col3 == COMMENT_33 in symQaRoot.dbo.test_nvarchar_max
-- (incorrect) col3 == empty/null value in the symQaClient.dbo.test_nvarchar_max
-- Observe col2 on both nodes is set to 33 (correct);

Select 'source' as node, s.* FROM SymQaRoot.dbo.test_nvarchar_max s
UNION ALL
Select 'target' as node, t.* FROM SymQaClient.dbo.test_nvarchar_max t;
Revision 2024-12-11 02:09 by pbelov
Description False-positive conflict change detection on update of Null or Empty values in NVARCHAR(MAX) and VARCHAR(MAX) columns (SQLServer-specific)
Affects SymmetricDS bi-directional replication with conflict resolution mode FALLBACK.
Affects LOB columns of the NVARCHAR(MAX) or VARCHAR(MAX) data types on MS SQL Server version 2005 and up.
Whenever an OLD_DATA for batch contains a blank or null value being replaced by a non-blank value, there is an additional (erroneous) change captured and sent as part of conflict resolution fallback, which sends blank value to the node on the opposite side of the group link.
Testing requires:
* MS SQL Server on both source and target nodes
* Bi-directional replication with conflict resolution of Detection Type=USE_PK_DATA, Resolution Type=FALLBACK, Ping Back=SINGLE_ROW
* Set Push interval for 5 seconds to speed-up testing.
* A table with column of NVARCHAR(MAX) or VARCHAR(MAX) data type, which is NOT a part of primary key.
* One more column, of a primitive data type (non-LOB) , which is also NOT a part of primary key.
* Initial value of null of blank string for the column of NVARCHAR(MAX) or VARCHAR(MAX) data type
* Two or more simultaneous and conflicting batches on opposite nodes, with an UPDATE change targeting different columns mentioned above.
Revision 2024-12-11 02:09 by pbelov
Steps To Reproduce Server db=SQL Server (SymQaRoot schema)
Client db=SQL Server (SymQaClient schema)

CREATE TABLE SymQaRoot.dbo.test_nvarchar_max(
    id int NOT NULL
    ,col2 int NULL
    ,col3 nvarchar(max) NULL
    ,CONSTRAINT test_nvarchar_max_pk UNIQUE (id)
);

INSERT INTO SymQaRoot.dbo.test_nvarchar_max VALUES (1, 11, NULL); -- Seed null value for col3

-- Create two conflicting updates (on two different nodes) targeting different columns:
UPDATE SymQaRoot.dbo.test_nvarchar_max SET col2=33 WHERE id=1;
UPDATE SymQaClient.dbo.test_nvarchar_max SET col3='COMMENT_33' WHERE id=1;

-- Observe:
-- Three changes to the col3 column, resulting in:
-- (correct) col3 == COMMENT_33 in symQaRoot.dbo.test_nvarchar_max
-- (incorrect) col3 == empty/null value in the symQaClient.dbo.test_nvarchar_max
-- Note: The col2 is set to 33 on both nodes (correct).

Select 'source' as node, s.* FROM SymQaRoot.dbo.test_nvarchar_max s
UNION ALL
Select 'target' as node, t.* FROM SymQaClient.dbo.test_nvarchar_max t;