database inconsistency - check program?

[matzen]

Hi,

we have messages like "FailObjPathInvalid", "FailIndeterminateCheckoutStatus" and "FailIndeterminateCheckoutStatus" in out vault server log. Clients are not able to login any more when the client cache is deleted and without deleting the client cache they cannot check out or add any file.

folder security is OFF

We have recently added a new repository with folder security ON. That repository seems not to be affected.

Is there a way to check the consistancy of the repository data? Is there a documentation of the database structure so that I can do some sqls to check what's wrong in our repository?

the size of the database is about 5 GB and the affected repository does contain about 90% of the data. Unfortunately our Spain programming crew depends heavily on vault, so we need to make it work again quickly.

I'm currently restoring a backup of the database, but on of our programmers told me that he did have a problem with that version, too. So I need a way to ensure that the database is ok before telling them to check in the new files.

CU,
Sven Erik Matzen

[jclausius]

Have you upgraded to Vault 3.0.7?

Also, let's take a look at things from within the database. Can you run the following:

1) SELECT repid, name, currenttxid FROM sgvault.dbo.tblrepositories

2) SELECT x.repid, x.txid FROM sgvault.dbo.tblfulltreerevisionfolders x WHERE EXISTS ( SELECT y.repid FROM sgvault.dbo.tblrepositories y WHERE y.repid = x.repid AND y.currenttxid = x.txid )

Send this by email if you would like to keep the information private.

[matzen]

Code: Select all

repid	name	currenttxid
1	Prodanet	82900
2	WebSites	82840


repid	txid
2	82840

[jclausius]

Its probably safe to assume your server is not on Vault 3.0.7.

Let's take a look at getting the repository back into shape (repository #1 is missing in the second query). We'll need to move the repository to the last known tree throw SQL.

I'll need to gather some information first:

DECLARE @repid [bigint];
SET @repid = 1;

SELECT MAX(txid) FROM sgvault.dbo.tblfulltreerevisionfolders WHERE repid = @repid;

SELECT t.* FROM sgvault.dbo.tbltransactions t WHERE t.repid = @repid AND t.txid >=
(SELECT MAX(txid) FROM sgvault.dbo.tblfulltreerevisionfolders WHERE repid = @repid);

[matzen]

Code: Select all

SELECT MAX(txid) FROM sgvault.dbo.tblfulltreerevisionfolders WHERE repid = @repid; 

returns:
82885

Code: Select all

SELECT t.* FROM sgvault.dbo.tbltransactions t WHERE t.repid = @repid AND t.txid >= 
(SELECT MAX(txid) FROM sgvault.dbo.tblfulltreerevisionfolders WHERE repid = @repid);

returns:

txid	repid	userid	txbegin	txend
82885	1	12	2005-06-13 12:34:19.967	2005-06-13 12:34:24.420
82887	1	6	2005-06-13 12:34:42.590	2005-06-13 12:34:44.013
82888	1	12	2005-06-13 12:36:38.903	2005-06-13 12:36:41.280
82889	1	57	2005-06-13 12:48:56.420	2005-06-13 12:48:57.547
82890	1	57	2005-06-13 12:49:07.810	2005-06-13 12:49:08.577
82891	1	6	2005-06-13 13:02:37.420	2005-06-13 13:02:40.140
82892	1	6	2005-06-13 14:33:19.187	2005-06-13 14:33:35.513
82893	1	3	2005-06-13 14:37:48.873	2005-06-13 14:37:58.170
82894	1	3	2005-06-13 14:38:03.187	2005-06-13 14:38:04.873
82895	1	12	2005-06-13 14:41:13.903	2005-06-13 14:41:15.217
82897	1	12	2005-06-13 15:31:06.043	2005-06-13 15:31:07.107
82899	1	6	2005-06-13 15:38:41.623	2005-06-13 15:38:42.890
82900	1	12	2005-06-13 16:06:30.763	2005-06-13 16:06:55.280

[/code]

[jclausius]

