SpyriadisNetworkBlob

Category: Umbraco

  • Umbraco Xml Exception – Error Publishing Invalid character

    A general problem that I recently encountered for the second time is an Umbraco site not being able to generate any page without displaying an error or timeout. After examining the log (umbracoLog table on SQL server) you will see the error below:

      Error Republishing: System.Xml.XmlException: '', hexadecimal value 0x1F, is an invalid character. Line 1, position 2166.
   at System.Xml.XmlTextReaderImpl.Throw(String res, String[] args)
   at System.Xml.XmlTextReaderImpl.ParseCDataOrComment(XmlNodeType type, Int32& outStartPos, Int32& outEndPos)
   at System.Xml.XmlTextReaderImpl.ParseCDataOrComment(XmlNodeType type)
   at System.Xml.XmlTextReaderImpl.ParseElementContent()
   at System.Xml.XmlLoader.LoadNode(Boolean skipOverWhitespace)
   at System.Xml.XmlLoader.LoadDocSequence(XmlDocument parentDoc)
   at System.Xml.XmlDocument.Load(XmlReader reader)
   at System.Xml.XmlDocument.LoadXml(String xml)
   at umbraco.content.LoadContentFromDatabase()

    This means that somehow (usually if you copy/paste text from other sources directly to the editor) an invalid character has been inserted on a node. That invalid character prevents the XML document created by Umbraco to be loaded and therefore no pages can be generated. To find which node has the error you can use the code below:

    DECLARE @invalidCharacter nvarchar(100) = convert(nvarchar(100), 0x1F)

SELECT * FROM [cmsContentXML]
WHERE
	xml like '%'+@invalidCharacter+'%'

    Now that you know where the problematic character is you can replace it either using Umbraco administrator or using the T-SQL below:

    DECLARE @invalidCharacter nvarchar(100) = convert(nvarchar(100), 0x1F)

UPDATE [cmsPropertyData] SET
	[dataNvarchar] = REPLACE(cast([dataNvarchar] as nvarchar(max)),@invalidCharacter,''),
	[dataNtext] = REPLACE(cast([dataNtext] as nvarchar(max)),@invalidCharacter,'')
WHERE
	[dataNvarchar] like '%'+@invalidCharacter+'%' OR
	[dataNtext] like '%'+@invalidCharacter+'%'

UPDATE [cmsContentXML] SET
	[xml] = REPLACE(cast([xml] as nvarchar(max)),@invalidCharacter,'')
WHERE
	[xml] like '%'+@invalidCharacter+'%'

    Depending on your error you need to change the hexadecimal value that creates the problem. In my case and the examples above I have used “0x1F“. This value is at the first line of the error message “.. , hexadecimal value 0x1F, …”.

     

  • Umbraco Clear Old Document Versions To Decrease Database Size And Improve Performance

    Recently I had a problem with an Umbraco 4.7.1 installation that had became 10GB in SQL Server and had about 20000 published pages. Since there is no way to do something like this from the umbraco administration panel I had to create a T-SQL to clear the database.

    DECLARE @createdDate Datetime = '2012-03-01'

DELETE FROM cmsPropertyData WHERE
	versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
	contentNodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)
DELETE FROM cmsPreviewXml WHERE
	versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
	nodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)
DELETE FROM cmsContentVersion WHERE
	versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
	ContentId  IN (SELECT DISTINCT nodeID FROM cmsDocument)
DELETE FROM cmsDocument WHERE
	versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
	nodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)

    Warning if you do not have any TSQL experience be careful because you could easily delete all the data in your database. Always take a backup before making any changes this way.

  • Umbraco Fix – Empty recycle bin using SQL if backend fails

    Updated thanks to jeffreypriebe

    -- Uncomment below to verify the number of nodes returned is the same as the number of nodes that is in the Recycle Bin
-- SELECT * FROM umbracoNode WHERE path LIKE '%-20%' AND id != -20

