EPiServer.Framework 12.13.0 - Failed to update database

Karol Berezicki

Vote:

Hi,

The EPiServer.Framework 12.13.0 update tries to alter the procedure [netSoftLinksGetBroken], which does not exist in the database for EPiServer.Framework 12.12.1.
Manually creating the stored procedure allows to proceed with the update.

Full stack trace:

2023-02-21 14:16:42.202 +01:00 [ERR] Initialize action failed for 'Initialize on class EPiServer.Data.DataInitialization, EPiServer.Data, Version=12.13.0.0, Culture=neutral, PublicKeyToken=8fe83dea738b45b7'
System.Data.DataException: Failed to update database during execution of statement 'ALTER PROCEDURE netSoftLinksGetBroken
	@SkipCount int,
	@MaxResults int,
	@RootPageId int
AS
BEGIN
	SELECT [pkID]
		,[fkOwnerContentID]
		,[fkReferencedContentGUID]
		,[OwnerLanguageID]
		,[ReferencedLanguageID]
		,[LinkURL]
		,[LinkType]
		,[LinkProtocol]
		,[ContentLink]
		,[LastCheckedDate]
		,[FirstDateBroken]
		,[HttpStatusCode]
		,[LinkStatus]
        ,[fkOwnerPropertyDefinitionID]
	FROM (
		SELECT [pkID]
			,[fkOwnerContentID]
			,[fkReferencedContentGUID]
			,[OwnerLanguageID]
			,[ReferencedLanguageID]
			,[LinkURL]
			,[LinkType]
			,[LinkProtocol]
			,[ContentLink]
			,[LastCheckedDate]
			,[FirstDateBroken]
			,[HttpStatusCode]
			,[LinkStatus]
            ,[fkOwnerPropertyDefinitionID]
			,ROW_NUMBER() OVER (ORDER BY pkID ASC) as RowNumber
		FROM [tblContentSoftlink]
		INNER JOIN tblTree ON tblContentSoftlink.fkOwnerContentID = tblTree.fkChildID 
		WHERE (tblTree.fkParentID = @RootPageId OR (tblContentSoftlink.fkOwnerContentID = @RootPageId AND tblTree.NestingLevel = 1)) AND LinkStatus <> 0
		) BrokenLinks
	WHERE BrokenLinks.RowNumber > @SkipCount AND BrokenLinks.RowNumber <= @SkipCount+@MaxResults
END
'
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'netSoftLinksGetBroken'.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at EPiServer.Data.SchemaUpdates.Internal.ScriptRunnerImpl.<>c__DisplayClass5_0.<ExecuteScript>b__0()
ClientConnectionId:5fb86f01-8c79-4d9a-a66f-39a02109db6d
Error Number:208,State:6,Class:16
   --- End of inner exception stack trace ---
   at EPiServer.Data.SchemaUpdates.Internal.ScriptRunnerImpl.<>c__DisplayClass5_0.<ExecuteScript>b__0()
   at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.<>c__DisplayClass26_0`1.<ExecuteTransaction>b__0()
   at EPiServer.Data.Providers.Internal.NoRetriesPolicy.Execute[TResult](Func`1 method)
   at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.ExecuteTransaction[TResult](Func`1 action)
   at EPiServer.Data.SchemaUpdates.Internal.ScriptRunnerImpl.ExecuteScript(IDatabaseExecutor databaseHandler, StreamReader stream)
   at EPiServer.Data.SchemaUpdates.Internal.ScriptRunnerImpl.<>c__DisplayClass2_0.<ExecuteScripts>b__0()
   at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.<>c__DisplayClass25_0.<ExecuteTransaction>b__0()
   at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.<>c__DisplayClass26_0`1.<ExecuteTransaction>b__0()
   at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method)
   at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.ExecuteTransaction[TResult](Func`1 action)
   at EPiServer.Data.Providers.Internal.SqlDatabaseExecutor.ExecuteTransaction(Action action)
   at EPiServer.Data.SchemaUpdates.Internal.ScriptRunnerImpl.ExecuteScripts(IDatabaseExecutor databaseHandler, IEnumerable`1 streams)
   at EPiServer.Data.SchemaUpdates.Internal.ScriptExecutorImplementation.ExecuteEmbeddedZippedScripts(String connectionString, Assembly assembly, String resourcePath)
   at EPiServer.Data.SchemaUpdates.Internal.DatabaseVersionValidator.Update(ConnectionStringOptions connectionStringOptions)
   at EPiServer.Data.SchemaUpdates.Internal.DatabaseSchemaManagerImplementation.EnsureDatabaseSchema(DataAccessOptions dataAccessOptions)
   at EPiServer.Data.DataInitialization.ValidateDatabaseSchema(InitializationEngine context)
   at EPiServer.Data.DataInitialization.Initialize(InitializationEngine context)
   at EPiServer.Framework.Initialization.Internal.ModuleNode.<>c__DisplayClass4_0.<Initialize>b__0()
   at EPiServer.Framework.Initialization.Internal.ModuleNode.Execute(Action a, String key)
   at EPiServer.Framework.Initialization.Internal.ModuleNode.Initialize(InitializationEngine context)
   at EPiServer.Framework.Initialization.InitializationEngine.InitializeModules()

