Cannot insert explicit value for identity column

[karlmacklin]

Getting a database related exception when trying to handle group based permissions:

[15:54:42 ERR] Exception (b3d7b5de).
Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table 'UFUserGroupSecurity' when IDENTITY_INSERT is set to OFF.
   at Microsoft.Data.SqlClient.SqlConnection.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 Umbraco.Cms.Infrastructure.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
   at NPoco.Database.ExecuteNonQueryHelper(DbCommand cmd)
   at NPoco.Database.InsertAsyncImp[T](PocoData pocoData, String tableName, String primaryKeyName, Boolean autoIncrement, T poco, Boolean sync)
ClientConnectionId:46e800e9-c50b-402b-9dcb-1672f0396526
Error Number:544,State:1,Class:16

@AndyButland I'm pretty certain this is not based on the previous issue I reported, albeit they might be related I guess?
Or it's something you already have considered but I mistook your explanations.

Specifics

Using Umbraco.Cms version 10.0.1 and Umbraco.Forms version 10.0.2.

Fresh/new database install on Docker image mcr.microsoft.com/azure-sql-edge:latest

Steps to reproduce

New Umbraco project by running dotnet new umbraco (after installing template for v10.0.1).

1. Define these appsettings for Forms:

"Forms": {
      "FormDesign": {
        "DisableAutomaticAdditionOfDataConsentField": true,
        "DisableDefaultWorkflow": true,
        "MaxNumberOfColumnsInFormGroup": 1
      },
      "Security": {
        "ManageSecurityWithUserGroups": true,
        "DefaultAccessToNewForms": false,
        "GrantAccessToNewFormsForUserGroups": "admin,editor",
        "EnableAntiForgeryToken": false
      }
    }

2. Install Umbraco
3. Go to Users > expand Forms Security > Group permissions.
4. Click any of the groups, check error log

Expected result

See and adjust group permissions

Actual result

Infinite loading in UI, error in backend from ajax request:

Cannot insert explicit value for identity column in table 'UFUserGroupSecurity' when IDENTITY_INSERT is set to OFF.

[AndyButland]

Unfortunately you've uncovered a mistake here made when I was setting up the schema migrations for Forms 10. There's an incorrect setting on one of the columns - specifically UFUserGroupSecurity.UserGroupId. It's set with IDENTITY (i.e. autoincrement) which it shouldn't have. We then get the error you see when we try to insert a record and we are supplying an explicit value for this column.

There's no issue with migrations from Forms 9, but the incorrect column setting is in place for any new installs using 10.0.0 to 10.0.3.

I've done some investigations this morning and it looks like this doesn't matter for SQLite databases, and can be worked around for SQL Server if we first issue a SET IDENTITY_INSERT UFUserGroupSecurity ON statement.

To resolve, I'm going to do the following.

• From 10.0.4, we'll fix this setting, so new databases created won't have the incorrect setting.
• We'll also check and if necessary set the IDENTITY_INSERT value when inserting records into this table, so the operations will succeed whether or not the value is there.

If, like you, you have a new install, you can also remove the incorrect setting yourself. Unfortunately SQL Server doesn't provide an ALTER TABLE... option to do this, but we can make this change via a temporary table. This is what SQL Management Studio does if you make the change via that interface.

The script for applying this is as follows:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.UFUserGroupSecurity
	DROP CONSTRAINT DF_UFUserGroupSecurity_ViewEntries
GO
ALTER TABLE dbo.UFUserGroupSecurity
	DROP CONSTRAINT DF_UFUserGroupSecurity_EditEntries
GO
CREATE TABLE dbo.Tmp_UFUserGroupSecurity
	(
	UserGroupId int NOT NULL,
	ManageDataSources bit NOT NULL,
	ManagePreValueSources bit NOT NULL,
	ManageWorkflows bit NOT NULL,
	ManageForms bit NOT NULL,
	ViewEntries bit NOT NULL,
	EditEntries bit NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_UFUserGroupSecurity SET (LOCK_ESCALATION = TABLE)
GO
ALTER TABLE dbo.Tmp_UFUserGroupSecurity ADD CONSTRAINT
	DF_UFUserGroupSecurity_ViewEntries DEFAULT ('0') FOR ViewEntries
GO
ALTER TABLE dbo.Tmp_UFUserGroupSecurity ADD CONSTRAINT
	DF_UFUserGroupSecurity_EditEntries DEFAULT ('0') FOR EditEntries
GO
IF EXISTS(SELECT * FROM dbo.UFUserGroupSecurity)
	 EXEC('INSERT INTO dbo.Tmp_UFUserGroupSecurity (UserGroupId, ManageDataSources, ManagePreValueSources, ManageWorkflows, ManageForms, ViewEntries, EditEntries)
		SELECT UserGroupId, ManageDataSources, ManagePreValueSources, ManageWorkflows, ManageForms, ViewEntries, EditEntries FROM dbo.UFUserGroupSecurity WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.UFUserGroupSecurity
GO
EXECUTE sp_rename N'dbo.Tmp_UFUserGroupSecurity', N'UFUserGroupSecurity', 'OBJECT' 
GO
ALTER TABLE dbo.UFUserGroupSecurity ADD CONSTRAINT
	PK_UserGroupSecurity PRIMARY KEY CLUSTERED 
	(
	UserGroupId
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

To summarise:

• To fix the issue now, please review and run the script above to correct the database column setting.
• Once 10.0.4 is out, it won't matter if this column setting is wrong, as we'll ensure the insert operation works anyway.

[karlmacklin]

Thanks for the info and help Andy!