AI OnAI Off

EPiServer 7.19 update script bug

Alexander Arques
Alexander Arques
Vote:
 

The update script for EPiServer 7.19 is not working!

It will try to re-perform the upgrade an infinite amount of times and break a primary key constraint.

This is the offending file: packages\EPiServer.Commerce.Core.8.5.0\tools\epiupdates_CMS\sql\8.0.1.1.sql

Here is the offending code:

--beginvalidatingquery
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'tblContentType') 
    BEGIN 
	IF EXISTS (SELECT pkid FROM dbo.tblContentType WHERE ModelType LIKE 'EPiServer.Core.CampaignFolder,EPiServer%')
		SELECT 0, 'CampaignFolder Content Type aldready exists'
	ELSE
		SELECT 1, 'Creating CampaignFolder Content Type.'
    END 
ELSE 
    select -1, 'Not an EPiServer CMS database' 
--endvalidatingquery

GO

DECLARE @fkContentType INT
INSERT INTO tblContentType (ContentTypeGUID, Created, ModelType, Name, Description, Available, SortOrder, IdString, WorkflowEditFields, ContentType) VALUES (
'{00C8157D-8117-4D0D-B449-B31960ABA2D4}',
'19990101 00:00',
'EPiServer.Core.CampaignFolder,EPiServer',
'SysCampaignFolder',
'Used as root of campaigns',
0,
10040,
'?id=',
0,
2)
SET @fkContentType = @@identity

EPiServer.Core.CampaignFolder,EPiServer does NOT match with

EPiServer.Core.CampaignFolder, EPiServer%

Notice the difference with the space.

select ModelType from tblContentType ct where ct.ContentTypeGUID = '00C8157D-8117-4D0D-B449-B31960ABA2D4'
  and ModelType LIKE 'EPiServer.Core.CampaignFolder, EPiServer%'

works

select ModelType from tblContentType ct where ct.ContentTypeGUID = '00C8157D-8117-4D0D-B449-B31960ABA2D4'
  and ModelType LIKE 'EPiServer.Core.CampaignFolder,EPiServer%'

does not.

Thank you and have a good day! :-)

#114608
Dec 16, 2014 11:20
Alexander Arques
Alexander Arques
Vote:
 

Full error message given during "update-epidatabase":

