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
    rboogaerts
    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.

  • Carlos Mosqueda 240 posts 431 karma points
    Apr 12, 2017 @ 17:20
    Carlos Mosqueda
    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.

  • rboogaerts 11 posts 132 karma points
    Apr 12, 2017 @ 18:48
    rboogaerts
    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.

  • Carlos Mosqueda 240 posts 431 karma points
    Apr 12, 2017 @ 19:20
    Carlos Mosqueda
    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.

    enter image description here

    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.

  • rboogaerts 11 posts 132 karma points
    Apr 13, 2017 @ 11:19
    rboogaerts
    0

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

    Thanks!

  • Dave Woestenborghs 3504 posts 12133 karma points MVP 8x admin c-trib
    Apr 12, 2017 @ 18:04
    Dave Woestenborghs
    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

  • rboogaerts 11 posts 132 karma points
    Apr 13, 2017 @ 11:22
    rboogaerts
    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.

  • rboogaerts 11 posts 132 karma points
    Apr 13, 2017 @ 11:17
    rboogaerts
    100

    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";
    
Please Sign in or register to post replies

Write your reply to:

Draft