Delete Sent folder via SQL (or other ideas)

[BowlingTracker]

After upgrading to vB5 and putting it into production last night, I tried to send a PM to my mods, only to discover I was about 30k messages above my 500 quota and it wouldn't let me send.

It seems that for every member that has ever joined my forum, there is a "welcome" message in my sent box. Nearly 2000 pages of sent pm's!

How can I purge my sent box? I can't "select all/delete" 2000 times. Or at least, I don't want to.

Help.

Thanks.

[BowlingTracker]

Anyone have a clue how I can purge my sent folder w/o having to do select all delete on all 2000 pages? I'm okay with a SQL script, if that's what it takes.

Thanks

[Lynne]

I think you need to write a script. You have all messages listed in the privatemessage table (under the nodeid) and then they are also listed in the node table and closure table (nodeid as both a parent and a child).

[BowlingTracker]

Hi Lynne,

Thanks for your help.. I see how vb_node and vb_privatemessage are tied by nodeid. But when deleting one from the UI, it doesn't delete it from these tables. So I tracked down how vb_sentto table holds the folderid for the message.

That led to a bigger problem.

I found vb_messagefolder has duplicates for my userid! (UserId 1 only). Everything except "sent_items" and "messages" is duplicated. ie. trash is listed twice with id's 62642 and 62643.

If i click the trash folder in the UI, the url shows folderid 62642 and the trash folder appears empty. (even though I have deleted some messages). For the messages that I deleted via the UI, the vb_sentto folder shows them in folder id 62643. Which is why I can't see them in the UI. If I change the URL to 62643, I can see the messages that I previously deleted.

I'm assuming this is going to require a manual fix.

Can I just delete the row from vb_message folder for "trash" id 62642. Will the UI start using the other one to show the trash folder? or should I update all the data to that points to folderid 62643 and change them to 62642 and then delete 62643 folder? Will it start moving them to the right folder when I delete via the UI?

If your unsure, I'll dig into the code or just try it and see what happens. Just wanted to see what your thoughts were before I go down the wrong path.

Thanks again.

--------------- Added [DATE]1409630782[/DATE] at [TIME]1409630782[/TIME] ---------------

I got it straightened out now.

All via SQL:
I updated all the vb_sentto rows tied to folderid 62643 to 62642, then renamed the 62643 trash folder to something else. (Tested to makes sure that was working right in the UI. Perfect!)

Then renamed all the other dupes rows in vb_sentto. I kept the lower id's and renamed the ones with higher id's. All seems to be functioning well in UI, so I deleted the rows that I had renamed.

Finally, to get rid of the 2000 pages of sent welcome messages, I executed this SQL:

(Note to others, the folderid and title text is particular to my install only. If you use this SQL, make sure you know what your doing and update it accordingly!)

update vb_sentto
set folderid = 62642, deleted = 1
where userid= 1 and nodeid in (
select nodeid
from vb_node n
where n.userid = 1
and n.contenttypeid = 15
and n.title like 'Welcome to BowlingTracker.com%');

[Lynne]

I'm glad you got it all sorted out. Thanks for posting what you did - it may help someone else down the line.