OK. Let's get an idea of files were changed. You don't need to report this back, but use this information to inform users they will need to re-commit their changes.

DECLARE @lasttxid [bigint], @repid [int];
SELECT @repid = 1, @lasttxid = 82885;

SELECT v.objid, u.login, n.name, t.*, td.* FROM sgvault.dbo.tbltransactions t
INNER JOIN sgvault.dbo.tbltransactiondetails td ON (td.txid = t.txid)
INNER JOIN sgvault.dbo.tblfsobjectversions v ON (v.objverid =
td.itemobjverid) INNER JOIN sgvault.dbo.tblfsobjectversionnames n ON (n.vernameid = v.vernameid) INNER JOIN sgvault.dbo.tblusers u ON (u.userid = t.userid) WHERE t.repid = @repid and t.txid > @lasttxid;

[EDIT] - actually, keep track of this list. We will need the v.objid column information at a later date.

[jclausius]

If you want to find these items in the tree, you could take a look at the objid column from above. Using this info, you should be able to find this in the tree:

DECLARE @repid [int], @lasttxid [bigint];
SELECT @repid = 1, @lasttxid = 82885;

DECLARE @@t TABLE
( treelevel int not null,
pph int not null,
objverid bigint not null,
objid bigint not null,
objversion bigint not null,
name nvarchar(256) not null,
objprops smallint not null,
pinnedfromobjverid bigint not null,
fph int not null,
fullpath nvarchar(1024) null,
primary key (treelevel, pph, fph, objid)
)

INSERT INTO @@t (treelevel, pph, objverid, objid, objversion, name,
objprops, pinnedfromobjverid, fph) SELECT treelevel, parentpathhash,
objverid, objid, objversion, LOWER(name), objprops, pinnedfromobjverid,
fullpathhash FROM sgvault.dbo.ufngettreestructure(@repid, @lasttxid, default)

DECLARE @@treelevel int, @@rowsaffected int

SET @@treelevel = 0
UPDATE @@t SET fullpath = name WHERE treelevel = @@treelevel SET
@@rowsaffected = @@ROWCOUNT WHILE ( @@rowsaffected > 0 ) BEGIN
UPDATE t SET t.fullpath = prev.fullpath + N'/' + t.name
FROM @@t t INNER JOIN
@@t prev ON (prev.treelevel = @@treelevel) AND
(prev.fph = t.pph)
WHERE (t.treelevel = (@@treelevel + 1))

SELECT @@rowsaffected = @@ROWCOUNT, @@treelevel = @@treelevel +
1 END -- WHILE

-- you could join on objid here to find the items affected
SELECT * FROM @@t ORDER BY treelevel, pph, name;

[matzen]

the first statement "SELECT v.objid, u.login, t.*, td.* FROM sgvault.dbo.tbltransactions t ..." did not return any rows.

the second one "DECLARE @repid [int], @lasttxid [bigint]; ..."
did return 22280 rows

[jclausius]

I had a typo in the first query, can you run that one again.

[matzen]

ok, now I have 38 changes listed

[jclausius]

OK. Let's get started. In case you do not have one, please make a backup of your existing database. If things go wrong, I want to make sure we have a backup.

After the backup, run these statements to correct the repository:

DECLARE @repid [int], @lasttxid [bigint], @objid [bigint]
SELECT @repid = 1, @lasttxid = 82885;

-- object id adjustment list
CREATE TABLE #objidlist (objid bigint NOT NULL);

INSERT INTO #objidlist (objid)
SELECT DISTINCT v.objid FROM sgvault.dbo.tblfsobjectversions v
WHERE EXISTS
(SELECT o.objid FROM sgvault.dbo.tblfsobjects o
WHERE o.repid = @repid AND v.objid = o.objid)
AND (v.origintxid > @lasttxid);

UPDATE sgvault.dbo.tblrepositories SET currenttxid = @lasttxid
WHERE repid = @repid;

DELETE FROM sgvault.dbo.tbltransactiondetails WHERE txid
IN (SELECT txid FROM sgvault.dbo.tbltransactions WHERE
repid = @repid AND txid > @lasttxid);

