I am attempting to upgrade a site from 7.6.4 to 7.15.6. I followed the general guidelines for upgrading and noted that there is a change in the way users are assigned to types/roles/groups in version 7.7.0.
After updating the files and merging configuration updates, etc., I pulled up the site and attempted to run the database update. The initial screen came up fine, but when I pressed the button to perform the upgrade, an error was generated.
Here are the relevant lines from the trace log:
2020-10-22 15:59:21,066 [P66024/D6/T62] INFO Umbraco.Core.Persistence.Migrations.MigrationRunner - Executing sql statement 26: INSERT INTO umbracoUserGroup(userGroupAlias, userGroupName)
SELECT 'permissionGroupFor' + userLogin, 'Migrated Permission Group for ' + userLogin
FROM umbracoUser
WHERE (id IN (
SELECT userid
FROM umbracoUser2NodePermission
))
AND id > 0
2020-10-22 15:59:21,112 [P66024/D6/T62] ERROR Umbraco.Core.Persistence.UmbracoDatabase - Exception (e009997d).
MySql.Data.MySqlClient.MySqlException (0x80004005): Truncated incorrect DOUBLE value: 'permissionGroupFor'
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, 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.ExecuteNonQuery()
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.Database.Execute(String sql, Object[] args)
2020-10-22 15:59:21,114 [P66024/D6/T62] ERROR Umbraco.Core.DatabaseContext - Database configuration failed
Umbraco.Core.Persistence.Migrations.DataLossException: An error occurred running a schema migration but the changes could not be rolled back. Error: Truncated incorrect DOUBLE value: 'permissionGroupFor'. In some cases, it may be required that the database be restored to it's original state before running this upgrade process again. ---> MySql.Data.MySqlClient.MySqlException: Truncated incorrect DOUBLE value: 'permissionGroupFor'
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, 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.ExecuteNonQuery()
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.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)
--- End of inner exception stack trace ---
at Umbraco.Core.Persistence.Migrations.MigrationRunner.Execute(Database database, DatabaseProviders databaseProvider, Boolean isUpgrade)
at Umbraco.Core.DatabaseContext.UpgradeSchemaAndData(IMigrationEntryService migrationEntryService)
When I try to run the errant query listed in the first line of the log above, I get an odd result. It appears that the + symbol is being used to concatenate strings, but MySql (or at least my version -- MariaDB 10.2.7) doesn't support that form of concatenation.
When I replace the offending lines with calls like:
CONCAT('permissionGroupFor', userLogin)
...the results look much better.
I don't know where to find and fix this error. I searched the entire site directory for files containing "permissionGroupFor", but didn't turn up anything, so I'm guessing it is compiled in a resource file or something. I tried to search the github repository as well, but am not sure what version introduced this query.
Can someone provide some insight as to next steps I can take to resolve this issue?
The SQL you are referring to is inside a 'migration' that runs during the upgrade step - this one is for the leap to 7.7 and the introduction of the new UserGroup tables:
I think the + operator is a valid way of concatenating strings in SQL, as far as I'm aware, and alot of people have upgraded past this step successfully - so I'm not sure it's the + that is the issue.
Is it possible that you have a rogue user entry in your site where the userLogin is blank? or has a character that breaks the SQL?
All my reading indicates that MySQL handles concatenation of strings differently than Microsoft. Rather than the plus operator, the common way to concatenate is with a call to CONCAT().
I tried a simple query using the plus symbol in the database and got an error. After switching the plus operator to a CONCAT() call, the query succeeded.
I was able to locate a helpful bug report that eventually set me on a path to the solution. I'm still working through it, but think I've gotten over the major issue.
I'll post my findings as a separate reply to the question directly.
After a failed attempt at upgrading to 7.15.6, knowing that the 7.7.0 user model updates were the problem, I chose to attempt an upgrade directly to the 7.7 branch, specifically 7.7.14.
The upgrade ended up producing the same error described in my original post. However, this time, I located the issue linked above and followed the steps in Sebastiaan Jenssen's workaround. This got me mostly there.
The process did produce the same error, but after hitting back as he suggested, and then performing the upgrade again, it went through OK.
At this point, I was able to log in but had no permissions. This was due to the failed queries that are, I believe, supposed to approximate the permissions from the old user model within the new user model.
I then fired up a fresh copy of Umbraco 7.7.14 with its own MySQL database and allowed it to complete installation. From there, I took the values defined in the new usergroup tables and copied them over, creating a script that granted me (user 0) full permissions again.
This is where I am in the process now. I believe the remaining steps will be to (1) update the other users in the new user group model and (2) update the custom code in my site that used to make use of the user types.
For the curious, here is the MySQL script I used. Note this does NOT include the queries that Sebastiaan recommends in his workaround. I ran those as well at the appropriate time.
Potential Bug: Upgrading across 7.7.0 with MySql
I am attempting to upgrade a site from 7.6.4 to 7.15.6. I followed the general guidelines for upgrading and noted that there is a change in the way users are assigned to types/roles/groups in version 7.7.0.
After updating the files and merging configuration updates, etc., I pulled up the site and attempted to run the database update. The initial screen came up fine, but when I pressed the button to perform the upgrade, an error was generated.
Here are the relevant lines from the trace log:
When I try to run the errant query listed in the first line of the log above, I get an odd result. It appears that the + symbol is being used to concatenate strings, but MySql (or at least my version -- MariaDB 10.2.7) doesn't support that form of concatenation.
When I replace the offending lines with calls like:
...the results look much better.
I don't know where to find and fix this error. I searched the entire site directory for files containing "permissionGroupFor", but didn't turn up anything, so I'm guessing it is compiled in a resource file or something. I tried to search the github repository as well, but am not sure what version introduced this query.
Can someone provide some insight as to next steps I can take to resolve this issue?
Hi George
The SQL you are referring to is inside a 'migration' that runs during the upgrade step - this one is for the leap to 7.7 and the introduction of the new UserGroup tables:
https://github.com/umbraco/Umbraco-CMS/blob/v7/dev/src/Umbraco.Core/Persistence/Migrations/Upgrades/TargetVersionSevenSevenZero/AddUserGroupTables.cs
and the migration of the content.
I think the + operator is a valid way of concatenating strings in SQL, as far as I'm aware, and alot of people have upgraded past this step successfully - so I'm not sure it's the + that is the issue.
Is it possible that you have a rogue user entry in your site where the userLogin is blank? or has a character that breaks the SQL?
regards
Marc
Thanks for sharing your suggestions, Marc.
All my reading indicates that MySQL handles concatenation of strings differently than Microsoft. Rather than the plus operator, the common way to concatenate is with a call to CONCAT().
I tried a simple query using the plus symbol in the database and got an error. After switching the plus operator to a CONCAT() call, the query succeeded.
I was able to locate a helpful bug report that eventually set me on a path to the solution. I'm still working through it, but think I've gotten over the major issue.
I'll post my findings as a separate reply to the question directly.
I found the way toward a solution to my problem here:
https://issues.umbraco.org/issue/U4-10472#comment=67-42233
After a failed attempt at upgrading to 7.15.6, knowing that the 7.7.0 user model updates were the problem, I chose to attempt an upgrade directly to the 7.7 branch, specifically 7.7.14.
The upgrade ended up producing the same error described in my original post. However, this time, I located the issue linked above and followed the steps in Sebastiaan Jenssen's workaround. This got me mostly there.
The process did produce the same error, but after hitting back as he suggested, and then performing the upgrade again, it went through OK.
At this point, I was able to log in but had no permissions. This was due to the failed queries that are, I believe, supposed to approximate the permissions from the old user model within the new user model.
I then fired up a fresh copy of Umbraco 7.7.14 with its own MySQL database and allowed it to complete installation. From there, I took the values defined in the new usergroup tables and copied them over, creating a script that granted me (user 0) full permissions again.
This is where I am in the process now. I believe the remaining steps will be to (1) update the other users in the new user group model and (2) update the custom code in my site that used to make use of the user types.
For the curious, here is the MySQL script I used. Note this does NOT include the queries that Sebastiaan recommends in his workaround. I ran those as well at the appropriate time.
is working on a reply...