Database error emails

[seannnr]

I'm getting a ton of emails each day, and they all say this:

Database error in vBulletin :

Link-ID == false, connect failed
mysql error:

mysql error number: 0

I heard on another forum that it means the MySQL server is crashing IIRC. What is this and is there anything I can do to fix the problem?

Thanks,
Sean Niner

[ultranerds]

Hi,

I had a similar problem before. Are you using php accelerator? That was the main problem on our install.

Also, just general high traffic can cause MySQL to crash (we are getting 100,000 page views a day.. so the load used to be very high).

Hope that helps.

Cheers

[Steve Machol]

This error means that either the info in your config.php file is wrong (in which case your forums wouldn't work at all) or there is a problem with the MySQL server. If you are sure that the db info is correct then you need to contact your host.

[Si-fer]

i've post about this also...looks like im not the only one with that problem...

i have 8 email today alone saying that exact same thing...

Database error in vBulletin :

Link-ID == false, connect failed
mysql error:

mysql error number: 0

[Steve Machol]

Same answer as in post #3.

[Si-fer]

oh the database info it correct, i can asure you that...cause i only have this problem today.
and if it's not correct i wouldn't be able to get into the forum at all. but i can get into the forum and post so it is correct...

[Steve Machol]

Then contact your host. It's a server issue.

[Si-fer]

opps i forgot to mention, that's the first thing i did...and they say there is nothing wrong with the server....:confused: :confused:

[Steve Machol]

I don't know what else to tell you. It's either the db info in config.php or the server.

[soopa]

I don't know what else to tell you. It's either the db info in config.php or the server.

While logic would suggest that to be true...

I too have noticed an increase in this type of message, and we run our own servers.

I'm investigating, and I find it of unlikely coincidence that so many here have begun experiencing this problem quite randomly.


In my case, it is possible that the latest PHP upgrade has something to do with it. These problems begain occuring slightly after that time.

I will find the problem. In the meantime, I feel there should be a code assessment. I'll say it again, it is unlikely this is by coincidence.

[soopa]

I'm noticing that 90% of the errors are from the cron.php...

Any speculation as to why this file in particular would have difficulty connecting?

[soopa]

Turning off persistent connections appears to have resolved the problem...

However, what's unclear is why this problem began happening randomly and with increasing frequency as of late.


Common response in this forum from staff seems to be "disable pconnects"


Surely we have a better solution?


Persistant connections can really save me a ton of resources seeing as my DB is on a seperate box from my VB.

[Steve Machol]

Note, many servers are being probed for the phpBB worm these days and the MySQL server is being overloaded. This could also account for these errors.

[soopa]

My mySQL server is not being overloaded. I'll post our graphics shortly...

We peak at under 100 SQL connections whereas we have the overhead for 1000+


Anyway, changing persistent connections did nothing. I spoke too soon...

vB had just filled up the mail queue with err messages ;)

[soopa]

over 30,000 plus e-mails like this today.......... mysql connections never broke 10%.

[Si-fer]

mine stoped, i haven't recieve anymore of these error after my host upgrade their server and PHP version...

so im assuming it's the server. and the new web worm going around...

[soopa]

mine stoped, i haven't recieve anymore of these error after my host upgrade their server and PHP version...

so im assuming it's the server. and the new web worm going around...

What version of PHP are you on? We are on 5.0.3.

This started happening slightly after our upgrade. Everyong *looks* to be correct from the PHP configuration.

[macrumors]

I'm getting the same problems...

tons of these emails a day.... mysql is not restarting. may have to do with recent cpanel upgrades... that's all I can think of.

If anyone has any ideas... I'd like to hear 'em.

arn

[Steve Machol]

See post #3.

[wfp]

over 30,000 plus e-mails like this today.......... mysql connections never broke 10%.

only 30,000? Last night I got 55,000 with the same error as you:confused:

[macrumors]

See post #3.

Yep Steve, saw post #3. not really helpful.... trying to figure out what's wrong. it's obviously not the config file since it's nothing's been changed and it works for most of the day.

Seems this is a new problem that has appeared for several of us. clearly something has changed in the past few weeks that is causing this. whether it's related to vb3.0.x or a php upgrade or other server software upgrade (such as cpanel) is what we're trying to figure out.

arn

[Steve Machol]

Then this is a server issue as I stated. Note, many servers are being probed for the phpBB worm these days and the MySQL server is being overloaded. This could account for these errors.

[sross]

i'm having the same or similar issue, my server usually runs at 0.7 to 1.2 usage, and today it went nuts and was hitting over 12.0, current everything is turned off. I guess al I can do it hope it's some kind of worm that will go away soon??

To those who have been 'wormed' what did you do? How long does it last? Any info is appreciated..

[eliasp]

Same problem here. Very frustrating. Hopefully someone can figure out the issue. Our site goes off about 3 times a day for minutes at a time, usually 10-40 minutes. Definately not the config file.

[sross]

