U4-10472 - Cannot upgrade to 7.7+ when using MySql

Created by jason Ladley 27 Sep 2017, 14:19:13 Updated by Marco Teodoro 15 Jun 2018, 13:31:35

Tags: Unscheduled Gold partner

Is duplicated by: U4-10683

I have been attempting to update my Umbraco project from version 7.6.3 to the current latest version 7.7.1. I have been able to update my project to version 7.6.8, but I am unable to update to 7.7.1 or 7.7.0. This is because I am unable to log into umbraco to authorize the Upgrade. Attempting to log in I see the error "Table '[MY_DATABASE_NAME].umbracouser2usergroup' doesn't exist.". I am using a mysql database as my datasource. Is there anything I can do to my project or database in version 7.6.8 to prepare me for the 7.7+ update? I have backed up both my code and my database structure and data prior to attempting this update.

If you would like to recreate my chicken or the egg situation do the following.

  1. Create a new database on your mysql server with a command like "create database umbraco_7_6_3_to_7_7_1".
  2. Create an empty ASP.NET Web Application called umbraco_7_6_3_to_7_7_1.
  3. Use NuGet and in the package manager console run "Install-Package UmbracoCms -Version 7.6.3"
  4. After the install is complete build and debug
  5. Enter your username and password and click the customize button. Choose the database type MySQL and then enter your server information, database name, login, and password. Click the continue button.
  6. Choose the "No Thanks, I do not want to install a starter website option.
  7. Once the install is complete and your in the Umbraco back office stop the debug in Visual Studio.
  8. Debug again and test your login. After logging in stop the debug.
  9. Now using NuGet and the package manager console run the command "Install-Package UmbracoCms -Version 7.7.1"
  10. Debug and then attempt to login. The page you will see on debuging is http://localhost:[portNumber]/umbraco/AuthorizeUpgrade........
  11. Your login will fail. The debugging will lauch an error window. Saying "Profiler DbCommand.cs not found" and "MySqlException was unhandled by user code" "Table 'umbraco_7_6_3_to_7_7_1.umbracouser2usergroup' doesn't exist"

Will your next release 7.7.2 handle upgrades from 7.6 to 7.7? Is there a mysql script file or a patch that can be released? Please advise on what I should do to update Umbraco.

Digging around this seems to be related to http://issues.umbraco.org/issue/U4-10138, but the developer thought they entered a bad password... I have just tested with a clean install and recreated this issue. See Stephan's note on the 27th of July 2017 at 18:02.

Thanks in advance, Jason

5 Attachments

Download databaseDump.zip

Download UmbracoTraceLog.DESKTOP-C8L18SK.txt

Comments

Sebastiaan Janssen 27 Sep 2017, 14:49:48

