Products

Evoq Content Overview Content Creation Workflow Asset Management Mobile Responsive Personalization Content Analytics SEO Integrations Security Website PerformanceEvoq Engage Overview Community Management  Dashboard  Analytics  Member Profile Gamification Advocate Marketing Community Engagement  Ideas  Answers  Discussions  Groups  Wikis  Events Mobile ReadyDNN Support PackagesEvoq: CMS FeaturesEvoq OnDemandCompare DNN's CMS Products

Solutions

Content Management SystemMulti-Channel PublishingDigital MarketingOnline Community ManagementIntranet Software SolutionOur CustomersPrime

Resources

Test DrivesSchedule A DemoDocumentation CenterWebinarsWhite PapersProduct ManualsRequest PricingData SheetsCase StudiesEvoq Preferred Products Content Management Ecommerce Forms Identity Management and Authentication Site Management Social Themes

Partners

DNN Partners Partner Directory Become a DNN Partner Partner Program Overview  Hosting  Implementation  ISV  Training  Competencies

Community

Participate Ask a Question Forums Community Showcase DNN MVP  Program Overview  Lifetime MVPs  Honorary MVPs Subscribe to DNN Digest Advisory Groups  Awareness Advisory Group  Developer Advisory Group  Partner Advisory Group  Technology Advisory GroupLearn Documentation Wiki Community Blog Video Library Project History Development RoadmapDownload DNN Store Language Packs Manuals Nightly BuildsSecurity Policies Security Center

Blog

About

News Press Releases News Coverage Press KitCareersContact DNN

QA

Ideas Test

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen
HomeHomeUsing DNN Platf...Using DNN Platf...Upgrading DNN P...Upgrading DNN P...7.2.1 to 7.2.2 upgrade fail!7.2.1 to 7.2.2 upgrade fail!
Previous
 
Next
New Post
3/20/2014 12:36 PM
 
Robert Fulop


www.fruitsteading.com/
Joined: 10/3/2009
Posts: 153
cathal connolly wrote:
this was one of a set of database optimizations contributed by a community member - they look fine themselves, but therer's always a danger that some old site may have invalid data (e.g. perhaps from a direct database update from a 3rd party module) and the fail - howevert I'm concerned that this is only an upgrade from 7.2.1->7.2.2 so that's much less likely. Would there be any way you could let us have a copy of your database so we can check and see what the issue might be?

Thanks Cathal, I sent you a download link to my db via pm.

Yeah, that's the thing, I was on the latest 7.2.1 and the error doesn't look like it's module related so it might be some glitch in 7.2.2..
 
New Post
3/20/2014 7:11 PM
 
Rod Weir


Joined: 6/10/2006
Posts: 224
This is the legacy of a bad database design. Since the early days of DNN, there has been little, or no referential integrity on key tables and structures. (This is typically what happens when software developers build database structures - little database knowledge) This means that you can have all sorts of bad, duplicate, or orphaned data in the database which can go unnoticed for a long time, and may or may not cause weird behaviour. It's only when you try to enforce this referential integrity, that SQL Server kicks in and throws these errors.

The best/only way to do this sort of upgrade is to first clean and sanitize the database of bad data, THEN, apply the RI constraints. If the latest DNN upgrade is not doing this, then there is going to be a LOT of this type of upgrade issues.
 
New Post
3/20/2014 11:06 PM
 
Robert Fulop


www.fruitsteading.com/
Joined: 10/3/2009
Posts: 153
Rod Weir wrote:
This is the legacy of a bad database design. Since the early days of DNN, there has been little, or no referential integrity on key tables and structures. (This is typically what happens when software developers build database structures - little database knowledge) This means that you can have all sorts of bad, duplicate, or orphaned data in the database which can go unnoticed for a long time, and may or may not cause weird behaviour. It's only when you try to enforce this referential integrity, that SQL Server kicks in and throws these errors.

The best/only way to do this sort of upgrade is to first clean and sanitize the database of bad data, THEN, apply the RI constraints. If the latest DNN upgrade is not doing this, then there is going to be a LOT of this type of upgrade issues.

***

That doesn't sound good Rod.. how do you 'clean and sanitize the database of bad data'? Is there an app for that? 
 
New Post
3/20/2014 11:16 PM
 
Robert Fulop


www.fruitsteading.com/
Joined: 10/3/2009
Posts: 153
**Update**
Restored from backup, deleted the duplicate row 855, 5, null, 36, now it's throwing me another similar one.. just keep deleting them??


26% ERROR occured - System.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'IX_FolderPermission'. Cannot insert duplicate key in object 'dbo.FolderPermission'. The duplicate key value is (855, 21, , 36).
The statement has been terminated.
at System.Data.SqlClient.SqlConnection. (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)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script)
ClientConnectionId:b3e0ef2f-2c7c-4e72-ae9d-c97bedf3bc59

--Correct error data
UPDATE dbo.[FolderPermission] SET RoleID = NULL WHERE UserID IS NOT NULL

System.Data.SqlClient.SqlException (0x80131904): The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.FolderPermission' and the index name 'IX_FolderPermission_Users'. The duplicate key value is (36, 855, 6).
The statement has been terminated.
at System.Data.SqlClient.SqlConnection. (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)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script)
ClientConnectionId:84f9cae3-4a58-401d-836a-977e2efa6da0

CREATE UNIQUE NONCLUSTERED INDEX IX_FolderPermission_Users ON dbo.[FolderPermission]
(UserID, FolderID, PermissionID)
INCLUDE (AllowAccess)
WHERE UserID IS NOT Null

System.Data.SqlClient.SqlException (0x80131904): The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.FolderPermission' and the index name 'IX_FolderPermission_Roles'. The duplicate key value is (-1, 378, 5).
The statement has been terminated.
at System.Data.SqlClient.SqlConnection. (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)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script)
ClientConnectionId:bab00e67-f406-4ba8-a24b-77fc66e9365d

CREATE UNIQUE NONCLUSTERED INDEX IX_FolderPermission_Roles ON dbo.[FolderPermission]
(RoleID, FolderID, PermissionID)
INCLUDE (AllowAccess)
WHERE RoleID IS NOT Null
 
New Post
3/21/2014 12:49 AM
 
Robert Fulop


www.fruitsteading.com/
Joined: 10/3/2009
Posts: 153
After deleting the 2nd duplicaate row (855, 21, null, 36) the upgrade went through!
The sites were very slow to reload it took about an hour to be able to log in and edit, I'll do some testing to see if everything works.
 
 Page 2 of 3
Previous123Next 
Previous
 
Next
HomeHomeUsing DNN Platf...Using DNN Platf...Upgrading DNN P...Upgrading DNN P...7.2.1 to 7.2.2 upgrade fail!7.2.1 to 7.2.2 upgrade fail!


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out