member datatype creation sql error - Using Umbraco 7

keilo 568 posts 1023 karma points

Sep 03, 2015 @ 23:09

Member DataType creation - SQL Error

I posted this on possibly the wrong section. Retrying here with the hope that one kind/wise soul might help:

I was using the Member section and adding custom data types with no issues.

For no apparent reason I am getting this error when I try to create a new data type in any (including default Member) Member Type.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_cmsPropertyType_cmsDataType_nodeId". The conflict occurred in database "STAGING", table "dbo.cmsDataType", column 'nodeId'.
The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_cmsPropertyType_cmsDataType_nodeId". The conflict occurred in database "STAGING", table "dbo.cmsDataType", column 'nodeId'.
The statement has been terminated.

Source Error: 
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace: 

[SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_cmsPropertyType_cmsDataType_nodeId". The conflict occurred in database "STAGING", table "dbo.cmsDataType", column 'nodeId'.
The statement has been terminated.]
   System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase) +506
   System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData) +401
   System.Action`1.EndInvoke(IAsyncResult result) +0
   System.Web.UI.d__3.MoveNext() +603
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +144
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +84
   System.Web.UI.d__0.MoveNext() +507
   System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +144
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +84
   System.Web.UI.d__2c.MoveNext() +1145


________________________________________
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.34249

When you edit any membertype it throws this error.

When digged deeper the only culprit is

Label datatype it is, for Failed Password Attempts datatype shows 0. Works fine everywhere else except MemberType...

This is the Profiler caught SQL, note the parameter 0 (for the failed pw):

exec sp_executesql N'UPDATE [cmsPropertyType] SET [dataTypeId] = @0, 
[contentTypeId] = @1, [propertyTypeGroupId] = @2, [Alias] = @3, [Name] = @4, 
[helpText] = @5, [sortOrder] = @6, [mandatory] = @7, [validationRegExp] = @8, 
[Description] = @9 
WHERE [id] = @10',N'@0 int,@1 int,@2 int,@3 nvarchar(4000),@4 nvarchar(4000),
@5 nvarchar(4000),@6 int,@7 int,@8 nvarchar(4000),@9 nvarchar(4000),@10 int',
@0=0,@1=19003,@2=107,@3=N'umbracoMemberFailedPasswordAttempts',@4=N'Failed Password Attempts',
@5=NULL,@6=1,@7=0,@8=N'',@9=N'',@10=336

Anyone knows what is likely causing or better how to fix it? on 7.1.8

Copy Link

Jeavon Leopold 3074 posts 13632 karma points MVP 11x admin c-trib

Sep 04, 2015 @ 08:31

Hi Keilo,

I have seen this issue with Ravi (who you have had a discussion with on a different thread). The problem is that I was not able to replicate the issue on a local development machine so I could debug it, it seems to only occur when deployed to a server environment, have you tried to replicate on a local development machine?

I narrowed the issue down to the MemberTypeService rather than anything wrong with the UI.

I would be interested to know what server environment you are running?

For reference the other threads on this issue are here and here

Jeavon

Copy Link

keilo 568 posts 1023 karma points

Sep 04, 2015 @ 09:00

Hi Jeavon

Thanks for your reply.

In Ravi's case it arise (if not mistaken) when deployed to a new server. However in my case it appeared on a system (7.1.8, on MSSQL2008R2, Windows 2008R2) thats been running for more than a year.

I was happily adding membertype properties, saving them etc. The last one I added was named "newsletter" with true/false - which was for the NewsletterStudio (didnt really used NewsletterStudio package's member email feature either, so I doubt it interfere).

I have gone through the most recent adds of member types and none of them seem to be the likely the culprit.

Interestingly its coming as the Failed Password Attempts Label datatype, from two different sql profiling on two (very likely) different systems; referring to my finding and Ravi's in this case (in the linked article in your post).

Im a bit lost as I cant seem to find where that 0 is coming from for the label datatype (again, it works just fine when used in doctype but not membertype) and I cant add/extend any membertype from the UI.

As there is a lot of work done, and tested, on this 7.1.8 I didnt venture into upgrading...

May I ask how did you narrow it down to MemberTypeService, obviously it happens only when MemberType is edited within the UI but I'm curious. Ravi reported, on the linked thread, that he was able to overcome that by running manual code, which I assume overrided the MemberTypeService?

Would love to hear if you have any more insight on this.

cheers!

Copy Link

Jeavon Leopold 3074 posts 13632 karma points MVP 11x admin c-trib

Sep 04, 2015 @ 11:47

Hi Keilo,

We tried upgrading to v7.2.8 to check it wasn't fixed elsewhere and it did not resolve the problem.

Actually running the code Ravi posted didn't solve the issue but it did narrow the issue down to the MemberTypeService. We had to workaround around the immediate issue by capturing SQL using profiler on a working local machine and rerunning it on the live db servers but this is a fairly extreme workaround.

It might of interest that Ravi was also trying to add a true/false property before the issue occurred.

I just wish I could replicate this locally so that I could debug and hopefully fix it, I have copied the database from live and checked the files are exactly the same but as soon as run it locally everything works! Annoying!

I was beginning to wonder if it was some sort of environment issue but we are running Windows Server 2012R2 so it seems that might be a dead theory also.

Jeavon

Copy Link

keilo 568 posts 1023 karma points

Sep 08, 2015 @ 06:57

Hi Jeavon

Thanks for the detailed reply.

I really want to find whats causing it, as its a heavily tested and developed system where venturing to upgrade is not ideal - will only introduce more puzzle.

In this case, for the membertypeservice, where the sql above is generated, is there a particular umbraco table entry that i can manually extract/update/delete to make it work?

I tried to go through the tables but its too deep and definitely need some guidance to where to look at; like if the last true/false data-type i added to membertype is culprit how can i delete that or better how can i locate the reference 0 being returned and manually edit/update that.

If you can provide some guidance on how to go about it would be much appreciated!

Copy Link

Jeavon Leopold 3074 posts 13632 karma points MVP 11x admin c-trib

Sep 08, 2015 @ 08:48

Hi Kelio,

Do you have any packages such as CMSImport, SEO Checker etc installed?

Thanks,

Jeavon

Copy Link

keilo 568 posts 1023 karma points

Sep 14, 2015 @ 18:56

Hi again Jeavon

Do you have any pointers on which tables/query i should look to perhaps locate and update/delete manually to turn it back to normal?

would greatly appreciate some pointers..

Copy Link

keilo 568 posts 1023 karma points

Sep 08, 2015 @ 17:02

Hi Jeavon

I have CmsImport Package version 2.5.2

installed and last used about a year ago, didnt touch it since then. I dont have SEO Checker, the list of packages below. Just to note I havent used any Import or fiddle with document type or data types for that matter, except adding one true/false field to MemberType.

Analytics Archetype CmsImport Config Tree Content Dropdown List Contour Multilingual Country Diplo Trace Log Viewer Document Type Usage Epiphany.SeoMetaData ezSearch FALM Housekeeping Lecoati.uMirror (not used, i.e. no schedule) Media Content Usage Newsletter Studio nuPickers RJP.MultiUrlPicker Seo Visualizer Skybrud.Social for Umbraco 7 Sortable String List U7 Grid Data Type uDateFoldersy uImport Umbraco Contour Union.PropertyEditors uSEO Pro Video Player Datatype Youtube for Umbraco

Copy Link

keilo 568 posts 1023 karma points

Nov 06, 2015 @ 04:29

Hi again Jeavon

Do you have any update about the potential cause, or how to fix it?

Copy Link

is working on a reply...

Flag this post as spam?

Member DataType creation - SQL Error