A bug in merge replication with FILESTREAM data

Jul 3

Posted by


I wish I could say that every DBA has a love/hate relationship with Replication, but, let’s face it, it’s only hate. But it could get worse: it could be Merge Replication. Or even worse: Merge Replication with FILESTREAM.

What could possibly top all this hatred and despair if not a bug? Well, I happened to find one, that I will describe here.

The scenario

I published tables with FILESTREAM data before, but it seems like there is a particular planetary alignment that triggers an error during the execution of the snapshot agent.

This unlikely combination consists in a merge article with a FILESTREAM column and two UNIQUE indexes on the ROWGUIDCOL column. Yes, I know that generally it does not make sense to have two indexes on the same column, but this happened to be one of the cases where it did, so we had a CLUSTERED PRIMARY KEY on the uniqueidentifier column decorated with the ROWGUIDCOL attribute and, on top, one more NONCLUSTERED UNIQUE index on the same column, backed by a UNIQUE constraint.

Setting up the publication does not throw any error, but generating the initial snapshot for the publication does:

Cannot create, drop, enable, or disable more than one constraint,
column, index, or trigger named 'ncMSmerge_conflict_TestMergeRep_DataStream'
in this context. Duplicate names are not allowed.

Basically, the snapshot agent is complaining about the uniqueness of the name of one of the indexes it is trying to create on the conflict table. The interesting fact about this bug is that it doesn’t appear when the table has no FILESTREAM column and it doesn’t appear when the table doesn’t have the redundant UNIQUE constraint on the ROWGUID column: both conditions need to be met.

The script

Here is the full script to reproduce the bug.

Before you run it, make sure that:

  1. FILESTREAM is enabled
  2. Distribution is configured


