Skip to main content

All items removed from Sitecore Web DB due to this bug in Sitecore Publishing Service v5

In this post, I am going to talk about a very weird issue we faced and how we troubleshooted it. One fine morning, a QA in our team updated us that she is unable to see anything on CD server. All she sees is the Sitecore aspx page with error saying layout not found. We started looking for the item in web db to find out what was wrong and phewww, web db was missing all the items we had built ever.

To fix this issue temporarily, we published everything from master to web db. But this kept happening over and over. We started looking at the Publishing Dashboard to get any cues and we saw hundreds of queued jobs as in snapshot below - 


These jobs seem to be getting queued automatically. We started investigating logs in Sitecore cm instance and on publishing service machine to get any traces of what is happening.

We also found that our publishing service instance went unresponsive due to such large number of requests. We recycled its application pool to get it back to working state.

We checked the publishing service logs and found lots of entries like this -
 
2022-06-07 16:17:49.024 -04:00 [Error] Error executing action in PublishJobQueueService
System.AggregateException: One or more errors occurred. ---> System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
at System.Data.Common.ADP.ExceptionWithStackTrace(Exception e)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Sitecore.Framework.TransientFaultHandling.Sql.SqlRetryHelper.<>c__DisplayClass18_0.<<OpenAsync>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Sitecore.Framework.Publishing.Data.AdoNet.DatabaseConnection`1.<OpenNewConnection>d__33.MoveNext()
--- End of inner exception stack trace ---
at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification)
at Sitecore.Framework.Publishing.Data.AdoNet.DatabaseConnection`1.get_UnderlyingConnection()
at Sitecore.Framework.Publishing.Data.AdoNet.DatabaseConnection`1.<ExecuteAsync>d__27`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Sitecore.Framework.Publishing.Service.Sql.PublishJobQueue.PublishJobQueueProvider.<CreateJob>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Sitecore.Framework.Publishing.PublishJobQueue.PublishJobQueueRepository.<CreateJob>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Sitecore.Framework.Publishing.PublishJobQueue.PublishJobQueueService.<>c__DisplayClass10_0.<<QueueJob>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Sitecore.Framework.Publishing.PublishJobQueue.PublishJobQueueService.<Handle>d__13`1.MoveNext()
---> (Inner Exception #0) System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
at System.Data.Common.ADP.ExceptionWithStackTrace(Exception e)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Sitecore.Framework.TransientFaultHandling.Sql.SqlRetryHelper.<>c__DisplayClass18_0.<<OpenAsync>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Sitecore.Framework.Publishing.Data.AdoNet.DatabaseConnection`1.<OpenNewConnection>d__33.MoveNext()<---


This was expected with such large number of publishing requests but the question remains that what caused such large number of publishing requests to get queued.

We looked at our code but we didn't find any program which can trigger such publishes or can delete items from web db.

We connected with Sitecore support to understand this issue and we were recommended to perform following steps to take up the publishing load our content authors were putting on Publishing Service - 
  • Add ";Max Pool Size=200" in the connection strings file to extend the pool size for the master database in Publishing Service
  • Increase the 'CommandTimeout' of the '<sql-backend-default>' connection behavior that can be found in the Publishing Service installation guide which already discussed during the meeting

This did help in reducing the timeout logs but the issue was still reproducible. And they still didn't explain why the items got deleted in web db.

With Sitecore support, we started looking into our tables and finally it was found to be a known issue related with SPS v5 - 
If you try to publish a /sitecore item in non-existing language version, ​SPS deletes all the items in the web database.479723
Basically, what this means is if you publish an item in your content tree in a non-existing language version, SPS deletes all the items in the web database. A non-exiting language version for an item was created in our application during the upgrade. Whenever the content authors published such items, it used to reproduce this issue and each time all items from web db will get deleted.

For SPS v5, Sitecore support team shared with us support package. Please use the following link to download the hotfix - SC Hotfix 479724-1.zip

This problem is already solved in SPS v6. 

