I'm trying to update a large volume of content via SQL but I'm struggling to get the site to fully pick up the update, for some reason there still seems to be a cached version that is kicking around that doesn't get updated until someone goes into the admin and republishes the page.
I'm updating the current record in the umbracoPropertyData table, linking to the umbracoContentVersion table to ensure I have the latest version.
Then I'm clearing down the cmsContentNu table to remove the database cache.
Then I'm stopping the site and removing all files from the "App_Data\TEMP" directories including the ExamineIndex, DistCache and NuCache.
I'm then restarting the site and rebuilding / reloading the caches using the Umbraco tools in "Published Status".
I can see that the changes are picked up correctly in the Umbraco admin and also from the Examine Indexes when I perform a search.
However when I go directly to the pages they are still pulling the old content and not the updated version even though I can see it in the admin page, there are no indicators in the admin that the page has saved changes that are not published either.
It sounds like you're updating the current version but not the published version. The umbracoDocumentVersion table will tell you which is the published version. If you look in cmsContentNu, you should find that the rows with published set to 0 include your changes, but the rows with published set to 1 don't.
Effectively you're doing the same thing as making changes and saving without publishing. But the admin section doesn't show that you have unpublished changes because it's looking at the edited flag in umbracoDocument rather than looking for differences in the values in umbracoPropertyData.
Not sure that the umbracoDocumentVersion table is what I need, I ran the SQL below and it returns no records, and looking at the foreign key constraint on the table this is the correct link.
SELECT *
FROM [dbo].[umbracoContentVersion] cv
INNER JOIN [dbo].[umbracoDocumentVersion] dv
ON dv.id = cv.id
WHERE cv.[current] = 1
and dv.published = 1
I'm updating the umbracoPropertyData where the current flag is published when linked to the umbracoContentVersion table.
You shouldn't find any versions which are both current and published, so it's OK for that SQL to return no results. Whenever you publish the current version of a document, that version is marked as published and a new version is created to become the current version.
If you change it to WHERE cv.[current] = 1 OR dv.published = 1, that should get you both versions that you want to update for each document.
Thanks Steve, can see what you mean now and this fixed the issue I was having.
For anyone else here is some SQL to help find the issues:
SELECT *
FROM [dbo].[umbracoContentVersion] cv
INNER JOIN [dbo].[umbracoDocumentVersion] dv
ON dv.id = cv.id
INNER JOIN [dbo].[umbracoPropertyData] pd
ON cv.id = pd.versionId
LEFT JOIN [dbo].[cmsTemplate] tp
ON dv.templateId = tp.nodeId
WHERE templateId IN (<templateId>)
AND (cv.[current] = 1 OR dv.published = 1)
--AND propertyTypeId IN (<propertyTypeId>)
--and cv.nodeId = <NodeId>
AND propertyTypeId = <propertyTypeId>
AND varcharValue LIKE '%"<varcharValue>"%'
Mass Content Update via the database
I'm trying to update a large volume of content via SQL but I'm struggling to get the site to fully pick up the update, for some reason there still seems to be a cached version that is kicking around that doesn't get updated until someone goes into the admin and republishes the page.
I'm updating the current record in the umbracoPropertyData table, linking to the umbracoContentVersion table to ensure I have the latest version.
Then I'm clearing down the cmsContentNu table to remove the database cache.
Then I'm stopping the site and removing all files from the "App_Data\TEMP" directories including the ExamineIndex, DistCache and NuCache.
I'm then restarting the site and rebuilding / reloading the caches using the Umbraco tools in "Published Status".
I can see that the changes are picked up correctly in the Umbraco admin and also from the Examine Indexes when I perform a search.
However when I go directly to the pages they are still pulling the old content and not the updated version even though I can see it in the admin page, there are no indicators in the admin that the page has saved changes that are not published either.
Any points or suggestions would be appreciated.
Thanks
It sounds like you're updating the current version but not the published version. The
umbracoDocumentVersiontable will tell you which is the published version. If you look incmsContentNu, you should find that the rows withpublishedset to 0 include your changes, but the rows withpublishedset to 1 don't.Effectively you're doing the same thing as making changes and saving without publishing. But the admin section doesn't show that you have unpublished changes because it's looking at the
editedflag inumbracoDocumentrather than looking for differences in the values inumbracoPropertyData.Hi Steve,
Not sure that the
umbracoDocumentVersiontable is what I need, I ran the SQL below and it returns no records, and looking at the foreign key constraint on the table this is the correct link.I'm updating the
umbracoPropertyDatawhere the current flag is published when linked to theumbracoContentVersiontable.Or maybe I've got the linking wrong.
You shouldn't find any versions which are both current and published, so it's OK for that SQL to return no results. Whenever you publish the current version of a document, that version is marked as published and a new version is created to become the current version.
If you change it to
WHERE cv.[current] = 1 OR dv.published = 1, that should get you both versions that you want to update for each document.Thanks Steve, will try that and see.
Thanks Steve, can see what you mean now and this fixed the issue I was having.
For anyone else here is some SQL to help find the issues:
is working on a reply...