Demigod
  • Posts: 59
  • Joined: 02/04/2007
I tried to delete a user from v1.9.0 tonight. No go. The UserPMessage and PMessage DB gave me fits. While trying to get the right items deleted, i ended up deleting all 3000+ PMs. Users are not happy. Now i finally got the user deleted only to find that it LEFT all of his posts in the forum.

I need to delete all his posts but can't because I get this error:

Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN SAME TABLE REFERENCE constraint 'FK_yaf_Message_yaf_Message'. The conflict occurred in database 'washingtonlakescom', table 'yaf_Message', column 'ReplyTo'.
The statement has been terminated.


Can someone give me a hand here? It has to be by username now because when I deleted him it must have changed his userID to something else.

Sponsor
Demigod
  • Posts: 59
  • Joined: 02/04/2007
Not exactly the response I was hoping for. 😞
Jaben
  • Posts: 2544
  • Joined: 09/10/2004
try this:



ALTER TABLE dbo.yaf_Message DROP CONSTRAINT FK_yaf_Message_yaf_Message

Demigod
  • Posts: 59
  • Joined: 02/04/2007
Thank you. I already changed the Flag on his posts to 30... in 1.9.0 will that effectively hide/delete them or do i need to run this and then actually delete them?
Demigod
  • Posts: 59
  • Joined: 02/04/2007
I followed your instuctions to remove the Constraint, but now that i am trying to upgrade the forum to 1.9.1.8 I am getting an error that I beleive is related to this change. During the install process it actually made it to the "Done" screen with the Finish button but I received this error at the end:

FILE:
constraints.sql

ERROR:
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraing 'FK_yaf_UserPMessage_yaf_PMessage'. The conflict occurred in database 'blahblah', table'yaf_PMessage, column 'PMessageID'.

Then it referenced the alter table code form the constraints.sql.

So I'm wondering how I can resolve this problem and get the data back to a state where I can re-enable this constraint... and do you think this will affect the way the site runs and its stability?
Demigod
  • Posts: 59
  • Joined: 02/04/2007
Is 2 years long enough to patiently wait for a reply so I can upgrade to the newest version?
bbobb
  • Posts: 1558
  • Joined: 21/10/2008
You're very patient, I lke you, you can easily wait a week or maybe a little more and you can upgrade to 1.9.5.5 RTM. We expect very little bugs in it.
Demigod
  • Posts: 59
  • Joined: 02/04/2007
Yeah, its the upgrade that bites me.. since taking out the constraint.. I just wish someone could give me an idea about what items are left in the DB because of the manual user deletion mentioned above... I could go manually fix it, add the constraint back, and everything would be happy again. I've put it off for years because of this issue.
Demigod
  • Posts: 59
  • Joined: 02/04/2007
Is there a database diagram anywhere? In the documentation perhaps? I want to recreate this FK but I am not sure where to start.
squirrel
  • Posts: 925
  • Joined: 14/01/2010
Originally Posted by: Demigod

Is there a database diagram anywhere? In the documentation perhaps? I want to recreate this FK but I am not sure where to start.



If it won't allow you to create the FK, there might be an index conflict somewhere - missing data in one table with present data in another can cause Key issues
If you can't find it using the forum search, try my signature link -- searches this site using Google: Google is my Friend 
Demigod
  • Posts: 59
  • Joined: 02/04/2007
Yep. Thats exactly the problem... but what I can't figure out is WHAT is present/missing. Take a look at the command I ran:


ALTER TABLE dbo.yaf_Message DROP CONSTRAINT FK_yaf_Message_yaf_Message

What foreign key did that remove? Would this be the opposide:

ALTER TABLE dbo.yaf_Message ADD CONSTRAINT FK_yaf_Message_yaf_Message

If not, can someone tell me what the command would be?
squirrel
  • Posts: 925
  • Joined: 14/01/2010
Originally Posted by: Demigod

Yep. Thats exactly the problem... but what I can't figure out is WHAT is present/missing. Take a look at the command I ran:


ALTER TABLE dbo.yaf_Message DROP CONSTRAINT FK_yaf_Message_yaf_Message

What foreign key did that remove? Would this be the opposide:

ALTER TABLE dbo.yaf_Message ADD CONSTRAINT FK_yaf_Message_yaf_Message

If not, can someone tell me what the command would be?



I will look into the procedures file and find out what that constraint is bound to --
If you can't find it using the forum search, try my signature link -- searches this site using Google: Google is my Friend 
Demigod
  • Posts: 59
  • Joined: 02/04/2007
So I decided to upgrade my test server from 1.9.1.8 to 1.9.5 today and hit the same error, but its a little more descriptive this time:

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Exception: FILE:
mssql/constraints.sql

ERROR:
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_yaf_UserPMessage_yaf_PMessage". The conflict occurred in database "washingtonlakescom_echo", table "dbo.yaf_PMessage", column 'PMessageID'.

STATEMENT:
if not exists (select top 1 1 from  dbo.sysobjects where name='FK_yaf_UserPMessage_yaf_PMessage' and parent_obj=object_id('[dbo].[yaf_UserPMessage]') and OBJECTPROPERTY(id,N'IsForeignKey')=1)
alter table [dbo].[yaf_UserPMessage] add constraint [FK_yaf_UserPMessage_yaf_PMessage] foreign key (PMessageID) references [dbo].[yaf_PMessage] (PMessageID)

Demigod
  • Posts: 59
  • Joined: 02/04/2007
So... I'm no DBA... but I learn fairly quickly as I go...

Tell me if this is correct:

The PMessageID field in the yaf_UserPMessage table is supposed to be a foreign key to PMessageID in yaf_PMessage.


If that is so (which in my DB it isnt right now...) then there shouldn't be any items in the yaf_UserPMessage table that don't have a corresponding record in the yaf_PMessage table.

When running this:

SELECT UserID, PMessageID FROM yaf_UserPMessage WHERE PMessageID NOT IN (SELECT PMessageID FROM yaf_PMessage)

I get 218 rows returned... 218 records in the UserPMessage table that dont have corresponding records in the PMessage table.

Would/Could one assume that since the actual message is gone that the record from UserPMessage can be deleted?

Demigod
  • Posts: 59
  • Joined: 02/04/2007
So.. not being one to wait... and since its a test DB... I deleted the records and ran the install script again and it completed that and moved onto user migration.

Problem solved? Good move to delete those records?
Users browsing this topic
    Forum Jump  
    • You cannot post new topics in this forum.
    • You cannot reply to topics in this forum.
    • You cannot delete your posts in this forum.
    • You cannot edit your posts in this forum.
    • You cannot create polls in this forum.
    • You cannot vote in polls in this forum.

    About Us

    The YAF.NET is an open source .NET forum project. YAF.NET is supported by an team of international developers who are build community by building community software.

    Powered by Resharper Donate with PayPal button

    Project Twitter Updates

    Copyright © YetAnotherForum.NET & Ingo Herbote. All rights reserved