Violation of PRIMARY KEY constraint 'PK_UFUserSecurity' when installing Umbraco Forms 11.0.1

[Wiggee11]

1. Installed a clean Umbraco 11.1.0 installation
2. Installed Umbraco Forms 11.0.1
3. Went to the Forms section and received the below error and error modal displayed
4. Went to the Content section and then back to the Forms section and all working fine

Microsoft.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'PK_UFUserSecurity'. Cannot insert duplicate key in object 'dbo.UFUserSecurity'. The duplicate key value is (-1).
The statement has been terminated.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteNonQuery() in C:\projects\dotnet\src\MiniProfiler.Shared\Data\ProfiledDbCommand.cs:line 281
   at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.FaultHandlingDbCommand.<ExecuteNonQuery>b__32_0()
   at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.FaultHandlingDbCommand.<>c__DisplayClass38_0`1.<Execute>b__0()
   at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
   at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.FaultHandlingDbCommand.Execute[T](Func`1 f)
   at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.FaultHandlingDbCommand.ExecuteNonQuery()
   at NPoco.Database.<>c__DisplayClass296_0.<ExecuteNonQueryHelper>b__0()
   at NPoco.Database.ExecutionHook[T](Func`1 action)
   at NPoco.Database.ExecuteNonQueryHelper(DbCommand cmd)
   at NPoco.Database.InsertAsyncImp[T](PocoData pocoData, String tableName, String primaryKeyName, Boolean autoIncrement, T poco, Boolean sync)
   at NPoco.AsyncHelper.RunSync[T](Task`1 task)
   at NPoco.Database.Insert[T](String tableName, String primaryKeyName, Boolean autoIncrement, T poco)
   at NPoco.Database.Insert[T](T poco)
   at Umbraco.Forms.Data.Storage.UserSecurityStorage.InsertUserSecurity(UserSecurity usersecurity)
   at Umbraco.Forms.Web.Editors.FormSecurityControllerBase.ApplyUserSecurity(FormSecurityForUser formSecurity, IUser user, Boolean isUserAdmin, Boolean explicitOnly)
   at Umbraco.Forms.Web.Editors.FormSecurityControllerBase.GetFormSecurityByUserId(Int32 userId, Boolean explicitOnly)
   at Umbraco.Forms.Web.Editors.CurrentUserFormSecurityController.GetForCurrentUser()
   at lambda_method1084(Closure, Object, Object[])
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncObjectResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|26_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
ClientConnectionId:4f823206-589a-4c61-9e7e-b9550c6e0a11
Error Number:2627,State:1,Class:14

Expected result

Umbraco Forms should be installed successfully and accessible immediately without throwing any SQL exceptions.

[AndyButland]

I haven't been able to replicate this I'm afraid @Wiggee11. These are the steps I took - if you can spot something different with how you replicate the error, I'd appreciate if you could let me know. Thanks.

• dotnet new install Umbraco.Templates
• dotnet new umbraco --name FormsTest
• cd FormsTest
• dotnet add package Umbraco.Forms
• Opened appSettings.json and configured a SQL server connection string to an empty database
• dotnet run

[MartinRyttler]

I have the same issue with Umbraco 10.4.0 and Umbraco.Forms 10.2.3 amd I dug a bit into it. The problem seem to be identifying a user with the id -1 in the table UFUserSecurity, so instead it tries to insert a new, causing the PK violation.

My primary admin user when installing Umbraco 10.4.0 gets the id -1. After installing Umbraco.Forms 10.2.3 I navigate to the Forms section in Backoffice and it works fine the first time. Looking in the database I can see a row getting inserted into UFUserSecurity with the User=-1. When I reload the Forms section I get the PK-violation error in the original post. If I delete the record with the user=-1 in UFUserSecurity and reload the Forms section it works fine again once. A new row with the user=-1 is inserted. And when I reload the Forms section I get the PK-violation error again.

If I create a new user in Umbraco, which gets an id > 0, and log in as this user, I can use Umbraco Forms as expected.

So a workaround if you encounter this problem might be to create a new user. But hopefully this can help fixing the issue.

[AndyButland]

Thanks @MartinRyttler - I think your clear description (plus your location!) has given me the clue as to what's going on here. We've had a few instances with how .NET encodes the minus sign in Swedish - and probably other cultures too, but for where Umbraco is generally used it seems to be Swedish is the one that comes up - so these lookups of the user with the "-1" ID can have issues where the parsing or creation isn't done with the the appropriate encoding.

I can see a case related here where we have missed this, and this would explain why I couldn't replicate the issue when it was first reported.

I've made an update that should fix this, and will be in the next releases.

If you had chance to try the fix on your installation to verify that it does solve the issue, it would be much appreciated. If so, you'll need to first add the following NuGet feed:

<add key="MyGetUmbracoNightly" value="https://www.myget.org/F/umbraconightly/api/v3/index.json" />

And once you have that, you should be able to install a preview of 10.2.4 by referencing the following package:

Umbraco.Forms.10.2.4--preview.2.g29753b0

Hopefully with that you'll find the "-1" user is added correctly and you no longer see this error being reported. Thanks in advance if you are able to verify.

[MartinRyttler]

Glad if it helped, and thanks for quick response. I'll see if I can install the preview version during the day to verify.

[RSDevelopsUK]

Hi all, just want to confirm that the above preview version fixed this issue for me.

I have a fresh install of Umbraco 10 I accidently installed Forms version 11 I uninstalled it Installed 10.2.3 Went into the back office and got the above error. Added the nuget package reference as per the above Installed the preview version Went into the back office again and no error!

Will keep you updated if anything else pops up :)

[AndyButland]

Nice, that's good to hear. Thanks for checking @RSDevelopsUK.

[MartinRyttler]

I can also verify that it works in my scenario. Good job @AndyButland H5YR