how to clear cache after updating data in the sql database

Go to solution

Press Ctrl / CMD + C to copy this to your clipboard.

Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at

rboogaerts 11 posts 132 karma points

Apr 12, 2017 @ 16:15
0

How to clear cache after updating data in the SQL database
I had to replace some string in all the RTE content of the whole website. My first thought was to do this with a SQL query:
```
UPDATE pd set pd.dataNtext = REPLACE(CONVERT(VARCHAR(MAX), pd.dataNText),'someString','someOtherString')
  FROM cmsPropertyData pd join cmsDocument doc on pd.versionId = doc.versionId where 
  dataNtext LIKE '%someString%' AND doc.newest = 1
```
The script worked and in /umbraco the data is all good. But when I look at the website the old data is still there. I cleared the cache and recycled the app pool but non of that worked. The only thing that seems to update the cache is republish the node.

Is there another way to update this? I don't like republishing the whole website, there are quite a few nodes.

EDIT:

I don't know exactly how the Umbraco backend works but my guess is that the old data is still in the cmsContentXml table. This table get's updated during a publish.
Copy Link
Carlos Mosqueda 244 posts 435 karma points

Apr 12, 2017 @ 17:20

1

Hi @rboogaerts,

You will most likely have to republish the pages or the site. From my understanding, the cache is local in Umbraco with a new publish and that is how it is updated.

Another option you could try is to "touch" the web.config. Add a space someplace after a closing tag, that typically has worked for me in the past when cache has been an issue. with .Net projects.

Copy Link
rboogaerts 11 posts 132 karma points

Apr 12, 2017 @ 18:48

0

Touching the web.config will result in an app pool recycle which I already tried. It's not in memory cache which leads to this problem. I don't know exactly how the Umbraco backend works but my guess is that the old data is still in the cmsContentXml table. This table get's updated during a publish.

Republishing the website is an option which will certainly work but i'm searching for a better way to do this. Republishing all nodes is very expensive and time consuming.

Thanks for your input.

Copy Link
Carlos Mosqueda 244 posts 435 karma points

Apr 12, 2017 @ 19:20

1

Hmm, I would have thought the application pool trick would have pulled a fresh batch to re-index. You could try Dave's solution below or the only other thing I can think of is to Rebuild the Examine Indexer. In Developer there is the tab for 'Examine Management'. This re-indexes the node, but again, if the cmsContentXml table is not updated, it may just re-index the current data. It is worth a shot. Just rebuild everything in the Indexers.

Dave mentioned in this post, https://our.umbraco.org/forum/extending-umbraco-and-using-the-api/76782-refresh-xml-of-content-node-and-update-cache

You could try to use the Content Service if the Examine rebuilding doesn't work.

Copy Link
rboogaerts 11 posts 132 karma points

Apr 13, 2017 @ 11:19

0

I solved the problem by using the contentService to only publish the changed nodes.

Thanks!

Copy Link
Dave Woestenborghs 3504 posts 12135 karma points MVP 9x admin c-trib

Apr 12, 2017 @ 18:04

1

Hi,

Did you have a look at this package : https://our.umbraco.org/projects/backoffice-extensions/find-and-replace/

This let's you do this without running any db scripts.

Dave

Copy Link
rboogaerts 11 posts 132 karma points

Apr 13, 2017 @ 11:22

0

Hi Dave

For this specific problem the package wouldn't solve it. It looks like an awesome package tho.

I solved the problem by using the contentService to only publish the changed nodes.

Thank you for your input.

Copy Link

rboogaerts 11 posts 132 karma points

Apr 13, 2017 @ 11:17

I solved the problem by only publishing the changed nodes in code using the contentService.

            var cs = ApplicationContext.Current.Services.ContentService;

            ThreadPool.QueueUserWorkItem(o =>
            {
                try
                {
                    HttpContext.Current = new HttpContext(new HttpRequest(null, "https://loyenslocal.be", null), new HttpResponse(null));
                    string sql = @"select doc.nodeId from  [dbo].[cmsPropertyData] pd join cmsDocument doc on pd.versionId = doc.versionId where dataNtext LIKE '%someString%' AND doc.newest = 1";
                    string constr = ConfigurationManager.ConnectionStrings["....."].ConnectionString;
                    List<int> nodes = new List<int>();


                    //Get all the changed nodes
                    using (var dr = SqlHelper.ExecuteReader(constr, CommandType.Text, sql))
                    {
                        //Loop trough all the results
                        while (dr.Read())
                        {
                            //Get the id 
                            nodes.Add(dr.GetInt32(0));
                        }
                    }

                    if (nodes.Any())
                    {
                        foreach (var nodeId in nodes)
                        {
                            var node = cs.GetById(nodeId);

                            if (node != null)
                            {
                                cs.Publish(node);
                            }
                        }
                    }

                    LogHelper.Info(this.GetType(), "All media items saved");

                }
                catch (Exception ex)
                {
                    LogHelper.Error(this.GetType(), "publishing changed items failed.", ex);
                    LogHelper.Error(this.GetType(), ex.Message, ex);
                }
            });


            return "process started";

Copy Link

is working on a reply...

Please Sign in or register to post replies

Flag this post as spam?