Upgrade issue from 7.6.13 > 7.7+ - Invalid column name 'userId'
I'm in the process of performing an upgrade and all has gone well so far, my path has been:
6.2.5 > 7.5.14 > 7.6.13
I am now trying to go 7.6.13 > 7.8.0 (also tried 7.7.10) however I have hit a show stopper, the upgrade fails immediately with the following exception:
The database configuration failed with the following message: The
INSERT statement conflicted with the FOREIGN KEY constraint
"FKumbracoUserGroup2NodePermissionumbracoNodeid". The conflict
occurred in database "SiteNameUmbraco", table
"dbo.umbracoNode", column 'id'.
When I check in the log file I have the following
2018-02-09 11:23:46,040 [81] ERROR Umbraco.Core.Persistence.UmbracoDatabase - Exception (0ba9247a).
System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'userId'.
Invalid column name 'userGroupId'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteDbDataReader(CommandBehavior behavior) in c:\Code\github\SamSaffron\MiniProfiler\StackExchange.Profiling\Data\ProfiledDbCommand.cs:line 248
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClass5_0.<ExecuteReaderWithRetry>b__0()
at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteReaderWithRetry(IDbCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy)
at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteReaderWithRetry(IDbCommand command, RetryPolicy retryPolicy)
at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteReaderWithRetry(IDbCommand command)
at Umbraco.Core.Persistence.Database.<Query>d__115`1.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Umbraco.Core.Persistence.Database.Fetch[T1,T2,T3,T4,TRet](Func`5 cb, Sql sql)
at Umbraco.Core.Persistence.Repositories.UserRepository.PerformGet(Int32 id)
at Umbraco.Core.Cache.DefaultRepositoryCachePolicy`2.Get(TId id, Func`2 performGet, Func`2 performGetAll)
at Umbraco.Core.Persistence.Repositories.RepositoryBase`2.Get(TId id)
at Umbraco.Core.Services.UserService.GetUserById(Int32 id)
ClientConnectionId:db1dfe01-8043-45b8-b4ee-114b8833501e
Error Number:207,State:1,Class:16
2018-02-09 11:25:16,551 [81] ERROR Umbraco.Core.Persistence.UmbracoDatabase - Exception (68e40dbe).
System.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_umbracoUserGroup2NodePermission_umbracoNode_id". The conflict occurred in database "SiteName_Umbraco", table "dbo.umbracoNode", column 'id'.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
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__DisplayClass2_0.<ExecuteNonQueryWithRetry>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.Execute(String sql, Object[] args)
at Umbraco.Core.Persistence.Migrations.MigrationRunner.ExecuteMigrations(IMigrationContext context, Database database)
at Umbraco.Core.Persistence.Migrations.MigrationRunner.Execute(Database database, DatabaseProviders databaseProvider, Boolean isUpgrade)
at Umbraco.Core.Persistence.Migrations.MigrationRunner.Execute(Database database, Boolean isUpgrade)
at Umbraco.Core.DatabaseContext.UpgradeSchemaAndData(IMigrationEntryService migrationEntryService)
ClientConnectionId:db1dfe01-8043-45b8-b4ee-114b8833501e
Error Number:547,State:0,Class:16
2018-02-09 11:25:16,558 [81] ERROR Umbraco.Core.DatabaseContext - Database configuration failed
System.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_umbracoUserGroup2NodePermission_umbracoNode_id". The conflict occurred in database "SiteName_Umbraco", table "dbo.umbracoNode", column 'id'.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
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__DisplayClass2_0.<ExecuteNonQueryWithRetry>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.Execute(String sql, Object[] args)
at Umbraco.Core.Persistence.Migrations.MigrationRunner.ExecuteMigrations(IMigrationContext context, Database database)
at Umbraco.Core.Persistence.Migrations.MigrationRunner.Execute(Database database, DatabaseProviders databaseProvider, Boolean isUpgrade)
at Umbraco.Core.Persistence.Migrations.MigrationRunner.Execute(Database database, Boolean isUpgrade)
at Umbraco.Core.DatabaseContext.UpgradeSchemaAndData(IMigrationEntryService migrationEntryService)
ClientConnectionId:db1dfe01-8043-45b8-b4ee-114b8833501e
Error Number:547,State:0,Class:16
2018-02-09 11:25:16,564 [81] ERROR Umbraco.Web.Install.Controllers.InstallApiController - Installation step DatabaseUpgrade failed.
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Umbraco.Web.Install.InstallException: The database failed to upgrade. ERROR: The database configuration failed with the following message: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_umbracoUserGroup2NodePermission_umbracoNode_id". The conflict occurred in database "SiteName_Umbraco", table "dbo.umbracoNode", column 'id'.
The statement has been terminated.
Please check log file for additional information (can be found in '/App_Data/Logs/UmbracoTraceLog.txt')
at Umbraco.Web.Install.InstallSteps.DatabaseUpgradeStep.Execute(Object model)
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at Umbraco.Web.Install.Controllers.InstallApiController.ExecuteStep(InstallSetupStep step, JToken instruction)
at Umbraco.Web.Install.Controllers.InstallApiController.PostPerformInstall(InstallInstructions installModel)
2018-02-09 11:25:16,565 [81] ERROR Umbraco.Web.Install.Controllers.InstallApiController - An error occurred during installation step DatabaseUpgrade
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Umbraco.Web.Install.InstallException: The database failed to upgrade. ERROR: The database configuration failed with the following message: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_umbracoUserGroup2NodePermission_umbracoNode_id". The conflict occurred in database "SiteName_Umbraco", table "dbo.umbracoNode", column 'id'.
The statement has been terminated.
Please check log file for additional information (can be found in '/App_Data/Logs/UmbracoTraceLog.txt')
at Umbraco.Web.Install.InstallSteps.DatabaseUpgradeStep.Execute(Object model)
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at Umbraco.Web.Install.Controllers.InstallApiController.ExecuteStep(InstallSetupStep step, JToken instruction)
at Umbraco.Web.Install.Controllers.InstallApiController.PostPerformInstall(InstallInstructions installModel)
I couldn't see anything relevant in the version-specific upgrade notes but comparing to another site running 7.7.x the umbracoUser2UserGroup table has two columns userId & userGroupId so has a migration somehow not run?
I've repeated the upgrade steps again and the issue remains. I will have to try it on clean installs of the versions I am upgrading from and then start an issue on the tracker if I can replicate it outside of this site.
The plot thickens! No issues with the standalone version upgrades. I've checked the original database for 6.2.5 on the live site and it already has the following tables:
umbracoUserGroup
umbracoUser2userGroup
…how so? Pretty sure they didn't appear in my standalone installs until the upgrade to 7.7. x. Is there another package that might have installed them in an earlier version of Umbraco?
I agree, they shouldn't be there. Only two reported installed Packages which are uBlogsy & Examine Dashboard and I don't think it is either of those. That's not to say that there hasn't previously been a package installed that didn't clean up after itself.
They have no data in them so I dropped them however I am still left with another exception preventing me from completing the upgrade.
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_umbracoUserGroup2NodePermission_umbracoNode_id". The conflict occurred in database "SiteName_Umbraco", table "dbo.umbracoNode", column 'id'
That does lead me to believe though that there is a node in the umbracoUser2NodePermission table that no longer exists and therefore cannot be migrated to the new umbracoUserGroup2NodePermission or something like that.
I had this issue. In my case, this poor database had been upgraded from v4 many years ago to v6, then v7, 7.1, 7.22 and we were trying to take it to 7.9.
The upgrade from 7.6.13 to 7.7.0 failed with the error in this post.
For us, this came down to a massive amount of orphaned rows in umbracoUser2NodePermission that no longer had records matching in umbracoNode.
DELETE FROM [dbo].[umbracoUser2NodePermission] WHERE nodeId IN (SELECT DISTINCT p.nodeId FROM [dbo].umbracoUser2NodePermission] p left join umbracoNode u on u.id = p.nodeId where u.id is null)
Purging them with the above query finalised the upgrade, and the new tables (umbracoUserGroup etc.) were finally created.
Upgrade issue from 7.6.13 > 7.7+ - Invalid column name 'userId'
I'm in the process of performing an upgrade and all has gone well so far, my path has been:
6.2.5>7.5.14>7.6.13I am now trying to go
7.6.13>7.8.0(also tried7.7.10) however I have hit a show stopper, the upgrade fails immediately with the following exception:When I check in the log file I have the following
I couldn't see anything relevant in the version-specific upgrade notes but comparing to another site running 7.7.x the
umbracoUser2UserGrouptable has two columnsuserId&userGroupIdso has a migration somehow not run?I've repeated the upgrade steps again and the issue remains. I will have to try it on clean installs of the versions I am upgrading from and then start an issue on the tracker if I can replicate it outside of this site.
The plot thickens! No issues with the standalone version upgrades. I've checked the original database for 6.2.5 on the live site and it already has the following tables:
umbracoUserGroup umbracoUser2userGroup
…how so? Pretty sure they didn't appear in my standalone installs until the upgrade to 7.7. x. Is there another package that might have installed them in an earlier version of Umbraco?
The two tables shouldn't be there by default in 6.2.5, so it must come from a package that managed to use the exact same names.
Have do you checked which packages are installed in the solution? By regular Umbraco packages and NuGet packages?
Anyways, assuming you have a backup of the database, I would try to delete the two tables (and their indexes), and then try the update again.
I agree, they shouldn't be there. Only two reported installed Packages which are uBlogsy & Examine Dashboard and I don't think it is either of those. That's not to say that there hasn't previously been a package installed that didn't clean up after itself.
They have no data in them so I dropped them however I am still left with another exception preventing me from completing the upgrade.
That does lead me to believe though that there is a node in the
umbracoUser2NodePermissiontable that no longer exists and therefore cannot be migrated to the newumbracoUserGroup2NodePermissionor something like that.So, solution was…
Drop the following tables
umbracoUserGroupumbracoUser2userGroup...and, my suspicions were confirmed with 600+ permissions set for nodes that had been removed. The following cleared them up:
Btw, in Umbraco 7.7.7, the to tables look like this:
Yes, even the column names are different in
umbracoUser2UserGroupI had this issue. In my case, this poor database had been upgraded from v4 many years ago to v6, then v7, 7.1, 7.22 and we were trying to take it to 7.9.
The upgrade from 7.6.13 to 7.7.0 failed with the error in this post.
For us, this came down to a massive amount of orphaned rows in umbracoUser2NodePermission that no longer had records matching in umbracoNode.
Purging them with the above query finalised the upgrade, and the new tables (umbracoUserGroup etc.) were finally created.
... and don't forget to remove your cookies if you are running against localhost
is working on a reply...