Error during database upgrade

Questions
1

When trying to upgrade the database from 6.2.3 to 7.0.2 the error below occurs. What causes it to and what do we need to do to fix it so we can complete the upgrade?

Error in SQL
Exception calling "ExecuteNonQuery" with "0" argument(s): "The INSERT statement conflicted with the 
FOREIGN KEY constraint "FK_FieldFramework_FieldDefinitionFieldData_FieldDefinition_OwnerSystemId". 
The conflict occurred in database "*****", table "FieldFramework.FieldDefinition", column 'SystemId'.
The statement has been terminated."

Litium version: 7

2

Regarding to the error message the FieldDefinitionFieldData table have rows but missing the owner in the FieldDefinition table, the tables exists for each area.

How you have ended up in this situation is unknown, but you should be able to clean out the FieldDefinitionFieldData tables from rows that missing the owner in FieldDefinition.

3

This query returns four rows; do you suggest we delete these rows?

SELECT * FROM Products.FieldDefinition
WHERE SystemId NOT IN (
    SELECT OwnerSystemId
    FROM Products.FieldDefinitionFieldData
)

 

SystemId                             CanBeGridColumn CanBeGridFilter FieldType                                                                                                                                                                                                                                                        Id                                                                                                   MultiCulture
------------------------------------ --------------- --------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ------------
FDC75807-B0E5-4359-90AC-501EA3566992 1               0               Text                                                                                                                                                                                                                                                             TempArticleStructureValues                                                                           0
9AA7EE90-88CA-4F5B-8A56-6C00CB0B25EB 0               0               MultirowText                                                                                                                                                                                                                                                     TagSorting                                                                                           0
B64125BC-B00D-427E-BBA9-71424690528B 1               0               Decimal                                                                                                                                                                                                                                                          BrandId                                                                                              0
16DF6CAD-569D-4778-8EAE-90015D5E0EC3 1               0               MediaPointerImage                                                                                                                                                                                                                                                BrandImage                                                                                           0

(4 rows affected)
4

Its the table *.FieldDefinitionFieldData that have the rows without the parent so you need to switch place on the table in the sql to something like

SELECT * FROM Products.FieldDefinitionFieldData
WHERE OwnerSystemId NOT IN (
    SELECT SystemId
    FROM Products.FieldDefinition
)
5

I thought so at first but those queries returns no data:

SELECT * FROM Customers.FieldDefinitionFieldData
WHERE OwnerSystemId NOT IN (
    SELECT SystemId
    FROM Customers.FieldDefinition
)

SELECT * FROM Media.FieldDefinitionFieldData
WHERE OwnerSystemId NOT IN (
    SELECT SystemId
    FROM Media.FieldDefinition
)

SELECT * FROM Products.FieldDefinitionFieldData
WHERE OwnerSystemId NOT IN (
    SELECT SystemId
    FROM Products.FieldDefinition
)
6

The foregin key is the following

ALTER TABLE [FieldFramework].[FieldDefinitionFieldData]  WITH CHECK ADD  CONSTRAINT [FK_FieldFramework_FieldDefinitionFieldData_FieldDefinition_OwnerSystemId] FOREIGN KEY([OwnerSystemId])
REFERENCES [FieldFramework].[FieldDefinition] ([SystemId])
ON DELETE CASCADE

so it should be if you try to inserting a row in that table that not have a matching system id.

I think you need to execute the sql-script manual and see what part of the script is creating this failure.

7

Yep, I just ran the script manually and got the same error:

Msg 547, Level 16, State 0, Line 24012
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_FieldFramework_FieldDefinitionFieldData_FieldDefinition_OwnerSystemId". The conflict occurred in database "nwt", table "FieldFramework.FieldDefinition", column 'SystemId'.
The statement has been terminated.

And these are the lines in the script (line 24007 and forward):

  -- Set OptionField types
   INSERT INTO [FieldFramework].[FieldDefinitionFieldData] ([OwnerSystemId],[FieldDefinitionId],[Culture],[Index],[JsonValue])  
			SELECT 
    			CPP.SystemId as OwnerSystemId,
				''_option'' as [FieldDefinitionId],
				''*'' as [Culture], 
				0 as [Index],
				dbo.GetPageOptionFieldType(CPP.PropertyName,CPP.PropertyType,CPP.IsArray) as [JsonValue]
				FROM @pageProperties AS CPP
				INNER JOIN [FieldFramework].[FieldDefinition] FD ON FD.SystemId = CPP.SystemId
				WHERE FD.FieldType = ''Pointer'' OR FD.FieldType = ''MultiField'';
8

Did you have any output from the pre-req database script?

9

I thought not but had a look again and yes, there were some errors (see below).