You meant: Update-Package UmbracoCms -Version 7.7.1 - correct? You should not start a fresh install. Make sure to answer "No to all" when asked to overwrite config files (see documentation: https://our.umbraco.org/documentation/Getting-Started/Setup/Upgrading/general#are-you-using-nuget)

If that is not the problem, then it's probably a MySQL issue, I can't reproduce this problem on SQL server using the steps you mentioned.

Made a video, it's at 200% speed so you might want to pause at some points for details. https://www.dropbox.com/s/trthswz8fo7qjzs/U4-10472.mp4?dl=0


jason Ladley 27 Sep 2017, 15:35:34

@sebastiaan

Thanks for the fast response! Yes I meant "Update-Package UmbracoCms -Version 7.7.1" but I was copying and pasting in my steps above and ended up writing "Install" instead.

I watched your video at 200% speed and can confirm that I followed the same steps, but used mysql as my datasource. Would you like me to make a video at 200% speed showing the error when using mysql?

Jason


jason Ladley 27 Sep 2017, 18:21:39

@sebastiaan

I've gone ahead and made a video documenting this bug. This video shows a clean install of Umbraco using MySql as the datasource. Skip ahead to minute 13 to see the error being thrown from visual studio. It looks like the file ProfiledDbCommand.cs could not be found when I clicked Login. That in turn throws the error "Table 'umbraco_7_6_3_to_7_7_1.umbracouser2usergroup' doesn't exist". So it looks like I can't update because I can't login. I can't login because the update is expecting code or database changes not shipped in the previous version. This issue exists going from 7.6.8 to 7.7.1 as well.

Here is the link https://youtu.be/hphoAoZMbBY

Note: I've used overlays to hide some of my personal information.


Sebastiaan Janssen 28 Sep 2017, 08:17:09

Do you have your MySQL database configured as being case sensitive or not case sensitive? FYI: Umbraco only works well if you turn off case sensitivity in MySQL.


Sebastiaan Janssen 28 Sep 2017, 08:22:33

Make sure to post your full log file as well as I can't see exactly where this error is coming from. The table umbracoUser2Usergroup is not supposed to exist, it should get added during the 7.7 upgrade. So I'm not sure exactly what triggers this error.


Gijs van Dam 29 Sep 2017, 09:37:54

@jladley I have the same issue (upgrading from 7.0.0 to 7.7.1) I don't use mySQL but MS SQL, so I don't think it's related to mySQL. cc: @sebastiaan


Gijs van Dam 29 Sep 2017, 09:49:29

@sebastiaan Full log:

2017-09-29 17:46:46,191 [P16380/D11/T41] ERROR Umbraco.Core.Persistence.UmbracoDatabase - Exception (f01d4014). System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'umbracoUser2UserGroup'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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, TaskCompletionSource1 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 Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClass5_0.b__0() at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func) at Umbraco.Core.Persistence.Database.<Query>d__1151.MoveNext() ClientConnectionId:6c794298-9d3d-484c-967a-05241c6ab80a Error Number:208,State:1,Class:16 2017-09-29 17:46:46,201 [P16380/D11/T41] INFO Umbraco.Core.Security.BackOfficeSignInManager - Event Id: 0,
state: Login attempt failed for username gijs@test.com from IP address 127.0.0.1


Sebastiaan Janssen 29 Sep 2017, 13:02:48

@gijswijs Can you send me a WeTransfer with a copy of the database you're trying to upgrade please? Make sure to put a strong zip password on the file and send it to me in a separate email: sj@umbraco.dk

If we can easily replicate it, we should be able to easily fix it! :-)


Sebastiaan Janssen 29 Sep 2017, 13:03:56

Neither of you changed the version in your web.config right? When upgrading Umbraco the old version is still set in umbracoConfigurationStatus and the connection string to the database is also still in your web.config?


jason Ladley 29 Sep 2017, 13:08:42

@sebastiaan I will post my full exception log on Monday. I am out of the office until then. Also, I did not manually change the version number in the web.config. I've seen that Umbraco does that after an update takes place through the web GUI.

Something to mention: I noticed that a clean install of Umbraco 7.7.1 using my same MySql database did work for me. It created a database with the umbracoUser2UserGroup table and a few others, but the update of my existing project did not. Also a clean install of Umbraco 7.6.8 or 7.6.3 did NOT add the umbracoUser2UserGroup table and a few others added from a 7.7.1 install. I think a clean install of Umbraco 7.7.0 added the tables I needed as well, but I'm not sure since I tested a few days ago.

Also, I think I have case sensitivity turned off on my database, but again I will confirm on Monday. The clean install of Umbraco 7.7.1 added the umbracoUser2UserGroup table so I don't think that's the issue.

@gijswijs It sounds like your issue is similar. Basically, when you go to update Umbraco you can't log in to authorize the update.


Sebastiaan Janssen 29 Sep 2017, 13:51:38

Thanks, not much I can do with a log unfortunately. :-)


jason Ladley 02 Oct 2017, 14:19:38

@sebastiaan

This morning I tried updating from 7.6.8 to 7.7.1

I have verified that my MySQL Database is set to be case insensitive. Attached is a picture showing that.

