Command-line SQL compare breaks FileStream - but not GUI

kodexekodexe Posts: 5
edited August 23, 2011 10:12AM in SQL Compare Previous Versions
I have a table with a FileStream blob field and a PK Guid ID, declared thusly:
CREATE TABLE [dbo].[MyTable](
	[Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[Data] [varbinary](max) FILESTREAM  NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] FILESTREAM_ON [FileStreamGroup1],
UNIQUE NONCLUSTERED 
(
	[Id] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [FileStreamGroup1]
(If you're wondering about ROWGUIDCOL, it's because SQL Server requires this on filestream guid table fields.)

When I synchronize two versions of this database, using the command-line SQL compare, even if there are no changes to this table, but only other tables, this table gets silently rebuilt, and broken in the process. By "silently" I mean that this table does not show up in the output - probably because there are no differences, so the changes just get added to the list of "related objects affected" (like views that need refreshing etc). And by "broken" I mean that the table gets rebuilt, but the primary key is not added back after the rebuild.

When I do the same thing using the GUI SQL Compare, the sync script looks fine and the table isn't affected.

The command line used is this: 
sqlcompare.exe /server1:x /username1:x /password1:x /db1:%sourcedb% /server2:x /username2:x /password2:x /db2:x /Exclude:user /Exclude:role /Exclude:Schema /Synchronize /rad /r:report.txt /sf:change.sql

Here's the sync SQL that gets added by the command-line SQL Compare but not by the GUI SQL Compare and that breaks the table: 
PRINT N'Rebuilding [dbo].[MyTable]'
GO
CREATE TABLE [dbo].[tmp_rg_xx_MyTable]
(
[Id] [uniqueidentifier] NOT NULL ROWGUIDCOL,
[Data] [varbinary] (max) FILESTREAM NULL
CONSTRAINT [tmp_rg_xx_UQ__MyTable__3214EC060EC32C7A] UNIQUE NONCLUSTERED  ([Id])
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
INSERT INTO [dbo].[tmp_rg_xx_MyTable]([Id], [Data]) SELECT [Id], [Data] FROM [dbo].[MyTable]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
DROP TABLE [dbo].[MyTable]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
EXEC sp_rename N'[dbo].[tmp_rg_xx_MyTable]', N'MyTable'
EXEC sp_rename N'[dbo].[MyTable].[tmp_rg_xx_UQ__MyTable__3214EC060EC32C7A]', N'UQ__MyTable__3214EC060EC32C7A', N'INDEX'

To a layman such as myself, it looks like the primary key constraint is simply forgotten, which means it won't be there after the rebuild.

But why is this table rebuilt at all when there are no differences in it? Is it because of the FileStream? Or because of the RowGuidCol?

And why does the command-line tool differ from the GUI tool? That made it kind of hard to track this down. :-)

Thanks in advance.

Using latest 8.5 (with all updates).

EDIT: Just tested with 9.0; exact same results.

EDIT: This very old post seems to cover the same bug. It ends with Red Gate not being able to reproduce. Please put more effort this time, the bug is real. Sample data available on request! Specifically this:
Brian Donahue wrote:
The reasons for table rebuilds are here: http://www.red-gate.com/supportcenter/C ... 000113.htm

When the rebuild is scripted, primary keys are not added back into the table.

Does that sound like an accurate description of the problem?

EDIT: It seems that a "reason for rebuild" is "Changing the filegroup specification for a table". This hasn't happened here - and even if it had, there are other mysteries (GUI vs CLI, non-related change etc). But it sounds related.
· Share on Twitter

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    In the last case, the user sent in the scripts, but the problem could not be reproduced in-house, so we could not take further action. Despite chasing the user down about three times, we could not get in touch with him again. And this is the only time it ever happened. So we cannot even say whether or not this problem and the other problem are the same.

    The other critical bit of information left out from my quote was that this only happened comparing scripts to a live database. There can be some oddness because on the live side we have to do a table rebuild in some cases and on the scripts side we don't (can't?).

    If you could let us know if this is a live database on both sides, the schema script on both sides, and the default collation of the databases, hopefully we can reproduce it.
    · Share on Twitter
  • kodexekodexe Posts: 5
    Brian Donahue wrote:
    In the last case, the user sent in the scripts, but the problem could not be reproduced in-house, so we could not take further action. Despite chasing the user down about three times, we could not get in touch with him again. And this is the only time it ever happened. So we cannot even say whether or not this problem and the other problem are the same.

    The other critical bit of information left out from my quote was that this only happened comparing scripts to a live database. There can be some oddness because on the live side we have to do a table rebuild in some cases and on the scripts side we don't (can't?).

    If you could let us know if this is a live database on both sides, the schema script on both sides, and the default collation of the databases, hopefully we can reproduce it.
    The quote was intended to refresh your memory, not to cast blame. :)

    Yes, this is a compare between two live running databases (if I understand your question correctly). There are no scripts involved (other than perhaps the generated scripts used during the operation). We haven't tried it in any other scenario. If you want us to, please advise on how that should be done.


    Not sure what you mean by "schema script", but here is "script as create to new query window":
    USE [master]
GO