Well, i finally tracked down what my issue was. BOTS, these damned search engine bots. I'm not talking about google and yahoo because I need those guys to drive my traffic, but these other engines out there (100's of them) that are spidering my forums were killing me. The 2 biggest lately were MSNBot and Ask Jeeves. So.. for now just to keep my isp happy I have blocked most bots via robots.txt after doing this my server usage went from hitting 12.50+ to 0.40 everyone is happy now. One question is.. Is this normal for bots to nail performance so hard? Should I force bots to only use my archive pages to help things?

[macrumors]

ok, here's an experiment...

maybe it is indirectly related to bots... but my latest thought is it may simply related to the unserialize bug (causes a performance hit) in php 4.3.10 (mentioned in this related thread link (http://www.vbulletin.com/forum/showthread.php?t=125587&page=2)) causing a dramatic increase in server load than we're used to.

I've installed the vbulletin.org hack to get around it (link (http://www.vbulletin.org/forum/showthread.php?t=75032)).

And we'll see if that helps... the following attachment is a graph of my MRTG stats... note the drop-outs... that's when the server has crashed/gone down over the past few days. I hope to you show u a solid graph tomorrow night.

arn

[Banana]

the mails contain an IP address (or 3 or 4 different one). Add them to your .htaccess

Problem solved. Well it was for me.

[eliasp]

I changed *.php files to chmod 644 and also updated a robots.txt file. So far so good. We recently changed webhosts and for some reason the chmod settings changed to readable and executable. Now it's back to read only like we had them before.

[macrumors]

well, the hack didn't fix things for me. still crashing.

What did you update your robots file with?

arn

[eliasp]

User-agent: *
Disallow: /forums/attachment.php
Disallow: /forums/avatar.php
Disallow: /forums/editpost.php
Disallow: /forums/member.php
Disallow: /forums/member2.php
Disallow: /forums/misc.php
Disallow: /forums/moderator.php
Disallow: /forums/newreply.php
Disallow: /forums/newthread.php
Disallow: /forums/online.php
Disallow: /forums/poll.php
Disallow: /forums/postings.php
Disallow: /forums/printthread.php
Disallow: /forums/private.php
Disallow: /forums/private2.php
Disallow: /forums/report.php
Disallow: /forums/search.php
Disallow: /forums/sendtofriend.php
Disallow: /forums/threadrate.php
Disallow: /forums/usercp.php
Disallow: /forums/admin/
Disallow: /forums/images/
Disallow: /forums/mod/
Disallow: /PHPlist/
Disallow: /develop/
Disallow: /cgi-bin/

I also went into every folder in vbulletin and changed all the files to read only (chmod 644). Before they were read and executable for some reason.

I don't want to jinx ourselves but I'm hoping that this is what resolved our problems. No more database problems.

[macrumors]

User-agent: *


heh.... not being selective I see. I guess aggressive bots could account for it all. Some - such as Google seem important for future indexing purposes.

arn

[GOAZCATS.com]

I am having the same problem and it started soon after I upgraded the 3.06. Bottom line, I agree with others. It seems more than coincidental that everyone is having this problem and the explanations don't seem right. So far I have seen the following:

1. Contact your host

2. There is a phpbb worm something or other going around and could cause for slow downs

3. Robots

Well, I could be wrong but really tend to think it is something with VBulletin or the new version of PHP. I don't know anything about this kind of stuff other than I have a board that worked fine and then I upgraded the version and I continually get database errors. I contacted the host and they said nothing has changed whatsoever. I log on here and see that many people are having the same problem and that VBulletin refuses to even acknowledge that it may not be a coincidence and at the very least investigate it.

Not really worth it to have a message board and buy VBulletin if no one can run the darn thing because of database errors. It would be very effective for VB to take a look and find out what the problem is and fix it. Too many people complaining on too many different hosts to be coincidental.

[Steve Machol]

I honestly really don't believe this is a vB 3.0.6 issue. Did you happen to notice the the release of the phpBB worm was at around the same time as 3.0.6? Nonetheless if you don't want to believe this then that's your right.

If you really think there is something wrong with 3.0.6, then post it in the Bug Tracker.

[macrumors]

I honestly really don't believe this is a vB 3.0.6 issue. Did you happen to notice the the release of the phpBB worm was at around the same time as 3.0.6? Nonetheless if you don't want to believe this then that's your right.

If you really think there is something wrong with 3.0.6, then post it in the Bug Tracker.

I don't think 3.0.6 is specifically related to it.

I _do_ think something has happened in the past 1-2 weeks however.... whether it is another software component that has been upgraded in many places (such as mysql, php etc..) or it is an external cause (bots, php probes etc...).... it's unclear as of yet.

arn

[aero1405]

I'm also pretty sure it has to do with phpbb worm because i have also received some error mails and after contacting my host the told me they were experiencing some issues with MySQL and that they were working on a solution to it.:cool:

[venomz]

well guess what im having the same error here also.....AND I JUST BOUGHT A DAMN VB3 yesterday and i get it today...what the hell is going on man.? IM REAL mad i want my money back if this **** continues cuz ima be real pissed lol.

[Steve Machol]

If you are mad, then the best place to address this anger is at your host since they are the only ones that can do anything about this. :)

[cmeinck]

I vented my anger at my host a few weeks back. They informed me they were upgrading the database I was on and the only suggestion was to move to another. Well, I moved to a new host and guess what...I've got the same issues (and then some). Seems they are rebooting the servers my db's are on to resolve these problems. Maybe this is a widespread problem that's not VB based.

[mcmahoniacs]

We have been having problems over the last couple of weeks too. Host claims its a software problem.

Vbulletin claims its a host problem. Confused.

Config.php is okay and the last Ip that generated a swamp of error messages was a member, although a bot was around the time the messages were generated.

Seriously there is not some worm around other than phpbb targeting php or vbulletin forums is there, overloading them.

[Behrooz_Ice]

Hello
I Have Same This Problem. Today i have seen ton eror e-mails like:
==========================
Database error in vBulletin :

Link-ID == false, connect failed
mysql error:

mysql error number: 0

Date: Wednesday 13th of April 2005 03:37:25 PM
Script: http://www.mysite.com/forums/search.php?do=512 (http://www.mysite.com/forums/search.php?do=512)
Referer: http://www.hackersite.net (http://www.hackersite.net/)
IP Address: 80.58.4.46
===========================

in Most Script Requested search.php file. also Referer these e-mail are same of them but ip adress are different. hosting server did suspend my site because mysql server did was crashed by our forum. 300 online user were online in my forum :confused: my vbulletin version is 3.0.3
plz tell me how we can solve this big problem. now our forums folder is chmod 0 until this problem will Fix. sorry for poor english. thanks.

[Steve Machol]

This error means that either:

1) The db info in your config.php file is wrong (in which case your forums won't work at all.)

2) That user doesn't have permission to access the database listed in config.php.

