vBulletin Crashing Database each morning

[hilaryl]

Hi Everyone,

I'm going to try and be descriptive as possible, and hopefully get some help with a big issue we are having. We run a fairly big forum, 120,000+ users with over 7 million posts.

We currently are running vBulletin 4.1.2, and it's been running fine. Yesterday morning when trying to access the website, we got a database connection error. This essentially locked us out of accessing anything, when I try to log into phpmyadmin, I get the 'max-user-connections' message. So we had to get our host to restart the sql server.

After doing so, we were able to get back in to phpmyadmin, and could see that the 'searchcore_text' table was marked as crashed and needed to be repaired. The table is 3.6GB in size, so the repair took a very long time.

After repairing, the table no longer had the error on it, but the 'threadviews' table now was saying 'in use'. However there were no processes running on it - we thought it was a false positive.

After coming back in this morning, the website was saying database connection error again and our host is repairing the table again I believe.

Both times, the site seemed to go down around the 4am area.

I'm not sure what our best course of action is, the table is repairing, and saying it's fixed, but then we get database connection error again.

It's almost as if something is running at a specific time in the morning that locks a table, and then there's a massive build of queries trying to access that table, and that causes the connections to become overloaded and everything just shuts down.

What kind of query could be causing this though?

Please let me know what other information you need, we really need to get this sorted.

Thanks,
hilaryl

[Zachery]

First, you should upgrade to 4.2.2
Second, you might want to move to new hardware, constant crashing is a bad sign for a server.
Third, you should invest in vBulletin sphinx from digitalpoint, or another vendor. The sphinx search system is 1000x better than what we're able to offer natively.

[hilaryl]

Hi Zachery,

We are actually on 4.1.9 Patch Level 9 (I was incorrect earlier) - I don't imagine that makes things any better though.

We don't believe it's the hardware, because it's only started happening yesterday - and then again this morning. And there weren't any big changes made the day before the big crash.

I will talk to my host about vBulletin sphinx - but right now, we just need things going again.

We have managed to repair the tables and everything seems to be working now. With the exception of a glitch in certain threads where you can't get to the last page. One example being there are 12 pages, and even if you click on page 12, it only shows you the content of page 11. It even says page 12 in the URL.

I've read that this can fixed by Rebuilding Thread Information. Though there are 420,000 threads - should this be done with the forum offline?

Are there any other steps I should take after repairing database tables to kind of refresh the forum? Anything to do with the System Cache perhaps?

Thanks,
hilaryl

[Zachery]

You should really upgrade to 4.2.2.

[ForceHSS]

If you have all security updates there is really no need to update unless the version you have is causing the crashes. Check server error logs and post them here

[ozzy47]

There is security fixes in 4.2.2 pl2 that is not in earlier versions, that is why it is advised to upgrade. :)

[hilaryl]

Hi ForceHSS,

We do have the latest security updates - which is why we don't believe it's the version causing the issue.

In regards to the server error logs, I'll have a look through and see if there is anything worth posting.

My question before amount maintenance after a table repair, is there any advice anyone has for that?

Thanks,
hilaryl

[AndrewSimm]

Is it crashing around the time you have a scheduled backup?

[ForceHSS]

Hi ForceHSS,
In regards to the server error logs, I'll have a look through and see if there is anything worth posting.

Thanks,
hilaryl
If anything is in the server error logs even if you think its not worth posting post it as it might help

[hilaryl]

Is it crashing around the time you have a scheduled backup?

Actually yes, the back-up happens around the same time.

My host has moved the scheduled backup to an earlier time for tonight - seeing if that makes a difference.

Do you know of an issue that relates to the backup?

[Zachery]

You should still upgrade to 4.2.2 for both security and bug fixes.

[hilaryl]

You should still upgrade to 4.2.2 for both security and bug fixes.

I agree we should upgrade to 4.2.2 - but I would prefer to upgrade an error free version of vBulletin. I think upgrading while having potential corruption issues would just cause more trouble then it would be worth.

It is definitely something we will look at after getting over this obstacle though.

[Marv]

Actually yes, the back-up happens around the same time.

My host has moved the scheduled backup to an earlier time for tonight - seeing if that makes a difference.

Do you know of an issue that relates to the backup?

Yes, we had something similiar. We found out that our ftp/server-repository had no space left and the backup task was setup to lock the tables and site until the backup was complete. Esp. the last point locked us out, flagged the site as inactive and we had to set the status back to active (deleting the unfinished backup task and restarting the server) via ssl first. Because there was no space left on the repository device (misconfiguration 6 yrs ago ;) ) we had to alter the partition sizes used.

[hilaryl]

Yes, we had something similiar. We found out that our ftp/server-repository had no space left and the backup task was setup to lock the tables and site until the backup was complete.

Hi Marv,

Some great information there. Really appreciate it.

I have been speaking with my host and we are going to try a few things to hopefully isolate the cause of the crashes.

I'll come back with any new information I find.

Cheers!

[AndrewSimm]

cPanel doesn't do a good job with large databases. Currently I use R1Soft CDP backup with the mysql extension and have no issues. I haven't used it to restore from any issues but I would like a more traditional backup option myself. It would be interesting to see whatever everyone else is using.

[cellarius]

Always a good recommendation for backup: http://www.mysqldumper.net/

Also works if you don't have root access.

[Marv]

Always a good recommendation for backup: http://www.mysqldumper.net/

Also works if you don't have root access.

MSQLD is a great tool. Unfortunal, MySQLDump wasn?t really helpful in our case. We had to repair some tables and a few were about 1.6 to 2.4GB. MSQLD couldn?t write this table back to the database, just because of the sheer amount of data. In the end we had to do this via the console. Any known workaround for MSQLD and big tables?

[cellarius]

mysqldump is not the same as mysqldumper ;)

[Marv]

Erm..I was just lazy and have shortend MySQLDumper to MySQLDump. Guess we?re still talking about the same thing, Sven ;)

[Mark.B]

I agree we should upgrade to 4.2.2 - but I would prefer to upgrade an error free version of vBulletin. I think upgrading while having potential corruption issues would just cause more trouble then it would be worth.

It is definitely something we will look at after getting over this obstacle though.
Sometimes such issues may be caused by bugs in earlier versions.

At the risk of sounding like a stuck record - you really need to upgrade.
It's not just about bugs, your site is *definitely* insecure at the moment due to unpatched security issues. To patch those you need to upgrade.

[hilaryl]

It's not just about bugs, your site is *definitely* insecure at the moment due to unpatched security issues. To patch those you need to upgrade.

Is this the case even if we have the latest patch's for our version?

[CAG CheechDogg]

I had this happen to me a while back, about a year or so ago maybe a bit longer and the issue was search bots hitting our site like fire ants .... the main ones were:

AhrefsBot
Baiduspider
Ezooms
MJ12bot
YandexBot

I used Ban Spiders by User Agent (https://vborg.vbsupport.ru/showthread.php?t=268208) to help with this issue ... now these bots and others don't hit the search feature which is something others around here will say doesn't happen with bots ...

If it worked for me I am sure it will work for you... there is also other things you can do to limit and reduce the amount of hits on your search feature.... you also want to check your mods to see if they are updated .. some modes can attract unwanted activity and actions on your forums ....

And about upgrading .... if you are careful with your site, forums and stay on top of security and suspicious behaviour you don't need to upgrade at all ... I don't mean to insult any of the previous individuals who say you should upgrade but it is not necessary to do so at all ....

[Lynne]

Is this the case even if we have the latest patch's for our version?
Yes. Only 4.2.2 and 4.2.3 have the latest security fixes. http://www.vbulletin.com/forum/forum/vbulletin-announcements/vbulletin-announcements_aa/4183258-security-exploit-fixed-in-vbulletin-4-2-2-and-4-2-3

[TheLastSuperman]

Limited space as someone said above and on rare occasions it could be related to spam, check the session table for an insane amount of entries (30k+ records) because once it hits around 30k records the session table is nothing but trouble and you need to truncate the table.

I'm pretty sure the disk space is the factor as Marv mentioned, tracing down the issue before attempting a fix is the best way to go about this though. As the others said upgrade to 4.2.2 in the least as 4.2.3 is still marked as Beta.

[tbworld]

And about upgrading .... if you are careful with your site, forums and stay on top of security and suspicious behaviour you don't need to upgrade at all ... I don't mean to insult any of the previous individuals who say you should upgrade but it is not necessary to do so at all ....

I have looked at the latest patches to vBulletin and although I have not tried to exploit the possible vulnerabilities, it does seem prudent to make the changes. When vBulletin releases their patched distribution, script kiddies get to work and try to exploit older boards that have not upgraded. Granted most of the script kiddies aren't the brightest, but it only takes one to release an attacking script.

Everything else I agree with you. Your still the man. :)

