Recent Forum Errors

[Wijitmaker]

Our database was recently corrupted so we had to make a recovery. Unfortunately things aren't quite the same as they were yesterday. If you see any errors (missing members, double posts, etc..) on the forums please post a link here. We'll try to fix them. Thanks.

[Paal_101]

I know Cory's post count is not kosher

[Titus Ultor]

Oh. Lol. I didn't even notice that. It's okay, I guess, because I have the little logo thing now. People won't think I'm bushy-tailed.

Edit: By the way. I had 22,093 posts beforehand. I want those back.

[CodeOptimist]

Edit: By the way. I had 22,093 posts beforehand. I want those back. 

←

You sure you aren't off by a factor of ten or more?

[Argalius]

Testing for my own post count...

EDIT: Seems to be right.

Edited April 11, 2005 by Argalius

[Centurion_13]

My count is down about 150 or so but I dont really care

[Lorian]

I wonder if mine has changed...

EDIT: Doesn't look like it has, goood

Edited April 11, 2005 by Lorian

[Wijitmaker]

k, fixed

[IME_Admin]

Hello everyone, including Jason and Tim!

Just a little additional insight here, take it for what it's worth. I don't believe the forums will contain any miscalculated post counts as reported by some. The corruption was really only a locking error experienced on (specifically) the members table only. And although this table contains various member info and numerical data, there was only a single line which was adversely affected as a result of this minor issue. A quick repair and optimization was run immediately upon noticing the problem and the row was removed and the forums were returned to complete functionality.

Losing something around the ballpark of 150 posts (if that was in all seriousness) would have to be the result of a completely unrelated issue, probably non-database related, and rather the result of old threads being removed or a particular forum being set to not count posts any longer. That would be my best guess. I also highly doubt it would be a result of posting 150 times throughout the duration of the problem (which was only a few minutes anyhow). And considering that only certain accounts up to a particular data row were functioning, that would have skewed that post-mongering likelihood even moreso (haha).

Lastly, and just to clarify, there should definitely not be any reports of double posts, as that table is completely separate from where the problem occured. Nevertheless, stuff happens, and if there is anything else anyone thinks might have occured in terms of oddities over the last day or so, please post here and I will continue to monitor this thread.

Thanks!

P.S. Everyone be sure to remind Tim how important it is to optimize MySQL databases more than once a year! (especially when you have such an awesome and growing, active community)

Haha, just kidding Timbo

[Centurion_13]

It was worth a try

[Argalius]

It was worth a try

←

, you cheater!!!

This is how you increase your post size

[DarkAngelBGE]

Haha, Matt. Thanks for posting on here.

[Wijitmaker]

Heyyas Matt.

Actually I think the corruption did have something to do with those oddities. Reflectiong back on the events - The prior evening I had just altered a member (Saggara) from the historian group to the regular forumer group. I then attempted to change another forumer (Titus Ultor) from regular forumer to the historian group. It wasn't successful, it wouldn't recognize his name or his forum ID number. I thought it might have been just a bad connection with my modem and didn't think much about it, and I sent Tim an email with the error:

mySQL error: Got error 134 from table handler

mySQL error code:

Date: Sunday 10th of April 2005 12:50:23 AM

I even tried the MSQL database and when I get to the members and near, I get:

#1030 - Got error 134 from table handler

The next day when you optimized the databases, it had some funky errors. Titus Ultor user id was completely missing from the database. So was Paal_101. So I checked my local backup database and manually entered them back in through phpmyadmin. Perhaps it was something to do with the historian user group... I don't know, but they were gone from the database.

Their post count was off, because I manually entered them, and if I put in a number for their post tally it would be a old. But, I fixed that by running the forum recount tool.

I'm not sure what the double/triple/quadrupal... (7 was the highest I saw) was. Perhaps maybe because people were posting while the forum was optimizing?

Matt: 2 other things:

1) I found where you optimize in the invision admin panel - so thats nice

2) Also, I found the IP of the referer spammer, and you were right, they were all from the same IP. So I denied them. - so hopefully that will stop that problem.