3) The database doesn't exist.

4) MySQL isn't accepting new connections. (The server could either be down or has reached it's maximum connections limit.)

If you are sure your config.php info is correct, then you will need to contact your host about this.

Note, many servers are being probed for the phpBB worm these days and the MySQL server is being overloaded. This could account for these errors.

[Behrooz_Ice]

Hello Dear steve , Thank You for Reply. But i sure my config.php file is correct because my forum is around 1 year Up without any problem. i did contact to my server host and they said this problem is for VBulletin. because that hosting server is powerfull and about 200 web sites are host on that. but only my vbulletin site in this hosting server have this problem and do crash mysql server. plz help me.
This error means that either:

1) The db info in your config.php file is wrong (in which case your forums won't work at all.)

2) That user doesn't have permission to access the database listed in config.php.

3) The database doesn't exist.

4) MySQL isn't accepting new connections. (The server could either be down or has reached it's maximum connections limit.)

If you are sure your config.php info is correct, then you will need to contact your host about this.

Note, many servers are being probed for the phpBB worm these days and the MySQL server is being overloaded. This could account for these errors.

[Behrooz_Ice]

Dear Steve for More Information plz See the Attachments. in these attachments files have been 3 pictures of my online user forum < online.php >
i think its for reason a worm or RoBot which sending many request to vbulletin forums. i'm waiting for your answer.

[Steve Machol]

i think its for reason a worm or RoBot which sending many request to vbulletin forums. i'm waiting for your answer.Yes it certainly could be that. In fact that is one of the possibiities I mentioned.

[Behrooz_Ice]

How to Block These bad Request and Solve this Problem? my forum is down now.
Yes it certainly could be that. In fact that is one of the possibiities I mentioned.

[Gholsie]

I just got 300 of these messages last night. My forum is still functioning with no problems. *I* am my host so I must contact myself. The configuration is fine. I did not do any upgrades and the system has been running great since my last upgrade 4 weeks ago. Now, I need to determine how to stop these emails.

The emails seem to all be coming from the same domain as listed in the errors. I have two types of errors coming through:


vBulletin (VB_AREA) Database error!
Link-ID == false, connect failed

mysql error:

mysql error number: 0

Date: Thursday 14th of April 2005 08:08:38 AM

Script: http://www.classiczcars.com/index.php (http://www.classiczcars.com/index.php)

Referer: http://firsthorizonmtg.com (http://firsthorizonmtg.com/)

IP Address: 161.106.225.93


and


vBulletin (Forum) Database error!
Database error in vBulletin :

Link-ID == false, connect failed

mysql error:

mysql error number: 0

Date: Thursday 14th of April 2005 08:08:38 AM

Script: http://www.classiczcars.com/forums/member.php?u=1769 (http://www.classiczcars.com/forums/member.php?u=1769)

Referer: http://www.classiczcars.com/forums/showthread.php?t=15911 (http://www.classiczcars.com/forums/showthread.php?t=15911)

IP Address: 195.68.26.2



It seems that we all need to try a few more things to get this to stop. I just modified my ROBOTS.TXT file. I also wanted to add some kind of ignore to my .htaccess but I don't know the syntax... Can someone help me with the syntax for this ?