I have also attached a screenshot of Visual Studio trying to locate the ProfiledDbCommand.cs file. It strangely seems to be looking for the file at c:\Code\github\SamSaffron\MiniProfiler\StackExchange.Profiling\Data\ProfiledDbCommand.cs. Is Sam Saffron a developer on your team?

I have also attached a strong zip password protected file that contains the database structure and the database data for the actual Umbraco site I'd like to update. Please send me a message to request the password.

Finally here is the log when the error is thrown. MySql.Data.MySqlClient.MySqlException was unhandled by user code HResult=-2147467259 Message=Table 'umbraco_7_6_8_to_7_7_1.umbracouser2usergroup' doesn't exist Source=MySql.Data ErrorCode=-2147467259 Number=1146 StackTrace: at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.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 235 at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClass5_0.b__0() InnerException:

I hope all this helps! Jason


Sebastiaan Janssen 02 Oct 2017, 18:05:43

Ah thanks @jladley - unfortunately I have no setup to test out MySQL at the moment. I'm really hoping @gijswijs will make it easy on us and can send a SQL database to reproduce.


Gijs van Dam 03 Oct 2017, 01:20:13

I'm an ass, did I tell you that?

So all this happened to me after an upgrade fro 4.10.11 all the way up to 7.7.1

I knew that in 7 you were supposed to login with you email address instead with you userLogin. Also I knew you had to use useLegacyEncoding="true" in the UsersMembershipProvider key in web.config

So I got the "umbracouser2usergroup doesn't exist" error, and I thought that I used the correct credentials, but that this error was blocking me signing on.

Because it was a SQL error I used SQL profiler to investigate a little bit more.

exec sp_executesql N'SELECT umbracoUser.* FROM [umbracoUser] WHERE (([umbracoUser].[userLogin] = @0))',N'@0 nvarchar(4000)',@0=N'info@test.com'

So it appears to be using the userLogin, still After using the userLogin I got a whole bunch more statements in SQL Profiler, including the one below:

exec sp_executesql N'UPDATE [umbracoUser] SET [securityStampToken] = @0, [failedLoginAttempts] = @1, [createDate] = @2, [updateDate] = @3 WHERE [id] = @4',N'@0 nvarchar(4000),@1 int,@2 datetime,@3 datetime,@4 int',@0=N'3e9caa39-1611-438b-800e-d8560700670d',@1=1,@2='2017-10-03 08:56:34.123',@3='2017-10-03 08:56:34.123',@4=0

So the login was failing!

Then I checked the UsersMembershipProvider key in web.config to find out that useLegacyEncoding was not set to "true".

I set it to true, logged on with userLogin and password, and got to the upgrade screen.

So long story short, I think I ran through tens of different combinations of useLegacyEncoding, userLogin, email address, different hashed passwords I know the plaintext password of, and maybe in between also mixed up the UsersMembershipProvider and UmbracoMembershipProvider keys, and accidentally never hit the right combination.

But due to the awesomeness of SQL Profiler I analyzed my way out of this.

@jladley Could you make sure you are using the correct settings as well?

  • Log on with your userLogin (check table umbracoUser to be sure)
  • Use useLegacyEncoding="true" in the UsersMembershipProvider key in web.config
  • Use the correct pasword (doh!)

P.S. @sebastiaan I'm not uploading my db, because if it's something else that is causing Jason's problem, you can't reproduce it with my database.


jason Ladley 03 Oct 2017, 14:10:27

@gijswijs Thanks for your suggestions. I'm glad you resolved your situation. On my side, I tried setting useLegacyEncoding="true" but it did not work for me. Also, I am using the correct password and user login. I've looked at the "UserLogin" field from my umbracoUser table.

@sebastiaan I could try a clean install on Umbraco 7.7.2 and use a new database. That will create the umbracouser2usergroup table and supporting tables for me. I could then take those tables and import them into my website's database. However, I would need direction on how to merge and remove depreciated tables. Would this be worth trying?


Sebastiaan Janssen 03 Oct 2017, 16:11:21

I can reproduce now and I have a workaround, but 7.7.2 is not going to work for you yet if you want to be able to update users due to this bug: http://issues.umbraco.org/issue/U4-10492

