Red Gate forums :: View topic - 2.2.1.23 Duplicate PK definition - SSC Inoperable on this DB
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Source Control 2
SQL Source Control 2 forum

2.2.1.23 Duplicate PK definition - SSC Inoperable on this DB

Search in SQL Source Control 2 forum
Post new topic   Reply to topic
Jump to:  
Go to page 1, 2  Next
Author Message
PDinCA



Joined: 25 Jul 2005
Posts: 509
Location: Costa Mesa, CA, USA

PostPosted: Fri Aug 05, 2011 7:48 pm    Post subject: 2.2.1.23 Duplicate PK definition - SSC Inoperable on this DB Reply with quote

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:
Code:
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.
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Mon Aug 08, 2011 6:25 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
PDinCA



Joined: 25 Jul 2005
Posts: 509
Location: Costa Mesa, CA, USA

PostPosted: Mon Aug 08, 2011 6:53 pm    Post subject: Reply with quote

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


Last edited by PDinCA on Mon Aug 08, 2011 6:57 pm; edited 1 time in total
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Mon Aug 08, 2011 6:55 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
PDinCA



Joined: 25 Jul 2005
Posts: 509
Location: Costa Mesa, CA, USA

PostPosted: Mon Aug 08, 2011 7:00 pm    Post subject: Reply with quote

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

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

Rerunning the findstr...
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Mon Aug 08, 2011 7:01 pm    Post subject: Reply with quote

Ah, no problem - hopefully it'll crop up now...
Back to top
View user's profile Send private message
PDinCA



Joined: 25 Jul 2005
Posts: 509
Location: Costa Mesa, CA, USA

PostPosted: Mon Aug 08, 2011 7:07 pm    Post subject: Reply with quote

Cropped up, yes. What to do with the info - do tell...
Code:
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?
Back to top
View user's profile Send private message
PDinCA



Joined: 25 Jul 2005
Posts: 509
Location: Costa Mesa, CA, USA

PostPosted: Mon Aug 08, 2011 7:14 pm    Post subject: Reply with quote

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...
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Mon Aug 08, 2011 7:15 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
PDinCA



Joined: 25 Jul 2005
Posts: 509
Location: Costa Mesa, CA, USA

PostPosted: Mon Aug 08, 2011 7:19 pm    Post subject: Reply with quote

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

Last edited by PDinCA on Mon Aug 08, 2011 7:31 pm; edited 1 time in total
Back to top
View user's profile Send private message
PDinCA



Joined: 25 Jul 2005
Posts: 509
Location: Costa Mesa, CA, USA

PostPosted: Mon Aug 08, 2011 7:25 pm    Post subject: Reply with quote

Eamiled to the support @ address with this thread URL as the subject.
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Mon Aug 08, 2011 7:26 pm    Post subject: Reply with quote

Thanks, I'll take a look.
Back to top
View user's profile Send private message
PDinCA



Joined: 25 Jul 2005
Posts: 509
Location: Costa Mesa, CA, USA

PostPosted: Mon Aug 08, 2011 10:11 pm    Post subject: Reply with quote

James, your directives worked fine:
Quote:
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...?
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Tue Aug 09, 2011 1:28 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
PDinCA



Joined: 25 Jul 2005
Posts: 509
Location: Costa Mesa, CA, USA

PostPosted: Tue Aug 09, 2011 7:24 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page 1, 2  Next
Page 1 of 2

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group