UBB.threads PHP Forum Software Community Forums UBB.threads 6 Version 6 Support Private messages lost during altertable-6.1-6.2, step 3

When I upgraded my currently 6.5 UBBT from 6.1.1 to 6.2 I ended up in troubles. Most probably while running the altertable-6.1-6.2, step 3; where the Messages table is made to use the uid instead of username. I started all over and deleted thousands of old messages and that seemed to do the trick...

But, after going all the way to version 6.5 no private messages are visible anymore for anybody. I didn't care too much at first, but now I'd like to try to fix the error that occurred.

Private messages sent and recieved after the upgrade all show OK, and no other problems have occurred.

Does anybody have any idea what could be the problem, how to investigate this, and how to fix this?

Tjerk.

Couple things. First, you need to make sure you have both a user #1, the placeholder user, and a user #2, your main admin. You can check both of these by running this query and see what you get back:

SELECT U_Number,U_Username FROM w3t_Users WHERE U_Number IN('1','2')

If either of those are missing, that could be part of the problem. We can check a few other things from there, however any of the old deleted PMs aren't recoverable unless we get you to restore to an old db prior to the upgrade and work out the initial problems.

The results of that SQL query looks ok to me;

U_Number U_Username
1 **DONOTDELETE**
2 Krejt

Tjerk.

ps. thanks for helping to sort this out !

[]....however any of the old deleted PMs aren't recoverable unless we get you to restore to an old db prior to the upgrade and work out the initial problems. [/]

I don't care about the old (really old) messages that I deleted myself, it is the more recent messages that I didn't delete but disappeared anyway - somewhere during the process. I have the feeling all these invisible messages are still in there somewhere..

See my w3t_Messages table; 7566 rows, 5.98 MB Data_length, 212 KB Index_length. Which is pretty much what I expected.

I'd love you to help me investigate that a little more!

Tjerk.

(where btw. did that old function to count and delete older private messages go?)

/me *tickles* Rick a little

Whoops, sorry about that. Hmm, I have a feeling what has happened is that you've got private messages that are attached (sent from) a user that has been deleted. Since it can't JOIN the message to a user it can't display it. The altertable is supposed to take care of that and set all of those PMs to a senderid of 1, so the message will still be available.

We could try to set the senderid to 1 on the older messages, but there's no easy way to figure out what message id you were on when the upgrade was done. What happens when the senderid is set to 1 is the sender is no longer visible.

You could grab the current message number and then maybe go back like a thousand and try that.

You'll want to do a db backup before doing this.

First get the current M_Number

SELECT MAX(M_Number) FROM w3t_Messages

Let's say that returned 10000. Subtract 1000 from that, so we get 9000 and then run this:

UPDATE w3t_Messages SET M_Sender='1' WHERE M_Number < 9000

Again, this might not catch all of the messages but should bring some of them back.

[]I have a feeling what has happened is that you've got private messages that are attached (sent from) a user that has been deleted.[/]I don't think that can be the problem since I only deleted about 5 users in the board's existance.

My problem is that right after the upgrade the database size is consistent with the amount of private messages I did not delete before the upgrade, indicating they are still in the database somewhere, but none of these pre-upgrade private messages are visible to their recievers.

The w3t_Messages info shows 7987 rows and 6.31 MB data length. The result of MAX(M_Number) is 67210, MIN(M_Number) is 57850, which could be correct, assuming I deleted all messages before number 57850, and about 9000 messages have been deleted by their recievers.

It feels to me as if the reference to the recievers of the messages has been lost, not the info (senderid) of the sender.

Rick, it would be nice to give my users their old messages back, but if you think this is not solvable it is not much of a problem to me. I'll simply delete all pre-upgrade messages if nothing helps.

Hmm, that might be a tough one to solve. What happens during the upgrade is the sender and receiver used to be the actual usernames but were converted to userids. You might try looking at a couple of old PMs in the db and see what the fields are set to. THis would give us an idea if there is anything we can do.

SELECT M_Uid,M_Sender FROM w3t_Messages ORDER BY M_Number LIMIT 5

That will give you the uid and sender of the first 5 messages in the system.

ok, this is fun! forensic detective work <img src="https://www.ubbcentral.com/boards/images/graemlins/wink.gif" alt="" />

After a little experimenting I dropped the limit on your query. The result shows thousands of messages with M_Sender=2. Makes sense, those are all welcome messages the board automatically sends to new members as if it was me sending them.
Then there are lots of messages with different M_Sender id's. Makes sense too; those are private messages pple sent to eachother over time.

The problem is in the M_Uid. All messages before a certain point show M_Uid=1. After this point the M_Uid column correctly goes like 55067, 55068, 55069, 55070, 55071 ... (obviously messages to new members) with only a few M_Uid's set to 1.

Note how user 55067 registered on 21/10/2004. I upgraded on 01/11/2004. So this 'point' in the message table is not related to the moment of the upgrade, as I thought. So there must be some users with 'old' messages.

I think this isn't fixable, right? I don't see how the correct M_Uid's could be recovered, unless we use the backup of the old database... If so, I better do a DELETE from w3t_Messages WHERE M_Uid='1' AND M_Sender='2' to delete all 5722 orphaned messages, unless you can think of any side effects?

That delete should be fine with one "if". If you actually login and use the user number "2" then any legit, non-welcome PMs, to other users will also be deleted.