When upgraded the Orchestrator to 23.10.3, following error is thrown " Cannot insert the value NULL into column 'Path', table dbo.OrganizationUnits column does not allow nulls"
Issue Description:
When upgrading the orchestrator to the 2023.10.x version the error "System.Exception: Migration failed: Cannot insert the value NULL into column 'Path', table 'dbo.OrganizationUnits'; column does not allow nulls." UPDATE fails is displayed and the upgrade process rolls back on hitting the OK button.
On checking the database 'dbo.OrganizationUnits' table before the roll back, it is observed that some rows have the PATH column with NULL value.
A PATH could not be created because of missing ParentKey and / or the CODE column does not contain the entire chain values of the parents.
Resolution:
- Identify the rows with NULL value in PATH column and check its Key alongside with the Parent Keys
- To identify the parents of your OU, check the ParentID column
- Once identified the OU chain, update the missing/ NULL Parent Keys with the appropriate keys
- Make sure the CODE column also follows the correct parent chain as well, as explained below.
Example:
Below observe that OU Id 14 has as a parent OU Id 10 which also has a parent that's ID 33. In the below screenshot you can see that OU 14 doesn't have a ParentKey, so that NULL value should be replaced with the key from OU Id 10. Id 33 is the first level parent hence it does not have a ParentID so the ParentKey will be NULL.
The CODE value of OU 14 does not contain the full hierarchical chain.
The value will be added manually and will contain the full hierarchical chain.
Example:
'0000000006.0000000002.0000000004.0000000001'
After correcting all the rows, start again the upgrade process.
Note: It is recommend to have a Full Database Backup taken before doing any changes or starting and upgrade / migration process.