[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]

Titus Ultor, on Apr 11 2005, 02:52 AM, said:

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.

This post has been edited by Argalius: 11 April 2005 - 03:07 PM

[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

This post has been edited by Lorian: 11 April 2005 - 05:56 PM

[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]

Centurion_13, on Apr 13 2005, 07:29 PM, said:

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:

Quote

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]

Wijitmaker, on Apr 13 2005, 08:20 PM, said:

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.

Wijitmaker, on Apr 13 2005, 08:20 PM, said:

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..."

Wijitmaker, on Apr 13 2005, 08:20 PM, said:

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.

Quote

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]

Quote

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]

DarkAngelBGE, on Apr 14 2005, 09:30 AM, said:

Quote

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]

Quote

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.

Quote

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...

Quote

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

Quote

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

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

Quote

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.wildfireg...hp?showuser=788 (user 'Viveka Amaligg')