Orphaned Private Messages

EightOhProlene · Post by **EightOhProlene** » Wed Oct 27, 2010 3:51 am

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 · Post by **EightOhProlene** » Wed Oct 27, 2010 3:20 pm

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?

Post by **stevemaury** » Wed Oct 27, 2010 3:29 pm

This is because the sender has a copy in their Sent messages folder.

EightOhProlene · Post by **EightOhProlene** » Wed Oct 27, 2010 3:39 pm

I thought perhaps so, but I have found quite a few where both the sender and recipient no longer exist.

EightOhProlene · Post by **EightOhProlene** » Wed Oct 27, 2010 3:51 pm

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.

Post by **Brf** » Wed Oct 27, 2010 4:23 pm

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.

Post by **stevemaury** » Wed Oct 27, 2010 4:25 pm

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.

Post by **Brf** » Wed Oct 27, 2010 4:30 pm

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 · Post by **EightOhProlene** » Wed Oct 27, 2010 4:46 pm

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 · Post by **EightOhProlene** » Wed Oct 27, 2010 4:54 pm

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 · Post by **EightOhProlene** » Wed Oct 27, 2010 5:19 pm

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 · Post by **Pit$Bull** » Wed Oct 27, 2010 5:23 pm

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.

Post by **stevemaury** » Wed Oct 27, 2010 10:52 pm

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?

EightOhProlene · Post by **EightOhProlene** » Wed Oct 27, 2010 11:14 pm

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.

Post by **stevemaury** » Thu Oct 28, 2010 3:23 pm

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);

advertisements