Orphaned Private Messages

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Get Involved
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
EightOhProlene
Registered User
Posts: 155
Joined: Sun Jan 18, 2004 4:44 pm

Orphaned Private Messages

Post by EightOhProlene »

Is there a way to remove orphaned private messages from my database? I routinely purge users who have not been active for a couple of years, but it seems the PMs they had in their Inbox (for sure, but perhaps other boxes, too, as I have not checked yet) are still in the database.
EightOhProlene
Registered User
Posts: 155
Joined: Sun Jan 18, 2004 4:44 pm

Re: Orphaned Private Messages

Post by EightOhProlene »

Well, ok... obviously there is no easy solution to this one.

Is this a bug? Should I file a bug report? Or is this an overlooked problem that could use correcting?
User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 51874
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Orphaned Private Messages

Post by stevemaury »

This is because the sender has a copy in their Sent messages folder.
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)
EightOhProlene
Registered User
Posts: 155
Joined: Sun Jan 18, 2004 4:44 pm

Re: Orphaned Private Messages

Post by EightOhProlene »

I thought perhaps so, but I have found quite a few where both the sender and recipient no longer exist.
EightOhProlene
Registered User
Posts: 155
Joined: Sun Jan 18, 2004 4:44 pm

Re: Orphaned Private Messages

Post by EightOhProlene »

Indeed, I found one where the sender was deleted after sending a PM, and when I check the recipient's PM boxes the PM is not in any of them. Yet it is still in the database. I am not sure if the PM was ever opened, so it might have been in the sender's Outbox when he was deleted.

But I also have other instances where neither the sender nor recipient still exist. So I am seeking advice on what to do.
User avatar
Brf
Support Team Member
Support Team Member
Posts: 52286
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Orphaned Private Messages

Post by Brf »

Actually, from looking at the code, the PRIVMSGS_TO_TABLE is deleted for anything that went to the deleted user, so the sent items for the other users are idea cleared, but the PRIVMSGS_TABLE for those messages is never deleted, so all of those messages are still in the database, although there is no way to access them.
Report it bugtracker.
User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 51874
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Orphaned Private Messages

Post by stevemaury »

I conducted an experiment,and determined that when a user is pruned from the ACP (that IS how you are doing it?), the author_id in the privmsgs and privmsgs_to tables is changed to 1 (Anonymous). The message remains in the recipient's inbox, but is marked "This message was sent by a user no longer registered." and is shown as being from Anonymous. If the recipient is pruned, the message is removed from the privmsgs_to table, but remains in the privmsgs table and the to_address still shows the user_id of the deleted user.

This is all by design, as removing the message completely would affect those to whom it was forwarded, or others in the group to which it was sent.
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)
User avatar
Brf
Support Team Member
Support Team Member
Posts: 52286
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Orphaned Private Messages

Post by Brf »

You have a point.
The same PRIVMSGS_TABLE record would be used for multiple recipients. You would not want to delete a message if it might have gone to more than one user.
EightOhProlene
Registered User
Posts: 155
Joined: Sun Jan 18, 2004 4:44 pm

Re: Orphaned Private Messages

Post by EightOhProlene »

That does not help PMs that are not for multiple users, though. My database is getting fat, and I have PMs in my database that are years old and simply from Anonymous to Guest, no other recipients.

And looking through the database, there are PMs addressed to my account personally, from Anonymous. None of them are in any of my PM boxes (I have long since deleted them), and the sender account no longer exists, either. So where, exactly, are they?
EightOhProlene
Registered User
Posts: 155
Joined: Sun Jan 18, 2004 4:44 pm

Re: Orphaned Private Messages

Post by EightOhProlene »

Forgot to answer this, in case it makes a difference:
stevemaury wrote:when a user is pruned from the ACP (that IS how you are doing it?
Yes, I am using the Prune Users - Last Active function in the ACP.
EightOhProlene
Registered User
Posts: 155
Joined: Sun Jan 18, 2004 4:44 pm

Re: Orphaned Private Messages

Post by EightOhProlene »

Nailed one:

From a dummy account, I sent myself three PMs.
PM 1 - Not read by recipient.
PM 2 - Read by recipient.
PM 3 - Deleted by sender before it could be received.

I deleted the sender's account. PMs 1 and 2 were still in my Inbox. I deleted both.

Though it appears in none of my PM boxes and the sender has been deleted, PM 1 is still in the database as from Anonymous to me.
Pit$Bull
Former Team Member
Posts: 23099
Joined: Sat Dec 02, 2006 4:08 pm
Name: Can't Remember

Re: Orphaned Private Messages

Post by Pit$Bull »

http://www.phpbb.com/rules/#rule4g
Bumping a topic (replying directly after your own post) is only permitted after six hours have elapsed. Priority is generally assigned to topics in chronological order, so excessive bumping will result in longer wait times.
To add additional information please edit your previous post.
You need not reply to this post.

Thank you.
User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 51874
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Orphaned Private Messages

Post by stevemaury »

EightOhProlene wrote:
Though it appears in none of my PM boxes and the sender has been deleted, PM 1 is still in the database as from Anonymous to me.

Yes, as I said. That is the way it works. Otherwise, it would have to check every other user to see if they had the same message and if so, it would still not be deleted. What is the size of your privmsgs table compared to your total database size?
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)
EightOhProlene
Registered User
Posts: 155
Joined: Sun Jan 18, 2004 4:44 pm

Re: Orphaned Private Messages

Post by EightOhProlene »

If that is the case, it does not seem efficient at all. Don't delete the orphaned PM on the chance that it might have been sent to more than one person? :|

I will get back to you on the sizes, but I do not see how that would legitimize the way this system seems to work.
User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 51874
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Orphaned Private Messages

Post by stevemaury »

The chance that it has been sent to more than one person, or forwarded, is pretty likely. It would, of course, be possible to check to see if a PM authored by an about-to-be-deleted user is in any other user's PM folder, but the overhead would not be trivial.

If you wish to do this manually:

Code: Select all

DELETE FROM phpbb_privmsgs WHERE `author_id` = 1 AND `msg_id` NOT IN(SELECT `msg_id` FROM phpbb_privmsgs_to);
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)
Locked

Return to “[3.0.x] Support Forum”