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:
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.

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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.
  • 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.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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.
  • 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.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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)
  • 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!
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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
    
Sign In or Register to comment.