PS: The messages flooded into my server at about 6:15am to 6:20am and just stopped. The system is working fine right now.

[BIGMONAY2K]

me to
Database error in vBulletin :

Link-ID == false, connect failed
mysql error:

mysql error number: 0

Date: Thursday 14th of April 2005 04:19:21 AM
Script: http://dafirm.net/forums/forumdisplay.php?s=&daysprune=-1&f=56
Referer: http://dafirm.net/forums/forumdisplay.php?s=&daysprune=&f=51
IP Address: 4.229.108.325

[Gholsie]

This really bites... Today I have received over 1,500 emails and it has brought my system to it's knees. I see in the error message that all of these referrals are coming from firsthorizonmtg.com . Is there any way I can stop the referrals... ? The IP addresses are all different so it might not be as easy to just put something in my .htaccess file.

I appreciate the help of the admin here. But, pointing out the cause really doesn't help. I could use some help determining how to FIX the problem. Any ideas?


Date: Thursday 14th of April 2005 09:31:03 PM

Script: http://www.classiczcars.com/index.php (http://www.classiczcars.com/index.php)

Referer: http://firsthorizonmtg.com (http://firsthorizonmtg.com/)

IP Address: 12.37.93.160


Isn't there a way to disable vBulletin emails and only display the errors to the client side? I'd like to do this until this is fixed.

[Steve Machol]

There is an option:

Admin CP -> vBulletin Options -> Error Handling & Logging ->
Disable Database Error Email Sending

However:

Error reports about database connection errors will still be sent.
So I guess the only other option is to edit config.php and remove the email addy from this line:

$technicalemail = '';

Note, by doing this you will miss other error emails that may indicate a serious problem with the database.

[BigCheeze]

I have been getting these too as of lately. I belive it's either a worm, or some other forum of attack. It ended up putting a big enough load on my site that I had to recycle Apache & mySQL to get the site back.

[krohnathlonman]

The most common reason for seeing a message like this (when your config file IS right) is when bots like yahoo + other search bots come through and nail you at once and eat up all of your MySQL connections. google for some example robots.txt files to find some good lists of some bots to block. If you have the CPU power go ahead and increase the connection number in my.cnf

[dwh]

http://dev.mysql.com/doc/mysql/en/old-client.html (http://dev.mysql.com/doc/mysql/en/old-client.html)

I think the cause of all this is totally different. It's about MySQL upgrade. They changed the way their passwords work and if your client mysql version is different than your server version it can happen. You need to use Old Style passwords. And those using cpanel are likely to run into this problem. You may not even realize that you are running a client mysql to connect to the mysql server...

Cpanel has a setting to fix this issue under the security settings. It refers to old style passwords. Check it and then click on the link to rebuild mysql.

[mykes]

I have been getting these errors recently, as well.

Post #3 is irrelevent. The server and board has been running for months before the errors started happening.

I am the admin of the server, so my hosting company has nothing to do with the problem.

mysql and apache are running on the same box, and mysql is set up to only listen on 127.0.0.1, so it can't be people connecting from some other machine.

I built apache, php, mysql, etc., from sources. They are not some canned installation.

I do have a bit of info to add to help people figure out what the problem is.

In /etc/my.cnf, I have:
max_connections = 450

In httpd.conf, I have:
MaxClients 250

Seems to me there should be 200 more mysql clients available than there are httpd children possible. I believe those 200 more mysql clients should be able to handle whatever cron tasks are requiring, as well.

Normal server load is < 1.00. When the problem happens, the server load is at 10 or more!

The site is under some sort of attack. I see multiple hits on the home page within a second of eachother from the same host. 100 times in a row or more. This is pretty constant, and the site runs fine with this kind of attack going on. At least 99.9% of the time.

Traffic to the site has grown by 2x since we upgraded from vb2 to vb3 and moved to this new server in mid-february. As I wrote earlier, the site has been running FINE since then, except for these occaisional error e-mails.

I wrote this nifty perl script that works like top, but with additional features. Once per second, it shows me:

Load Average, processes, processes running
CPU states (user/sys/nice/idle)
Memory usage (total, used, free, shared, buffer, cache, avail)
Swap (though I have no swap enabled on the server, so it shows 0)
Disk activity:
> disk I/O for each disk
> disk_rblk for each disk
> disk_rio for each disk
> disk_wblk for each disk
> disk_wio for each disk
Virtual Memory:
> page in
> page out