--Setup the temporary table
CREATE TABLE #temp ([id] int);
INSERT #temp select id from umbracoNode where path like '%-20%' and id!=-20

-- Delete all 'related' nodes and table contents...
DELETE FROM cmsPreviewXml WHERE nodeId IN (SELECT id FROM #temp)
DELETE FROM cmsContentVersion WHERE contentId IN (SELECT id FROM #temp)
DELETE FROM cmsDocument WHERE nodeId IN (SELECT id FROM #temp)
DELETE FROM cmsContentXML WHERE nodeId IN (SELECT id FROM #temp)
DELETE FROM cmsContent WHERE nodeId IN (SELECT id FROM #temp)
DELETE FROM cmsPropertyData WHERE contentNodeId IN (SELECT id FROM #temp)
DELETE FROM umbracoUser2NodePermission WHERE nodeId IN (SELECT id FROM #temp)
DELETE FROM umbracoRelation  where parentId in (select id from #temp) or childId in (select id from #temp)

-- remove fk constraint so that umbracoNode can delete
ALTER TABLE umbracoNode DROP CONSTRAINT FK_umbracoNode_umbracoNode
-- delete the XML nodes...
DELETE FROM umbracoNode WHERE id in (SELECT id FROM #temp)
-- re add fk constraint to umbracoNode
ALTER TABLE umbracoNode WITH NOCHECK ADD CONSTRAINT FK_umbracoNode_umbracoNode
FOREIGN KEY (parentId)
REFERENCES umbracoNode (id)

--Cleanup temp
DROP TABLE #temp;

  • Umbraco Fix – Content tree not loaded on very large sites

    Recently one of my sites that was created using umbraco has started to behave somewhat strange. There was nothing that seem to be wrong and all the errors produced were:
     No Document exists with Version ‘00000000-0000-0000-0000-000000000000’

    As it turned out the problem was due to extreme delay in the SQL Server. What made me look  into that was that the tree in the content area of the backend was not loading for the folder with all the articles in it.

    Also it seemed that the problem might have originated because I also use DateFolders extention. Adding the index below dramatically increased the speed of the sql that runs to generate the content trees (from 32sec to less that 2sec for more than 7000 records) and it seems to resolved all my other problems as well.

    /****** Object:  Index [IDX_cmsDocument_nodeId_versionId]    Script Date: 09/20/2011 13:13:20 ******/
CREATE NONCLUSTERED INDEX [IDX_cmsDocument_nodeId_versionId] ON [cmsDocument]
(
	[nodeId] ASC,
	[versionId] ASC
)
INCLUDE ( [published]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
    S3GCC5KF6B2M

  • Umbraco Fix – No Document exists with Version

    Recently I had problems with a site I have created using umbraco. The problem was that for some reason in the version control of the application there were many orphan versions that where visible on the cmsContentVersion table but not on the cmsDocument. You can find those using the query below:

    SELECT * FROM cmsContentVersion
WHERE
	cmsContentVersion.VersionId NOT IN (SELECT VersionId FROM cmsDocument) AND
	cmsContentVersion.ContentId IN (SELECT nodeId FROM cmsDocument)

    Deleting those record fixed my problem and the problematic nodes where shown again in both the content tree view and edit view in umbraco CMS. To delete those versions in your database use the code below:

    DELETE FROM cmsContentVersion
WHERE
	cmsContentVersion.VersionId NOT IN (SELECT VersionId FROM cmsDocument) AND
	cmsContentVersion.ContentId IN (SELECT nodeId FROM cmsDocument)

  • XSLTSearch extension error when saving XSLT file in Umbraco 4.6 beta

    If you have problems when using XSLTsearch extension for umbraco on version 4.6 installation the easiest way to resolve it is to simply complile the .cs file to a .dll using Visual Studio and then declare it as an xslt extension using the line below:

    <ext assembly="XSLTsearch" type="PS.XSLTsearch" alias="PS.XSLTsearch" />

    To make this even easier for you here is the .dll you need.