2.2.1.23 Duplicate PK definition - SSC Inoperable on this DB

PDinCA

Committed major architectural change on Monday, involving a table that used to be in DB Rpt and was replaced by a table of the same name and same PK name in DB Trans - this meant ZERO code changes as a synonym was added for the old table to reference the replacement.

This is the first attempt to commit a change to the Trans database since that time: and resutled in the following error that has locked me out of SSC for the DB completely:

RedGate.SQLSourceControl.Engine.SqlCompareException: A duplicate definition was found for the index pk_t_meter_clean. Ensure that case sensitivity options are set correctly and all object creation scripts are valid. If the problem persists, contact our support. ---> #8rg.#7rg: A duplicate definition was found for the index pk_t_meter_clean. Ensure that case sensitivity options are set correctly and all object creation scripts are valid. If the problem persists, contact our support. ---> #8rg.#7rg: A duplicate index name (pk_t_meter_clean) has been found. This may occur if the SQL Server that you are registering is case sensitive but the case sensitive option is not set. ---> System.ArgumentException: An item with the same key has already been added.
   at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
   at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
   at System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value)
   at RedGate.SQLCompare.Engine.KeyedCollection`1.Add(T value)
   at RedGate.SQLCompare.Engine.SerializableDatabaseObjectCollection`1.Add(T value)
   --- End of inner exception stack trace ---
   at RedGate.SQLCompare.Rewriter.Analysis.Analyser`1.#KPz(Exception exception)
   at RedGate.SQLCompare.Rewriter.Analysis.Analyser`1.WaitForBackgroundThread()
   at #Eyg.#Gyg.#CGh(FileInfo file)
   at #Eyg.#Gyg.#vl()
   --- End of inner exception stack trace ---
   at #Eyg.#Gyg.#vl()
   at RedGate.SQLCompare.Engine.Database.Register(String path, ScriptDatabaseInformation dbinfo, Options options)
   at #qlhb.#L3c.#t.#A2.#m36()
   at #GWeb.#7Jf.#gKf(Action )
   --- End of inner exception stack trace ---

Server stack trace: 
   at #GWeb.#7Jf.#gKf(Action )
   at #GWeb.#7Jf.#t.#z2.#rkW()
   at RedGate.SQLSourceControl.Engine.SharedUtilsUtils.DoActionWithCancel(ICancellableOperationStatus status, ICancellable cancellable, Action action)
   at #GWeb.#7Jf.#jKf(ICancellableOperationStatus , ICancellable , Action )
   at #qlhb.#L3c.#l36(String , #x36 )
   at #qlhb.#K3c.#83c(String , #O9U , ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks )
   at #qlhb.#L3c.#f6c(String , ScriptDatabaseInformation , Options , ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks )
   at #GWeb.#WXM.#f6c(String )
   at #GWeb.#WXM.#WtV()
   at #GWeb.#Wheb.#tieb(Func`1 )
   at #GWeb.#WXM.#VtV()
   at RedGate.SQLSourceControl.Engine.Cancellables.CancellableOperation.#t.#izb.#j5f()
   at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)

Exception rethrown at [0]: 
   at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)
   at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)
   at System.Action.EndInvoke(IAsyncResult result)
   at RedGate.SQLSourceControl.Engine.Cancellables.CancellableOperation.DoInParallel(ICancellableOperationStatus status, Pair`2[] labelledActions)
   at #GWeb.#WXM.#rYM()
   at #GWeb.#WXM.#qYM()
   at #GWeb.#XXM.#iS()
   at #qlhb.#0heb.#l36(String , #1heb )
   at #qlhb.#K3c.#83c(String , #O9U , ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks )
   at #qlhb.#0heb.#wieb(#L3c , #ZZ7 , #A57 , #tEc , IObjectExplorerStateService , #xHR , #9ynb , #4Ec , #j3l , Options , ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks )
   at #GWeb.#VXM.#mYM[#eTb](Func`2 , ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks )
   at #GWeb.#VXM.#kYM(ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks )
   at #GWeb.#4Ec.#8Jc(ICancellableOperationStatus , IReadOnlySourceControlServerCallBacks )
   at #GWeb.#4Ec.#t.#B3.#qfA(ICancellableOperationStatus )
   at RedGate.SQLSourceControl.Engine.Cancellables.MutexedCancellableOperation`1.#t.#u3.#k5f()
   at RedGate.SQLSourceControl.Engine.Cancellables.CancellableOperationBase.InvokeWithTracker(Action action)
   at RedGate.SQLSourceControl.Engine.Cancellables.MutexedCancellableOperation`1.Invoke()
   at #eEc.#Qlg.Invoke()
   at #JLc.#PLc.#t.#Jfb.#EJf()
   at RedGate.SQLSourceControl.Engine.SmartAssembly.ExceptionReporting.ErrorReporterBase.Do(Action , Predicate`1 , Boolean )
   at RedGate.SQLSourceControl.Engine.SmartAssembly.ExceptionReporting.ErrorReporterBase.DoWithObviousExceptionsRethrowAll(Action action)
   at RedGate.SQLSourceControl.CommonUI.Forms.ErrorDialog.DoWithObviousExceptionsRethrowAll(Action action)
   at #JLc.#PLc.#CTc(ICancellableOperation`1 , Object )

Case sensitivity has NEVER changed.
I submitted the information to the Support Team via the "Is this error serious?..." Yes, I'd say it IS serious - Dead in the water serious.

ASSISTANCE HEREBY REQUESTED...

Thanks.

James B

Thanks for your post, and apologies for the delay in replying - the "send error report" option actually updates our bugtracker that the developers use, but doesn't raise a ticket with support.

