Posted By tmcguire on 06 Mar 2007 4:41 PM
I am receiving the following error after I upgrade from 4.03 to 4.37:
Upgrading DotNetNuke
Current Assembly Version: 04.03.07
Current Database Version: 04.00.03
00: - Executing Script: E:\webs\resource.scrantontimestribune.com\Providers\DataProviders\SqlDataProvider\Upgrade.SqlDataProvider
Upgrade Status Report
00:00:00.046 - Upgrading to Version: 4.0.4 Error! (seeE:\webs\resource.mysite.com\Providers\DataProviders\SqlDataProvider\04.00.04.log for more information)
00:00:06.406 - Upgrading to Version: 4.0.5 Success
00:00:06.406 - Upgrading to Version: 4.0.6 Success
00:00:06.421 - Upgrading to Version: 4.0.7 Success
00:00:06.421 - Upgrading to Version: 4.3.0 Success
00:00:06.484 - Upgrading to Version: 4.3.1 Error! (seeE:\webs\resource.mysite.com\Providers\DataProviders\SqlDataProvider\04.03.01.log for more information)
00:00:06.625 - Upgrading to Version: 4.3.2 Success
00:00:06.859 - Upgrading to Version: 4.3.3 Success
00:00:07.625 - Upgrading to Version: 4.3.4 Success
00:00:07.671 - Upgrading to Version: 4.3.5 Success
00:00:08.828 - Upgrading to Version: 4.3.6 Success
00:00:12.125 - Upgrading to Version: 4.3.7 Success
00:00:13.390 - Performing General Upgrades
Upgrade Complete
04.00.04.log says
System.Data.SqlClient.SqlException: The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Files_Folders". The conflict occurred in database "Resource", table "dbo.Folders", column 'FolderID'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL)
at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions)
If (SELECT dbo.fn_GetVersion(3,2,3)) = 0
BEGIN
ALTER TABLE dbo.Files ADD CONSTRAINT
FK_Files_Folders FOREIGN KEY
(
FolderID
) REFERENCES dbo.Folders
(
FolderID
)
END
04.03.01.log says
System.Data.SqlClient.SqlException: The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Files_Folders". The conflict occurred in database "Resource", table "dbo.Folders", column 'FolderID'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL)
at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions)
/* This tries to apply a foreign key on the files/folders tables */
/*****************************************************************/
IF NOT EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N'FK_Files_Folders') AND OBJECTPROPERTY(id, N'IsForeignKey') = 1)
BEGIN
ALTER TABLE dbo.Files ADD CONSTRAINT
FK_Files_Folders FOREIGN KEY
(
FolderID
) REFERENCES dbo.Folders
(
FolderID
)
END
Can anyone help me make heads or tails of this? We have been trying to upgrade for weeks now and nothing has worked.
Thanks.
I think Lee's advice is on the right track. Specifically, what has happened is that you have at least one 'FolderID', in the Files table that does not exist in the Folders table. This could be caused by a bug, or because you have some stuff in the trash that you haven't deleted that references the non-existent Folder, or something else which caused this issue.
What I do, in general, when I have FK problems is to create another database, copy the tables referenced to the new database. Then attempt to apply the FK constraints you have above. You will get an error of course, but usually it will give you more information in the database log on specifically which record(s) have a bad FolderID.
You can also run queries to discover this. I've not got the db in front of me, but generally, something like the following:
SELECT * from Files
WHERE FolderID NOT IN (SELECT FolderID from Folders)
Hope this helps.