U4-8440 - Saving Content and Media in back office throws 'Database exception occurred' in SQL Server 2008 R2 in Umbraco 7.4.2 but not in SQL Server 2012.

Created by Rob 11 May 2016, 10:56:09 Updated by Rob 11 May 2016, 13:57:25

I have an installation of Umbraco 7.4.2 with a SQL Server 2008 R2 database, and I am unable to save or update any content in the back office. This DOES NOT occur if I use SQL Server 2012, so the issue is related to a SQL Server 2008 R2 database.

I am however able to create document types, users, change permissions etc.

The exception is thrown in umbraco/backoffice/UmbracoApi/Content/PostSave.

Whilst debugging, I can see errors similar to:

Violation of PRIMARY KEY constraint 'PK_cmsContentXml'. Cannot insert duplicate key in object 'dbo.cmsContentXml'. The duplicate key value is (1053). The statement has been terminated.

When the project is deployed, I just get console errors saying 'An error has occurred'.

Any potential fixes, or at the very least, any information as to why this occurs in 2008 R2 but not in 2012 would be great.

Comments

Sebastiaan Janssen 11 May 2016, 11:14:11

You seem to have duplicate values in the 2008 database, that's got nothing to do with the version of sql.. unless both databases are exactly the same but I doubt that.


Rob 11 May 2016, 11:22:49

@sebastiaan All I did was to create a fresh Umbraco project, from a blank 2008 R2 database, created a document type with no additional properies and saved it. When I try to create this content it says 'Server error: Contact administrator, see log for full details. Record could not be inserted or updated.' when I click 'Save and Publish'.

The log is:

2016-05-11 12:20:59,864 [P1408/D5/T16] ERROR Umbraco.Core.Persistence.UmbracoDatabase - Database exception occurred System.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'PK_cmsContentPreviewXml'. Cannot insert duplicate key in object 'dbo.cmsPreviewXml'. The duplicate key value is (1047, fc1d270d-09bd-4682-a577-8b73fb9e3bed). The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 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 266 at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClass1.b__0() at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command, RetryPolicy retryPolicy) at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteNonQueryWithRetry(IDbCommand command) at Umbraco.Core.Persistence.Database.Insert(String tableName, String primaryKeyName, Boolean autoIncrement, Object poco)


Sebastiaan Janssen 11 May 2016, 11:54:23

Interesting, might be good to compare the schema of the databases.. But we don't do anything different for different versions of SQL, so not sure how that would've happened.


Rob 11 May 2016, 13:21:23

@sebastiaan how would you go about doing this?

After further digging, it is for all versions > 7.2.8 - not sure if that helps explain what's going on?


Sebastiaan Janssen 11 May 2016, 13:49:17

So it works in 7.2.7?

I'm sure there's SQL compare tools out there, I know Red Gate makes one.


Rob 11 May 2016, 13:57:25

It works on 7.2.8 - but not on anything above that. I haven't tried anything below that so far.

I just did a schema compare using Data Tools and the 2008 R2 schema and 2012 schema are identical.

Also - when clicking 'Save and Publish' it does seem to save the changes to the content, but it is the 'post-save' events such as the xml previews which are causing the error to be displayed.


Priority: Normal

Type: Bug

State: Submitted

Assignee:

Difficulty: Difficult

Category: Architecture

Backwards Compatible: True

Fix Submitted:

Affected versions: 7.3.0, 7.4.0, 7.3.1, 7.3.2, 7.3.3, 7.3.4, 7.4.1, 7.3.5, 7.3.6, 7.3.7, 7.3.8, 7.4.2, 7.4.3

Due in version:

Sprint:

Story Points:

Cycle: