UBB.Developers Forums UBB.threads 7 Development How Do I? Orphaned Posts Help

Due to my user table getting corrupted several times throughout the years, I have many posts that are orphaned because the user associated with them was lost from the user table.

How can I clean this up? If I simply delete all the posts from these non-existent users, will that screw up the forum? Any help with this matter would be greatly appreciated.

If you delete random posts using a SQL command, you will orphan many other posts that potentially tie to the ones you just deleted.

Is there any way to fix this then? The posts with no user connected to them don't even show up.

It seems that whenever I have a problem, it is with the User table, and when I repair the table it always loses a couple of rows. Sometimes I can restore the users that are lost, but sometimes I don't know which users were lost.

You could probably write a query that would assign these orphaned posts to B_PosterId='1' (which is the DONOTDELETE Anonymous gump).

The issue you would face is gleaning all the specific posts that are orphaned.

If the problem is indeed that the posts do not have a Poster ID assigned to them, then the following query should work:


*Always take care and backup your database before attempting queries you are not 100% sure about.*

That won't work - because probably those posts have a usernumber already associated with them - however that user doesn't exist.

What you need to do - is recreate the missing users (with the correct number) which will make them reappear.

There was a recent post here - where I posted a script which cycles through all your posts and lists all your missing users.

Let me find that post.

This thread will help you - about 4 or 5 posts down - I post the script that'll check for missing users.

https://www.ubbdev.com/forum/showflat.php?Cat=0&Number=106554

Thanks Josh! It lists my missing users...but how do I know who they were to recreate them? I might me able to find some of these users from old backups...but I think that many of them are gone forever. I guess I could just make up names for them so at leasts the posts will show up.

Yeah, what you need to do is register a user - then change that user's number.

UPDATE w3t_Users
SET U_Number = xxxx
WHERE U_Username = 'yyyyyyyy'


Or - you can set those posts to anonymous - so if you know user #506 is missing, you can do a query like this:

UPDATE w3t_Posts
SET B_PosterId = 1,
B_Reged = 'n'
WHERE B_PosterId = 506


That'll set all posts by #506 to an anonymous post.

Hope that helps.

My user table got corrupted AGAIN today. This seems to be happening a lot lately. Is there any reason you guys know of why this would suddenly start happening frequently?

This is generally trouble with MySQL or the MySQL configuration. If your server is overloaded or MySQL isn't configured correctly this will happen.

A longshot..when my user table got corrupted I found I had a bad sector on the Hard Drive..Hard Drive went out shortly thereafter

[]ChAoS said:
A longshot..when my user table got corrupted I found I had a bad sector on the Hard Drive..Hard Drive went out shortly thereafter [/]

so in other words BACKUP NOW!