[Red_08]

Ohh, so that's why my post count is lower.

I was gonna say, "Hey, I had already passed 800."

[IME_Admin]

Heyyas Matt.

Actually I think the corruption did have something to do with those oddities.  Reflectiong back on the events - The prior evening I had just altered a member (Saggara) from the historian group to the regular forumer group.  I then attempted to change another forumer (Titus Ultor) from regular forumer to the historian group.  It wasn't successful, it wouldn't recognize his name or his forum ID number.  I thought it might have been just a bad connection with my modem and didn't think much about it, and I sent Tim an email with the error:

The next day when you optimized the databases, it had some funky errors.  Titus Ultor user id was completely missing from the database.  So was Paal_101.  So I checked my local backup database and manually entered them back in through phpmyadmin.  Perhaps it was something to do with the historian user group... I don't know, but they were gone from the database.

It is completely possible that the initial locking stemmed from these two users and some failed modifications, so I'll assume this is where it all started. For future reference (since I know this is the largest your community has ever been), generally it is a good idea to run that forum optimization in the IPB panel whenever you get a chance. I run one particular forum with around 100,000 posts (cleaned a ton out over the last year even), and we have over 8,000 members. I generally make it a good practice to optimize my databases every week or so. I'd recommend the same for you, or at least maybe once a month if you forget. Also, when you start to see an error code like that when attempting to move a member... the very first thing you should do is run the forum optimizer in the IPB control panel, then come back to it. Normally this will correct anything that is parsing slowly, or having a tough time finding a record. Again, I cannot stress enough, MySQL optimization is your best friend. And not even having to do it from the command line should be cake.

Ok, one other thing I'm curious about, how did you enter the two members (and their ID's) back into the database? In an existing row? Or a totally new row? It is not (and I mean never) a very good idea to add back old members by creating a new row (if that was what you did). Now IF it was already existing, and it was still the correct and original ID where you plugged extra info in, then you're fine. Here's why it is NOT a good idea to create a new member row manually: As you know, members are assigned an ID number upon initial registration, but what you may not realize is this column is associated with another number which is essentially a hidden value (set by auto-increment). And sometimes, depending on if you have deleted members in the past, these numbers will not sync, but will always remain a constant distance apart with each deletion or member movement through the forum administration panel. Now, by simply readding an old ID with a new row, the auto-increment value (used for indexing and searching) will still act as if a new member is being added through the registration module, when indeed that is not the case at all. Unfortunately, you cannot prevent the auto-increment number from being assigned unless you drop the entire table, change the table type, and import all the numbers back into it (this would be slower too).

Essentially what all this jargon means is this... the auto-incremented number becomes skewed when you add a new row with old data. This may sound relatively harmless, but it's actually about the most damaging thing you can do to any database since the entire basis IPB uses of searching through the forums for an ID record relies on that auto-incrementing value. So let's say (example) you have deleted a total of maybe, 7 members over time, yes? So then member number 455 would be on the auto-increment slot of 462 (the true amount of members you've ever had, plus 7 -- for those you have deleted). So then when you try to put, for example, 123 (the old ID presumably) manually into a new row, the auto-increment (again, it's hidden and cannot be altered) value might really be 732, or pick any number really, while it would be expecting and will *assume* the ID to be assigned to it will be given 725 (which your auto registration would have taken care of). Now the forum itself has no clue what changes were made outside of it's recounting and registration processes, and yes, it just keeps acting normally like you'd expect it to...

So at this point, you might be alright for a few days, weeks, months, maybe even a couple of years... until you try to mess with the user (and unknowingly, that ID number) by moving it to another group. The forum inevitably goes bonkers because it has no idea how to find it, and might get stuck in a loop and lock the table up. This is when the forum has had enough, at least for the time being, and invariably tells you to stick it.

Now, is this what really happened?

Honestly, I really have no idea. Maybe not at all, and then you can call me the idiot for rambling about this, haha. But if you ever *have* manually edited forum members and ID info in the past through PHPMyAdmin, it may have spurred this whole thing up, but just held it in hiatus for a while... if you get what I'm saying.