DELETE FROM sgvault.dbo.tbltreerevisionfolders WHERE
EXISTS (SELECT t.txid FROM sgvault.dbo.tbltransactions t
WHERE t.repid = @repid AND t.txid > @lasttxid
AND sgvault.dbo.tbltreerevisionfolders.txid = t.txid);

DELETE FROM sgvault.dbo.tblcheckoutlistitems
WHERE EXISTS
( SELECT c.colistid FROM sgvault.dbo.tblcheckoutlists c
INNER JOIN sgvault.dbo.tblfsobjectversions v ON
(v.objverid = sgvault.dbo.tblcheckoutlistitems.objverid)
INNER JOIN sgvault.dbo.tblfsobjects o ON (o.repid = @repid AND
o.objid = v.objid)
AND v.origintxid IN
(SELECT txid FROM sgvault.dbo.tbltransactions WHERE
repid = @repid AND txid > @lasttxid)
WHERE c.repid = @repid AND c.colistid =
sgvault.dbo.tblcheckoutlistitems.colistid);

DELETE FROM sgvault.dbo.tblfsobjectversions WHERE origintxid
IN (SELECT txid FROM sgvault.dbo.tbltransactions WHERE repid = @repid
AND txid > @lasttxid);

DELETE FROM sgvault.dbo.tblfsobjectversions
WHERE EXISTS (SELECT o.objid FROM sgvault.dbo.tblfsobjects o
WHERE o.repid = @repid AND
sgvault.dbo.tblfsobjectversions.objid = o.objid)
AND sgvault.dbo.tblfsobjectversions.origintxid IN
(SELECT txid FROM sgvault.dbo.tbltransactions WHERE
repid = @repid AND txid > @lasttxid);

DELETE FROM sgvault.dbo.tbltransactions WHERE repid = @repid AND
txid > @lasttxid;

DECLARE c CURSOR FOR SELECT objid FROM #objidlist;
OPEN c;

FETCH NEXT FROM c INTO @objid;
WHILE ((@@ERROR = 0) AND (@@FETCH_STATUS = 0))
BEGIN
UPDATE sgvault.dbo.tblfsobjects SET sgvault.dbo.tblfsobjects.lastobjversion = (SELECT MAX(v.objversion) FROM sgvault.dbo.tblfsobjectversions v WHERE v.objid = @objid) WHERE sgvault.dbo.tblfsobjects.repid = @repid AND sgvault.dbo.tblfsobjects.objid = @objid;
FETCH NEXT FROM c INTO @objid;
END
CLOSE c;
DEALLOCATE c ;

SELECT objid FROM #objidlist;

DROP TABLE #objidlist;

[jclausius]

Assuming the query runs correctly, try out a client. Can you now pull up the repository?

Next, please make sure you download / install Vault 3.0.7 on your Vault server. This upgrade will make sure the situation which arose today does not save things to the database.

Once Vault 3.0.7 is running on the server, you will need to have everyone who accesses the Prodanet repository to remove CacheMember_Repository and CacheMember_LastStructureGetTime from users %APPDATA%\SourceGear\Vault_1\Client\{GUID-FROM-DATABASE}\{user-name}

where {GUID-FROM-DATABASE} is
SELECT uniquerepid FROM sgvault.dbo.tblrepositories WHERE repid = 1

and {user-name} is their vault login.


Now, regarding the repository changes from today. Using the info of the list of object ids, those files within the tree need to be re-updated. If you need help getting the path from the tree with that list, please let me know.

Getting back to the issue, assuming these files still exist:
1) Have the owners copy their local file from the working folder to another directory.

2) Next, do a GET and choose to OVERWRITE the file ( hold down shift if you have this dialog disabled ).

3) Check out the file ( it just have a "blank" status )

4) Copy the file from step 1 over the file from step 2

5) Show the differences, make sure the file looks correct.

6) Now commit the changes.


That should solve this issue. Let me know if I can be of more assistance.