Thanks for reading it. Hope it helps you guys!
Labels:

Popular posts from this blog

Sitecore PowerShell Script to create all language versions for an item from en version

  We have lots of media items and our business wants to copy the data from en version of media item to all other language versions defined in System/Languages. This ensures that media is available in all the languages. So, we created the below powershell script to achieve the same -  #Get all language versions defined in System/Languages $languages = Get-ChildItem /sitecore/System/Languages -recurse | Select $_.name | Where-Object {$_.name -ne "en"} | Select Name #Ensuring correct items are updated by comparing the template ID  $items = Get-ChildItem -Path "/sitecore/media library/MyProjects" -Recurse | Where-Object {'<media item template id>' -contains $_.TemplateID} #Bulk update context to improve performance New-UsingBlock (New-Object Sitecore.Data.BulkUpdateContext) { foreach($item in $items){    foreach($language in $languages){ $languageVersion = Get-Item -Path $item.Paths.Path -Language $language.Name #Check if language versi
Read more

Export Sitecore media library files to zip using SPE

If you ever require to export Sitecore media files to zip (may be to optimize them), SPE (Sitecore Powershell Extension) has probably the easiest way to do this for you. It's as easy as the below 3 steps -  1. Right click on your folder (icons folder in snap)>Click on Scripts> Click on Download 2. SPE will start zipping all the media files placed within this folder. 3. Once zipping is done, you will see the Download option in the next screen. Click Download Zip containing the media files within is available on your local machine. You can play around with the images now. Hope this helps!! Like and Share ;)
Read more

Make Sitecore instance faster using Roslyn Compiler

When we install the Sitecore instance on local, the first load is slow. After each code deploy also, it takes a while for the Sitecore instance to load and experience editor to come up. For us, the load time for Sitecore instance on local machines was around 4 minutes. We started looking for ways to minimize it and found that if we update our Web.config to use Roslyn compiler and include the relevant Nugets into the project, our load times will improve. We followed the simple steps - Go to the Project you wish to add the NuGet package and right click the project and click 'Manage NuGet Packages'. Make sure your 'Package Source' is set to nuget.org and go to the 'Browse' Tab and search Microsoft.CodeDom.Providers.DotNetCompilerPlatform. Install whichever version you desire, make sure you note which version you installed. You can learn more about it  here . After installation, deploy your project, make sure the Microsoft.CodeDom.Providers.DotNetCompilerPlatform.d
Read more

Experience of a first time Sitecore MVP

The Journey I have been working in Sitecore for almost 10 years now. When I was a beginner in Sitecore, I was highly impressed by the incredible community support. In fact, my initial Sitecore learning path was entirely based on community written blogs on Sitecore. During a discussion with my then technology lead Neeraj Gulia , he proposed the idea that I should start giving back to developer community whenever I get chance. Just like I have been helped by many developers via online blogs, stackoverflow etc., I should also try to help others. Fast forward a few years and I met  Nehemiah Jeyakumar  (now an MVP). He had a big archive of his technical notes in the form Sitecore blogs. I realized my first blog dont have to be perfect and it can be as simple as notes to a specific problem for reference in future. That's when I probably created my first blog post on Sitecore. At that time, I didn't knew about the Sitecore MVP program. Over the years, I gained more confidence to write
Read more

Clean Coding Principles in CSharp

A code shall be easy to read and understand. In this post, I am outlining basic principles  about clean coding after researching through expert recommended books, trainings and based on my experience. A common example to start with is a variable declaration like - int i  The above statement did not clarify the purpose of variable i. However,  the same variable can be declared as -  int pageNumber The moment we declared the variable as int pageNumber, our brain realized that the variable is going to store the value for number of pages. We have set the context in our brain now and it is ready to understand what the code is going to do next with these page numbers. This is one of the basic advantages of clean coding. Reasons for clean coding -  • Reading clean code is easier - Every code is revisited after certain amount of time either by the same or different developer who created it. In both the cases, if the code is unclean, its difficult to understand and update it. • To avoid s
Read more