I run this tool in a shell window 24/7. I caught the server misbehaving and watched the output of the "top" program. It showed one of the 4 CPUs (it's dual CPU, but acts like 4 because of hyperthreading) using 100% of the CPU, the process count at 3x normal (and growing), no disk activity whatsoever, no paging going on.

So some process was hogging the entire machine. Seems like a table gets locked in MySQL and all the rest of the processes have to wait for that one task to finish.

During this whole time, the site is unresponsive. Click reload in the browser and it just hangs, waiting, waiting, waiting... I'm sure the process count grows because my users/posters are hitting reload all at the same time, too.

All of a sudden, this mysterious process finishes, and the machine catches up. I see # of processes go from 450+ back down to < 150 over time. I see the running # of processes as a really big number - like 30 or 50 - while it is catching up. This is what I expect.

AND, I get a flood of database connect error e-mails in my inbox.

For those of you interested, I've attached a png of a shell window running this perl script.

[dwh]

Mike I think you have a different problem then some other people. But I have a question for you. Go into phpMyAdmin. What version of mysql does it say is installed? Then go into phpinfo. What version of mysql is installed there?


As for that one process, it sounds like one sql statement is bad. When this is happening do a mysql processes to see if you have a bunch of locked mysql statements. But even better, turn on slow query logging. Wait a few hours. Then check the log. It will tell you which query(s) is causing the problem.

[mykes]

Mike I think you have a different problem then some other people. But I have a question for you. Go into phpMyAdmin. What version of mysql does it say is installed? Then go into phpinfo. What version of mysql is installed there?


As for that one process, it sounds like one sql statement is bad. When this is happening do a mysql processes to see if you have a bunch of locked mysql statements. But even better, turn on slow query logging. Wait a few hours. Then check the log. It will tell you which query(s) is causing the problem.

php 4.3.10 - I know 4.3.11 is out, I'll do an upgrade
mysql Ver 12.22 Distrib 4.0.23, for pc-linux (i686)

I believe the problem is exactly what other people are describing, they just don't have the tools I do to see what's going on...

[dwh]

php 4.3.10 - I know 4.3.11 is out, I'll do an upgrade
mysql Ver 12.22 Distrib 4.0.23, for pc-linux (i686)

I believe the problem is exactly what other people are describing, they just don't have the tools I do to see what's going on...

The upgrade is important. I believe 4.3.10 is vulnerable to an exploit somewhere. I just read that yesterday I think.

But I didn't explain properly. In phpinfo scroll down to mysql section and there it will tell you the version of mysql client, that is compiled w/ php.

[mykes]

The upgrade is important. I believe 4.3.10 is vulnerable to an exploit somewhere. I just read that yesterday I think.

But I didn't explain properly. In phpinfo scroll down to mysql section and there it will tell you the version of mysql client, that is compiled w/ php.

I don't use phpMyAdmin (isn't installed, nor do I want it).

The mysql client libraries are the same ones built when I built mysql and php.

What exactly are you looking for?

[dwh]

It's happened to me that I expected both the client and server mysql versions to be the same and was shocked when it turned out that phpinfo was reporting a different version than phpmyadmin (although you can use any tool you want). In fact, I didn't understand why there was even a client and a server version on the same machine. I think what it was that cpanel compiles an extra version of php and had some weird compatibility issue.

BTW, is it that you don't like phpmyadmin or you happen to use another tool?

[mykes]

It's happened to me that I expected both the client and server mysql versions to be the same and was shocked when it turned out that phpinfo was reporting a different version than phpmyadmin (although you can use any tool you want). In fact, I didn't understand why there was even a client and a server version on the same machine. I think what it was that cpanel compiles an extra version of php and had some weird compatibility issue.

BTW, is it that you don't like phpmyadmin or you happen to use another tool?

when you build mysql from sources, it builds both the client and server libraries. Since I'm running mysql on the same server as apache and php, the proper client/server libraries are being used. If you compile mysql on a dedicated db server and then compile mysql on some www host, you can get a mismatch, no doubt. I don't think that would cause a real problem, though.

I'm a security freak. I don't want any www pages accessible that could cause some real damage to the site. admincp and modcp are bad enough ;-)

I do everything from the command line via ssh. Even ssh isn't 100% secure, but it's the best I can come up with. The only things I have listening on any ports are sendmail, httpd, and ssh.

So, from the command line, I use tools like mysqladmin, mysql command-line query program, etc. I pipe the output into other programs like grep and less to see various reports I care about.

And this perl script I wrote is simply amazing. I can tell by looking at the output for about 5 seconds if the server is CPU bound or memory bound or disk bound, etc. If I tweak various server paramters (my.cnf, httpd.conf, etc.), I can see the results with the tool.

If you look at the .png I posted, it shows the machine is hardly going to disk at all, and has about 60% of the RAM used for caching the filesystem (to boost MySQL performance). The CPUs are rarely below 66% idle, so the machine isn't CPU bound.

The site I run has 350-450 users online much of the time, and has well over 2M posts. I notice in other threads on this board that others with similar sized sites are running them on multi-server configurations, but I doubt those sites run any faster than basketballboards.net.

I would love to add a graphics server and move all the graphics to it. Then I would set up httpd so it didn't do keep alives at all. I bet the performance would increase by 300%... Even if the site is doing 15 page views per second, I can't imaging why it would need more than, say, 30 httpd children processes. Right now, it's using about 150 total processes, and that's because there's many httpd children waiting for browsers to end their connections (i.e. no more get requests for graphic items).

