Error upgrading from 4.x.x to 9.1.0

[roman-yagodin]

Describe the bug

Attempt to upgrade the existing module to the latest 9.1.0 release version results in SQL error.

Software Versions

• DNN: 09.08.01, recently upgraded from DNN 08.00.04
• New module version: 09.01.00
• Installed module version: 04.70.01

Expected behavior

Upgrading extension should proceed without issues.

Error log

Failure SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException (0x80131904):
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_SurveyResults_Users".
The conflict occurred in database "copy-1.volgau.com", table "dbo.Users", column 'UserID'.
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& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at DotNetNuke.Data.SqlDatabaseConnectionProvider.ExecuteNonQuery(String connectionString, CommandType commandType, Int32 commandTimeout, String query)
at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script, Int32 timeoutSec)
ClientConnectionId:2f77ed62-1597-458e-97b3-5e7cdfb07823
Error Number:547,State:0,Class:16 ALTER TABLE dbo.SurveyResults WITH CHECK ADD CONSTRAINT FK_SurveyResults_Users FOREIGN KEY (UserID) REFERENCES dbo.Users (UserID) ON DELETE SET NULL 

Additional context

The issue is caused by some installation-specific content in the database tables, namely UserIDs of non-existent (removed) users.

In my case, there are number of records in dbo.SurveyResults which reference non-existing UserID, which can be checked with this statement:

select * from dbo.SurveyResults where UserID <> -1 and UserID not in (select UserID from dbo.Users)

The issue is fixed (for me) by executing this statement prior to module upgrade:

delete from dbo.SurveyResults where UserID <> -1 and UserID not in (select UserID from dbo.Users)

[MaiklT]

Version 4.70.1 was not an official release.

[roman-yagodin]

Version 4.70.1 was not an official release.

It would be, but I see what I see. Screenshot from DNN 8.0.4 portal (prior to upgrading to DNN 9):

[valadas]

There is a 4.70.1 tag in here but a release for it was not published @EPTamminga created the tag. @EPTamminga do you have any insight on this ?

[roman-yagodin]

This could have happened if someone (e.g. me) used latest available (at the time) source to create install package 😄
But the issue is not about version numbering.

[EPTamminga]

@valadas The only thing I did at the time was to create a new version # (tag) for a bug fix release, but the release was never finished nor published

[MaiklT]

As the version was not an official release, the upgrade goes from 4.70.0 to 9.0.0. Version 4.70.1 is not supported.

I would recommend uninstalling it, and install the latest release.