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.
