U4-1516 - DataLayer error when deleting template.

Created by Merijn van Mourik 22 Jan 2013, 14:36:48 Updated by Dan Booth 11 May 2015, 12:29:51

Is duplicated by: U4-1607

Relates to: U4-2356

Relates to: U4-1956

Installed the 6.0.0 RC. Changed WebForms to Mvc in UmbracoSettings.config (don't know if this is important for this issue) Created a few document types and templates. Deleted one or two document types.

Then when deleting the template, an error occurs in the backoffice. So I cannot delete the template.

So it looks like the code doesn't check when deleting document types whether related templates exist.

2013-01-22 15:17:51,596 [5] ERROR umbraco.DataLayer.SqlHelper1[[System.Data.SqlClient.SqlParameter, System.Data, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089]] - [Thread 17] Error executing query delete from cmsTemplate where NodeId =1047 System.Data.SqlClient.SqlException (0x80131904): The DELETE statement conflicted with the REFERENCE constraint "FK_cmsDocumentType_cmsTemplate_nodeId". The conflict occurred in database "doecadeau_umbraco_dev_local", table "dbo.cmsDocumentType", column 'templateNodeId'. The statement has been terminated. bij System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) bij System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) bij System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) bij System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) bij System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout) bij System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) bij System.Data.SqlClient.SqlCommand.ExecuteNonQuery() bij Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) bij Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) bij umbraco.DataLayer.SqlHelpers.SqlServer.SqlServerHelper.ExecuteNonQuery(String commandText, SqlParameter[] parameters) bij umbraco.DataLayer.SqlHelper`1.ExecuteNonQuery(String commandText, IParameter[] parameters) ClientConnectionId:f2aecac8-757f-4f8c-8f80-240f02a257e6


Morten Christensen 24 Jan 2013, 10:53:03

Thanks for reporting this, and nice find by the way. The issue occured because of changes to the legacy Document and DocumentType class for v6.

The issue has now been fixed and will be part of the final release.

Luke Alderton 19 Mar 2013, 13:16:29

Looks like this is the same sort of issue? http://our.umbraco.org/forum/core/general/19332-Error-deleting-template-(SQL-helper-exception-in-ExcuteNonQuery)-v47?p=1#comment143775 it's been going on since way before v6 was even thought of.

Funka! 31 Aug 2013, 00:51:05

The problem is still (from at least as far back as v4.7.2 and continuing through v6.1.3 in my experience) related to fact that umbraco is trying to delete the template record from the {{cmsTemplate}} table, but it fails because the templateId column in the {{cmsDocument}} table is still referencing it!

This is what the {{FK_cmsDocument_cmsTemplate}} reference constraint is telling us. I don't think it has anything to do with API changes in the C# codebase, it's the database that prevents this from happening.

The worst part is, these are just old versions of the same old documents in the {{cmsDocument}} table that are historical and you have no easy way of cleaning these up unless you want to muck around in the SQL to do this. (I usually do.) So if you've ever used your template at least once in an older version of a living page at any time, you can't delete it.

Simon Steed 11 May 2015, 11:29:46

Old post but still relevant - the reason for this error is that the template is not assigned to any doc types. I've just had the same error, then I assigned that template to a temp doc type which allowed me to delete the template.

Just in case anyone else gets this error going forwards - i'm sure i've got a site somewhere running v7 with the same issue.

Dan Booth 11 May 2015, 11:49:15

I've also reported this - http://issues.umbraco.org/issue/U4-2356 and it still happens in Umbraco 7. But no-one wants to believe me :)

Simon Steed 11 May 2015, 12:02:33

It's definitely a problem - i'll bump up the issue for you :-)

Simon Steed 11 May 2015, 12:04:48

However you may need to open a new issue for it :(

Dan Booth 11 May 2015, 12:29:51

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: Morten Christensen

Difficulty: Normal


Backwards Compatible: True

Fix Submitted:

Affected versions: 6.0.0

Due in version: 6.0.0


Story Points: