DNN Community Blog

The Community Blog is a personal opinion of community members and by no means the official standpoint of DNN Corp or DNN Platform. This is a place to express personal thoughts about DNNPlatform, the community and its ecosystem. Do you have useful information that you would like to share with the DNN Community in a featured article or blog? If so, please contact .

The use of the Community Blog is covered by our Community Blog Guidelines - please read before commenting or posting.


Preparing for an upgrade

Today I had to upgrade an old DNN 3.2.2 multiportal installation to DNN 4.8.1. There are 3 major areas that changed in the past DotNetNuke versions:

  1. New contstraints and unique indexes on Files table
  2. DNN Membership provider
  3. Consolidation of http handlers in one assembly

In order to smoothly pass the third change, I always do a 2-step upgrade. First to DNN 4.5.5, then to the last available version, 4.8.1 in this case. The consolidation of http handlers happened in DNN 4.6.0.

When upgrading from DNN versions prior to 3.3.x / 4.3.x, the upgrade process will upgrade the data in the DNN Database from almost pure asp.net membership, to the more loosely coupled DNN Membership. Major changes are: switch from unique ApplicationID / Name per portal to one ApplicationId / name per DNN installation, and DNNMembership no longer uses the aspnet roles and profile tables. However, even though all data is upgraded, no essential data is deleted. In order to finalize the upgrade, you can manually delete the old data using the following SQL:

DELETE FROM dbo.aspnet_UsersInRoles

DELETE FROM dbo.aspnet_Profile

DELETE FROM dbo.aspnet_Roles

DELETE FROM dbo.aspnet_Membership
WHERE ApplicationId <> (SELECT ApplicationId FROM dbo.aspnet_Applications WHERE ApplicationName='DotNetNuke')

DELETE FROM dbo.spnet_Users 
WHERE ApplicationId <> (SELECT ApplicationId FROM dbo.aspnet_Applications WHERE ApplicationName='DotNetNuke')

You can run this script from SQL Management Studio, or from Host > SQL (select run as script). NB: Always make a backup of your database prior to executing this SQL!

 

The reason upgrades fail mostly have to do with the first change: new constraints and unique indexes on the files table. Problems arise from duplicate files in the files table, and files in the files table without a corresponding folder in the folders table. Even though the DNN upgrade sql tries to handle a few cases, in my experience, there are often cases that are not handled. Today i created the following sql which fixed all upgrade issues for me:

ALTER TABLE [dbo].Files ADD FolderID int NOT NULL CONSTRAINT DF_Files_FolderID DEFAULT 0

GO

UPDATE [dbo].Folders
SET    FolderPath = FolderPath + '/'
WHERE  FolderPath <> '' AND RIGHT(FolderPath, 1) <> '/'

GO

UPDATE [dbo].Files
SET    FolderID = FO.FolderID
FROM   [dbo].Files F, [dbo].Folders FO
WHERE F.Folder = FO.FolderPath
    AND ( ( F.PortalID = FO.PortalID ) OR ( F.PortalID is NULL AND  FO.PortalID is NULL ) )
GO

INSERT INTO [dbo].[Folders] (PortalId, FolderPath)
SELECT     TOP 100 PERCENT F.PortalId, F.Folder
FROM         [dbo].Files F LEFT OUTER JOIN
                      [dbo].Folders FO ON F.FolderID = FO.FolderID
WHERE     (FO.FolderID IS NULL)
GROUP BY F.Folder, F.PortalId
ORDER BY F.PortalId, F.Folder

GO



-- delete duplicates from folders table
DELETE FROM [dbo].Folders
  WHERE FolderId in (
    SELECT FolderId  
    FROM [dbo].Folders FO 
      join (SELECT PortalId, FolderPath
            FROM [dbo].Folders 
            GROUP BY PortalId,FolderPath 
            HAVING COUNT(*) > 1) DUP 
      ON DUP.FolderPath = FO.FolderPath AND (DUP.PortalId = FO.PortalId OR ( FO.PortalID is NULL AND  DUP.PortalID is NULL ))
      WHERE FO.FolderId NOT IN 
        (SELECT FolderId=MIN(FolderId)
         FROM [dbo].Folders 
         GROUP BY PortalId,FolderPath 
         HAVING COUNT(*) > 1))

GO

