Vault 2014 Professional SQL Data Conflicts in Replicated Environment

[tdennis]

We are trying to work through our reseller / consultants on this but figured we might as well post it here. If anyone has any ideas or has dealt with similar problems I would appreciate any advice. Autodesk (via our reseller) just wants us to rebuild the subscribers but A) that is time consuming and will cause downtime, B) Could cause data loss if items on the subscriber aren't replicted? C) Might not fix the problem if the issues are with the publisher database! There are a couple of subscribers that do not seem to get these issues which does provide some validity to the idea of rebuilding the affected but I worry about losing data that is in fact NOT replicated. I should also note that I can go and make changes on the subs and they seem to replicate fine, but that sql additive conflict error locks up the merge agent for 5 - 10 minutes so replication can be a bit delayed.

Recently I was asked to see why a file (.dxf) was not showing up the the webclient. The file was released from Subscriber A, and Publisher saw the change, but not other subscribers. I changed the state from the publisher and then it replicated properly. This led me to look into the replication a bit and I find we are getting many conflict errors and some of the subscribers merge agents are crashing regularly and restarting. 

I have enabled verbose logging and attached one fo the files (verbose level 4) on an affected subscriber. I also found out the SQL job on the publisher for "Distribution Clean Up: AutodeskReplication" and "Replication Monitoring refresher for AutodeskReplication" are both disabled, and our 23GB vault database actually has a 10GB distribution database with 41GB.LDF file!!!!!! (I have asked autodoesk if this is desired, seems by default these are disabled and I wonder if part of the problem is the very large distribution database).

-----------------------------------------------------------------------------------------------------

Errors from the status of the subscription in SQL mgmt studio include:

The 'Microsoft SQL Server Additive Conflict Resolver' failed to resolve the conflict. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147024809)
Get help: http://help/MSSQL_REPL-2147024809

The merge process could not store conflict information for article 'MasterChanges'. Check the publication properties to determine where conflicts records are stored. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.

[Neil_Cross]

I'd have probably asked here too if I was stuck on something like this, but I'm sure you can appreciate this type of issue is really difficult to troubleshoot on a forum unless someone has had the exact same issue previously.

Couple of questions though, I guess just to see what the origins of the environment are:

Has replication ever worked seemlessly, is this a new issue in a previously fine setup? 

Was replication built using the ADMS to initiate and create the subscriptions? Or had these been manually built? 

Are all the Subscribers and Publisher at the same SQL service pack level?

Which version of Vault Pro are you on? Autodesk did release a replication related hotfix whilst the 2014 platform was the current release.  But if you have a reseller on the case I would expect them to have verified that basic step already.

What's the latency between the sites? 

[tdennis]

Hi Neil, thanks for the reply.

Yes this is tough to troubleshoot in a forum setting! I am not sure when the problems began but I think when it was first deployed it was fine. I think this problem is fairly recent. 

The replication was built using the ADMS consoles only, no manual subscription or replication work was completed at any point. The one line that really jumps out at me in the replication logs is this:

2015-01-15 14:50:51.311 OLE DB Subscriber 'WGI09FS2\AUTODESKVAULT': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}

2015-01-15 14:50:51.312 The merge process is retrying a failed operation made to article 'MasterChanges' - Reason: 'Cannot insert the value NULL into column 'EntityId', table 'Vault.dbo.IndexEntity'; column does not allow nulls. INSERT fails.'.

2015-01-15 14:50:51.313 Repl Agent Status: 3

I have no idea why the null insert is being attempted by the vault software. So far autodesk hasn't been as helpful as I'd like, with suggestions such as rebuilding 3 of our 5 subsribers that see errors, setting up email alerts so I get notified earlier if something like this happens, and sending me KB articles asking me to verify if items such as contstraint violations are occurring. BUT - a gentleman from autodesk is going to look at things via teamviewer this afternoon so hopefully that gets us somewhere and I can have a more positive experience with them. I will update this post after that is completed with any news. I suspect some conflicts are expected with replicated workgroups, mainly the entityowner table seems to get them, but this masterchanges ones I think are not normal and due to the null inserts.

Travis

[katterbauer]

HI,

i am experiencing exactly the same issues with replication with Vault Prof 2014.