/****** Object:  Database [MyDatabase]    Script Date: 08/09/2011 09:17:29 ******/
CREATE DATABASE [MyDatabase] ON  PRIMARY 
( NAME = N'MyDatabase', FILENAME = N'F:\db\DATA\MyDatabase.mdf' , SIZE = 1149952KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [FileStreamGroup1] CONTAINS FILESTREAM  DEFAULT 
( NAME = N'Catstream', FILENAME = N'f:\db\fstream' )
 LOG ON 
( NAME = N'MyDatabase_log', FILENAME = N'F:\db\DATA\MyDatabase_Log.ldf' , SIZE = 353216KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [MyDatabase] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [MyDatabase].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [MyDatabase] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [MyDatabase] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [MyDatabase] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [MyDatabase] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [MyDatabase] SET ARITHABORT OFF 
GO
ALTER DATABASE [MyDatabase] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [MyDatabase] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [MyDatabase] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [MyDatabase] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [MyDatabase] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [MyDatabase] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [MyDatabase] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [MyDatabase] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [MyDatabase] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [MyDatabase] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [MyDatabase] SET  DISABLE_BROKER 
GO
ALTER DATABASE [MyDatabase] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [MyDatabase] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [MyDatabase] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [MyDatabase] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [MyDatabase] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [MyDatabase] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [MyDatabase] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [MyDatabase] SET  READ_WRITE 
GO
ALTER DATABASE [MyDatabase] SET RECOVERY FULL 
GO
ALTER DATABASE [MyDatabase] SET  MULTI_USER 
GO
ALTER DATABASE [MyDatabase] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [MyDatabase] SET DB_CHAINING OFF 
GO

    The create script for the source and target databases are identical, except for the names and file names.

    The default collation for both databases is 
    Finnish_Swedish_CI_AS

    I can also provide you with actual database backup files if needed.
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    I have opened a bug about this ( SC-5219 ). The problem seems to occur when a column is both a primary key column and the unique index for a filestream at the same time. SQL Compare will filter out this index if this is the case. The fix for this may not appear for some time as it is not trivial to implement.
    · Share on Twitter
  • kodexekodexe Posts: 5
    Brian Donahue wrote:
    I have opened a bug about this ( SC-5219 ). The problem seems to occur when a column is both a primary key column and the unique index for a filestream at the same time. SQL Compare will filter out this index if this is the case. The fix for this may not appear for some time as it is not trivial to implement.
    Any idea about why the GUI version of the same tool does NOT exhibit this erroneous behavior? To a layman like me, it sounds like you just need to copy the synchronization code from SqlCompareGui.bas to SqlCompareCmd.bas.
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    The bug report is for the GUI. It creates the synchronization in the same way given the same options. And yes, it is the same code (RedGate.SqlCompare.Engine.dll)
    · Share on Twitter
  • kodexekodexe Posts: 5
    Brian Donahue wrote:
    The bug report is for the GUI. It creates the synchronization in the same way given the same options. And yes, it is the same code (RedGate.SqlCompare.Engine.dll)
    Actually, as mentioned in the original post in this topic, we've recreated situations where the command-line tool actually produces different results (in this case, the GUI tool did *not* see any changes in the target database, which means no index rebuild or table recreate was made). I'll see if I can get databases to help you see this!
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Produced by command-line: 
    /*
Run this script on:

        XXX\sql2008r2.50855-B    -  This database will be modified

to synchronize it with:

        XXX\sql2008r2.50855-A

You are recommended to back up your database before running this script

Script created by SQL Compare version 9.0.0 from Red Gate Software Ltd at 23/08/2011 14:57:40

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Rebuilding [dbo].[CatalogStorage]'
GO
CREATE TABLE [dbo].[tmp_rg_xx_CatalogStorage]
(
[Id] [uniqueidentifier] NOT NULL ROWGUIDCOL,
[CatalogData] [varbinary] (max) FILESTREAM NULL,
[TimeOfCreation] [datetimeoffset] NULL
CONSTRAINT [tmp_rg_xx_UQ__CatalogS__3214EC06023D5A04] UNIQUE NONCLUSTERED  ([Id])
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
INSERT INTO [dbo].[tmp_rg_xx_CatalogStorage]([Id], [CatalogData], [TimeOfCreation]) SELECT [Id], [CatalogData], [TimeOfCreation] FROM [dbo].[CatalogStorage]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
DROP TABLE [dbo].[CatalogStorage]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
EXEC sp_rename N'[dbo].[tmp_rg_xx_CatalogStorage]', N'CatalogStorage'
EXEC sp_rename N'[dbo].[CatalogStorage].[tmp_rg_xx_UQ__CatalogS__3214EC06023D5A04]', N'UQ__CatalogS__3214EC06023D5A04', N'INDEX'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
    Produced by SQL Compare GUI: 
    /*
Run this script on:

        XXX\SQL2008R2.50855-B    -  This database will be modified

to synchronize it with:

        XXX\SQL2008R2.50855-A

You are recommended to back up your database before running this script

Script created by SQL Compare version 9.0.0 from Red Gate Software Ltd at 23/08/2011 15:11:17

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Rebuilding [dbo].[CatalogStorage]'
GO
CREATE TABLE [dbo].[tmp_rg_xx_CatalogStorage]
(
[Id] [uniqueidentifier] NOT NULL ROWGUIDCOL,
[CatalogData] [varbinary] (max) FILESTREAM NULL,
[TimeOfCreation] [datetimeoffset] NULL
CONSTRAINT [tmp_rg_xx_UQ__CatalogS__3214EC06023D5A04] UNIQUE NONCLUSTERED  ([Id])
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
INSERT INTO [dbo].[tmp_rg_xx_CatalogStorage]([Id], [CatalogData], [TimeOfCreation]) SELECT [Id], [CatalogData], [TimeOfCreation] FROM [dbo].[CatalogStorage]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
DROP TABLE [dbo].[CatalogStorage]
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
EXEC sp_rename N'[dbo].[tmp_rg_xx_CatalogStorage]', N'CatalogStorage'
EXEC sp_rename N'[dbo].[CatalogStorage].[tmp_rg_xx_UQ__CatalogS__3214EC06023D5A04]', N'UQ__CatalogS__3214EC06023D5A04', N'INDEX'
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
    · Share on Twitter
Sign In or Register to comment.