COU-519 - In some rare cases Courier seems to create cmsDocument entries that have duplicate newest/published flags

Created by Shannon Deminick 17 Feb 2017, 05:21:54 Updated by Shannon Deminick 08 Aug 2017, 00:28:28

Relates to: U4-9536

Subtask of: UAASSCRUM-810

I have a feeling it has something to do with caches, and potentially also the retry policies we've put in place. We had this problem before with the Core too: http://issues.umbraco.org/issue/U4-2560 however that was ultimately due to people using the APIs incorrectly and accessing the database concurrently within different processes. It could very well be something similar with Courier.

2 Attachments

Download find_duplicates_COU_519.sql

Comments

Shannon Deminick 24 Feb 2017, 07:21:36

PR to make the process or resetting newest/published flags more robust: https://github.com/umbraco/UmbracoDeploy/pull/81


Claus Jensen 27 Feb 2017, 13:55:40

@Shandem Have added a few questions on the PR - you can put it back to me after answering if you want me to merge in :)


Claus Jensen 02 Mar 2017, 08:53:25

Just to be sure I've confirmed that the Core fix will fix up the broken flags whenever an item is republished in a site, resulting in the data no longer being invalid/corrupt.

I've also tested that deploying from a broken (or a non-broken) source to a broken target, will fix up broken data in the target - resulting in valid data in the target even though it had been corrupted before the deploy.

All looks good from here! :)


Mark Bowser 08 Aug 2017, 00:08:47

Hey, we are still experiencing this problem on two near duplicate umbraco 7.5.9 sites running Courier version 3.0.6.0. Both sites are owned by the same client. At first, we thought this might be a fluke or some preexisting duplicates that we didn't notice until recently, but we've definitely confirmed a case of cmsDocument records with duplicate published flags. This most recent case happened on August 2nd, 2017.

Here is my story about what we've been experiencing. I apologize if this would be more appropriate for a question in the our.umbraco forum. I'm not completely sure where this should fall.

The first time this happened, we saw it on site A. This is the only time we've seen the problem on site 'A' so far. To remove the duplicates, I had to unpublish and then republish each problematic node. Republishing without first unpublishing did not help. In addition, every time I would deploy with courier more duplicates would show up until I fixed the problem by unpublishing and republishing. The continual duplication caused by courier deploys is unique to this first time we saw the problem. There was a mixture of nodes with duplicate newest flags and unpublished flags. This happened on site A and, with one exception, only happened on a subset of the site where the client has reported deploying hundreds of child nodes under a list view at once. They also report running into errors on occasion due to timeouts.

The 2nd time we saw this, it happened on site B which is very closely related to site A. One was created by copying the other a while back and we keep their code bases somewhat mirrored. One of our project managers was able to make the duplicates disappear by simply right clicking on the nodes and "publishing" them through the context menu. They didn't do any unpublishing or save and publishing.

The third time we saw this, it happened on site B again. This time, the client reported seeing intermittent errors saying, "Server was unable to process request. ---> Tried opening session with id ... There is already an active session with id ... and there can only be one" before they noticed the duplicates.

The fourth time was site B again. This time, I was able to resolve the duplicate flag problem by redeploying the problematic node via courier. This is new. I've tried redeploying via courier on previous occasions to no avail.

Is it possible that this issue is not entirely solved? Or have our sites gotten themselves into some crazy situation? I've included some screenshots of what the problem looks like through SQL Server Management Studio as well as the queries I'm using to detect the problems before content editors find them. Unfortunately, I've already resolved the problem and can't show you a screenshot of the duplicates in the backoffice.


Shannon Deminick 08 Aug 2017, 00:28:28

Courier is on 3.1.3 so you should definitely be using the latest version https://our.umbraco.org/projects/umbraco-pro/umbraco-courier/

In the meantime, here's a script you can use (at your own risk) to check and fix the duplicates. Obviously if you just want to output the stats before running any script that changes data, just execute the stats part.

And see related issue on core: http://issues.umbraco.org/issue/U4-9536 You should try to upgrade to the latest core version too.

-- Write stats

