Error sql when running the upgrade

[thienvc]

https://github.com/dnnsoftware/Dnn.Platform/blob/ea2cc0373d4ee8d5c28805cf6901fbb4e57d88b6/DNN Platform/Website/Providers/DataProviders/SqlDataProvider/09.13.08.SqlDataProvider#L12

When running the upgrade, I encountered an SQL error at this point because the index IX_Lists_ListName_Value_Text_ParentID was not found in the database.
I think we need to add the following code for security.
IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'IX_{objectQualifier}Lists_ListName_Value_Text_ParentID')
The code is the same as in the old version:

Dnn.Platform/DNN Platform/Website/Providers/DataProviders/SqlDataProvider/05.06.00.SqlDataProvider

Line 1044 in ea2cc03

IF EXISTS (SELECT * FROM sys.indexes WHERE name = N'IX_{objectQualifier}Lists_ListName_Value_Text_ParentID')

[tpperlman]

I had that problem too. I ended up writing this script to run before the upgrade to resolve it.

DROP INDEX IF EXISTS [IX_Lists_SortOrder] ON [dbo].[Lists]
GO

DROP INDEX IF EXISTS [IX_Lists_ParentID] ON [dbo].[Lists]
GO

DROP INDEX IF EXISTS [IX_Lists_ListName_ParentID] ON [dbo].[Lists]
GO

CREATE NONCLUSTERED INDEX [IX_Lists_ListName_ParentID] ON [dbo].[Lists]
(
	[ListName] ASC,
	[ParentID] ASC
)
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_Lists_ParentID] ON [dbo].[Lists]
(
	[ParentID] ASC,
	[ListName] ASC,
	[Value] ASC
)
INCLUDE([DefinitionID],[SortOrder],[Text])
GO

CREATE NONCLUSTERED INDEX [IX_Lists_SortOrder] ON [dbo].[Lists]
(
	[PortalID] ASC,
	[ParentID] ASC,
	[ListName] ASC,
	[SortOrder] ASC
)
INCLUDE([DefinitionID],[Text],[Value])
GO

[valadas]

We don't usually re-release already released versions. Can you explain a bit how to get into that predicament in the first place, I have done a lot of upgrades without that issue. Was the upgrade path followed here https://docs.dnncommunity.org/content/getting-started/setup/upgrades/suggested-upgrade-path/index.html

[valadas]

I'll move this to a discussion for now and bring it back to an issue if we have a more actionable idea on this.

[tpperlman]

We ran into this on a DNN site that was originally installed on April 19, 2012, so it must have been v6.1.x. I assume that across many subsequent upgrades the indexes were either deleted or not created.

[mitchelsellers]

Do you know the specific upgrade steps that were followed? More than anything was the suggested upgrade path followed?

[tpperlman]

I can't speak for @thienvc, but we do generally follow the recommended path but I can't guarantee we did over 14 years of updates. We rarely do initial milestone upgrades unless there are functional or security reasons justify it.

[thienvc]

Do you know the specific upgrade steps that were followed? More than anything was the suggested upgrade path followed?

As the @tpperlman above mentioned, my current DNN is an upgrade from version 5.x, then 6.x, 7.x, 8x, 9x, and soon to 10.2. It's possible that during a previous update, that "index" was dropped at some point, and nobody remembers when.