1) I found where you optimize in the invision admin panel - so thats nice

Awesome... everybody now... "optimization is my friend... optimization is my friend... optimization is my friend..."

2) Also, I found the IP of the referer spammer, and you were right, they were all from the same IP.  So I denied them.  - so hopefully that will stop that problem.

←

Excellent, did you set a global ban in .htaccess? That would probably be your best option. And be sure to set it from your root web directory so it covers everything underneath it.

And before I go, the PHP 5.0.4 integration is coming around, I'm really just trying to take every precaution prior to installing it full-throttle on your server. I'd almost like to experiment by running everything through PHP5 as .php5 extensions for now to ensure both modules can run together peacefully. Then migrating over. Now, if PHP5 was 100% backwards compatible I would have rolled it out months ago. Sheesh, stupid upstart developers.

j/k! ... take care

[Wijitmaker]

Me need sleepy... I'll have to reply tomorrow.

Quick comment though - yeah the auto incrimenting in our database is probably all scrwed up. Not just because of this one instance, but because when we merged our 3 forums into this one last October, we used this conversion tool that established a ID number for critical things (posts, topics, forums) in the secondary (to be merged forum) and added it to the highest number of the primary forum (merged into forum). Sloppy way to do it, but it was the only tool we could find.

So at this point, you might be alright for a few days, weeks, months, maybe even a couple of years... until you try to mess with the user (and unknowingly, that ID number) by moving it to another group. The forum inevitably goes bonkers because it has no idea how to find it, and might get stuck in a loop and lock the table up. This is when the forum has had enough for the time being, and invariably tells you to stick it.

Yep, pretty sure thats probably what did it.

*goes to bed*

[DarkAngelBGE]

I'm not sure what the double/triple/quadrupal... (7 was the highest I saw) was. Perhaps maybe because people were posting while the forum was optimizing?

Actually I am really sure that this has happened. When I ran into phpmyadmin when those errors occured I manually entered some SQL codes just for checking...they'd be run successfully although the errors would appear too, so it must have been that.

Additionally, I know where I posted how often when the errors occured - those posts were all in there. Deleted them now though.

Will use the optimization tool moreo ften.

[IME_Admin]

I'm not sure what the double/triple/quadrupal... (7 was the highest I saw) was. Perhaps maybe because people were posting while the forum was optimizing?

Actually I am really sure that this has happened. When I ran into phpmyadmin when those errors occured I manually entered some SQL codes just for checking...they'd be run successfully although the errors would appear too, so it must have been that.

Additionally, I know where I posted how often when the errors occured - those posts were all in there. Deleted them now though.

←

Hey Tim! What exact errors appeared in PHPMyAdmin? and what commands did you run? Also, what specific areas were these occuring in?

Just curious, maybe there is something else causing the double posts. Maybe topic and post ID's mixing or something from previous instances/sections of your forum prior to the merge. I know this hasn't become a major problem since all of your data is still here, but it's just one of those little things I'd like to figure out.

[Wijitmaker]

Ok, one other thing I'm curious about, how did you enter the two members (and their ID's) back into the database? In an existing row? Or a totally new row?

