System.InvalidOperationException: Nullable object must have a value. in the Umbraco Database Migration from 7.15 to 8
Hi there, I have an issue with many people encountering the same issue when they migrate from Umbraco 7.15 to Umbraco 8.1, that is the System.InvalidOperationException: Nullable object must have a value error in the logs, I have specified in the web config to debug with further info, but there is no useful information to resolve hte issue, and I have also used the ProWorks Migration Helper package on Umbraco 7.15 for the Umbraco 7 database migration update and used the ProWorks Migration Helper (new one) on 8.1 but it still is stuck on this error. I have also tried to migrated from 7.15.6 too which is specified by ProWorks.
Here is the specific part on the log. I can send the whole log if necessary if you can provide an email address.
{"@t":"2021-05-21T09:49:33.8010574Z","@mt":"SQL [{ContextIndex}]: {Sql}","ContextIndex":550,"Sql":"CREATE TABLE [umbracoContentSchedule] ([id] UniqueIdentifier NOT NULL,[nodeId] INTEGER NOT NULL,[languageId] INTEGER NULL,[date] DATETIME NOT NULL,[action] NVARCHAR(255) NOT NULL)","SourceContext":"Umbraco.Core.Migrations.Expressions.Execute.Expressions.ExecuteSqlStatementExpression","ProcessId":20264,"ProcessName":"iisexpress","ThreadId":9,"AppDomainId":2,"AppDomainAppId":"LMW3SVC2ROOT","MachineName":"ABHW-LT22677","Log4NetLevel":"INFO ","HttpRequestNumber":9,"HttpRequestId":"bb6f4193-9a07-4382-a581-370329179310"}
{"@t":"2021-05-21T09:49:34.1794964Z","@mt":"Database configuration failed","@l":"Error","@x":"System.InvalidOperationException: Nullable object must have a value.\r\n at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource)\r\n at Umbraco.Core.Migrations.Upgrade.V800.TablesForScheduledPublishing.Migrate() in d:\a\1\s\src\Umbraco.Core\Migrations\Upgrade\V800\TablesForScheduledPublishing.cs:line 17\r\n at Umbraco.Core.Migrations.MigrationBase.Umbraco.Core.Migrations.IMigration.Migrate() in d:\a\1\s\src\Umbraco.Core\Migrations\MigrationBase.cs:line 73\r\n at Umbraco.Core.Migrations.MigrationPlan.Execute(IScope scope, String fromState, IMigrationBuilder migrationBuilder, ILogger logger) in d:\a\1\s\src\Umbraco.Core\Migrations\MigrationPlan.cs:line 309\r\n at Umbraco.Core.Migrations.Upgrade.Upgrader.Execute(IScopeProvider scopeProvider, IMigrationBuilder migrationBuilder, IKeyValueService keyValueService, ILogger logger) in d:\a\1\s\src\Umbraco.Core\Migrations\Upgrade\Upgrader.cs:line 67\r\n at Umbraco.Core.Migrations.Install.DatabaseBuilder.UpgradeSchemaAndData(MigrationPlan plan) in d:\a\1\s\src\Umbraco.Core\Migrations\Install\DatabaseBuilder.cs:line 498","SourceContext":"Umbraco.Core.Migrations.Install.DatabaseBuilder","ProcessId":20264,"ProcessName":"iisexpress","ThreadId":9,"AppDomainId":2,"AppDomainAppId":"LMW3SVC2ROOT","MachineName":"ABHW-LT22677","Log4NetLevel":"ERROR","HttpRequestNumber":9,"HttpRequestId":"bb6f4193-9a07-4382-a581-370329179310"}
{
This particular migration is trying to move information from Umbraco V7 about any instructions that exist to schedule the publish of a content item or unpublish a content item into the new V8 mechanism, that has it's own Database Table.
So you can see for each scheduled publish (releases) and each scheduled unpublish (expiries) for your existing V7 data - the same rule is attempted to be inserted into the new database.
eg.
foreach(var s in releases)
{
var date = s.Value;
var action = ContentScheduleAction.Release.ToString();
Insert.IntoTable(ContentScheduleDto.TableName)
.Row(new { id = Guid.NewGuid(), nodeId = s.Key, date = date, action = action })
.Do();
}
The error message 'nullable object must have a value' is often triggered when you call .Value on a DateTime object - you can see this stackoverflow question here:
So it feels like in your existing site you 'might' have a scheduled publish or unpublish task for a content item with a date that can't be parsed, or a blank entry instead of a NULL entry...
So if you run something like this against your V7 database:
SELECT * FROM umbraco umbracoDocument WHERE expiryDate IS NOT NULL Or releaseDate IS NOT NULL
you can see if there are any empty fields and if so, set them explicitly to be NULL via SQL, or perhaps there is date in a weird format - or you can just set all the expiryDate and releaseDate values to be NULL - if you don't need to migrate these existing rules into V8!
Then you should be able to run the migration again successfully...
(we could also I think make a PR to the core to update the migration in the code to check for HasValue before the attempt is made to insert)
System.InvalidOperationException: Nullable object must have a value. in the Umbraco Database Migration from 7.15 to 8
Hi there, I have an issue with many people encountering the same issue when they migrate from Umbraco 7.15 to Umbraco 8.1, that is the System.InvalidOperationException: Nullable object must have a value error in the logs, I have specified in the web config to debug with further info, but there is no useful information to resolve hte issue, and I have also used the ProWorks Migration Helper package on Umbraco 7.15 for the Umbraco 7 database migration update and used the ProWorks Migration Helper (new one) on 8.1 but it still is stuck on this error. I have also tried to migrated from 7.15.6 too which is specified by ProWorks.
Here is the specific part on the log. I can send the whole log if necessary if you can provide an email address.
{"@t":"2021-05-21T09:49:33.8010574Z","@mt":"SQL [{ContextIndex}]: {Sql}","ContextIndex":550,"Sql":"CREATE TABLE [umbracoContentSchedule] ([id] UniqueIdentifier NOT NULL,[nodeId] INTEGER NOT NULL,[languageId] INTEGER NULL,[date] DATETIME NOT NULL,[action] NVARCHAR(255) NOT NULL)","SourceContext":"Umbraco.Core.Migrations.Expressions.Execute.Expressions.ExecuteSqlStatementExpression","ProcessId":20264,"ProcessName":"iisexpress","ThreadId":9,"AppDomainId":2,"AppDomainAppId":"LMW3SVC2ROOT","MachineName":"ABHW-LT22677","Log4NetLevel":"INFO ","HttpRequestNumber":9,"HttpRequestId":"bb6f4193-9a07-4382-a581-370329179310"} {"@t":"2021-05-21T09:49:34.1794964Z","@mt":"Database configuration failed","@l":"Error","@x":"System.InvalidOperationException: Nullable object must have a value.\r\n at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource)\r\n at Umbraco.Core.Migrations.Upgrade.V800.TablesForScheduledPublishing.Migrate() in d:\a\1\s\src\Umbraco.Core\Migrations\Upgrade\V800\TablesForScheduledPublishing.cs:line 17\r\n at Umbraco.Core.Migrations.MigrationBase.Umbraco.Core.Migrations.IMigration.Migrate() in d:\a\1\s\src\Umbraco.Core\Migrations\MigrationBase.cs:line 73\r\n at Umbraco.Core.Migrations.MigrationPlan.Execute(IScope scope, String fromState, IMigrationBuilder migrationBuilder, ILogger logger) in d:\a\1\s\src\Umbraco.Core\Migrations\MigrationPlan.cs:line 309\r\n at Umbraco.Core.Migrations.Upgrade.Upgrader.Execute(IScopeProvider scopeProvider, IMigrationBuilder migrationBuilder, IKeyValueService keyValueService, ILogger logger) in d:\a\1\s\src\Umbraco.Core\Migrations\Upgrade\Upgrader.cs:line 67\r\n at Umbraco.Core.Migrations.Install.DatabaseBuilder.UpgradeSchemaAndData(MigrationPlan plan) in d:\a\1\s\src\Umbraco.Core\Migrations\Install\DatabaseBuilder.cs:line 498","SourceContext":"Umbraco.Core.Migrations.Install.DatabaseBuilder","ProcessId":20264,"ProcessName":"iisexpress","ThreadId":9,"AppDomainId":2,"AppDomainAppId":"LMW3SVC2ROOT","MachineName":"ABHW-LT22677","Log4NetLevel":"ERROR","HttpRequestNumber":9,"HttpRequestId":"bb6f4193-9a07-4382-a581-370329179310"} {
Hi Hon
This particular migration is trying to move information from Umbraco V7 about any instructions that exist to schedule the publish of a content item or unpublish a content item into the new V8 mechanism, that has it's own Database Table.
You can see the implementation here;
https://github.com/umbraco/Umbraco-CMS/blob/d428a4543f33bb7094cf7db5f6b6fdc2d1de3063/src/Umbraco.Core/Migrations/Upgrade/V80_0/TablesForScheduledPublishing.cs
So you can see for each scheduled publish (releases) and each scheduled unpublish (expiries) for your existing V7 data - the same rule is attempted to be inserted into the new database.
eg.
The error message 'nullable object must have a value' is often triggered when you call .Value on a DateTime object - you can see this stackoverflow question here:
https://stackoverflow.com/questions/1896185/nullable-object-must-have-a-value
So it feels like in your existing site you 'might' have a scheduled publish or unpublish task for a content item with a date that can't be parsed, or a blank entry instead of a NULL entry...
So if you run something like this against your V7 database:
SELECT * FROM umbraco umbracoDocument WHERE expiryDate IS NOT NULL Or releaseDate IS NOT NULL
you can see if there are any empty fields and if so, set them explicitly to be NULL via SQL, or perhaps there is date in a weird format - or you can just set all the expiryDate and releaseDate values to be NULL - if you don't need to migrate these existing rules into V8!
Then you should be able to run the migration again successfully...
(we could also I think make a PR to the core to update the migration in the code to check for HasValue before the attempt is made to insert)
regards
Marc
is working on a reply...