Boris Masis

Current versions of Umbraco appear to have a bug that may cause corruption of publishing times and cause documents to re-publish themselves many times over. This can lead to an extremely large CMSPropertyData table and the need for frequent application pool recycles. See http://forum.umbraco.org/yaf_postst4699p2_Extremely-large-Umbraco-database-55GB-table-cmsPropertyData-contains-over-135-million-records.aspx and http://forum.umbraco.org/yaf_postst6564_305-Database-Huge–cmsPropertyData–392MB.aspx for a discussion of the issue.

We’ve done several things to control this issue, though a true fix has yet to be developed:

1. We applied the suggested fix of changing the umbraco.presentation.cache.CacheRefresher() constructor (this seemed to have no effect):

   public CacheRefresher() { if (System.Web.HttpContext.Current != null) { this.Url = "http://" + System.Web.HttpContext.Current.Request.ServerVariables["SERVER_NAME"] + "/umbraco/webservices/cacheRefresher.asmx"; } }

   public CacheRefresher() { if (System.Web.HttpContext.Current != null) { this.Url = "http://" + System.Web.HttpContext.Current.Request.ServerVariables["SERVER_NAME"] + "/umbraco/webservices/cacheRefresher.asmx"; } }

2. We removed the future publishing functionality from the UI (umbraco/presentation/umbraco/editContent.aspx.cs in umbraco 3.03):

   //dpRelease.ID = "releaseDate"; //dpRelease.DateTime = _document.ReleaseDate; //dpRelease.ShowTime = true; //publishProps.addProperty(ui.Text("content", "releaseDate", base.getUser()), dpRelease); //dpExpire.ID = "expireDate"; //dpExpire.DateTime = _document.ExpireDate; //dpExpire.ShowTime = true; //publishProps.addProperty(ui.Text("content", "expireDate", base.getUser()), dpExpire);

   //dpRelease.ID = "releaseDate"; //dpRelease.DateTime = _document.ReleaseDate; //dpRelease.ShowTime = true; //publishProps.addProperty(ui.Text("content", "releaseDate", base.getUser()), dpRelease); //dpExpire.ID = "expireDate"; //dpExpire.DateTime = _document.ExpireDate; //dpExpire.ShowTime = true; //publishProps.addProperty(ui.Text("content", "expireDate", base.getUser()), dpExpire);

3. We commended out the publishing timer which seems to have stopped the growth without adverse effects (thought I imagine that scheduled publishing would no longer work) (/umbraco/presentation/requestModule.cs in umbraco 3.03)

   //publishingTimer = // new Timer(new TimerCallback(publishingService.CheckPublishing), HttpApp.Context, 600000, 60000);

   //publishingTimer = // new Timer(new TimerCallback(publishingService.CheckPublishing), HttpApp.Context, 600000, 60000);

The author of ClientTools promises to release a new version which should be able to cleanup the large number of item revisions in the database.

Stored procedures useful for troubleshooting this issue include:

• find items with the most revisions:

  SELECT contentNodeId, COUNT(contentNodeId) FROM cmsPropertyData GROUP BY contentNodeId ORDER BY COUNT(contentNodeid) DESC

  select contentNodeId, count(contentNodeId) from cmsPropertyData group by contentNodeId order by count(contentNodeid) desc

• manually delete an item (if you can’t find the item in the UI using http://site/umbraco/editContent.aspx?id=IDHERE):

  DELETE FROM cmsPropertyData WHERE contentNodeId = '4023'

  delete from cmsPropertyData where contentNodeId = '4023'

• find lost documents:

  SELECT * FROM umbracoNode WHERE nodeObjectType = 'c66ba18e-eaf3-4cff-8a22-41b16d66a972' AND ID NOT IN (SELECT Nodeid FROM cmsDocument)

  SELECT * FROM umbracoNode WHERE nodeObjectType = 'c66ba18e-eaf3-4cff-8a22-41b16d66a972' AND ID not in (select Nodeid from cmsDocument)

• find items scheduled to publish:

  SELECT DISTINCT nodeId, level, sortOrder FROM cmsDocument INNER JOIN umbracoNode ON umbracoNode.id = cmsDocument.nodeId WHERE newest = 1 AND NOT releaseDate IS NULL AND releaseDate < = getdate() ORDER BY level, sortOrder

  select distinct nodeId, level, sortOrder from cmsDocument inner join umbracoNode on umbracoNode.id = cmsDocument.nodeId where newest = 1 and not releaseDate is null and releaseDate < = getdate() order by level, sortOrder