Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Simon Dingley 1385 posts 3236 karma points c-trib
    Feb 09, 2018 @ 11:45
    Simon Dingley
    1

    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?

  • Simon Dingley 1385 posts 3236 karma points c-trib
    Feb 09, 2018 @ 12:55
    Simon Dingley
    0

    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.

  • Simon Dingley 1385 posts 3236 karma points c-trib
    Feb 09, 2018 @ 13:48
    Simon Dingley
    0

    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?

  • Anders Bjerner 407 posts 2269 karma points MVP 3x admin c-trib
    Feb 09, 2018 @ 15:52
    Anders Bjerner
    0

    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.

  • Simon Dingley 1385 posts 3236 karma points c-trib
    Feb 09, 2018 @ 16:04
    Simon Dingley
    0

    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.

  • Simon Dingley 1385 posts 3236 karma points c-trib
    Feb 09, 2018 @ 16:42
    Simon Dingley
    0

    So, solution was…

    Drop the following tables

    • umbracoUserGroup
    • umbracoUser2userGroup

    ...and, my suspicions were confirmed with 600+ permissions set for nodes that had been removed. The following cleared them up:

    SELECT        userId, nodeId, permission
    FROM            umbracoUser2NodePermission AS t1
    WHERE        (NOT EXISTS
                                 (SELECT        id
                                   FROM            umbracoNode AS t2
                                   WHERE        (t1.nodeId = id)))
    
  • Anders Bjerner 407 posts 2269 karma points MVP 3x admin c-trib
    Feb 09, 2018 @ 15:58
    Anders Bjerner
    1

    Btw, in Umbraco 7.7.7, the to tables look like this:

    image

    image

  • Simon Dingley 1385 posts 3236 karma points c-trib
    Feb 09, 2018 @ 16:05
    Simon Dingley
    0

    Yes, even the column names are different in umbracoUser2UserGroup

  • Simon Miller 13 posts 36 karma points
    Mar 16, 2018 @ 03:54
    Simon Miller
    1

    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.

  • Damiaan 434 posts 1278 karma points MVP 2x c-trib
    Feb 20, 2019 @ 21:21
    Damiaan
    0

    ... and don't forget to remove your cookies if you are running against localhost

Please Sign in or register to post replies

Write your reply to:

Draft