Well, after you ran the optimization (which allowed me to view the members data - before it wouldn't even allow me to do that because it was doing its loop thing) we had a look over the forums. There were a ton of extra posts (which I think was due to people posting while the database was being optimized) and those 2 members Paal_101 and Titus Ultor were completely 'gone' from the forum their posts were still there, but when you when to a thread where they posted you would only see the content of their post and to the left with all the user info/avatar/etc... there was nothing. So, I tried to do a member search for them through the forum. I tried both their member id and their name. Nothing. So, I thought I'd look in phpmyadmin to see if I could track them down there. I went to the members table (now freed up after optimization) and sorted the members by IDs. I knew what their ID should have been, so that wasn't a problem. I searched and I found that their row of data was gone. I also seached for their name, still nothing. I didn't check all 700+ entries, but I assumed that their row must have disappeared in the optimization. So, I pulled up a local copy of the database and inserted a new row with exactly the same information from the local to the web.

Thats what I did.

Their ID is the same, but, not sure about that hidden id your talking about. That likely is different.

Awesome... everybody now... "optimization is my friend... optimization is my friend... optimization is my friend..."

optimization is my friend... optimization is my friend... optimization is my friend...

Again, I cannot stress enough, MySQL optimization is your best friend. And not even having to do it from the command line should be cake.

K, we'll be sure to do that often

And before I go, the PHP 5.0.4 integration is coming around...

Ok, cool. FYI, I haven't ran across any error yet.

Just curious, maybe there is something else causing the double posts.

Well, whatever it was, it was only happened over a brief time - like over 2-3 min. After that, it stopped and hasn't happened since (to my knowledge)

[CodeOptimist]

Hmm - is it possible that some member entries are still 'out of whack'? See here -> http://www.wildfiregames.com/forum/index.php?showuser=788 (user 'Viveka Amaligg')

[IME_Admin]

Probably so. Here are the last 15 members sorted by default. It uses the exact order they were inserted and via the auto-incrementing number.

781 godlike

782 Satif

783 litwol

784 sauron

785 reebash

786 Vox

787 mebus

211 Paal_101

439 Titus Ultor

791 ToBB

792 yoop

793 NTICompass

795 proxann

796 yyz

797 kingroy1

Notice the bolded entries, they appear just after 787 (you were trying to query ID #788). Therefore, I'm assuming this is the cutoff ID where the members table locked up and became corrupted. Also notice these two entries are completely out-of-order, and non-conforming to the auto-incrementing value. This was a result of them being re-added manually afterwards. All of the values after these are new registrations since the corruption occured. Additionally, 794 does not exist, so I'm assuming this member was deleted recently.

Note: There are lots of other entries in the table that are also out-of-order, and this is mostly a result of the merging of the three member databases of all of the WFG forums. It indeed worked, but it was essentially like putting duct tape on a broken leg. Things will just take longer to query and to keep in order.

I do have a great idea though, and it's something which may help this entire situation. I have a little method I've used in the past to reorganize ID's into the correct order without sacrificing the auto-incrementing functionality. Basically it involves downloading the database table in CSV format, and sorting it via ID column while keeping the rest of the info intact. Then converting it to SQL code (insert, into, where, etc) and recreating the table after deletion. The newly sorted values will be aligned then when the re-creation occurs, therefore allowing the forum to search, query, etc through all of the values properly.

I'll probably give it a go tomorrow if Jason and Tim will allow.

[Wijitmaker]

It indeed worked, but it was essentially like putting duct tape on a broken leg.

Yeah pretty much. Don't forget the hammer too

I'll probably give it a go tomorrow if Jason and Tim will allow.

Yeah, that would be great, because we obviously have a problem.

[DarkAngelBGE]

Yeah, that would be great, because we obviously have a problem.

Amen to that.

@Matt: Well I simply entered a command into the posts table the way it would be executed by the forum software. And I got the exact same error as when I was posting on the forums. The data row was entered though.

[IME_Admin]

@Matt: Well I simply entered a command into the posts table the way it would be executed by the forum software. And I got the exact same error as when I was posting on the forums. The data row was entered though.

←

Yes, but the forum software would have assigned that user a new ID number rather than an old one. While manually, you could have assigned the user as ID number -82372 or 1000000000000 (or even "BOB" if it was a text capable column, haha!) if you really wanted to. Nevertheless, it works, it's just not entirely correct when it comes to the database index syncing with the flow of the forum software, i.e. keeping track of the right posts and ID's and log actions, etc.

I will extract and apply some corrections to the database this evening starting around 10:00 pm (CST), let's get online and coordinate this so you can enable maintenence mode temporarily while the members are restored into their correct slots. IM me or shoot me an e-mail

[Wijitmaker]

ok, I"ll turn on my AIM account