Anyway, to your error. What's happening is that SQL Source Control is picking up two definitions for "pk_t_meter_clean". Looking at your post you mentioned that a table was moved to a different database. Because each database should have its own folder structure, this shouldn't cause any trouble; but it looks like maybe something has been left behind.

The first thing to do is search through your source control repository (using the location the database is linked to) for files that reference the offending object. Hopefully, you'll find two, one of which shouldn't be there. You can then remove the offending file from your repo, and unlink + relink the database, and you should be back up and running.

Not sure which source control system you're using, but to help you find the relevant files quickly, you might want to check out a copy of the repo folder structure to a temporary folder somewhere, then run the findstr command that another user found useful, as in this post

PDinCA

Tried:

findstr /S /C:"pk_t_meter_clean" *.*

from a command prompt at the root of the SSC Tortoise SVN folder structure.

ZERO HITS

Suggestions as to what to search for or additional /<option> settings?

I also just expanded the database folder in the repository and it does NOT reflect what I applied to it on Monday of last week - it looks just like the OLD DB schema. Appears there's a more fundamental corruption caused when I (apparently successfully) applied the schema updates.

Do you suggest unlink-relink? Or what?

NOT a SVN officiando, BTW, merely a User.

Thanks.

James B

Hmm, that's odd. So it's not there at all. It may just be stuck in your local working files.
Can you try unlinking + relinking - that will create a new set of files.

PDinCA

Told you I wasn't a SVN person...

Had to do an "Update" in explorer to get the latest versions of everything... :oops:

Rerunning the findstr...

James B

Ah, no problem - hopefully it'll crop up now...

PDinCA

Cropped up, yes. What to do with the info - do tell...

U:\EPS_Deployment\xChangePoint\src\Database\SSC\xcp_trans>findstr /S /C:"pk_t_meter_clean" *.*
Tables\.svn\text-base\dbo.t_meter_clean.sql.svn-base:ALTER TABLE [dbo].[t_meter_clean] ADD CONSTRAINT [pk_t_meter_clean] PRIMARY KEY CLUSTERED  ([location_variable_id], [t_stamp], [input_id], [t_stamp_utc_delta]) ON [PRIMARY]
Tables\dbo.t_meter_clean.sql:ALTER TABLE [dbo].[t_meter_clean] ADD CONSTRAINT [pk_t_meter_clean] PRIMARY KEY CLUSTERED  ([location_variable_id], [t_stamp], [input_id], [t_stamp_utc_delta]) ON [PRIMARY]
Views\.svn\text-base\dbo.t_meter_clean.sql.svn-base:CONSTRAINT [pk_t_meter_clean] PRIMARY KEY CLUSTERED  ([location_variable_id], [t_stamp], [input_id], [t_stamp_utc_delta]) ON [PRIMARY]
Views\dbo.t_meter_clean.sql:CONSTRAINT [pk_t_meter_clean] PRIMARY KEY CLUSTERED ([location_variable_id], [t_stamp], [input_id], [t_stamp_utc_delta]) ON [PRIMARY]

The VIEW no longer exists - I nuked it in the re-architecture. The fact that it's still there is a reflection of the history of the database and is correct. However, it's existence in the annals of history prevents me from reflecting the current DB schema correctly, as reflected in the Tables\ rows.

How does one clean this up?

PDinCA

I looked down the views\ folder and at the .svn content. The trigger on the TABLE has been assigned to the defunct VIEW, which never had a trigger as it was a materialized view.

Wires appear crossed during the update where the View deletion was not completed and the object removed from candidature for dependant objects...

James B

OK, looking at that, I think the problem is that it's still listed in the views folder. If you deleted the view and committed the change, it should have removed that file. SVN will still handle the history ok, but the actual file should have gone.
If you want to zip up the whole folder structure and send it across to us I can verify that's definitely it; but otherwise I'd suggest removing the file from the view folder and the unlinking relinking.

*edit*
Just seen your more recent message. That sounds a bit odd if it's assigned the trigger to the view. It sounds like something went a bit strange, presumably because the objects were named the same, but it's not something I've come across before.

PDinCA

DB is actually small in schema but relatively large in data, so I'll gladly zip the SVN folder. Where should I send it?

PDinCA

Eamiled to the support @ address with this thread URL as the subject.

James B

Thanks, I'll take a look.

PDinCA

James, your directives worked fine:

So, I can get it working simply by deleting the file in the "Views" folder using Tortoise's repo browser then unlinking and relinking. Assuming that the view is no longer required (which sounds like the case based on your email) it should be the quickest way to get things running.

DB unlinked and relinked and I can now Commit again.

Perhaps it would be valuable to examine how the trigger update was assigned to the wrong object on Commit...? Maybe the object type qualification was missed in the logic...?

James B

If you can let me know the steps you took and supply details of the objects involved I can try to replicate the problem here. Once we can reproduce it a fix can be investigated to stop something similar happening in future changes.

PDinCA

The process, in simple terms, all under the dbo schema, was:

DROP Indexed View t_meter_clean, which had a PK of pk_t_meter_clean. DROP the table on which the View was built (only 1 table in the view's SELECT). CREATE table t_meter_clean. ADD PK constraint pk_t_meter_clean. Add TRIGGER to table t_meter_clean. COMMIT to SSC.

The result:

View t_meter_clean persisted in SVN. The table was added to SVN. The TRIGGER was added to the VIEW. Any attempt to Commit AFTER the COMMIT above failed due to the presence of the pk_t_meter_clean PK on the VIEW.

Hope this helps.

James B

Just a quick update on this - i've now managed to run through the repro steps supplied in the post above, and I get the same behaviour with it breaking source control.
I've logged a bug in our system so hopefully it can get looked at in a future release.