So the workaround:

  • Make sure to open a private browsing window so you don't have any localhost cookies laying around any more (actually, the error you're seeing is very closely related to the one @gijswijs was seeing but I'm guessing the problem is a bit more complicated in MySQL)
  • In your particular database there's a key and an index named wrong, this SQL script will help: ALTER TABLE your_site_name_website.umbracouser DROP FOREIGN KEY umbracouser_ibfk_1; ALTER TABLE your_site_name_website.umbracouser DROP INDEX userType , ADD INDEX umbracoUser_PK_user (userType ASC); ALTER TABLE your_site_name_website.umbracouser ADD CONSTRAINT umbracoUser_FK_umbracoUser_umbracoUserType_id FOREIGN KEY (userType) REFERENCES your_site_name_website.umbracousertype (id);
  • Go into web.config and set the umbraco version to empty (so: <add key="umbracoConfigurationStatus" value="" />)
  • Change the connection string and make it empty too (so: <add name="umbracoDbDSN" connectionString="" providerName="" />)
  • You will then asked to install Umbraco again, enter your user details
  • Click on the customize button to provide the MySQL connection details
  • Umbraco will detect then that you already have a database and will try to upgrade it
  • You MIGHT get an error the upgrade, I clicked "Back" and tried again and then it worked

This should upgrade everything but you will run into http://issues.umbraco.org/issue/U4-10492 instead

FYI: MySQL support is unfortunately very low priority on our list so it will take a while for all of this to be fixed.


jason Ladley 03 Oct 2017, 18:33:18

@sebastiaan Thank you Sebastiaan! This worked for me. Although I did have to remove the umbracoUser_PK_user index and umbracoUser_FK_umbracoUser_umbracoUserType_id constraint for the update and then add afterwards. I am now running version 7.7.2

You are right, I am experiencing a similar issue to http://issues.umbraco.org/issue/U4-10492 but in my case I have no options on the user screen and no error messages... See attached image. I will be watching http://issues.umbraco.org/issue/U4-10492 for updates


Sekou Terry 23 Oct 2017, 22:53:30

I've come across a similar issue upgrading from v7.6.7 to v7.7.3, running MS SQL. useLegacyEncoding is set to true for every membership provider in my web.config. Whenever I try to upgrade versions, I'll get to the authorize upgrade screen and then see a 'Login failed' notification. As with the log files above, I'm seeing an SqlException: Invalid object name 'umbracoUser2UserGroup'.


Mirko Matytschak 25 Oct 2017, 14:09:02

I have a similar issue with Sql CE. I don't know exactly, if this is related to this current issue. I can Login and see the upgrade screen. It shows the correct versions:

''To read a report of changes between your current version 7.6.3 and this version you're upgrading to 7.7.4''

useLegacyEncoding="true" (otherwise I couldn't get to the upgrade screen).

Pressing ''Continue'' I get the error screen (error during installation).

Further examination shows that even before I press ''Continue'' in the upgrade screen the following log message appears:

2017-10-25 16:00:21,897 [P15548/D6/T19] ERROR Umbraco.Core.Persistence.UmbracoDatabase - Exception (c9ce2b93). System.Data.SqlServerCe.SqlCeException (0x80004005): The specified table does not exist. [ umbracoUser2UserGroup ] bei System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr) bei System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan() bei System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options) bei System.Data.SqlServerCe.SqlCeCommand.ExecuteDbDataReader(CommandBehavior behavior) bei Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClass5_0.b__0() bei Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func) bei Umbraco.Core.Persistence.Database.<Query>d__1151.MoveNext()

After pressing ''Continue'' no further error message appears in the log, but the upgrade fails anyway.


Mirko Matytschak 25 Oct 2017, 14:42:42

This workaround solved the problem for me. After calling the API with /umbraco/api/Version/update I set the umbracoConfigurationStatus to 7.7.4. Seems to work now.