#296946

Feb 21, 2023 14:36

Quan Mai

Vote:

Thanks for letting us know. I will file a bug report to Content Platform team, and will get back to you

#296947

Feb 21, 2023 14:45

Quan Mai - Feb 21, 2023 14:48

Created bug CMS-26818

Karol Berezicki - Feb 21, 2023 14:55

Thanks @Quan

Miroslav Mihaljević

Vote:

@Quan Mai, exactly the same thing happened when I updated to version 12.13.1

DataException: Failed to update database during execution of statement 'ALTER PROCEDURE [dbo].[netSoftLinksGetBroken]
	@SkipCount int,
	@MaxResults int,
	@RootPageId int
AS
BEGIN
	SELECT [pkID]
		,[fkOwnerContentID]
		,[fkReferencedContentGUID]
		,[OwnerLanguageID]
		,[ReferencedLanguageID]
		,[LinkURL]
		,[LinkType]
		,[LinkProtocol]
		,[ContentLink]
		,[LastCheckedDate]
		,[FirstDateBroken]
		,[HttpStatusCode]
		,[LinkStatus]
        ,[fkOwnerPropertyDefinitionID]
	FROM (
		SELECT [pkID]
			,[fkOwnerContentID]
			,[fkReferencedContentGUID]
			,[OwnerLanguageID]
			,[ReferencedLanguageID]
			,[LinkURL]
			,[LinkType]
			,[LinkProtocol]
			,[ContentLink]
			,[LastCheckedDate]
			,[FirstDateBroken]
			,[HttpStatusCode]
			,[LinkStatus]
            ,[fkOwnerPropertyDefinitionID]
			,ROW_NUMBER() OVER (ORDER BY pkID ASC) as RowNumber
		FROM [tblContentSoftlink]
		INNER JOIN tblTree ON tblContentSoftlink.fkOwnerContentID = tblTree.fkChildID 
		WHERE (tblTree.fkParentID = @RootPageId OR (tblContentSoftlink.fkOwnerContentID = @RootPageId AND tblTree.NestingLevel = 1)) AND LinkStatus <> 0
		) BrokenLinks
	WHERE BrokenLinks.RowNumber > @SkipCount AND BrokenLinks.RowNumber <= @SkipCount+@MaxResults
END
'

#298353

Mar 14, 2023 23:20

Quan Mai

Vote:

Can you please post the full log regarding the issue?

Meanwhile you might be able to get pass the issue by running this on your database, if the problem is missing stored procedure:

CREATE PROCEDURE [dbo].[netSoftLinksGetBroken]
	@SkipCount int,
	@MaxResults int,
	@RootPageId int