|Description|Type|
|---|---|
|Could not convert Content Property "ButtonText" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "ButtonUrl" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "CampaignBottomParagraph" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "CampaignShortDescription" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "Canonical" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "CssClass" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "customCss" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "CustomCssForHeader" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "customHtml" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "Description" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "EmailSender" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "Files" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "Heading" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "Image1" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "Image2" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "Introduction" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "Link" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "SubHeading" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "Terms" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
|Could not convert Content Property "Text" in table dbo.CMS_PageTypeProperty to field because it has multiple definitions. Please make sure to have one defintion for each content property. Read more on https://docs.litium.com/documentation/upgrading-to-litium-7/upgrading-the-database#Fields|Error|
10

We’ve solved the errors reported by the pre-req script but still get this error from the upgrade script.
Any ideas?

Msg 547, Level 16, State 0, Line 24012
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_FieldFramework_FieldDefinitionFieldData_FieldDefinition_OwnerSystemId". The conflict occurred in database "nwt", table "FieldFramework.FieldDefinition", column 'SystemId'.
The statement has been terminated.
11

Please, open a support ticket where you can provide a database backup so we can help out to find what is causing the upgrade error.

13

I got the ticket and will come back after I analyzed the error.

14

Do you have a database script to update to solve the errors? The database that you provided have still errors for them.

15

Try this (untested but at least got rid of the errors):

--Select CMS_PageType.PagetypeName + '-' + prop.PropertyName, prop.* 
UPDATE prop Set prop.PropertyName = CMS_PageType.PagetypeName + '-' + prop.PropertyName
from CMS_PageTypeProperty prop
Inner join CMS_PageType On CMS_PageType.PageTypeID = prop.PageTypeID
Where PropertyName in(

SELECT a.PropertyName 
	FROM (SELECT PropertyName 
			FROM dbo.CMS_PageTypeProperty 
			GROUP BY PageTypeID, PropertyName, PropertyType, IsContentProperty, IsArray) a
	GROUP BY PropertyName
	HAVING COUNT(1) > 1)

	Go

	
--Select CMS_PageType.PagetypeName + '-' + prop.PropertyName, prop.* 
UPDATE prop Set prop.PropertyName = CMS_PageType.PagetypeName + '-' + prop.PropertyName
from CMS_PageProperty prop
Inner join CMS_PageType On CMS_PageType.PageTypeID = prop.PageTypeID
Where PropertyName in(

SELECT a.PropertyName 
	FROM (SELECT PropertyName 
			FROM dbo.CMS_PageTypeProperty 
			GROUP BY PageTypeID, PropertyName, PropertyType, IsContentProperty, IsArray) a
	GROUP BY PropertyName
	HAVING COUNT(1) > 1)

	Go

	UPDATE prop Set prop.PropertyName = CMS_PageType.PagetypeName + '-' + prop.PropertyName
	--Select CMS_PageType.PagetypeName + '-' + prop.PropertyName, prop.* 
	from CMS_PagePropertyValue prop
Inner join CMS_PageType On CMS_PageType.PageTypeID = prop.PageTypeID
Where PropertyName in(

SELECT a.PropertyName 
	FROM (SELECT PropertyName 
			FROM dbo.CMS_PageTypeProperty 
			GROUP BY PageTypeID, PropertyName, PropertyType, IsContentProperty, IsArray) a
	GROUP BY PropertyName
	HAVING COUNT(1) > 1)
	Go

	UPDATE prop Set prop.PropertyName = CMS_PageType.PagetypeName + '-' + prop.PropertyName
--Select CMS_PageType.PagetypeName + '-' + prop.PropertyName, prop.* 
from [CMS_VersionProperty] prop
Inner join CMS_Page On CMS_Page.PageID = prop.VersionID
Inner join CMS_PageType On CMS_PageType.PageTypeID = CMS_Page.PageTypeID
Where PropertyName in(

SELECT a.PropertyName 
	FROM (SELECT PropertyName 
			FROM dbo.CMS_PageTypeProperty 
			GROUP BY PageTypeID, PropertyName, PropertyType, IsContentProperty, IsArray) a
	GROUP BY PropertyName
	HAVING COUNT(1) > 1)
	Go

	
	
	--Select CMS_PageType.PagetypeName + '-' + prop.PropertyName, prop.* 
	UPDATE prop Set prop.PropertyName = CMS_PageType.PagetypeName + '-' + prop.PropertyName
	from CMS_WorkingCopyProperty prop
Inner join CMS_Page On CMS_Page.PageID = prop.WorkingCopyID
Inner join CMS_PageType On CMS_PageType.PageTypeID = CMS_Page.PageTypeID
Where PropertyName in(

SELECT a.PropertyName 
	FROM (SELECT PropertyName 
			FROM dbo.CMS_PageTypeProperty 
			GROUP BY PageTypeID, PropertyName, PropertyType, IsContentProperty, IsArray) a
	GROUP BY PropertyName
	HAVING COUNT(1) > 1)
	Go

We also had to change some of the VAT percentages to get past the pre-req script…

