<< Added forums for discussions related to Righthand and my (free) tools | Integrating MvcMiniProfiler and LLBLGenPro >>

A workaround to a problem when upgrading BlogEngine from 2.0 to 2.5

by Miha Markič 16. July 2011 16:54

During the BlogEngine upgrade from 2.0 to 2.5 (one that hosts this blog) I’ve come across a problem. The problem might happen during execution of the SQL Server upgrade scripts that come with BlogEngine 2.5.

After running the scripts I’ve got an error mentioning that constraint FK_be_PostComment_be_Posts can’t be enforced. Huh? After some experimenting I’ve seen that the 2.0 database isn’t exactly well enforced with constraints and I had some comments left that don’t belong to any of the posts (I guess I’ve deleted the posts but comments were still there because database didn’t enforce the constraints and BlogEngine didn’t delete them).

Here is what I’ve did to upgrade.

1. In the upgrade script comment this statement:

          1: ALTER TABLE dbo.be_PostComment

         2:   ADD CONSTRAINT FK_be_PostComment_be_Posts FOREIGN KEY (BlogID, PostID) REFERENCES dbo.be_Posts (BlogID, PostID)

         3: GO

2. Run the upgrade script.

3. (Optional) To find if any comment is parentless execute this SELECT statement

       1: SELECT * FROM dbo.be_PostComment WHERE postid NOT IN (SELECT postid FROM be_posts)

4. Delete the orphaned comments

       1: DELETE FROM dbo.be_PostComment WHERE postid NOT IN (SELECT postid FROM be_posts)

5. Run the statement from upgrade script that you’ve commented in #1.

That’s it. I guess you could delete orphaned comments even before running the upgrade script and thus avoid first and last step.

Observation: Looks like at least database in version 2.0 wasn’t very well enforced, hopefully 2.5 rectifies this problem (it adds constrains here and there). Don’t forget, database is the last defense tier against bad data and should be as much protected as it can be.

961c7437-fa54-4c4f-b895-82c2dcbd1e62|0|.0

Tags: blogengine.net, upgrade, workaround

SQL | Tip

Peculiar problem involving Windows 2003, VMWare Server, SQL Server 2005 and networkingOver the weekend I've built my new server - yes, the content you are reading right now is ...Problems signing an assembly with a key stored in key container (VS2005)This is one of those problems you can't understand how could it happen. Some background: In the pa...Dell 2001FP LCD and USB hub problemSince long I have a nice 20" Dell 2001FP LCD monitor that works just fine. As a bonus it has an inte...

Comments (6) -

Andrea D'Orio

7/27/2011 1:16:50 AM #

Hi,

i've the same problem but with the sql ce upgrade script from 2.0 to 2.5. Can you help me with that script?

Thanks a lot,
Andrea

Miha Markic

7/27/2011 10:12:05 AM #

Send me the 2.0 database and I'll check it out.

Miha Markic

7/27/2011 10:13:14 AM #

My e-mail address is miha rthand com <- fill in @ and . respectively

Edward

8/19/2011 2:46:59 AM #

I upgraded via Godaddy from BE 2.0 to BE 2.5 and now the app crashed and the GD roll back option is not working, I don`t have a back up of the 2.0 files, 3 months work sitting in the upgraded files folder that don`t work. The staff at GoDaddy are no experts with "third party" apps like BlogEngine, they said. I was going to manually change it all back but the upgrade is major, not sure that would work. Is there a way to analyze this and get my 100 posts back in the new or earlier version?? the one GoDaddy provides has the internal database, in a content folder.

Miha Markic

8/19/2011 11:41:13 AM #

It depends on the problem. Did you do the upgrade? Or did it GoDaddy?

Edward

8/19/2011 6:30:23 PM #

GD did, later I found out that if I had added any plugins it would be bad and so it did. A friend named Brian Davis helped me a lot last night, online from USA and he was able to get a fresh copy of BE 2.0 into the site, but he asked me to get help to extract the data base info which is one created by GoDaddy so I can recover the posts. There I`m stuck, he told me to talk to BenAdm at the blogengine blog. The blog is back to neanderthal mode, when you install it. I`m just wondering how to get the DB extracted from GD in order to insert it back into the fresh copy we uploaded.

Add comment

Name* Required Please choose another name

E-mail*

Country Country flag

b i u quote

Comment
Preview

Notify me when new comments are added

Miha Markic

INETA Country Leader for Slovenia

Miha currently works as a free lance consultant and software developer specialized in .net area.
He graduated in Computer and information science at the University of Ljubljana, Slovenia. He has accumulated experience in various programming languages such as Java, Visual Basic 3-6 (MCP), Visual C++, Delphi, C# and VB.Net through years.
He has experience in practically all (technical) stages of project development, including planning, framework development, user interface, business processes, as well as testing and documenting. He has worked on big and small projects in Slovenia and abroad (e.g. participated in completing level 3 IS for the Nucor steel plant, Hertford, USA).
Currently he enjoys programming in .net environment using C#. Since 2000 he has been active in Developer Express' DX Squad and has been ECDL trainer and tester. He also gives lectures on conferences and other events in Slovenia.