AS
BEGIN
END

Thanks for reporting this, I will forward it to the CMS Core (Content Platform) team

#298354

Mar 15, 2023 7:05

Shahram Shahinzadeh

Vote:

Karol Berezicki

We have tried to reproduce the problem here but unfortunately without success, would you like to check if the sp exists in your db before updating to 12.13.1. If the sp does not exists then the question is why the sp has been deleted as we know the sp should be created by basline script. but if it is already in the db would you like to check which schema it has. otherwise if you have or know kind of procedure to reproduce it we wil be happy to know.

thanks

#298356

Mar 15, 2023 8:17

Karol Berezicki

Vote:

Hi Shahram,

I've looked, but unfortunately I don't have such backup and I didn't expirence that problem when upgrading from 12.12.1 to 12.13.1.
IIRC for me the procedure existed before the upgrade to 12.12.1, but was deleted during the upgrade and then the upgrade failed, because the SP did not exist at that point.

Perhaps Miroslav may be able to help you with detecting this issue when upgrading to 12.13.1.

#298371

Mar 15, 2023 16:51

Miroslav Mihaljević

Vote:

Hi Quan Mai, thanks for the advice, that is what I did to avoid the problem (create dummy stored procedure).

I am the only one in my team that has tried upgrading (that was actually planned for later, so they won't do it right now), maybe something is wrong only with my DB, but I have actually made a backup before upgrade, I'm gonna ask my team lead if it is ok to send it to you if it helps to debug.

Regarding the logs, I didn't have any of them stored, and this exception and stack trace was the only useful thing I had. If somebody form the rest of the team gets the same issue we will log it better and send it.

#298377

Mar 15, 2023 20:35

Vote:

I still got this problem in upgrade to version 12.14. This is due to my db SPs looks like this:

but I executed the following to change schema name to dbo.

ALTER SCHEMA dbo TRANSFER [EPiServerDB_cfc07238User].[netSoftLinksGetBrokenCount];
ALTER SCHEMA dbo TRANSFER [EPiServerDB_cfc07238User].[netSoftLinksGetBroken];

I also had another SP with wrong schema name:
ALTER SCHEMA dbo TRANSFER [EPiServerDB_cfc07238User].[netPageDynamicBlockDelete];

Had to run "Query Editor" in Azure to solve it on server.

Hope it helps for other having same problem.

Regards Luc

#300580

Apr 23, 2023 8:01

Daniel Isaacs

Vote:

Same -- looks like the bug is still in the backlog: https://world.optimizely.com/support/Bug-list/bug/CMS-26818

#300681

Apr 24, 2023 23:09

QuirijnLangedijk

Vote:

Has anyone found a solution to this problem? Were having the same issue on our local environment. They seem to have closed the bug report...

#302973

Edited, Jun 05, 2023 8:56

Vote:

Yes! My reply doesnt work for you?

#302974

Jun 05, 2023 9:03

QuirijnLangedijk

Vote:

Hi Luc, your solution seems to have worked. Thanks a lot!

#303039

Jun 06, 2023 14:13

Surjit Bharath

Vote:

Thank you Luc

#303044

Jun 06, 2023 15:51

Penguin

Vote:

The issue is because the Cms.Core.sql [12.5.0 12.12.0] creating the procedures without specifying the 'dbo' schema:

CREATE PROCEDURE netSoftLinksGetBrokenCount
@OwnerContentID int
AS ...

CREATE PROCEDURE netSoftLinksGetBroken
@SkipCount int, ...

but then at later version upgrading the new script looks for the objects at the 'dbo'.

If using the Foundation will have similar error on some objects of the Foundation.Commerce database: OpenIddictApplications, OpenIddictAuthorizations, OpenIddictScopes, OpenIddictTokens. Upgrade the EPiServer.OpenIDConnect to version 3.8.0 will fix the wrong chema on these objects.

#304158

Edited, Jun 27, 2023 4:16