UBB.Developers Forums Community Development Programming MySQL and Server Configuration UBBT Database Corruption Sniffing Scheme

We're about to launch a forum based on UBBT, importing several hundred thousand posts into UBBT (from from wwwboard). We wrote the following SQL queries to make sure that the database was internally consistent after our import, but we were also wondering how often similar corruptions may creep into a large UBBT site in normal operation. The UBBT code doesn't include protections to ensure that all of the internal dependencies it relies upon are enforced, which means that you may occasionally have race conditions or other situations which would cause certain corruptions. These would generally be pretty benign, but may cause message loss or other such problems.

We're curious to see if anyone running a UBBT installation would care to run the following SQL queries to see if they have any such problems. None of the queries should select any rows if the database is consistent (we think; these things may not, in fact, be testing real invariants):

1. Find any posts with missing parents or threads:
SELECT t1.B_Number, t1.B_Parent, t3.B_Number as ParentNum, t1.B_Main,
t2.B_Number as MainNum, t1.B_Board FROM w3t_Posts AS t1
LEFT JOIN w3t_Posts AS t2 ON t1.B_Main = t2.B_Number
LEFT JOIN w3t_Posts AS t3 ON t1.B_Parent = t3.B_Number
WHERE t2.B_Number IS NULL OR (t1.B_Parent != 0 AND t3.B_Number IS NULL)

2. Find any posts belonging to nonexistant boards:
SELECT B_Board, B_Number FROM w3t_Posts LEFT JOIN w3t_Boards
ON w3t_Posts.B_Board = w3t_Boards.Bo_Keyword WHERE
w3t_Boards.Bo_Keyword IS NULL

3. Find any threads with an inaccurate reply count:
SELECT t1.B_Number, t1.B_Main, t1.B_Replies, COUNT(*) AS ThreadTotal
FROM w3t_Posts AS t1 JOIN w3t_Posts as t2 ON t1.B_Number = t2.B_Main
GROUP BY t2.B_Main HAVING ThreadTotal != (t1.B_Replies + 1)

4. Find any posts posted by nonexistent users:
SELECT B_Number, B_PosterId FROM w3t_Posts LEFT JOIN w3t_Users
ON B_PosterId = U_Number WHERE U_Number IS NULL

