Site announcements, suggestions, Q&A
#22257
the thread does not show up in when I look through the "Videos" section of the board.
Well, about this particular post, I actually found it in the video index (page 4 - its a may 2010 thread), but it appears as a 0 comments thread. When you open it, the comments are there.
#22259
davidognomo wrote:
the thread does not show up in when I look through the "Videos" section of the board.
Well, about this particular post, I actually found it in the video index (page 4 - its a may 2010 thread), but it appears as a 0 comments thread. When you open it, the comments are there.
Ah, you are right. But, as you say, the thread shouldn't show up with zero comments. Also, it should display on the first page of the video section because it recently had a comment posted.

James, help please!

Cheers,

Michi.
#22264
it's definitely there in the thread list, but you have to look further back as it's registering the date of the first post instead of the last post. not sure why that happens, but i noticed that problem in the past on a few threads (before the host upgrade, so it's not due to that), and if i recall correctly it eventually sorts itself out.
#22325
james, if you read my post, the problem with the wrong 'last post' dates registering on some topic lists actually started before the move, so don't do all that work for nothing! i think we can live with the occasional wrong date on a topic list, as long as we don't actually lose any posts. i don't think there's any way to fully fix this problem without some specialist software that compares all the tables and fixes any inconsistencies. you could probably also do this manually for any posts that this problem is found on, but that seems harder.
#22326
The problem is that, if data type invariants are violated (as they clearly are), the indexes and table entries can disagree with each other, with usually unpredictable consequences. As long as only additions are made to the database, things usually hold together. But once edits or deletions are made (which happens quite often), all hell can break loose, such as an edit affecting the wrong post, or a deletion removing the wrong article. This actually happened to me when I tried to fix some of my posts that were corrupted, so we clearly have a problem.

I don't think that continuing to run with a corrupted database is advisable. I've seen many a database self-destruct over the years, sometimes months after the initial damage was done. I just recently spent several days recovering just such a database…

Cheers,

Michi.
#22328
ok, but since the database was corrupted some time ago (before the server upgrade), we'd have to try to figure out what date this started happening (at least 6 months since i noticed it, but could be a lot longer than that). the further you go back, the more posts... there could potentially be a couple of thousand posts to add!

if we go back to a backup from a year ago (just as an example), how would we add all the posts without them getting corrupted again? what if we do all that work and the glitches actually started happening a lot earlier than that?

also, are you saying that edits and deletions done using the forum software cause this problem? because people will always want to do that, and posts sometimes need to be moved, merged or split in addition to content edits and deletions. with the rapid growth rate of the forum, we can expect this to happen a lot more, and to clean up the database regularly sounds like a lot of work. short of disallowing edits and deletions, i can't think how to prevent the problem.

to date there are over 20,000 posts and so far the only issues we have with missing posts was over that 2 day period. the only other issue i'm aware of is with the dates on the threads (and this had nothing to do with the server upgrade as it's an older thread that was most likely affected earlier, since i came across that glitch once or twice before). i appreciate being cautious, but i wonder if we are really at danger of losing posts?
#22330
bubudi wrote:ok, but since the database was corrupted some time ago (before the server upgrade), we'd have to try to figure out what date this started happening (at least 6 months since i noticed it, but could be a lot longer than that). the further you go back, the more posts... there could potentially be a couple of thousand posts to add!
No, I don't think so. It certainly looks like there were no missing posts until about ten days ago. The corruption needs fixing, but there is no need to restore thousands of posts, as far as I can see.
if we go back to a backup from a year ago (just as an example), how would we add all the posts without them getting corrupted again? what if we do all that work and the glitches actually started happening a lot earlier than that?
Run a database consistency check and fix anything that doesn't hang together. That gets the database back into a consistent state, so things work normally again.
also, are you saying that edits and deletions done using the forum software cause this problem?
No, I don't think that's the case. It seems that James's attempts to migrate the missed articles from the old server are partly responsible. At least, I saw all the weird problems with wrong images, missing images, editing the wrong post, etc. after that. Editing and deleting is dangerous only when it's done on an inconsistent database.
to date there are over 20,000 posts and so far the only issues we have with missing posts was over that 2 day period.
Yes, and since then the database has been in a weird state.
i appreciate being cautious, but i wonder if we are really at danger of losing posts?
I believe we are, at least judging by the symptoms I saw a few days ago. Database corruption can lurk for months without doing any harm whatsoever, and then suddenly cause large chunks of data to be lost.