PM> update-epidatabase
Processing P:\Mercurial\Piab.CMS\Piab.CMS\packages\EPiServer.CMS.Core.7.19.0\tools\epiupdates\sql\7.8.0.sql
Processing P:\Mercurial\Piab.CMS\Piab.CMS\packages\EPiServer.CMS.Core.7.19.0\tools\epiupdates\sql\7.10.0.sql
Processing P:\Mercurial\Piab.CMS\Piab.CMS\packages\EPiServer.CMS.Core.7.19.0\tools\epiupdates\sql\7.11.0.sql
Processing P:\Mercurial\Piab.CMS\Piab.CMS\packages\EPiServer.CMS.Core.7.19.0\tools\epiupdates\sql\7.12.0.sql
Processing P:\Mercurial\Piab.CMS\Piab.CMS\packages\EPiServer.CMS.Core.7.19.0\tools\epiupdates\sql\7.13.0.sql
Processing P:\Mercurial\Piab.CMS\Piab.CMS\packages\EPiServer.CMS.Core.7.19.0\tools\epiupdates\sql\7.14.0.sql
Processing P:\Mercurial\Piab.CMS\Piab.CMS\packages\EPiServer.CMS.Core.7.19.0\tools\epiupdates\sql\7.16.0.sql
Processing P:\Mercurial\Piab.CMS\Piab.CMS\packages\EPiServer.CMS.Core.7.19.0\tools\epiupdates\sql\7.19.0.sql
Processing P:\Mercurial\Piab.CMS\Piab.CMS\packages\EPiServer.Commerce.Core.8.5.0\tools\epiupdates_CMS\sql\7.6.0.1.sql
Processing P:\Mercurial\Piab.CMS\Piab.CMS\packages\EPiServer.Commerce.Core.8.5.0\tools\epiupdates_CMS\sql\8.0.1.0.sql
Processing P:\Mercurial\Piab.CMS\Piab.CMS\packages\EPiServer.Commerce.Core.8.5.0\tools\epiupdates_CMS\sql\8.0.1.1.sql
epideploy.exe : 
At P:\Mercurial\Piab.CMS\Piab.CMS\packages\EPiServer.Framework.7.19.0\tools\upgrade.psm1:188 char:3
+         &$epiDeployPath  -a $action -s $sitePath  -p $updatePath\* -c $settings["conne ...
+    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError
 
EPiDeploy was stopped due to an exception, more details:


System.Data.SqlClient.SqlException (0x80131904): Cannot insert duplicate key row in object 'dbo.tblContent' with unique index 'IDX_tblContent_ContentGUID'.
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblWorkContent_tblContent". The
 conflict occurred in database "PiabComTest_EPiServerDB", table "dbo.tblContent", column 'pkID'.
Cannot insert duplicate key row in object 'dbo.tblContentLanguage' with unique index 'IDX_tblContentLanguage_ContentGUID'.
The statement has been terminated.
The statement has been terminated.
The statement has been terminated.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at EPiDeploy.Sql.ScriptRunner.<>c__DisplayClass5.<ExecuteScript>b__3()
   at EPiDeploy.Sql.DatabaseHandler.Execute[T](Func`1 f, Boolean commit)
   at EPiDeploy.Sql.ScriptRunner.ExecuteScript(StreamReader stream)
   at EPiDeploy.Sql.ScriptRunner.ExecuteScripts(IEnumerable`1 files, Boolean requiresValidation)
   at EPiDeploy.Sql.ScriptRunner.<>c__DisplayClass1.<Execute>b__0()
   at EPiDeploy.Sql.DatabaseHandler.Execute[T](Func`1 f, Boolean commit)
   at EPiDeploy.Common.Executor.Execute(ILocation location)
   at EPiDeploy.Common.Executor.Execute(ILocation location)
   at EPiDeploy.Common.Executor.Execute(ILocation location)
   at EPiDeploy.Common.Executor.Execute(ILocation location)
   at EPiDeploy.Common.Executor.Execute(ILocation location)
   at EPiDeploy.Common.Executor.Execute(ILocation location)
   at EPiDeploy.Common.Executor.Execute(ILocation location)
   at EPiDeploy.Common.Executor.Execute(ILocation location)
   at EPiDeploy.Common.Executor.Execute(ILocation location)
   at EPiDeploy.Common.Executor.Execute(ILocation location)
   at EPiDeploy.Deploy.Execute(Options options, ILocation startPosition)
   at EPiDeploy.Deploy.Run(Options options)
   at EPiDeploy.Program.Main(String[] args)
ClientConnectionId:26b0d7ab-215f-4a3f-84b3-92359ccfdb83
#114609
Dec 16, 2014 11:21
Alexander Arques
Alexander Arques
Vote:
 

Actually thinking about this more, it looks like your insert does not insert this item WITH the space. So I don't know what initially caused this issue in our database.
I should probably just try to repair it myself. But I don't know what initially caused this issue.

Could it be an earlier version of the script that caused this?

#114611
Dec 16, 2014 11:28
Alexander Arques
Alexander Arques
Vote:
 

The standard in the database for ModelType seems to be to HAVE spaces. This query:

SELECT COUNT(*), LEN(ModelType) - LEN(REPLACE(ModelType,' ','')) as Spaces from tblContentType
  group by LEN(ModelType) - LEN(REPLACE(ModelType,' ',''))

Gives this result:

Count Spaces
2        NULL
2        0
54      4

The only two ModelTypes without spaces are these:

EPiServer.Core.ContentFolder,EPiServer
EPiServer.Core.ContentAssetFolder,EPiServer

Usual form looks like this:

EPiServer.Core.CampaignFolder, EPiServer.Business.Commerce, Version=8.5.0.366, Culture=neutral, PublicKeyToken=8fe83dea738b45b7
EPiServer.Core.CampaignData, EPiServer.Business.Commerce, Version=8.5.0.366, Culture=neutral, PublicKeyToken=8fe83dea738b45b7

What is the problem here?

#114612
Dec 16, 2014 11:41
Alexander Arques
Alexander Arques
Vote:
 

Could it be the initialization framework not being compatible with the update script. Maybe it's breaking the ModelType column in a way not compatible with the script? as it is synchronizing the code-first models with the database?

#114616
Dec 16, 2014 12:09
Johan Book
Johan Book
Vote:
 

I get the same error, although everything seems to work fine anyway... O_o

#114630
Dec 16, 2014 15:55
Alexander Arques
Alexander Arques
Vote:
 

Yes, well it's just the upgrade script that is failing to detect that the database is already updated.

It will try to upgrade it again which will fail.

I'm not 100% sure what is causing this, but I'm pretty sure that the fix is to add spaces to the database update script.

It's good to hear that I'm not the only one with issues though.

#114631
Dec 16, 2014 15:58
Petter Klang
Petter Klang
Vote:
 

Can confirm that I got the same yesterday but as Johan said. Everything seemed to work anyway.

#114632
Dec 16, 2014 16:53
Quan Mai
Quan Mai
Vote:
 

Hi,

We noticed the problem and a more robust version of the file is finding its way to release. 

Thank you for your understanding.

Regards.

/Q

#114651
Dec 17, 2014 5:03
Azhar Mahmood
Azhar Mahmood
Vote:
 

Where can I find the "robust verson", I upgraded today and got the same error.

#121847
May 18, 2015 19:14
Quan Mai
Quan Mai
Vote:
 

The more robust version should be Commerce 8.6.0.

Regards.

/Q

#121853
May 19, 2015 8:29
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.