I am absolutely positive my problems are the same as everyone else's. The site runs like a top for hours or days at a time, then this spurrious condition that causes the error e-mails. I have to be in front of the computer to see it happen; next time I'll look at mytop and see if there's some strange query locking things up.

The fact that the server "catches up" and reverts to normal operation is also similarly indicated by others in this thread...

regards,

[Typ01]

I hardly think all of the locked tables and eons taken to sort are due to a bot or worm probe.

We did one thing to bring this about it seems....turned off avatars and sigs for a short time. Did anyone else do this as well and suffer this connection problem as a result?

[Steve Machol]

We did one thing to bring this about it seems....turned off avatars and sigs for a short time. Did anyone else do this as well and suffer this connection problem as a result?When you do this you also reduce the nunebr of MySQL connections.

[mykes]

As a suggestion, people may want to look at their apache error_log files and see if there's anything strange in there.

[NetRover]

I get the same frequent errors also!

I sought advice from my ISP and they said it is when the server / forum is getting spammed.

[Steve Machol]

I sought advice from my ISP and they said it is when the server / forum is getting spammed.That's pretty vague of them. It would seem they should know what's happening on their server.

However having said that the one time when this 'false, connect failed' issue became a problem on my forum is when it was being inundated with incoming spam email. The server had so much trouble keeping up that even MySQL connections were faling. I sent had the host put in some spam guards and I haven't had this issue since.

[Typ01]

That's pretty vague of them. It would seem they should know what's happening on their server.

However having said that the one time when this 'false, connect failed' issue became a problem on my forum is when it was being inundated with incoming spam email. The server had so much trouble keeping up that even MySQL connections were faling. I sent had the host put in some spam guards and I haven't had this issue since.

look...I'm not stupid. I am a professional DBA just not MySQL. I thought I pretty clearly said there was deadlocking in the database....the thread and user tables are locked. Can you please tell me how in gods name there can be something other than an application problem when there is deadlocks in the database that ONLY interfaces with the application? Frankly, the attempts at passing the buck is pathetic and clearly indictate paying for the licensing is not worth it...and just so you all know I am not blowing smoke and I did actually do the testing (and log it)....here is one example:

Query 349 Locked UPDATE user SET lastactivity = 1114527827 WHERE userid = 3692

OH MY GOODNESS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

PLEASE DON'T PASS THIS BS BACK TO ME THAT IT'S A PROBLEM WITH THE HOSTING COMPANY!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[krohnathlonman]

I've only seen problems like that when a servers hard drives are overloaded...... basically its waiting for the drive before the query can actually happen.

[Steve Machol]

Typ01, to get vB support on these forums you first need to be a licensed customer and register for Priority Forum Support. To do this, please go here:

http://members.vbulletin.com/membersupport_priority.php

...and enter your email address in one of the boxes. You'll need to have your customer number and password to access the page.

As for your comments, I really don't know what more I can tell you. This is a server issue whether you want to believe it or not.

[Typ01]

Typ01, to get vB support on these forums you first need to be a licensed customer and register for Priority Forum Support. To do this, please go here:

http://members.vbulletin.com/membersupport_priority.php

...and enter your email address in one of the boxes. You'll need to have your customer number and password to access the page.

As for your comments, I really don't know what more I can tell you. This is a server issue whether you want to believe it or not.

Sorry to sound bitchy...I was in a lot of pain this morning not that it's an excuse...I think it has to do with the bots (the connection problem not my pain :D) and this page:

http://www.vbulletin.com/forum/archive/index.php/f-59.html

I think the bots are flying through the thread thread table too fast by following the links (which is creating a lot of threads in mysql) and other things are being made to wait. As far as the database goes it should help alleviate that problem by bringing down the number of threads that print on the page...but it also is counting in that menu at the top that will have more pages to print out. I don't know much about the bots...but I assume they index heirarchically.

[NetRover]

Hi there, I've woke this morning to find about 400 database error emails:-

My board is totally offline with the standard database error message and email administrator.

here are some samples:-
--------------------------------------------
Database error in vBulletin 3.0.6:
Invalid SQL:
SELECT post.postid, post.pagetext, IFNULL( user.username , post.username ) AS username, dateline
FROM post AS post
LEFT JOIN user AS user ON (user.userid = post.userid)
LEFT JOIN deletionlog AS deletionlog ON (deletionlog.primaryid = post.postid AND deletionlog.type = 'post')
WHERE threadid = 1587
AND visible = 1
AND deletionlog.primaryid IS NULL

ORDER BY dateline ASC
LIMIT 0, 250

mysql error: Can't open file: 'user.MYI'. (errno: 145)
mysql error number: 1016
---------------------------------------------------------------
Database error in vBulletin 3.0.6:
Invalid SQL: SELECT userid, lastactivity, options, username, opentag, closetag
FROM user as user
LEFT JOIN usergroup as usergroup ON(user.usergroupid = usergroup.usergroupid)
WHERE lastactivity > 1114815600 ORDER BY username
mysql error: Can't open file: 'user.MYI'. (errno: 145)
mysql error number: 1016
Date: Saturday 30th of April 2005 01:30:02 AM
Script:**********************
Referer: *****************
Username: Unregistered
IP Address: 81.76.3.72
------------------------------------------------------------

