U4-2356 - Sql Exception if you try and delete a template that isn't linked to a doc type

Created by Dan Booth 11 Jun 2013, 19:59:18 Updated by Dan Booth 11 May 2015, 12:30:40

Is duplicated by: U4-4165

Relates to: U4-5239

Relates to: U4-1516

Relates to: U4-1956

I'm running 6.11 and a bug that was in 4.7 is still present - I get: Exception: System.Data.SqlServerCe.SqlCeException (0x80004005): The primary key value cannot be deleted because references to this key still exist. [ Foreign key constraint name = FK_cmsDocument_cmsTemplate_nodeId ] at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr) at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr& pCursor, Boolean& isBaseTableCursor) at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options) at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery() at SqlCE4Umbraco.SqlCeApplicationBlock.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlCeParameter[] commandParameters) at SqlCE4Umbraco.SqlCeApplicationBlock.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlCeParameter[] commandParameters) at SqlCE4Umbraco.SqlCEHelper.ExecuteNonQuery(String commandText, SqlCeParameter[] parameters) at umbraco.DataLayer.SqlHelper1.ExecuteNonQuery(String commandText, IParameter[] parameters) 2013-06-11 20:57:20,407 [7] ERROR umbraco.DataLayer.SqlHelper1[[System.Data.SqlServerCe.SqlCeParameter, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91]] - [Thread 10] Error executing query delete from cmsTemplate where NodeId =1122 SqlCE4Umbraco.SqlCeProviderException: Error running NonQuery: SQL Statement: delete from cmsTemplate where NodeId =1122

Exception: System.Data.SqlServerCe.SqlCeException (0x80004005): The primary key value cannot be deleted because references to this key still exist. [ Foreign key constraint name = FK_cmsDocument_cmsTemplate_nodeId ] at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr) at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr& pCursor, Boolean& isBaseTableCursor) at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options) at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery() at SqlCE4Umbraco.SqlCeApplicationBlock.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlCeParameter[] commandParameters) at SqlCE4Umbraco.SqlCeApplicationBlock.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlCeParameter[] commandParameters) at SqlCE4Umbraco.SqlCEHelper.ExecuteNonQuery(String commandText, SqlCeParameter[] parameters) at umbraco.DataLayer.SqlHelper`1.ExecuteNonQuery(String commandText, IParameter[] parameters)

This occurs if I try and delete a template that is NOT assigned to a document type or content page. This appears to be the same issue as http://our.umbraco.org/forum/core/general/19332-Error-deleting-template-(SQL-helper-exception-in-ExcuteNonQuery)-v47 - since once I assign the template to a document type it can be deleted. I'm running SQL CE, btw.

2 Attachments

Comments

Sebastiaan Janssen 21 Jun 2013, 15:54:51

Sorry, I can't reproduce this in either 6.0.6 nor 6.1.1. The description of this is confusing because you keep saying it's NOT assigned to anything for all of your tests, so I tried:

  • Create Template from the interface (using both Mvc and WebForms)
  • Delete said Template --> no problem
  • Create Template from the interface (using both Mvc and WebForms)
  • Attach that template to a doctype and don't use it on any content item (so no content items use this template)
  • Delete said Template --> no problem
  • Create Template from the interface (using both Mvc and WebForms)
  • Attach that template to a doctype and it on a content item
  • Delete said Template --> no problem

So if you have better steps to reproduce I'm all ear but so far I've had no luck.


Dan Booth 21 Jun 2013, 19:57:30

When I say, "It's not assigned" then that is exactly what I mean :) It only occured when I tried to delete a template that wasn't assigned to any content nodes. If you read the thread you'll see I'm not the only person who has this problem.

If you cannot reproduce then it may be because the site was upgraded from an earlier version of Umbraco. It was also, as you can probably tell for the exception, using SQL CE. I realise it's difficult to diagnose without reproducible steps, but believe me it did happen to me (and other people). If I find definitive steps I'll add them, but you can see from the exception where the basic problem lies.


Sebastiaan Janssen 24 Jun 2013, 10:26:00

Sorry Dan, turns out I was a bit tired last week, upon reading it again it's clear. :-) We use SQL CE for development so that's what I tested it on as well. I understand where the problem is, just not why I'm not seeing it! ;) I have the FK in place which should look like the attached screenshot.


Dan Booth 24 Jun 2013, 12:11:34

No worries, Seb. I have tried to replicate this reliably, but haven't been able to - my feeling is that it might have been a DB schema problem that was corrected in later versions of Umbraco, but that wasn't pulled-through when upgrading. So it may not exist in fresh installations at all, hence the problem reproducing.

So I'm happy for you to close it - Obviously I can't expect you to fix things that can't be reproduced. I can re-open it again if I (or anyone else) can reliably reproduce it. Thx :)


Hua 26 Aug 2013, 01:48:09

Similar problem happened on my site (4.7.2) when I tried to empty 'Recycle bin' {"Message":"Umbraco Exception (DataLayer): SQL helper exception in ExecuteNonQuery","StackTrace":" at umbraco.DataLayer.SqlHelper`1.ExecuteNonQuery(String commandText, IParameter[] parameters) in C:\Dev\Umbraco\umbraco\umbraco\datalayer\SqlHelper.cs:line 224\r\n at CMSImportLibrary.DAL.State.DeleteRelationRecordForDocument(Int32 id)\r\n at CMSImportLibrary.Events.DeleteRelationRecord.\u0002(Document \u0002, DeleteEventArgs \u0003)\r\n at umbraco.cms.businesslogic.web.Document.DeleteEventHandler.Invoke(Document sender, DeleteEventArgs e)\r\n at umbraco.cms.businesslogic.web.Document.FireBeforeDelete(DeleteEventArgs e)\r\n at umbraco.cms.businesslogic.web.Document.DeletePermanently()\r\n at umbraco.cms.businesslogic.web.Document.delete(Boolean deletePermanently)\r\n at umbraco.presentation.webservices.legacyAjaxCalls.DeleteContentPermanently(String nodeId, String nodeType) in d:\Dropbox\Dev\UmbracoSource_v4\umbraco\presentation\umbraco\webservices\legacyAjaxCalls.asmx.cs:line 99","ExceptionType":"umbraco.DataLayer.SqlHelperException"}


