Private Messages Problem - PMs with no subject or date in PM Boxes

[Quarterbore]

First,I had to do an emergency recovery from a backup and after this step is when the problem showed up. I tried going back to my backup a second time with the same result. Secondly, I did install a new hack when this started happening (I installed vBSEO) so it is possible the issue is there somehow but I am trying to debug the best I can...

The issue is that my PM boxes have blank messages showing at the bottom of members PM Box and they show in both the INBOX and SENT BOX

The format of the messages is always the same

<<icon>> <<BLANK TITLE>> <<07:00 PM 12-31-1969>>

So, I assume this would mean I am looking for a value in my PMTEXT table that has NULL values. The problem is, there are no "NULL values" in the table I tried looking based on the dateline as well as title and all of the PMs have data!

So, what I am trying to figure out is if the PMs are stored in some other data manager? Is there some place I can go to rebuild these? I also looked at my ts4mspmreceipt and ts4mspm tables and I do not see an issue their either.

Thanks for any help with this...

[Jake Bunce]

PM records are joined across the pm and pmtext tables. Maybe the backup / recovery introduced incomplete joined records between these tables.

[Quarterbore]

OK, so I understand that you are saying that I could have rows in the PM table that are not in my PMTEXT table.

I will write a PHP script to query for this later tonight to test this possibility...

Thanks for the suggeted place to look as It never occured to me but that would be logical! It would also make it easier for me to fix this from a backup as if there are PMTEXT entries missing I can go to a backup and move these over (at least in theory)!

I'll post if this was the issue and how I fixed it...

[Quarterbore]

OK, this must be the problem...

In my own PM box I had 22 PMs with no subject or date but when I queried the database for all PMs based on the PMTEXTID to compare the tables for my userid I discovered exactly 22 entries in the PM table that was not in the PMTEXT table!

Now I just need to pull a couple archived backups and get the data where I can access it and replace these missing PM Text entries!

Thanks for pointing me in the right direction... Cheers!

[Quarterbore]

OK, after going back to my backup I was able to copy the PMTEXT entries from my backup of my database and insert them into my PMTEXT table on my live site...

I still have one issue as the Private Messages indicator says that members have "UNREAD" private messages. I have ran some queries and I know I do not have any unread messages (the system says I have 3 unread).

I have not tried to evaluate my pmreceipt table yet but I do not see 3-PMS listed in there for myself so I do not think this is where the issue is comming from.

Can you help me find this one too?

Thanks!

EDIT:

OK, I know the problem is my "USER" table and specically the "pmunread" value stored in the database. I tried running the update counters scripts in the AdminCP and it does not look like these are able to do this...

Is there a utility that will rebuild the "pmtotal" and "pmunread" values?

I just about have things fixed but I really don't want to run a query to mark the unred to zero as some members likely do have unread PMs...

[Steve Machol]

Try running all the User-related counters here:

Admin CP -> Maintenance -> Update Counters

[Quarterbore]

No, I don't think those update the user table... I can test again if you think it should but I tried running all of the various update scripts in there and nothing seemed to fix the user table...

So, I coded a fix and released it here:

http://www.vbulletin.org/forum/showthread.php?t=138827

If vBulletin would like to improve on this and try to get included with a future release I would not complain in the least!

[Steve Machol]

Actually that has worked for me in the past which is why I suggested it.

[Quarterbore]

OK, I manually set my usertable on my Dev board to incorrect values and I ran every update in there... I really am pretty confident that there is no script to do this...

So, can I get this moved over to a feature request?

Perhaps with this you can add an option to prune PMs that are over X-months old where X is a setting?

I welcome someone to prove me wrong but I looked pretty long and hard to find a way to fix this!

[carcomp]

I'm having a similar issue, except it more reflects my pm table. I recently performed a site restore and for some reason, my pm table no longer refers to all of the pmtext entries.

What I'm asking is:

Does anyone know the SQL command to rebuild the pm table based on the data in the pmtext table?

I tried it manually ... Went into phpmyadmin and then to the pm table, did an insert with the data i want from the pmtext table (pmtextid=169 and userid=152) and then that user had the pm available to view again.

I know it will work, i'm just not good enough at sql to know how to do it.