please advise what i need to do, desperate!!!!! :(

[dwh]

Looks like you have a major problem. Did you run out of hard drive space? Can you run a repair? It looks like it isn't finding your user table at all.

[NetRover]

Hi thanks for the reply and the advice.

A repair and optimise as you recommended has got things up and running. Early days but things appear to be ok.

thanks so much

b.t.w
any preventative measures on this type of thing or advice. I don't really perform any or backups or anything. Just rely on my host! :o

[Steve Machol]

For future reference:

mysql error: Can't open file: 'user.MYI'. (errno: 145)

This error means:

MySQL error: 145 = Table was marked as crashed and should be repaired

[dwh]

The only prevention I can think of is to monitor disk space. But most importantly, backupbackupbackupbackup. Including offsite backup. And the offsite backup should be done by SFTPing INTO the server and pulling database. If your password is hacked they can get into your remote backup if you PUSH it to a remote backup server instead of PULLING it from your webserver.

[shiva]

I just got my system updated a few days ago, and now have the same problem as everyone else. I guess I'm the 30th person on the 30th different host. I had my boards switched off, soon as I switched on, boom, but I have had these errors as well over the past few days, in fact, was a problem with 4.x.9 as well, but never saw this as bad as it is.

I'm sorry Steve, but too many people are having the same problem here for it to be a server host problem. I've double checked everything according to #3, it's the same as before, and I can't see any problem at all. I have a decent robots.txt file as well, and non members are banned from viewing the forum as well.

[shiva]

Okay, add what I upgraded too...
PHP 4.3.10
MySQL 3.23.58

I get this error in mySQL,
PMA Database ... not OK[ Documentation (http://66.49.130.147/MyAdmin/Documentation.html#pmadb) ]
General relation features Disabled
everything repaired and optimised
My post count table is 9.4 mb, highest there.

Base stats: 5,500 members, generate 2.5 million pages last month
In PHP 4.3.9, I got this error a couple times a day
Since we got upgraded, it has increased while the site was on admin view to about 5 times a day.
This greatly increased soon after I reopened the site, and noticed it after I tried to write a second post.

I use hacks (doesn't everyone?) vba CMPS, ibproarcade, Geeks Article hack, and a couple minor ones for thumbnails and permissions. They were running fine.
My server has Frontpage extensions as well (You never know, always seems to point to Microsoft when there's a problem)

Maybe if people write their lists like this, maybe a common variable will show up? Just a idea. Seems very weird I have a runaway server when php got upgraded

[Steve Machol]

I'm sorry Steve, but too many people are having the same problem here for it to be a server host problem.I'm also sorry but I have posted all the known causes of this problem. I have also asked the other vB staff and no one has come up with anything new that would cause this error. If there is an alternate cause, then this is something that no one - staff, customers or hosts - has been able to identify.

One of the Devs did add this point:

An addendum to point 4 is that PHP wasn't able to get a socket (at the OS level) to do the connect.

I would say this is about the same as what can happen to fsockopen(). From the PHP Manual (http://php.he.net/manual/en/function.fsockopen.php):
If the value returned in errno is 0 and the function returned FALSE, it is an indication that the error occurred before the connect() call. This is most likely due to a problem initializing the socket.

At any rate, the problem is that MySQL cannot be connected to. If your forums work sometimes, then this has to be a server issue.

[shiva]

Yeah, I see this same error in other threads as well. I will send the info to my host, but doubt he could do anything about it either. This happened only when I went to php .10, so maybe it's a trigger somewhere. I've seen hosts that use VB as their forum software have the same problem

Only thing I am concerned about is when it does happen, whether it affects the entire server, and not just my site. Anyone know if it does or not?

[Steve Machol]

Most likely this is server-wide, unless the host has a maximum connections setting for your user and your site has exceeded this setting.

[mykes]

I'm also sorry but I have posted all the known causes of this problem. I have also asked the other vB staff and no one has come up with anything new that would cause this error. If there is an alternate cause, then this is something that no one - staff, customers or hosts - has been able to identify.

One of the Devs did add this point:



At any rate, the problem is that MySQL cannot be connected to. If your forums work sometimes, then this has to be a server issue.

Please pass this on to your developers:


I am seeing an issue with vBulletin 3.0.6. First, a bit about the system:
1) dual xenon processors at 3.0 GHz
2) 2 GB RAM
3) MySQL and apache running in the same machine
4) MySQL version 4.1.10
5) swap is disabled
6) max_connections in my.cnf is 450
7) MaxClients in httpd.conf is 250

Periodically, the site stops responding. This is every few days.

I can see that three of the processors are 100% idle, the fourth is 0% idle. For 2-3 minutes, it is like this. While the one CPU is 0% idle, the number of processes grows from a normal of 140 or so to over 700. After the 2-3 minute period, all 4 CPUS (2 real, 2 hyperthread) go back to 90%+ idle, processes running goes to 30-40 per top refresh, process count drops back to the normal 140-ish over a few seconds, and the site hums along.