What I found out (but could not resolve so far):

- MasterChanges Table has to do with changing the ownership of files/folders in Vault

- If the SQL replication has troubles and users are trying to change the ownership this is not possible due to the missing communication between the servers which leads to updates in the MasterChanges / EntityOwners / IndexEntitiy tables on the specific site but can not be replicated to the other sites/publisher

- At the point in time when replication works again it tries to update the MasterChanges table which it can not do because it expects an existing entry in the IndexEntity table. This entry is missing - this can be evaluated by querying the database manually. This is why the errors come up telling you it can not insert null values

Question is now:

- Can it be verified by Autodesk that the problem has to do with changing the ownership?

- Why does this lead to bad database entries in the vault database - which is the reason for the correct errors in the replication logs?

- Can the bad but identifiable bad database entries (the conflicts) be deleted without causing troubles (i would DELETE the MasterChanges entries manually by script and the conflict errors would be gone)?

Hope you get some answers from Autodesk today 🙂

klaus

[tdennis]

EDIT: I should also note that I checked that row on subscribers and the data matches the publisher so it seems like the data is the same on both sides?!

Hi Klaus, 

A tech from autodesk worked remotely on our servers with me for an hour yesterday gathering more information. He is going back to the developers for advice/troubleshooting.

One thing I have tried is manually resolving the conficts via the conflict viewer on the publisher, some conflicts show up where there doesn't seem to be a loser so I submit the winner (their advice) but the same data keeps coming back into the conflict table. I attached a screenshot of one example that I cleared twice and the same masterid and rowguid keeps coming back. I think those are the ones that the additive resolver is failing to resolve automatically!

I will post again when I have more info from autodesk!

Travis

[katterbauer]

Hi Travis

after analyzing the dependencies to and from the MasterChanges table I found out that is has just 1 dependency to the Master table. 

I saw that NONE of the failing MasterChanges entries has a correspondig entry in the Master table. So this row does not make sense in the vault database - you can´t find out what this "thing" is (a folder, file,...) - it is just a useless entry. 

Additionally as you also found out this is true for all the subscribers.

So I made a backup of my publisher database, stopped the database replication and deleted all those entries per sql delete script from the MasterChanges table in the database. Then I removed all the conflicts with the conflict viewer from SQL studio and activated the replication again.

All conflicts are gone since then and did not come back.

No problems so far.

Unfortunately there is no official statement from Autodesk until now if this can lead to problems...but as replication was extremly slow this was the only solution to get everything up and running again...

klaus

[johannes.bauer]

As there is an active product support case for this issue for tdennis,I just want to emphasise that any direct modification to the Vault Database on SQL Level is not supported.

Thanks.

[tdennis]

Hi Klaus,

I wanted to update this one so you could have some more information as to what the result was. An autodesk developer connected in to our system and reviewed and found the nulls in the database and had a script tool he used to clean them up. They ran into this recently in a test environment and are working on a hotfix to release for vault 2014 pro sp2. We run Sp1 so we will upgrade to sp2 and install the hotfix when available. Since they ran the cleanup tool on the DB our replication performance went from 1-3 rows/s to 30-50 rows /s.

The query to view the nulls was:

select * from MasterChanges mc
left outer join master m on m.MasterID = mc.MasterId
where m.MasterID is NULL

The exe he ran was called mcconflicts.exe:

mcconflicts -S=".\AutodeskVault" -U=xxxxx -P=xxxxxxxxx -V=Vault  (username and password left out)

The case number was 10352988: 

VP 2014 replication errors: Microsoft SQL Server Additive Conflict Resolver' failed to resolve the conflict