The way to be sure is to run a complete consistency check, making sure that the indexes don't contain posts that don't exist, and making sure the all posts are indexed. That also should fix the incorrect reply counts we saw.

Cheers,

Michi.
#22335
michi wrote:
bubudi wrote:ok, but since the database was corrupted some time ago (before the server upgrade), we'd have to try to figure out what date this started happening (at least 6 months since i noticed it, but could be a lot longer than that). the further you go back, the more posts... there could potentially be a couple of thousand posts to add!
No, I don't think so. It certainly looks like there were no missing posts until about ten days ago. The corruption needs fixing, but there is no need to restore thousands of posts, as far as I can see.
i was actually referring to the topic lists showing an older 'last post' date, so the thread ends up out of order. davidognomo was saying he thought the post count on one such thread was out, even though no posts were missing. this problem with the dates/post count definitely started at least 6 months before the migration of the site. there is no knowing how many posts are affected (unless we check the whole database).
michi wrote:Run a database consistency check and fix anything that doesn't hang together. That gets the database back into a consistent state, so things work normally again.
just curious but how do you run a database consistency check/fix? is there automated software for this?
#22337
bubudi wrote:just curious but how do you run a database consistency check/fix? is there automated software for this?
I don't know whether someone has written a consistency checker for phpBB, and I don't know phpBB well enough to tell you how do it for that.

In general, databases contain a number of tables. In each table, there are records with a number of fields. Some of the fields contain values that refer to a corresponding field in another table. For example, you might have a table that contains a list of file names and post IDs, where each file name shows where on disk you find the corresponding image and the post ID records which post attaches that image. Another table of posts might have a field with the post ID and another field that lists the images that are attached to that post.

When you look at all the posts in the posts table and collect all the image lists, you should find a corresponding entry for each image in the image table, so there should be no posts that list images that don't also appear in the image table. And, conversely, when you take all the images that are in the image table, there should be none that are not referenced by the image list of some post.

A complex database, such as for the forum, will probably have dozens of tables with many such interrelationships. Doing a consistency check means to examine all of the relationships and finding any discrepancies.

Second, modern databases maintain indexes. They exist to make retrieval of particular pieces of information more efficient. For example, you might have a table of all posts, with a field in that table telling you which forum the post belongs to. When someone looks at a forum page, such as "Media", the software needs to display all the articles in that forum. One way of doing that would be to scan through the list of posts in the posts table, fishing out all the ones that belong with the "Media" section. But doing that is very inefficient.

An index is a lookup data structure (such as a tree or hash table) that makes it much more efficient to retrieve this information. For example, the DB might contain an index that indexes all posts by the section they were posted in. When the software wants to know what articles are in the "Media" section, it goes to the section index, looks for "Media" and picks up the record identifiers for all the posts in the posts table that were posted in "Media". Doing that avoids scanning over the posts table and is much more efficient.

The potential problem is that the index stores some of the same information (the section) as the posts table, meaning that, when a post is added or deleted, both the index and the posts table need to be updated in the same way. The index must not contain any posts in the posts table that don't exist, and the posts table must not contain any posts that aren't also in the index. If the index goes out of sync with the table, lookups in the database display inconsistent results, depending on whether the information was retrieved via the index or via the table.

A tool to check consistency will retrieve all the information in the database (not using indexes) and check all these relationships. For any relationships that are damaged, you usually need to look at them individually to decide what to do (maybe delete the affected post, or remove an image attachment where the corresponding image has been lost). Once all the information is consistent, the tool then checks that all the indexes agree with what's actually in their corresponding table and rebuilds the indexes that are incorrect.

Not sure whether any of this has much to do with djembe anymore... ;-)

Cheers,

Michi.
#22338
thanks, that's close to what i thought would be involved, but then it seems the only viable way to do that is with a tool made specifically for phpbb. i'm sure james is looking for one by now!

michi wrote:Not sure whether any of this has much to do with djembe anymore...
as is the case with many of our threads in the social section. it's there to prove we can survive 5 minutes without talking about the djembe ;)
#22340
bubudi wrote:
michi wrote:Not sure whether any of this has much to do with djembe anymore...
as is the case with many of our threads in the social section. it's there to prove we can survive 5 minutes without talking about the djembe ;)
Five minutes? Don't you think that's pushing it a bit? ;)

Michi.
#22342
Ok, since some this corruption is really old, I don't think it's going to cause too much trouble.

I have been speaking to some phpBB experts and I am backing out my changes and re-importing the missing posts correctly.

If we spot any further trouble after that, we'll deal with it as best we can.