Funka! 31 Aug 2013, 01:23:50

I came up with more detailed steps for consistently reproducing this on a 6.1.3 site just now, and posted them over in one of the Our Umbraco forum threads, but in case that gets lost over there, I'm going to copy it here too... Thanks!

'''Steps to reproduce in 6.1.3 using MS SQL Server 2008:'''

Create a new Document Type called "Deleter Tester" and leave the "Create matching template" box checked.

Create a new document in your site based on this new DeleterTester document type. Name it "Deleter Doc".

Now hit the "Save and Publish" button to ensure this page gets published. (Note that the Audit Trail now has two saved versions of this document in the {{cmsDocument}} table.)

Go back to settings and edit the "Deleter Tester" document type. UNCHECK all allowed templates and save the document type.

Now go try to delete the "Deleter Tester" template. (In my case, this was created by default as an MVC view but same problem exists in older WebForms/master pages as well if I recall.) You will get a SQL Exception because of the foreign key reference from {{cmsDocument}} to {{cmsTemplate}}

The weird thing is this:

  • If you delete the "Deleter Doc" content node, and then purge it from the recycle bin, you can now delete the template. (Historical audit trail was wiped, no more database references to the {{cmsTemplate}} table.)
  • Or, if you go back to any document type and allow the "Deleter Tester" template as one of the choices, ... for some reason now when you try to delete the template, it somehow knows to clean up the old historical references in the cmsDocument table and actually lets you delete the template?? I watched this happen before my eyes in SSMS.

Hope this helps!


Mark van Schaik 15 May 2014, 04:43:58

pretty sure i've fixed this in a pull request just submitted - https://github.com/umbraco/Umbraco-CMS/pull/379


Shannon Deminick 15 May 2014, 05:38:30

Nice find Mark,

I'll get this into 6/7 and see if can get a test written for it.


Shannon Deminick 15 May 2014, 06:11:44

Thats all merged in + unit test to confirm, thanks again.


Mark van Schaik 15 May 2014, 06:20:03

Glad to help!


Peter Hrebicek 17 Sep 2014, 12:39:47

If this is the glitch i have stumbled up on, it apparently is still existing in 7.1.6. It happened when i was trying to delete a Template that is not assigned to a Document Type (any more). SQL CE/IIS 8.5


Shannon Deminick 17 Sep 2014, 13:34:38

If you can reproduce any issue that is closed, you need to open a new one since this is tagged under an already released version.


Dan Booth 27 Nov 2014, 14:59:39

It's still in 7.18 - can't you just "untag" or "reopen" this issue, rather than having to create a new version?


Sebastiaan Janssen 27 Nov 2014, 15:07:53

@DanDiplo Please open a new issue with steps to reproduce.


Simon Steed 11 May 2015, 12:04:00

This is still in 7.2.1 - simple to reproduce, done it on two sites this morning, I have a template which is not assigned to a doc type (was previously). I cannot delete it. If I then assign to another doc type (even a temp one), I can then delete the template.

Had this on a 4.11.10 site and just done the same thing on a 7.2.1 site a few mins ago.


Dan Booth 11 May 2015, 12:30:40

@sebastiaan @SimonAntony OK, I've opened a new issue for this, as requested - http://issues.umbraco.org/issue/U4-6609


Priority: Normal

Type: Bug

State: Fixed

Assignee: Shannon Deminick

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted: Pull request

Affected versions: 6.0.0, 6.1.0, 7.0.0, 7.1.0, 6.0.1, 6.0.2, 6.0.3, 6.0.4, 6.1.1, 6.0.6, 6.0.5, 6.0.7, 6.1.2, 6.1.3, 6.1.4, 6.1.5, 6.1.6, 7.0.1, 7.0.2, 7.0.3, 7.0.4, 7.1.1, 7.1.2

Due in version: 7.1.3, 6.2.1

Sprint:

Story Points:

Cycle: