I tried to update a db from v 7.115.1 to 8.1.1 (through 4.11.10 and 7.7.13) but the update fails with the following error:
The database failed to upgrade. ERROR: The database configuration failed with the following message: Cannot insert the value NULL into column 'versionId', table 'brandtoolboxUmb7.14.dbo.cmsPropertyData'; column does not allow nulls. UPDATE fails. The statement has been terminated. Please check log file for additional information (can be found in '/AppData/Logs/')
Does anyone know how to fix this and complete the upgrade?
This probably means that you have rows in cmsPropertyData where the versionId doesn't exist in cmsContentVersion.
The migration wants to change versionId to be an integer rather than a GUID. If the GUID doesn't exist in cmsContentVersion then it has no integer ID to use for that version, and ends up trying to assign a null value.
Experiencing the same issue on a v7 > v8 migration, last answer from Steve didn't really fix it, don't have any version in cmsPropertyData that doesn't exist in cmsContentVersion. Completely blocked at migration...
upgrade to v8.5.5 (if you have a rather large db, check log settings, we've set the max file size to unlimited (it'll jump back to 2gb - but that was fine)
make all changes in backend (in our case, get all doc types ready for migrating from vorto to variants (our setup was using nested content, so all of the NC doc types were also converted to element types)
run the vorto>variants healthcheck (need to google it... not exactly sure where i've found it), did a local customized version of it, as we had some very special edge cases not entirely covered by the healthcheck)
once you've finished that one, migration to v8 latest, v9, v10, v11 is a breeze
Interesting, I've actually run a trial migration skipping v9 entirely and gone as follows:
v7.15.10 > v8.18 > v10.5.1 > v12.x
The hardest work by far has been fixing up all the nested content which is a bit like inception as they can go as deep as they choose in some cases and within each level I have to fix any other now redundant or changing property data. Now I am circling back to fix issues with the data I found later in the process - in particular all of the media was missing.
I have now found what I think to be the cause of the missing media but was hitting this issue with the migration process trying to insert NULL values in VersionId column which I have actually just resolved a moment ago with the following query:
DELETE
FROM [dbo].[cmsPropertyData]
WHERE versionId NOT IN (Select
VersionId FROM cmsContentVersion)
After eyeballing the data I could see all rows were either NULL or empty so it was safe to execute it.
Thanks for the heads-up about converting element types, that's not something I have done and hadn't noticed any issues in not doing so which is interesting (or worrying!).
Hope all is good with you - it's many years since I saw you last!
If you're getting a timeout error rather than just seeing the migration stall for a long time, you'll probably also need to add a large Connection Timeout value to your connection string.
That should get you past the current problem. You may still run into other slow-running parts of the migration. Which property editors you use can affect how much work the migrations need to do.
Thanks Steve, i got past it by doing that then got stuck again :(
I have found doing the updates / drops myself quite useful so far, like this one which was next to get stuck -
UPDATE cmsPropertyData SET versionId2=cmsContentVersion.id FROM cmsContentVersion INNER JOIN cmsPropertyData ON cmsContentVersion.versionId = cmsPropertyData.versionId
{"@t":"2020-02-20T15:00:10.4822147Z","@mt":"SQL [{ContextIndex}]: {Sql}","ContextIndex":4197,"Sql":"ALTER TABLE [cmsContentVersion] DROP COLUMN [ContentId];","SourceContext":"Umbraco.Core.Migrations.Expressions.Delete.Expressions.DeleteColumnExpression","ProcessId":8356,"ProcessName":"iisexpress","ThreadId":5,"AppDomainId":2,"AppDomainAppId":"LMW3SVC2ROOT","MachineName":"LAPTOP","Log4NetLevel":"INFO ","HttpRequestNumber":7,"HttpRequestId":"bb149359-90b8-4804-afad-25bb82cdd03f"}
So i will wait to see if you's figure it out there!
I was trying Umbraco 7.14 to 8.5.3 at first then 7.15.3 to 8.5.3 then started again with 7.15.3 to 8.1.5 (as the docs refer to 8.1x for upgrades) and 8.1 seems to get stuck less, so not sure what's best v8 to go for.
Update to Umbraco 7.15.1 -> 8.1.1 Fails
Hello.
I tried to update a db from v 7.115.1 to 8.1.1 (through 4.11.10 and 7.7.13) but the update fails with the following error:
The database failed to upgrade. ERROR: The database configuration failed with the following message: Cannot insert the value NULL into column 'versionId', table 'brandtoolboxUmb7.14.dbo.cmsPropertyData'; column does not allow nulls. UPDATE fails. The statement has been terminated. Please check log file for additional information (can be found in '/AppData/Logs/')
Does anyone know how to fix this and complete the upgrade?
Thank you
I’m having a similar problem migrating database tables from v7 to v8. I believe this is a known migration issue.
Does anyone know when there will be a fix for migration issue?
This probably means that you have rows in cmsPropertyData where the versionId doesn't exist in cmsContentVersion.
The migration wants to change versionId to be an integer rather than a GUID. If the GUID doesn't exist in cmsContentVersion then it has no integer ID to use for that version, and ends up trying to assign a null value.
Experiencing the same issue on a v7 > v8 migration, last answer from Steve didn't really fix it, don't have any version in cmsPropertyData that doesn't exist in cmsContentVersion. Completely blocked at migration...
--Dirk
How did you get around this @Dirk?
Hi Simon,
As for the migration, i went for this route...
upgrade to v7 latest (was 7.15.10 I think)
upgrade to v8.5.5 (if you have a rather large db, check log settings, we've set the max file size to unlimited (it'll jump back to 2gb - but that was fine)
make all changes in backend (in our case, get all doc types ready for migrating from vorto to variants (our setup was using nested content, so all of the NC doc types were also converted to element types)
run the vorto>variants healthcheck (need to google it... not exactly sure where i've found it), did a local customized version of it, as we had some very special edge cases not entirely covered by the healthcheck)
once you've finished that one, migration to v8 latest, v9, v10, v11 is a breeze
Hope it helps
Dirk
Interesting, I've actually run a trial migration skipping v9 entirely and gone as follows:
v7.15.10 > v8.18 > v10.5.1 > v12.x
The hardest work by far has been fixing up all the nested content which is a bit like inception as they can go as deep as they choose in some cases and within each level I have to fix any other now redundant or changing property data. Now I am circling back to fix issues with the data I found later in the process - in particular all of the media was missing.
I have now found what I think to be the cause of the missing media but was hitting this issue with the migration process trying to insert NULL values in VersionId column which I have actually just resolved a moment ago with the following query:
After eyeballing the data I could see all rows were either NULL or empty so it was safe to execute it.
Thanks for the heads-up about converting element types, that's not something I have done and hadn't noticed any issues in not doing so which is interesting (or worrying!).
Hope all is good with you - it's many years since I saw you last!
All right, good you found your way, migrations are not painless :)
Everything still good, hope to get back to real person conferences, have missed a few episodes already :)
Let's hope we can share a beer again any time soon
Did you even get the upgrade working? I have the same issue.
Its timing out at this line.
There are some big speed improvements on the way for migrations, but I think they'll be in the 8.7.0 release.
You can speed up that step by renaming the
dataNtext
column yourself before running the migration:sp_rename 'cmsPropertyData.dataNtext', 'textValue', 'COLUMN'
If you're getting a timeout error rather than just seeing the migration stall for a long time, you'll probably also need to add a large
Connection Timeout
value to your connection string.That should get you past the current problem. You may still run into other slow-running parts of the migration. Which property editors you use can affect how much work the migrations need to do.
Thanks Steve, i got past it by doing that then got stuck again :(
I have found doing the updates / drops myself quite useful so far, like this one which was next to get stuck -
Then I could drop the versionId column manually.
Now im stuck on the same as this post - https://our.umbraco.com/forum/using-umbraco-and-getting-started/100594-migration-from-714-to-8
So i will wait to see if you's figure it out there!
I was trying Umbraco 7.14 to 8.5.3 at first then 7.15.3 to 8.5.3 then started again with 7.15.3 to 8.1.5 (as the docs refer to 8.1x for upgrades) and 8.1 seems to get stuck less, so not sure what's best v8 to go for.
Also i am same issue.
{"@t":"2023-10-11T05:18:13.0621738Z","@mt":"SQL [{ContextIndex}]: {Sql}","ContextIndex":2104,"Sql":"UPDATE [cmsPropertyData] SET [textValue]=[dataNtext]","SourceContext":"Umbraco.Core.Migrations.Expressions.Execute.Expressions.ExecuteSqlStatementExpression","ProcessId":16236,"ProcessName":"iisexpress","ThreadId":6,"AppDomainId":2,"AppDomainAppId":"","MachineName":"-2","Log4NetLevel":"INFO ","HttpRequestNumber":6,"HttpRequestId":"-d764-4c9a-9c12-"}
Hi Mary,
Did you ever find a solution to this? I am currently stuck on the exact same issue.
Cheers, Dan
is working on a reply...