SELECT COUNT(*) totalpublished FROM cmsDocument WHERE published = 1
SELECT COUNT(*) totalnewest FROM cmsDocument WHERE newest = 1
SELECT COUNT(*)  totalinvalid FROM 
(SELECT cmsDocument.nodeId, MAX(p.publishedCount) publishedCount, MAX(n.newestCount) newestCount FROM cmsDocument
LEFT JOIN (SELECT nodeId, COUNT(*) as publishedCount FROM cmsDocument WHERE published = 1 GROUP BY nodeId) p ON p.nodeId = cmsDocument.nodeId
LEFT JOIN (SELECT nodeId, COUNT(*) as newestCount FROM cmsDocument WHERE newest = 1 GROUP BY nodeId) n  ON n.nodeId = cmsDocument.nodeId
WHERE (publishedCount > 1 OR newestCount > 1)
GROUP BY cmsDocument.nodeId) as invalid 

-- Create foo that has invalid data:

SELECT cmsDocument.nodeId, MAX(p.publishedCount) publishedCount, MAX(n.newestCount) newestCount 
	INTO foo 
 		FROM cmsDocument
		LEFT JOIN (SELECT nodeId, COUNT(*) as publishedCount FROM cmsDocument WHERE published = 1 GROUP BY nodeId) p ON p.nodeId = cmsDocument.nodeId
		LEFT JOIN (SELECT nodeId, COUNT(*) as newestCount FROM cmsDocument WHERE newest = 1 GROUP BY nodeId) n  ON n.nodeId = cmsDocument.nodeId
		WHERE publishedCount > 1 OR newestCount > 1
		GROUP BY cmsDocument.nodeId
		
-- Update all cmsDocument newest to zero that exist in foo

UPDATE cmsDocument
 SET newest = 0
 WHERE nodeId IN 
	(SELECT nodeId FROM foo)
GO
	
-- Update all cmsDocument newest to one where the cmsDocument version has the max PK (latest one) for the foo data

UPDATE cmsDocument
	SET newest = 1
	WHERE VersionId IN 
		(SELECT cv.VersionId
		FROM 
			(SELECT Max(cmsContentVersion.id) as maxPk, cmsContentVersion.ContentId FROM cmsContentVersion 
			WHERE cmsContentVersion.ContentId IN
				(SELECT nodeId FROM	foo)
			GROUP BY cmsContentVersion.ContentId) as cv2
		INNER JOIN cmsContentVersion cv ON cv.id = cv2.maxPk)
GO

-- Update all cmsDocument published to zero where the cmsDocument's exist in foo and the version of any published
-- row is not the latest version
UPDATE cmsDocument
	SET published = 0
	WHERE VersionId NOT IN 
		(SELECT cv.VersionId
		FROM 
			(SELECT Max(cmsContentVersion.id) as maxPk, cmsContentVersion.ContentId FROM cmsContentVersion 
			JOIN cmsDocument ON cmsDocument.versionId=cmsContentVersion.VersionId
			WHERE cmsContentVersion.ContentId IN
				(SELECT nodeId FROM	foo)
			AND cmsDocument.published=1
			GROUP BY cmsContentVersion.ContentId) as cv2
		INNER JOIN cmsContentVersion cv ON cv.id = cv2.maxPk)
	AND cmsDocument.nodeId IN (SELECT nodeId FROM foo)
GO

DROP TABLE foo
GO

-- Write stats

SELECT COUNT(*) totalpublished FROM cmsDocument WHERE published = 1
SELECT COUNT(*) totalnewest FROM cmsDocument WHERE newest = 1
SELECT COUNT(*)  totalinvalid FROM 
(SELECT cmsDocument.nodeId, MAX(p.publishedCount) publishedCount, MAX(n.newestCount) newestCount FROM cmsDocument
LEFT JOIN (SELECT nodeId, COUNT(*) as publishedCount FROM cmsDocument WHERE published = 1 GROUP BY nodeId) p ON p.nodeId = cmsDocument.nodeId
LEFT JOIN (SELECT nodeId, COUNT(*) as newestCount FROM cmsDocument WHERE newest = 1 GROUP BY nodeId) n  ON n.nodeId = cmsDocument.nodeId
WHERE (publishedCount > 1 OR newestCount > 1)
GROUP BY cmsDocument.nodeId) as invalid 


Priority: Normal

Type: Bug

State: Fixed

Assignee: Shannon Deminick

Difficulty:

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions:

Due in version: 3.0.6

Sprint: Sprint 53

Story Points: 1

Cycle: