Umbraco-CMS: Login with OpenIdConnect gives FOREIGN KEY constraint failed error

Which exact Umbraco version are you using? For example: 9.0.1 - don’t just write v9

10.1.0

Bug summary

I was trying to do the same setup as from https://github.com/umbraco/Umbraco-CMS/issues/12749, but on a new 10.1.0 project with SQLite. When I try to login I’m redirect to the Identity Provider. After I login there I’m redirected back to Umbraco, but that gives the following error:

SQLite Error 19: 'FOREIGN KEY constraint failed'.

Specifics

This is the full error from the logs:

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'FOREIGN KEY constraint failed'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at Umbraco.Cms.Persistence.Sqlite.Services.SqlitePreferDeferredTransactionsConnection.CommandWrapper.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 NPoco.DatabaseType.InsertBulk[T](IDatabase db, IEnumerable`1 pocos, InsertBulkOptions options)
   at NPoco.Database.InsertBulk[T](IEnumerable`1 pocos, InsertBulkOptions options)

Timestamp 2022-08-15T07:59:30.3014175+00:00 @MessageTemplate Exception ({InstanceId}). InstanceId 7865c0d9 SourceContext Umbraco.Cms.Infrastructure.Persistence.UmbracoDatabase ActionId 4351ce4e-1744-44b1-9368-d4ab995b85e4 ActionName Umbraco.Cms.Web.Website.Controllers.UmbExternalLoginController.ExternalLoginCallback (Umbraco.Web.Website) RequestId 800000c3-0002-fa00-b63f-84710c7967bb RequestPath /login/ ProcessId 9964 ProcessName iisexpress ThreadId 66 ApplicationId 0521d4839bc6f50f6ff21829f41b9aeff4fb79a6 MachineName 4QG5RQ2 Log4NetLevel ERROR HttpRequestId 4803ef19-5686-4c97-8e34-aa5fd4f36718 HttpRequestNumber 4 HttpSessionId ea74524a-ef0f-e05a-ebc4-35f00479889d

Steps to reproduce

Follow the steps to reproduce from https://github.com/umbraco/Umbraco-CMS/issues/12749 on a 10.1.0 site with SQLite. Then try to login to the external provider with a new member.

Expected result / actual result

After I login into the external provider I should also be logged into Umbraco. My updated tokens should be stored in the umbracoExternalLoginToken table.

---

This item has been added to our backlog AB#21855