Update Products.PriceListItem Set VatPercentage = 0.250000 
	from Products.PriceListItem pli
	Inner join Products.PriceList pl On pl.SystemId = pli.PriceListSystemId
	INNER JOIN Foundation_Currency c on c.CurrencyID = pl.CurrencySystemId
	Where VatPercentage <= 1 And VatPercentage >= -1 And CurrencyCode <> 'EUR'
	Go
	Update Products.PriceListItem Set VatPercentage = 0.240000 
	from Products.PriceListItem pli
	Inner join Products.PriceList pl On pl.SystemId = pli.PriceListSystemId
	INNER JOIN Foundation_Currency c on c.CurrencyID = pl.CurrencySystemId
	Where VatPercentage <= 1 And VatPercentage >= -1 And CurrencyCode = 'EUR'
	Go
16

Your script for replacing property name was not including all needed tables. I put this script together and now I can run the upgrade script without errors.

UPDATE PLI SET PLI.VatPercentage = 0.250000 
FROM Products.PriceListItem PLI
INNER JOIN Products.PriceList PL ON PL.SystemId = PLI.PriceListSystemId
INNER JOIN Foundation_Currency C ON C.CurrencyID = PL.CurrencySystemId
WHERE VatPercentage <= 1 AND VatPercentage >= -1 AND CurrencyCode <> 'EUR'
GO

UPDATE PLI Set VatPercentage = 0.240000 
FROM Products.PriceListItem PLI
INNER JOIN Products.PriceList PL ON PL.SystemId = PLI.PriceListSystemId
INNER JOIN Foundation_Currency C ON C.CurrencyID = PL.CurrencySystemId
WHERE VatPercentage <= 1 AND VatPercentage >= -1 AND CurrencyCode = 'EUR'
GO

UPDATE PTP Set PTP.PropertyName = PT.PagetypeName + '-' + PTP.PropertyName
FROM dbo.CMS_PageTypeProperty PTP
INNER JOIN dbo.CMS_PageType PT On PT.PageTypeID = PTP.PageTypeID
WHERE PTP.PropertyName IN (
	SELECT a.PropertyName 
	FROM (SELECT PropertyName 
			FROM dbo.CMS_PageTypeProperty 
			GROUP BY PageTypeID, PropertyName, PropertyType, IsContentProperty, IsArray) a
	GROUP BY a.PropertyName
	HAVING COUNT(1) > 1
)
GO

UPDATE PP SET PP.PropertyName = PTP.PropertyName
FROM dbo.CMS_PageType PT
INNER JOIN dbo.CMS_PageTypeProperty PTP ON PT.PageTypeID = PTP.PageTypeID
INNER JOIN dbo.CMS_Page P ON P.PageTypeID = PT.PageTypeID
INNER JOIN dbo.CMS_PageProperty PP ON PP.PageID = P.PageID AND (PP.PropertyName = PTP.PropertyName OR ((PT.PageTypeName + '-' + PP.PropertyName) = PTP.PropertyName))
WHERE PTP.PropertyName <> PP.PropertyName
GO

UPDATE PP SET PP.PropertyName = PTP.PropertyName
FROM dbo.CMS_PageType PT
INNER JOIN dbo.CMS_PageTypeProperty PTP ON PT.PageTypeID = PTP.PageTypeID
INNER JOIN dbo.CMS_Page P ON P.PageTypeID = PT.PageTypeID
INNER JOIN dbo.CMS_PagePropertyValue PP ON PP.PageID = P.PageID AND (PP.PropertyName = PTP.PropertyName OR ((PT.PageTypeName + '-' + PP.PropertyName) = PTP.PropertyName))
WHERE PTP.PropertyName <> PP.PropertyName
GO

UPDATE PP SET PP.PropertyName = PTP.PropertyName
FROM dbo.CMS_PageType PT
INNER JOIN dbo.CMS_PageTypeProperty PTP ON PT.PageTypeID = PTP.PageTypeID
INNER JOIN dbo.CMS_Page P ON P.PageTypeID = PT.PageTypeID
INNER JOIN dbo.CMS_Version V ON P.PageID = V.PageID
INNER JOIN dbo.CMS_VersionProperty PP ON PP.VersionID = V.VersionID AND (PP.PropertyName = PTP.PropertyName OR ((PT.PageTypeName + '-' + PP.PropertyName) = PTP.PropertyName))
WHERE PTP.PropertyName <> PP.PropertyName
GO

UPDATE PP SET PP.PropertyName = PTP.PropertyName
FROM dbo.CMS_PageType PT
INNER JOIN dbo.CMS_PageTypeProperty PTP ON PT.PageTypeID = PTP.PageTypeID
INNER JOIN dbo.CMS_Page P ON P.PageTypeID = PT.PageTypeID
INNER JOIN dbo.CMS_WorkingCopy W ON P.PageID = W.PageID
INNER JOIN dbo.CMS_WorkingCopyProperty PP ON PP.WorkingCopyID = W.WorkingCopyID AND (PP.PropertyName = PTP.PropertyName OR ((PT.PageTypeName + '-' + PP.PropertyName) = PTP.PropertyName))
WHERE PTP.PropertyName <> PP.PropertyName
GO
17

@hans Did my updated script for you solve the upgrade problem?

18

Yes, thank you!