using System; using System.Linq; using System.Web.Http; using Semver; using Umbraco.Core; using Umbraco.Core.Logging; using Umbraco.Core.Persistence.Migrations; using Umbraco.Web; using Umbraco.Web.WebApi; using Umbraco.Core.Configuration;

namespace Formfakten { public class VersionController : UmbracoApiController { [HttpGet] public object Update() { const string productName = "Umbraco"; var currentVersion = new SemVersion( 7, 6, 3 );

        var migrations = ApplicationContext.Current.Services.MigrationEntryService.GetAll( productName );
        var latestMigration = migrations.OrderByDescending( x => x.Version ).FirstOrDefault();

        if (latestMigration != null)
            currentVersion = latestMigration.Version;

        var targetVersion = new SemVersion( 7, 7, 4 );
        if (targetVersion == currentVersion)
            return "Nothing to update";

        var migrationsRunner = new MigrationRunner(
          ApplicationContext.Current.Services.MigrationEntryService,
          ApplicationContext.Current.ProfilingLogger.Logger,
          currentVersion,
          targetVersion,
          productName );

        try
        {
            migrationsRunner.Execute( UmbracoContext.Current.Application.DatabaseContext.Database );
        }
        catch (Exception e)
        {
            LogHelper.Error<VersionController>( "Error running Umbraco migration", e );
			return e.ToString();
        }
		
		return "OK";
    }		
}

}


Matthew 29 Nov 2017, 19:57:27

I just ran into this issue upgrading from 7.5.13 to 7.7.6 on a site connected to SQL Server Express.

EDIT: I got this to work. The useLegacyCoding="true" attribute needs to be added to the UsersMembershipProvider, not just the UmbracoMembershipProvider. This isn't immediately obvious if you're following the upgrade guide.


Shannon Deminick 02 Mar 2018, 00:40:27

This is fixed in PR: https://github.com/umbraco/Umbraco-CMS/pull/2493

The problem is due to us only checking for exception types specific to Sql Server and SqlCe. There's a work around in place for logging in to upgrade and dealing with the error that umbracouser2usergroup doesn't exist. However, this check excluded the exception type thrown by MySql. The change uses the more generic DbException type instead of the more explicit SqlException or SqlCeException types and we've used this work around in the UserService before and correctly used DbException in that case. So this is unfortunate since we should have known to use DbException based on previous experience.

Once that work around is in place, the upgrader still fails and this is because we are trying to drop a column: umbracoUser.userType, however there is a Foreign Key assigned to this column and although MSSQL will implicitly drop the FK when dropping the column, MySql is not so forgiving. So the easy fix is to explicitly drop the FK before dropping the column.

For testing, I've tested:

  • Upgrading from 7.5.8 -> 7.9.3 on MySql
  • Upgrading from 7.5.8 -> 7.9.3 on SqlCe
  • Upgrading from 7.5.8 -> 7.9.3 on MSSQL

and all works. As for the explicit FK drop, you can see in the logs output that all of these servers are ok with explicitly dropping the FK before the column (which makes total sense). Here's the log outputs:

MySql

2018-03-02 11:02:22,365 [P8684/D23/T21] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Executing sql statement 83: ALTER TABLE `umbracoUser` DROP FOREIGN KEY `umbracouser_ibfk_1`

2018-03-02 11:02:22,393 [P8684/D23/T21] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Executing sql statement 84: ALTER TABLE `umbracoUser` DROP COLUMN `userType`

MSSQL + SqlCe

2018-03-02 11:23:03,409 [P8684/D26/T18] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Executing sql statement 84: ALTER TABLE [umbracoUser] DROP CONSTRAINT [FK_umbracoUser_umbracoUserType_id]

2018-03-02 11:23:03,414 [P8684/D26/T18] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Executing sql statement 85: ALTER TABLE [umbracoUser] DROP COLUMN [userType]


Sebastiaan Janssen 21 Mar 2018, 13:27:29

Alright, unfortunately that seems to break something. On non- MySQL. I have installed a clean 7.5.8 (NuGet) using SQL CE, then upgraded it to the output of this PR:

