BUG: No Right to Access Repository

[Dathan]

I am getting a data issue using 4.0.6 (this is a 4.0.4 upgraded repository) and had the issue once on 4.0.4 as well. When logging in to the repository, the client is telling me I have no right to access the repository. SQL Profiler on the sgvault db is showing the following error response during the login process on a call to spGetTreeStructure:

Msg 2627, Level 14, State 1, Procedure spgettreestructure, Line 31
Violation of PRIMARY KEY constraint 'PK__ufngettreestruct__414EAC47'. Cannot insert duplicate key in object 'dbo.@resulttree'.
The statement has been terminated.

The error is being caused by a call inside the sp to ufnGetTreeStructure, which appears to be defining a table variable called @resultTree with a primary key and then inserting data that violates the key. I can't tell why because both the sp and the fn are encrypted.

However, the respository is down and it looks like I need to go back now and restore a backup ( not nice to have to do on your source code repository!).

Is there any other way to resolve this so that the repository does not need to be rolled back?

Thanks,
Dathan

[jclausius]

The source code within the repository is fine.

A couple of questions. Was this repository made with Vault 3 or Vault 4? Did it contain any "shared" folders?

We'll need to clean up the repository structure on disk to allow it to build correctly. This can be done with some inspection and a couple of iterations w/ support. You can mail us at support at sourcegear dot com if you want to tackle the problem in this manner.

[Dathan]

The repository was created with v3 (I think, definitely not v4). And there are lots of shared folders.

I will email and get the process started.

Regards,
Dathan

[ierceg]

Hi,

We are having the same issue. Has the status of this bug been confirmed and will it be fixed in the next release? Also, any recommendations on how to avoid it in the future? Our repository was created with V4 and it had a lot of shared folders.

Thanks,
Ivan

[Dathan]

Support seemed able to fix it given enough time to locate the source of the issue, but for comfort we eventually decided to start a new repository. It's supposedly a bug in v3.5.2, but the fact that your repository is created in v4 challenges this (and I was also somewhat suspicious that v4 also had or carried forward the issue since we didn't see it until v4.0.4 ourselves).

What version of 4 did you start on and what version are you on now?

[jclausius]

We believe the problem from the original poster was with a repository created in Vault 3.x. There were some issues if moves, renames and delete items were executed on parents of shared folders.

If you've always been on Vault 4.x, then it may not be related to this same issue.

Can you see if you have any information in the
Vault Server Log. If you have sensitive data, you can send this to support at sourcegear dot com.

[ierceg]

We started on 4.0.5.15922 and we are still on 4.0.5.15922. There were no changes on the server or in the repository - it just stopped working over the weekend.

[ierceg]

Jeff,

This is what I get in the log each time I try to access the repository from admin web client:

----1/28/2008 12:48:23 PM <* REMOVED CONNECTION INFO *>--SSL Enabled System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK__ufngettreestruct__451F3D2B'. Cannot insert duplicate key in object 'dbo.@resulttree'.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at VaultServiceSQL.VaultSqlSCC.GetRepositoryInfoAll(VaultSqlConn conn, Int32 nRepID, VaultRepositoryInfo& vrInfo) at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at VaultServiceSQL.VaultSqlSCC.GetRepositoryInfoAll(VaultSqlConn conn, Int32 nRepID, VaultRepositoryInfo& vrInfo)
----1/28/2008 12:48:23 PM <* REMOVED CONNECTION INFO *>--SSL Enabled VaultLib.VaultDbException: Error in the application.
at VaultServiceAPILib.VaultAdminServiceAPI.GetRepositoryInfoAll(Int32 nRepID, VaultRepositoryInfo& vrInfo)
at VaultService.Repository.Page_Load(Object sender, EventArgs e)
at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at VaultServiceAPILib.VaultAdminServiceAPI.GetRepositoryInfoAll(Int32 nRepID, VaultRepositoryInfo& vrInfo)
at VaultService.Repository.Page_Load(Object sender, EventArgs e)
at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Access to non-repository specific parts of administration interface works fine.

Thanks,
Ivan

[Dathan]

Hi Ivan,

Jeff's right, it could be something else, but that's the precise error we received as well. If you go through support (i.e. instead of going through this post), can you post back the end result? The issue for us was multiple entries in the DB relating to certain folders that had been shared/moved/renamed.

FWIW we are on 4.0.6 now, but are treading carefully as our 'repository refresh' cost us about 6 months of history (we can still go back to an old backup, but it's obviously not very convenient). According to SG there is not a concrete issue known to them in 4.x on this, so we are still watching very closely for issues (sharing is critical to our development method), so again, please post back what you find.

Good luck... D

[ierceg]

Dathan, thank you for all the info. I have already sent email to support so we'll see... I'll post here how it all goes.

Thanks again,
Ivan

[Dathan]

Ivan,

Happy to be of help, and for the record: you are in great hands. Jeff and his team provided an outstanding level of support to us through this issue.

D

[jclausius]

Grrr... "PRIMARY KEY constraint 'PK__ufngettreestruct__451F3D2B'. Cannot insert duplicate key in object 'dbo.@resulttree'." This problem looks similar to Dathan's issue (which I thought was fixed in a maintenance release of Vault 3.5).

ierceg, we'll pick this up in the SourceGear Support tracking system.

I'll be in contact shortly.

[Dathan]

Jeff,

An idea: I am going to configure a job to run on our server every minute to run the query that generates the error (it's just a read if I recall), along with a SQL Profiler job that records on a rolling log.

If the error occurs, the job will lock down the server and send me an email. At that point, we should know exactly the transaction that caused the issue from the combination of the web logs, sgvault logs, profiler logs, and the user who calls to complain that the server is down. I can then contact you with more detail.

Does this help, and do you have any concerns with this approach?

D

[jclausius]

Dathan:

Yes, that would work. It might have a little performance hit as the READ locks will be applied while the query runs. Also, you'll need to make sure you use -1 for the transaction id to always build the latest tree.

I've reviewed the code changes made back on Vault 3 within the Vault 4 code base. The code is correct, and that part of it (a move or rename of a folder of shared folders) is not the issue here.

I'm assuming you have not encountered anything like this since? Your approach should work.

Another thing that may have happened is a case where a "Critical Error" log message occurred in the server's log file. This was fixed within Vault 4.0.6. Do you happen to have some old logs I could search for this message?

[jclausius]

I have a theory I'm working on. If there was a "Critical Error in the Application" error in your logs and you were on "pre-Vault 4.0.6", theoretically, there is a way to create a repository state which could cause this behavior.

But I need a log with that error message to confirm.