This is evidence to me that some php script is causing a vital table to be locked while a very long query takes place, causing all the other httpd children to wait for the table to be unlocked.

I now have slow queries being logged, and I will report further if I find that abnormally long (2-3 minute) query.

Even though there are 200 more mysql connections than possible httpd children, I get bombarded with e-mails saying "cannot connect to mysql."

Please look into the cause of this.

[Marco van Herwaarden]

Just a question: Are you using InnoDB or MyIsam tables?

[mykes]

Just a question: Are you using InnoDB or MyIsam tables?

MyIsam

[Marco van Herwaarden]

Hmm if you are sure you don't have any InnoDB tables, then that should rule out an automatic lock by MySQL.

Does this always happen at the same time of the day?

Try if you can run (mysqladmin) a "SHOW FULL PROCESSLIST;" SQL statement.

If you can get that info, it could be helpfull.

[mykes]

Hmm if you are sure you don't have any InnoDB tables, then that should rule out an automatic lock by MySQL.

Does this always happen at the same time of the day?

Try if you can run (mysqladmin) a "SHOW FULL PROCESSLIST;" SQL statement.

If you can get that info, it could be helpfull.

See attached txt file (in the zip)

It's happening right now, so it should have the offending queries.

I see "Locked" on almost all of them.

[mykes]

Here's a suspicious query form the slow-query log (13 seconds and locked for 10?):

# User@Host: basketba_1[basketba_1] @ localhost []
# Query_time: 13 Lock_time: 10 Rows_sent: 1 Rows_examined: 0
SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddat
e) AS passworddate,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displa
ygroupid,
language.phrasegroup_holiday AS phrasegroup_holiday,
language.phrasegroup_global AS phrasegroup_global,
language.options AS lang_options,
language.languagecode AS lang_code,
language.charset AS lang_charset,
language.locale AS lang_locale,
language.imagesoverride AS lang_imagesoverride,
language.dateoverride AS lang_dateoverride,
language.timeoverride AS lang_timeoverride,
language.registereddateoverride AS lang_registereddateov
erride,
language.calformat1override AS lang_calformat1override,
language.calformat2override AS lang_calformat2override,
language.logdateoverride AS lang_logdateoverride,
language.decimalsep AS lang_decimalsep,
language.thousandsep AS lang_thousandsep
FROM user AS user
LEFT JOIN userfield AS userfield ON (user.userid = userfield.use
rid)
LEFT JOIN usertextfield AS usertextfield ON (usertextfield.useri
d = user.userid) INNER JOIN language AS language ON (language.languageid = IF(us
er.languageid = 0, 1, user.languageid))
WHERE user.userid = 8682;

[mykes]

It'd be interesting to see if all the people with complaints are using vBookie. I found the following problem and I propose a fix. I posted it on vbulletin.org in the vBookie forum, as well. But I think this is important to post here, too, because it'll keep the vbulletin developers from having to scratch their heads too much.


# Query_time: 977 Lock_time: 455 Rows_sent: 20 Rows_examined: 125192586
SELECT item.item_id, item.item_title, item.threadid, grp.group_title, item.item_
status, item.item_created, item.item_open_until, item.item_pay_after, thread.thr
eadid, thread.forumid, thread.postuserid FROM vbookie_items AS item
LEFT JOIN vbookie_groups AS grp ON grp.group_id = item.group_id
LEFT JOIN thread AS thread ON thread.vbookie_item_id = item.item
_id
WHERE item.item_status='SETTLED'
ORDER BY item.item_id DESC
LIMIT 0, 20;


vBookie locking the thread table for 455 seconds! The whole site stops for that long. Look at how many rows examined! Dang these LEFT JOINs.

Did some digging:

mysql> select count(*) from thread;
+----------+
| count(*) |
+----------+
| 159281 |
+----------+

mysql> select count(*) from vbookie_items;
+----------+
| count(*) |
+----------+
| 844 |
+----------+

I think it scanned the entire thread table about 844 times to do the query, and it created temporary tables to boot.


My fix was to enter the following query:

create index thread_vbookie on thread (vbookie_item_id);

This costs sizeof(MEDIUM_INT)*159281 bytes of RAM to keep the needed field in RAM all the time.

At MEDIUM_INT = 2 bytes, it's 320K. At 4 bytes, it's 640K. Puny amount of memory to save all those table scans.

I'm pretty sure it worked, as I went to the vbookie.php page and did all kinds of sorts and they were instant.

[Marco van Herwaarden]

Yes the log clearly shows this.

Nice to see that we could solve this long time mystery.

And yes, your solution looks good.

PS I doubt that all that where complaining about this have been using this hack.

[tommyxv]

After numerous support tickets and complaining, my host did an upgrade on the server last night, and I haven't received anymore MySQL Link-Connect database error emails. So far so good. (fingers crossed and knocking on wood)