update-package umbracocms -version 7.9.2-alpha201803011723 -source c:\nugetrepo -pre

This succeeds but when I run the project and the upgrader I get an error:

2018-03-21 14:24:01,655 [P192/D3/T5] ERROR Umbraco.Core.Persistence.UmbracoDatabase - Exception (1ca1f16a). System.Data.SqlServerCe.SqlCeException (0x80004005): The foreign key constraint does not exist. [ FK_umbracoUser_umbracoUserType_id ] at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr) at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr& pCursor, Boolean& isBaseTableCursor) at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options) at System.Data.SqlServerCe.SqlCeCommand.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.b__0() at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 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) 2018-03-21 14:24:01,671 [P192/D3/T5] ERROR Umbraco.Core.DatabaseContext - Database configuration failed System.Data.SqlServerCe.SqlCeException (0x80004005): The foreign key constraint does not exist. [ FK_umbracoUser_umbracoUserType_id ] at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr) at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr& pCursor, Boolean& isBaseTableCursor) at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options) at System.Data.SqlServerCe.SqlCeCommand.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](Func1 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)

Log file attached.

I did this twice, just to make sure I didn't do anything silly.


Shannon Deminick 21 Mar 2018, 22:43:05

Is this just in the log file? Or does the upgrade not work? The log file is normal, it will log this for all upgrades. I'll retest though


Shannon Deminick 21 Mar 2018, 22:46:02

Ah sorry, I misread. It's the fk drop change. I tested in mssql but not sqlce. That is odd though. I'll fix up and test asap


Shannon Deminick 22 Mar 2018, 04:27:48

Will debug this, something is odd on this one since this statement (as seen in your logs) executes twice, but there's only one line of code to execute this so we might also have an underlying issue in our Sql Syntax stuff that we never knew about:

 2018-03-21 14:24:01,640 [P192/D3/T5] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Executing sql statement 84: ALTER TABLE [umbracoUser] DROP CONSTRAINT [FK_umbracoUser_umbracoUserType_id]

 2018-03-21 14:24:01,655 [P192/D3/T5] INFO  Umbraco.Core.Persistence.Migrations.MigrationRunner - Executing sql statement 85: ALTER TABLE [umbracoUser] DROP CONSTRAINT [FK_umbracoUser_umbracoUserType_id]

stay tuned, will figure this out.


Shannon Deminick 22 Mar 2018, 04:55:45

Duh... i'm an idiot. Fixed in rev https://github.com/umbraco/Umbraco-CMS/commit/760175dcb289122966afe677bc89311d1f5db803?w=1

Here was the problem: I was using the nice syntax Delete.ForeignKey().FromTable("umbracoUser").ForeignColumn("userType").ToTable("umbracoUserType").PrimaryColumn("id"); to drop the FK which does work for all DB types BUT, i was blind and didn't notice that I was already explicitly dropping the FK above this line with this call Delete.ForeignKey("FK_umbracoUser_umbracoUserType_id").OnTable("umbracoUser"); ... that syntax doesn't work for MySql, only for MSSQL/SQLCE. So what was happening is that it was trying to double drop the FK in SQL servers which is why you saw that exception.

The fix is easy, and we do this in a few migrations already:

  • Check if it's MySql and use the nice syntax
  • Else, continue what we were doing before and explicitly dropping the FK by name (and even the legacy FK name)


Marco Teodoro 15 Jun 2018, 13:31:35

I @Shannon i'm trying to upgrade version 7.6.3 to the latest version. 7.10 but getting this error. The specified table does not exist. [ umbracoUser2UserGroup ] i've tried first upgrading to version 7.7 since i saw that this table was introduced in that version but no luck. then i found this ticket and tried first upgrading to v 7.9.3 then 7.9.4 and same issue. does't anyone found a workaround? i'm using SQL server not mysql.


Priority: Normal

Type: Bug

State: Fixed

Assignee:

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions:

Due in version: 7.9.3

Sprint: Sprint 81

Story Points: 1

Cycle: 9