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.
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
Send me the 2.0 database and I'll check it out.
My e-mail address is miha rthand com <- fill in @ and . respectively 🙂
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.
It depends on the problem. Did you do the upgrade? Or did it GoDaddy?
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.