Ron at autodesk was the front man and did a great job getting this resolved for us, and the developer was quick to spot and fix the issues. I wish it was a bit faster to get to that point (collected a LOT of logs that I don't think really pertained, when they could have probably jumped on sooner to fix it) but overall I am happy with the support on this one!

Travis

[katterbauer]

Hi Travis,

thanks for your follow up.

Glad to hear that Autodesk used the same solution than me: deleting all masterchanges entries with no corresponding master. this gives me a little bit more safety 🙂

hopefully the hotfix really solves the issue as i see the conflicts are coming back as soon as users start to work again in vault. good news is that those conflicts can be solved by the conflict solver from SQL as long as the database replication runs (as soon as the sql server stops the conflicts stay in the queue and have to be deleted manually again).

Klaus

[paul.gunn]

Hi,

Just to clarify this, conflicts are expected on this table. The intent of the table is that changes are communicated across the various workgroups. The problem was that one type of conflict delete/update wasn't being handled appropriately (the null error). The hotfix will prevent that null error from occurring moving forward by handling this case.

Paul

[tdennis]

I just wanted to update this thread again.... so we were given a hotfix from autodesk and in order to install it we had to move to SP2, CU4 (Which I did).

Sadly today after a couple of weeks we have null values returned by the query autodesk provided to check on this issue.

select * from MasterChanges mc
left outer join master m on m.MasterID = mc.MasterId
where m.MasterID is NULL

Returns 2 records again..... which I was told should be automatically  handled now by the new hotfix. Does not appear to be working, at least in this case.

Travis

[paul.gunn]

Hi,

Are you still seeing actual end user problems? The hotfix should eliminate the problems caused by the orphaned records. So the orphaned records should now be  immaterial - we are planning on cleaning these up in the future but likely not in a hotfix.

Paul

[tdennis]

Nothing on the client side yet but from what happened last time once several of these were in the DB we saw the problem. Perhaps I misunderstood what the tech and developer were saying that they would 'handle' these conflicts via the hotfix.

The official word from autodesk is that we will still see the null values (and conflicts) in SQL and they will not get cleared, but they won't affect SQL replication?? I sort of thought the hotfix was introducing a stored procedure to handle the conflict to ensure they don't dump the nulls in the database in the first place but like I said I could be wrong.

I opened a new case referencing this one to see if I can get official word, or maybe even have someone check our DB to ensure the new stored p is actually installed/applied

[paul.gunn]

Hi,

You may see these orphaned entries in some workflows - but they should no longer create problems once the hotfix has been applied.

Paul

[katterbauer]

Hi Paul,

can you give me an info exactly which hotfix this is? I can not see a hotfix release AFTER the conversation we had and in the latest cumulative hotfix 4 it seems that it handles replication issues on items (does it also affect "our" problem?)

For now i have a job in SQL that cleares the orphaned db entries - so i don´t experience those problems any more...

klaus

[paul.gunn]

Hi,

It looks like this was fixed in hotfix DM182241 and the intent is to also include this in the next cumulative hotfix.

Paul

[tdennis]

I can confirm that hotfix number - DM182241 was planned to fix this. As I stated we still get the null orphans and Paul has stated that is expected.. this is contrary to what was communicated via the technician as he made it seem that the orphans should no longer occur in the first place so I am not sure what to think!

I know it shows Paul as an employee also of Autodesk - Paul have you checked with the dev side to see if the intention was to prevent the nulls and orphans or for it to not effect replication? In my mind right now I would have a problem with SQL replication conflicts as Publisher has the nulls, subscriber does not, so it will try to replicate them and fail due to constraints on the table PK? However I just checked all subs and they also have the 2 rows returned by the query so maybe I am overthinking this.

Travis

select * from MasterChanges mc
left outer join master m on m.MasterID = mc.MasterId
where m.MasterID is NULL

[paul.gunn]

Hi,

I am on the dev side and what I described is what was implemented. The trigger that was failing on the nulls has been rewritten to handle them appropriately. We plan on actively removing the orphan entries at some time in the future but this isn't viewed as a big or immediate concern - and not one that is appropriately handled in a hotfix. There should be no replication problems with this approach - as it is replication itself which is resulting in these entries. Hope this clarifies things.

Paul

[tdennis]

It sure does Paul, thank you very much for confirming! We had created a tool to alert me if these values showed up so that is why I was concerned, I will just disable that check and know that it won't cause our conflict resolvers to crash anymore.

Have a good day and thanks!

[katterbauer]

I can not see a hotfix  DM182241 on the Autodesk Service & Support website and also not in the Autodesk Subscription Center.

The latest hotfix that is visible for me is  DM182240 as shown in the attached screenshot (order by most recent hotfixes in version 2014).

Also a google search returned no result.

can you give me the link to this hotfix - why is it not officially listed on the website? Why am i too stupid to find it ?