Delete from Files 
WHERE FileID in (
    SELECT FileID
    FROM [dbo].Files F 
      join (SELECT FolderId, FileName
            FROM [dbo].Files 
            GROUP BY folder,FolderId,FileName 
            HAVING COUNT(*) > 1) DUP
      on DUP.FolderId = F.FolderID and DUP.FileName = F.FileName
    WHERE F.FileID NOT IN
     (SELECT FileID = MIN(FileId)
            FROM [dbo].Files 
            GROUP BY folder,FolderId,FileName 
            HAVING COUNT(*) > 1))

ALTER TABLE [dbo].Files
    DROP CONSTRAINT DF_Files_FolderID
GO

ALTER TABLE [dbo].Files DROP COLUMN FolderId

GO

This script will do the following:

  • make sure that all foldernames in the files table exist in the folders table (add if neccesary)
  • delete duplicates from folders table
  • delete duplicates from files table.

Of course.. there is a risk. Because the final step is to delete duplicate files, links to that files will seize to work. It will be necessary to do a manual check of your site, to see if this happened on your site.

You can run this script from SQL Server management studio, or from Host >SQL (if you are using a non standard databaseowner and/or an object qualifier, you will need to edit this script to meet your needs) and again: Always make a backup of your database prior to executing this SQL!

[edit]I edited this post as for some reason the script ended up all garbled[/edit]

Comments

Comment Form

Only registered users may post comments.

NewsArchives


Aderson Oliveira (15)
Alec Whittington (11)
Alex Shirley (10)
Andrew Nurse (30)
Andy Tryba (1)
Anthony Glenwright (5)
Antonio Chagoury (28)
Ash Prasad (32)
Ben Schmidt (1)
Benjamin Hermann (25)
Benoit Sarton (9)
Beth Firebaugh (12)
Bill Walker (36)
Bob Kruger (5)
Bogdan Litescu (1)
Brian Dukes (2)
Brice Snow (1)
Bruce Chapman (20)
Bryan Andrews (1)
cathal connolly (55)
Charles Nurse (163)
Chris Hammond (209)
Chris Paterra (55)
Clinton Patterson (40)
Cuong Dang (21)
Daniel Bartholomew (2)
Daniel Mettler (154)
Dave Buckner (2)
David Poindexter (4)
David Rodriguez (3)
Doug Howell (11)
Erik van Ballegoij (30)
Ernst Peter Tamminga (74)
Geoff Barlow (10)
George Alatrash (6)
Gifford Watkins (3)
Gilles Le Pigocher (3)
Ian Robinson (7)
Israel Martinez (17)
Jan Blomquist (2)
Jan Jonas (3)
Jaspreet Bhatia (1)
Jenni Merrifield (6)
Joe Brinkman (274)
John Mitchell (1)
Jon Henning (14)
Jonathan Sheely (4)
Jordan Coopersmith (1)
Joseph Craig (2)
Kan Ma (1)
Keivan Beigi (3)
Ken Grierson (10)
Kevin Schreiner (6)
Leigh Pointer (31)
Lorraine Young (60)
Malik Khan (1)
Matthias Schlomann (15)
Mauricio Márquez (5)
Michael Doxsey (7)
Michael Tobisch (3)
Michael Washington (202)
Miguel Gatmaytan (3)
Mike Horton (19)
Mitchel Sellers (28)
Nathan Rover (3)
Navin V Nagiah (14)
Néstor Sánchez (31)
Nik Kalyani (14)
Peter Donker (54)
Philip Beadle (135)
Philipp Becker (4)
Richard Dumas (22)
Robert J Collins (5)
Roger Selwyn (8)
Ruben Lopez (1)
Ryan Martinez (1)
Salar Golestanian (4)
Sanjay Mehrotra (9)
Scott McCulloch (1)
Scott S (11)
Scott Wilkinson (3)
Scott Willhite (97)
Sebastian Leupold (80)
Shaun Walker (237)
Shawn Mehaffie (17)
Stefan Cullmann (12)
Stefan Kamphuis (12)
Steve Fabian (31)
Timo Breumelhof (24)
Tony Henrich (3)
Torsten Weggen (2)
Vicenç Masanas (27)
Vincent Nguyen (3)
Vitaly Kozadayev (6)
Will Morgenweck (40)
Will Strohl (165)
William Severance (5)

Content Layout

Subscribe to DNN Digest

DNN Digest is our monthly email newsletter. It highlights news and content from around the DNN ecosystem, such as new modules and themes, messages from leadership, blog posts and notable tweets. Keep your finger on the pulse of the ecosystem by subscribing.  


Copyright 2017 by DNN Corp Terms of Use Privacy
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out