U4-7698 - Change document type results in sql error

Created by Marc van de Wert 12 Jan 2016, 07:46:35 Updated by Claus Jensen 26 Jan 2016, 13:15:21

Relates to: U4-7833

Hi I changed the document type of a document so that no content uses the document type anymore, and when I delete the document type I get a SQL exception.

Comments

Sebastiaan Janssen 12 Jan 2016, 09:46:46

What's the exception? Where? Are you unable to edit content now?


Marc van de Wert 12 Jan 2016, 12:16:30

I haven't tested editing content because I was afraid I was going to break the website.

Received an error from the server Failed to delete item 1082

The DELETE statement conflicted with the REFERENCE constraint "FK_cmsPropertyData_cmsPropertyType_id". The conflict occurred in database "Umbraco_Ecommerce", table "dbo.cmsPropertyData", column 'propertytypeid'. The statement has been terminated. Exception Details: System.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_cmsPropertyData_cmsPropertyType_id". The conflict occurred in database "Umbraco_Ecommerce", table "dbo.cmsPropertyData", column 'propertytypeid'. The statement has been terminated. Stacktrace:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery() in c:\Code\github\SamSaffron\MiniProfiler\StackExchange.Profiling\Data\ProfiledDbCommand.cs:line 279

at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClass1.b__0()

at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)

at Umbraco.Core.Persistence.Database.Execute(String sql, Object[] args)

at Umbraco.Core.Persistence.Repositories.PetaPocoRepositoryBase`2.PersistDeletedItem(TEntity entity)

at Umbraco.Core.Persistence.Repositories.RepositoryBase`2.PersistDeletedItem(IEntity entity)

at Umbraco.Core.Persistence.UnitOfWork.PetaPocoUnitOfWork.Commit(Action`1 transactionCompleting)

at Umbraco.Core.Services.ContentTypeService.Delete(IContentType contentType, Int32 userId)

at umbraco.nodetypeTasks.PerformDelete()

at Umbraco.Web.Editors.LegacyController.DeleteLegacyItem(String nodeId, String alias, String nodeType)

at lambda_method(Closure , Object , Object[] )

at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.b__9(Object instance, Object[] methodParameters)

at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at System.Web.Http.Controllers.ApiControllerActionInvoker.d__0.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at System.Web.Http.Filters.ActionFilterAttribute.d__0.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at System.Web.Http.Filters.ActionFilterAttribute.d__0.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Web.Http.Filters.ActionFilterAttribute.d__5.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at System.Web.Http.Filters.ActionFilterAttribute.d__0.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at System.Web.Http.Controllers.ActionFilterResult.d__2.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at System.Web.Http.Filters.AuthorizationFilterAttribute.d__2.MoveNext()

--- End of stack trace from previous location where exception was thrown ---

at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)

at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)

at System.Web.Http.Dispatcher.HttpControllerDispatcher.d__1.MoveNext()


Sebastiaan Janssen 12 Jan 2016, 12:19:04

It's still tied to some document I guess? Anything in the recycle bin that's still of this document type?


Marc van de Wert 12 Jan 2016, 12:22:58

No, I was cleaning up (trying to) because I used a document type that was being used by only this (one) content type, and it's exactly the same as a generic content type. So no, there should be no other references to the document type I was trying to delete. In the basket or whatsoever.


Claus Jensen 13 Jan 2016, 10:35:27

I can confirm this one in a clean 7.4.0-beta - have assigned it to sprint.


Marc van de Wert 13 Jan 2016, 10:43:09

Nice, thankyou!


Shannon Deminick 25 Jan 2016, 15:44:19

@Claus.Jensen can you confirm that you can still replicate this issue in dev-v7 branch? I've fixed on thing in rev: 3712a80031096c733f4fb080b29a64f913d9ef5e and now if i change a content item do a diff doc type, i can successfully delete that doc type, so maybe this issue has been fixed by another change?


Claus Jensen 26 Jan 2016, 10:04:34

@Shandem I can still replicate this consistently. It's due to revisions/versions in the database and the fact that we do not update/delete those on document type change.

When we change the document type we don't clear the old revisions of the document - which means there's an old version of the document and this one still has the old references from the old document to the old properties (which has a constraint resulting in a sql error when we try to delete the seemingly unused document type).

I propose a fix where we clear the revisions on all documents affected by a document type change (with a warning in the UI). I don't see how it would not break (or at least not do a rollback as you would expect anyway) if you actually tried to rollback a document past a document type change, since we're doing some manual mapping of oldProperty to newProperty when you do the change.

If you want to reproduce:

  • Create dt1 with prop1 on it (textstring)
  • Create dt2 with prop2 on it (textstring)
  • Create document1 from dt1 in root with value1 in prop1
  • Switch the document type of document1 to dt2 and make sure to map prop1 to prop2 while switching
  • Go to document types and try to delete dt1


Shannon Deminick 26 Jan 2016, 11:14:59

Made a pr for review: https://github.com/umbraco/Umbraco-CMS/pull/1055

We now ensure that all old referenced property data for a particular doc type is cleared when it is deleted. This is normally done at the service level because we clear all related content first, but in this particular case we don't so we just ensure that it's definitely gone.


Claus Jensen 26 Jan 2016, 13:15:11

Code looks good. Tried a few tests to make it fail (like changing to a doctype - then to another and then deleting the intermediate one .. all worked as expected) - its merged.


Priority: Task - Pri 1

Type: Bug

State: Fixed

Assignee:

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions: 7.4.0, 7.3.4

Due in version: 7.4.0

Sprint: Sprint 7

Story Points:

Cycle: