Firefox PHP

"New Private Messages" when there aren't any...

Posted by Skye N. 
"New Private Messages" when there aren't any...
March 30, 2013 05:53PM
Seeing this strange behaviour on one of my websites, seems extremely rare and not sure how it came about

Several of my users (myself included) see the "New Private Messages" notification, and when you go to your Inbox, it's marked as "1 new" but there are no new messages

Having a look at the pm_xref table, there is indeed a message marked new:

SELECT * from p5_pm_xref where pm_folder_id = 0 AND special_folder = 'inbox' AND user_id = 694;
+------------+---------+--------------+----------------+---------------+-----------+------------+
| pm_xref_id | user_id | pm_folder_id | special_folder | pm_message_id | read_flag | reply_flag |
+------------+---------+--------------+----------------+---------------+-----------+------------+
|        166 |     694 |            0 | inbox          |           149 |         0 |          0 |
|        172 |     694 |            0 | inbox          |           155 |         1 |          0 |
+------------+---------+--------------+----------------+---------------+-----------+------------+

However, if you look up that pm_message_id 149

select * from p5_pm_messages where pm_message_id = 149;
Empty set (0.00 sec)

So, 2 questions...

1. Any idea how this happened?

2. Is there any script that will "clean up" the pm_xref table to remove messages that don't actually exist?

Thanks
Skye

___
Skye Nott
Corvus Digital
Re: "New Private Messages" when there aren't any...
April 02, 2013 02:23AM
I forgot I had manually deleted a spammer that was sending PMs to every member... I must have deleted the PMs but accidentally left the rows in the pm_xref table

Please disregard this question

Thanks
Skye

___
Skye Nott
Corvus Digital
Re: "New Private Messages" when there aren't any...
December 06, 2020 02:37PM
Hi,

For those who come here for the solution to this problem, here a SQL query:

Language: SQL
DELETE FROM phorum_pm_xref WHERE pm_message_id NOT IN (SELECT pm_message_id FROM phorum_pm_messages)

This will delete all reference of private message in the xref table if there's no more private message in the pm_messages table.
As usual with running queries directly, please TEST before in your dev environment :)
It worked for me, that's all I can say.

Cactus : [www.cactuspro.com]
Sorry, only registered users may post in this forum.

Click here to login