[CAG CheechDogg]

I have looked at the latest patches to vBulletin and although I have not tried to exploit the possible vulnerabilities, it does seem prudent to make the changes. When vBulletin releases their patched distribution, script kiddies get to work and try to exploit older boards that have not upgraded. Granted most of the script kiddies aren't the brightest, but it only takes one to release an attacking script.

Everything else I agree with you. Your still the man. :)

I hear you tbworld ... thanks for the little props lol ... And I was thinking after my post and for those who aren't as active and "savvy" maybe as most of us it would be best for them to update or upgraded to the latest version with patches ...

It was not at all smart or considerate of myself to say that upgrading is not necessary and I apologize to everyone ... because we should all upgrade and install the patches regardless of our experience to "avoid" headaches later on ...

So I am really truly sorry you guys ...

[tbworld]

You are being too hard on yourself. :) I think in general you were referring to upgrades not vulnerability patches. In this case I just happened to be snooping at the changes they made in the code from v4.2.2 to v4.2.3. I'm a big snoop! :)

You do way more good around here then any slight miswording error. I have to retreive my own foot out of my mouth at least once a day. I was trying to inform, not cast judgement, I am sorry if it felt that way.

[RichieBoy67]

I second that.

Don't worry about Cheech!

[hilaryl]

Hi everyone,

First of all - thanks for all your feedback and suggestions. It has definitely helped to isolate where we should be looking, and what we should be looking for.

After some trial and error testing, we did find that during our nightly database back-ups, we were running out of space on the server. This caused somewhat of a chain effect which was congesting our mysql database, effectively shutting it down. Somewhere in that chain, the 'searchcore_text' table was trying to be optimised, and because of a lack of space, this couldn't complete and left the table corrupt.

We will obviously need to upgrade the space on our server - which is our next step.

The 'searchcore_text' was a contributor to our issue, but only because of the sheer size of the table. We will potential have to look at making this smaller without jeopardising the search function of our forum.

And then look at upgrading to the latest vBulletin.

Thanks again,
hilaryl

[Marv]

Hi everyone,

First of all - thanks for all your feedback and suggestions. It has definitely helped to isolate where we should be looking, and what we should be looking for.

After some trial and error testing, we did find that during our nightly database back-ups, we were running out of space on the server. This caused somewhat of a chain effect which was congesting our mysql database, effectively shutting it down. Somewhere in that chain, the 'searchcore_text' table was trying to be optimised, and because of a lack of space, this couldn't complete and left the table corrupt.

We will obviously need to upgrade the space on our server - which is our next step.

The 'searchcore_text' was a contributor to our issue, but only because of the sheer size of the table. We will potential have to look at making this smaller without jeopardising the search function of our forum.

And then look at upgrading to the latest vBulletin.

Thanks again,
hilaryl

Good to hear you were able to figure that out. We had ourselfs a hard night until we stumbled over the disk quota issue, when we run into that. Nice to get a feedback that we could help you out getting back in the tracks.

[Zachery]

Get sphinx, and you can empty that entire table.