5. Find any boards with invalid info for last poster/main/post (this one is particularly sticky in 6.2.1, because you can break the invariant using the UBB Admin UI by deleting the last user to post in any board, and it'll then fail to display that board on the main index page):
SELECT Bo_Keyword, Bo_LastNumber, t2.B_Number AS LastPostNumber,
Bo_lastMain, t3.B_Number AS LastPostThread, Bo_Posterid,
U_Number As LastPoster FROM w3t_Boards AS t1 LEFT JOIN
w3t_Posts AS t2 ON t1.Bo_LastNumber = t2.B_Number LEFT JOIN
w3t_Posts AS t3 ON t1.Bo_lastMain = t3.B_Number LEFT JOIN
w3t_Users AS t4 ON t1.Bo_Posterid = t4.U_Number WHERE
t1.Bo_LastNumber != 0 AND t1.Bo_lastMain != 0 AND
t1.Bo_Posterid != 0 AND (t2.B_Number IS NULL OR
t3.B_Number IS NULL or t4.U_Number IS NULL)

6. Find any posts which have inconsistent thread parent information:
SELECT B_Number, B_Parent, B_Main FROM w3t_Posts WHERE (B_Parent = 0
AND B_Number != B_Main) OR (B_Parent != 0 AND B_Number = B_Main);

7. Find boards where the count of posts in that board is incorrect:
SELECT Bo_Keyword, Bo_Total, COUNT(*) AS ActualPosts FROM w3t_Boards
JOIN w3t_Posts ON B_Board = Bo_Keyword GROUP BY Bo_Keyword HAVING
Bo_Total != ActualPosts

8. Find boards where the count of threads in that board is incorrect:
SELECT Bo_Keyword, Bo_Threads, COUNT(*) AS ActualThreads FROM
w3t_Boards JOIN w3t_Posts ON B_Board = Bo_Keyword WHERE
B_Number = B_Main GROUP BY Bo_Keyword HAVING
ActualThreads != Bo_Threads

Any of these should be executable from the mysql command-line prompt; if you're worried that some of them might generate pages of output, you can try them at first with a LIMIT 10 restriction on the end. We're using version 6.2.1; I don't know when or whether any of these database fields have changed before or after that version.

Of course, there are a large number of other nominal invariants that could be tested.

We're curious as to how often such inconsistencies slip in. All can be repaired in one way or another, either by recalculating values, or deleting or reattaching posts, and we'd like to make a plan for whether we'll have to do that and if so, how often. #7 and 8 are particularly interesting for us right now, because they fail on our database, and we're trying to track down whether it's a problem with our code or normal UBB operation.

Thanks for the info.

I ran this on a fairly old forum - one that came from the wwwthreads days.

And I got results for all of them. Modern versions have some checks built in to prevent some of this stuff. Like I believe in 6.3 or so - a check was added to recalculate the number of replies if it's inaccurate. And I know in 6.2.3 or so a check was added so that if the parent post was deleted while you were replying, it stops you from making the post.

Very interesting thread!

My board has also some inconsistencies. Most of them where created during an reimport of postings.

The 6.2.x versions have some bugs with the replies count, but they should be resolved in the 6.3.x versions. I'm currently updating my board from 6.2.3 to 6.3.2, so I can't say for sure.

Deleting posts seems to be a weak point in ubb.threads. I think that this caused the inconsistence regarding posts, replies and threads count.

Have you already made some queries/scripts to repair the errors?
Sharing them would be very appreciated.

If you need some help, test data etc. just let me know.

Because of your query #4 I found two more users who are missing in my db.

Almost every query gives some results on my db. What a mess . Going to fix this asap.

Thanks for this interesting topic!

very nice can this be made into a maintenance.php script for the admin panel?

Well some of these queries need to be centralized. Like alot of the ON B_Board = Bo_Keyword choke for some reason or another.

On most queries I was fine but two choked as above and the ones that did come up all dealt with the shoutbox

Yes, but with an "auto-fix" button next to each inconsistency, please

Can you say what you mean by 'choked'? Did the queries fail in some way? Also, what do you mean by 'centralized'?

Thanks,
- Tim

As in the Quieries did not run. Could be centralized the MYSQL version that causes the query not to run

I run 3.23.55 still.

FWIW, TimD works with me. So if you have other comments (or thoughts on his posting), please go ahead and respond to him.

These do not run on my mysql just as a FYI the rest do

SELECT t1.B_Number, t1.B_Main, t1.B_Replies, COUNT( * ) AS ThreadTotal
FROM w3t_Posts AS t1
JOIN w3t_Posts AS t2 ON t1.B_Number = t2.B_Main
GROUP BY t2.B_Main
HAVING ThreadTotal != (t1.B_Replies + 1 )

SELECT Bo_Keyword, Bo_Total, COUNT( * ) AS ActualPosts
FROM w3t_Boards
JOIN w3t_Posts ON B_Board = Bo_Keyword
GROUP BY Bo_Keyword
HAVING Bo_Total != ActualPosts

SELECT Bo_Keyword, Bo_Threads, COUNT(*) AS ActualThreads FROM
w3t_Boards JOIN w3t_Posts ON B_Board = Bo_Keyword WHERE
B_Number = B_Main GROUP BY Bo_Keyword HAVING
ActualThreads != Bo_Threads

Nice set of queries. Something similar to this will be in 6.5 that is in the early stages of development along with repair options.

As for trying to track down the problem with #7 and #8. On import are you setting B_Approved to 'yes' on all imported posts? The Bo_Total and Bo_Threads fields in the w3t_Boards table should only match the total # of threads/posts that have the B_Approved field set to 'yes'. If any threads/posts do not have that field set properly you may find some inconsistencies.

Hey wait a second Scream ya haven't gotten 6.4 out to us yet and your planning on 6.5!

[]scroungr said:
Hey wait a second Scream ya haven't gotten 6.4 out to us yet and your planning on 6.5! [/]
You should see the 7.0 screenshots. I mean, wow.

Come on now guys leave the dude alone. 6.4 will be out before you know it. I mean they are on beta1 in the member area but yet on beta3 in the beta group. I would bet this thing does right to final without anymore beta trials to members.

but but but

Yes, and don't you know that we're already developing 6.4.1

Rick you gonna make the guy keel over in anticipation.

or die from frustration of waiting for 6.4...

To QUOTE Navaho

It's Done when It's Done!

To Quote George Armstrong Custer "Argghhhh"

[]scroungr said:
To Quote George Armstrong Custer "Argghhhh" [/]

[]Omegatron said:
Come on now guys leave the dude alone. 6.4 will be out before you know it. I mean they are on beta1 in the member area but yet on beta3 in the beta group. I would bet this thing does right to final without anymore beta trials to members. [/]
To be fair, there was an issue of *someone* posting a fake 6.4 changelog announcement.
And to qoute Josh qouting Navaho: "have a ball with it, tease your friends and neighbors with it because they can't get one yet ". Granted, that for b1, but I think the sentiment still applies.

I am anxiously awaiting 6.4. I know that it has been screaming through, but I know that it will be ready "when its ready". Its just fun to joke about it a little to take the edge off of the waiting. If I had beta 3, it would probally be installed on my site right now and hacked to heck, but I can't afford to put b1 on my production site, no matter how sweet the calender is and how much my higher up is breathing down my neck for it. I probally get asked if the new version is out yet, about, 3 times as often as I make, or even see, a comment about it yet.

So, in sumation, I want it now.
But, I understand that I have to wait.

Nothing wrong with some light-hearted joking around I know you guys are anxiously waiting for it and I really want to get it out to you as soon as we can. Yeah, it takes awhile to get a version up to the final release, but during that time we're always working on the next version to keep the ball rolling Hopefully it won't be too much longer

[]Scream said:
As for trying to track down the problem with #7 and #8. On import are you setting B_Approved to 'yes' on all imported posts? The Bo_Total and Bo_Threads fields in the w3t_Boards table should only match the total # of threads/posts that have the B_Approved field set to 'yes'. If any threads/posts do not have that field set properly you may find some inconsistencies. [/]

Thanks for the update (I'm the author of the original queries; Jim is working with me on the site and posted them here).

FYI, all, I've fixed those queries to be:

SELECT Bo_Keyword, Bo_Threads, COUNT(*) AS ActualThreads FROM w3t_Boards JOIN w3t_Posts ON B_Board = Bo_Keyword WHERE B_Number = B_Main AND B_Approved='yes' GROUP BY Bo_Keyword HAVING ActualThreads != Bo_Threads

and

SELECT Bo_Keyword, Bo_Total, COUNT(*) AS ActualPosts FROM w3t_Boards JOIN w3t_Posts ON B_Board = Bo_Keyword WHERE B_Approved='yes' GROUP BY Bo_Keyword HAVING Bo_Total != ActualPosts

Unfortunately, this hasn't gotten rid of my inconsistencies: we're still trying to track down what action creates them. They show up on boards other than imported boards, so I think it's not an import issue.

Also, here's another one, which finds posts that are on a different board from their parent post or their main thread post:

SELECT t1.B_Number, t1.B_Parent, t1.B_Main, t1.B_Board, t2.B_Board as ParentBoard, t3.B_Board as ThreadBoard FROM w3t_Posts AS t1 JOIN w3t_Posts AS t2 ON t1.B_Parent = t2.B_Number JOIN w3t_Posts AS t3 ON t1.B_Main = t3.B_Number WHERE t1.B_Parent != 0 AND (t1.B_Board != t2.B_Board OR t1.B_Board != t3.B_Board)

I'm also working on a script which will find, display, and repair all of these problems. I'll post a reference to it here when I'm done, if anyone's interested.

yes, of course.. sounds like a great utility

I'd recommend to update to 6.3.2.
If I remember correctly, 6.2.1 still had some bugs regarding counting the replies of a threads with caused errors while displaying threads (Empty pages or posts weren't visible). This had been fixed in newer versions.

You can manually apply this fix to 6.2.1 of course. I did that for my board. Rick used a slightly modified version of my fix in the 6.3.x versions.