USE master;
GO
—
— CLEANUP
—
IF DB_ID('TestMergeRep') IS NOT NULL
BEGIN
BEGIN TRY
exec TestMergeRep.sys.sp_dropmergepublication @publication=N'TestMergeRep';
END TRY
BEGIN CATCH
PRINT 'sp_dropmergepublication failed'
END CATCH
BEGIN TRY
exec TestMergeRep.sys.sp_removedbreplication 'TestMergeRep', 'merge';
END TRY
BEGIN CATCH
PRINT 'sp_removedbreplication failed'
END CATCH
BEGIN TRY
exec TestMergeRep.sys.sp_replicationdboption @dbname = N'TestMergeRep', @optname = N'merge publish', @value = N'false';
END TRY
BEGIN CATCH
PRINT 'sp_replicationdboption failed'
END CATCH
ALTER DATABASE TestMergeRep SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE TestMergeRep;
END
GO
—
— CREATE DATABASE
—
CREATE DATABASE TestMergeRep;
GO
— WE NEED A FILESTREAM FILEGROUP
DECLARE @path nvarchar(128), @sql nvarchar(max);
SELECT @path = LEFT(physical_name, LEN(physical_name) – CHARINDEX('\', REVERSE(physical_name),1) + 1)
FROM sys.database_files
WHERE type = 0
ALTER DATABASE TestMergeRep
ADD
FILEGROUP [TestmergeRep_FileStream01] CONTAINS FILESTREAM;
SET @sql = '
ALTER DATABASE TestMergeRep
ADD
FILE
( NAME = N''TestmergeRep_FS01'', FILENAME = ''' + @path + 'TestMergeRep_FS01'' , MAXSIZE = UNLIMITED)
TO FILEGROUP [TestmergeRep_FileStream01];
'
EXEC(@sql)
—
— CREATE TABLE
—
USE TestMergeRep;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DataStream](
[DataStreamGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ValueData] [varbinary](max) FILESTREAM NOT NULL,
CONSTRAINT [DataStream_DataStream_PK] PRIMARY KEY CLUSTERED
(
[DataStreamGUID] ASC
)
)
GO
— I know it doesn't make sense, but the bug only shows
— when the table has a second UNIQUE constraint on the PK column
ALTER TABLE [DataStream] ADD CONSTRAINT UQ_MESL_DataStreamPK UNIQUE ([DataStreamGUID]);
— WORKAROUND: create the UNIQUE index without creating the UNIQUE constraint:
–CREATE UNIQUE NONCLUSTERED INDEX UQ_MESL_DataStreamPK ON [DataStream] ([DataStreamGUID]);
—
— SET UP REPLICATION
—
USE master
EXEC sp_replicationdboption
@dbname = N'TestMergeRep',
@optname = N'merge publish',
@value = N'true';
use [TestMergeRep]
exec sp_addmergepublication
@publication = N'TestMergeRep',
@description = N'Merge publication of database TestMergeRep.',
@retention = 30,
@sync_mode = N'native',
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'false',
@enabled_for_internet = N'false',
@conflict_logging = N'publisher',
@dynamic_filters = N'false',
@snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false',
@ftp_port = 21,
@ftp_login = N'anonymous',
@conflict_retention = 14,
@keep_partition_changes = N'false',
@allow_subscription_copy = N'false',
@allow_synctoalternate = N'false',
@add_to_active_directory = N'false',
@max_concurrent_merge = 0,
@max_concurrent_dynamic_snapshots = 0,
@publication_compatibility_level = N'100RTM',
@use_partition_groups = N'false';
exec sp_addpublication_snapshot
@publication = N'TestMergeRep',
@frequency_type = 1,
@frequency_interval = 1,
@frequency_relative_interval = 1,
@frequency_recurrence_factor = 1,
@frequency_subday = 1,
@frequency_subday_interval = 5,
@active_start_date = 0,
@active_end_date = 0,
@active_start_time_of_day = 10000,
@active_end_time_of_day = 235959;
exec sp_addmergearticle
@publication = N'TestMergeRep',
@article = N'DataStream',
@source_owner = N'dbo',
@source_object = N'DataStream',
@type = N'table',
@description = null,
@column_tracking = N'true',
@pre_creation_cmd = N'drop',
@creation_script = null,
@schema_option = 0x000000010C034FD1,
@article_resolver = null,
@subset_filterclause = N'',
@vertical_partition = N'false',
@destination_owner = N'dbo',
@verify_resolver_signature = 0,
@allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true',
@check_permissions = 0,
@identityrangemanagementoption = 'manual',
@delete_tracking = N'true',
@stream_blob_columns = N'false',
@force_invalidate_snapshot = 1;
— Sets all merge jobs owned by sa
DECLARE @job_id uniqueidentifier
DECLARE c CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT job_id
FROM msdb.dbo.sysjobs AS sj
INNER JOIN msdb.dbo.syscategories AS sc
ON sj.category_id = sc.category_id
WHERE sc.name = 'REPL-Merge';
OPEN c
FETCH NEXT FROM c INTO @job_id
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC msdb.dbo.sp_update_job @job_id=@job_id , @owner_login_name=N'sa'
FETCH NEXT FROM c INTO @job_id
END
CLOSE c
DEALLOCATE c

view raw

TestMergeRep.sql

hosted with ❤ by GitHub

After running the script, start the snapshot agent and you’ll see the error appearing:

snapshot

Workaround

One way to get rid of the bug is to enforce the uniqueness of the data by using a UNIQUE index instead of a UNIQUE constraint:

CREATE UNIQUE NONCLUSTERED INDEX UQ_MESL_DataStreamPK
ON [DataStream] ([DataStreamGUID]);

With this index, the snapshot agent completes correctly. Please note that the index would have been UNIQUE anyway, because its key is a superset of the primary key.

Hope this helps!

Please Vote!

This bug has been filed on UserVoice and can be found here: https://feedback.azure.com/forums/908035-sql-server/suggestions/34735489-bug-in-merge-replication-snapshot-agent-with-files

Please upvote it!

Posted on July 3, 2018, in SQL Server and tagged , , , . Bookmark the permalink. 4 Comments.

  1. patchy_drizzle | July 11, 2018 at 1:09 pm

    From the name, it looks like the error occurs creating an index twice on the conflicts table with same name.

    So there must be two triggers within your ddl that cause that to be.

    The conflict table index includes ROWGUID; I suppose the act of publishing
    the table creates this conflict table and index.

    It looks as if this bit
    ‘ALTER TABLE [DataStream] ADD CONSTRAINT UQ_MESL_DataStreamPK UNIQUE ([DataStreamGUID’

    must also be picked up by the publication process and cause it to try and add the same unique index on the conflict table with the same system generated name.

    Why add another unique index to that GUID column anyway ? Perhaps you don’t need…

    Also You might want to check the guid is defined with ‘newsequentialid’ to avoid lots of page
    splitting. Especially as it’s your clustered index …

    Reply
    • spaghettidba | July 11, 2018 at 1:15 pm

      Thanks, I appreciate the tips, but they have nothing to do with the bug itself. It’s the constraint combined with the filestream column that causes the unwanted behavior

      Reply
  2. spaghettidba | July 12, 2018 at 9:18 am

    The error message appears to be similar, but it’s a default constraint in that case. However, there seems to be a bond between the FILESTREAM attribute on the column and the bug: if you remove it from the script, everything works just fine. I have absolutely no idea why FILESTREAM could cause a problem like that, but turns out it does.

    Reply

  1. Pingback: Problem With Merge Replication And FILESTREAM – Curated SQL

Leave a comment