MySQL Got an error reading communication packets
Posted on Dec 10, 2008 by Paul White
Aborted connection 147 to db: 'mydatabase' user: 'myuser' host: 'localhost' (Got an error reading communication packets)
Lots of MySQL
Warnings in your Event Viewer along with lots of ASP.NET
Quick and Dirty Answer
is not closing the connections. I know what you are thinking. I am a good programmer I have an entire line of code dedicated
to opening and closing my DB connections. Of course you do. But it takes a little investigating to figure out why.
First to verify that the connections are not being closed you need to take a look at your Query Logs. Those on you on a shared hosting
account are out of luck. Those of you with a VPS
server have acess to your query logs. Most likely located at
C:Program FilesMySQLMySQL Server 5.0data
There should be a file that looks like this "somefile.err"
By default MySQL
does not log queries, mainly because the log file can grow faster than your IIS logs. Give it a week and you might have a log file over 1 GB. You have to login as MySQL
administrator and tell it to log queries. Usually this has to be done while logged into MySQL
administrator from the local machine. ASP.NET
has most likey created lots of entries into your Application Event logs. Looking something like this
So we open one of the entries to see the details
Here we can see connection # 147 had the issue, so we use a text editor to open our mySQL
query logs to see what the query was all about. Make sure you use something designed for large text files such as EditPad Pro
Notice how every connection started with a "Connect", followed by some Query, and ending with "Quit". On connection 147 there is no Quit command. This is the reason you got the error in asp.net
What caused this?
At this time I have not 100% certain, but my theory is that its due to not enough system resources. This error first started to pop up on me three weeks ago. I am on a 1GB VPS
. First I noticed this error then I started to notice my entire box was starting to drag. After all a VPS
is not a dedicated
box. You still share CPU time and bandwidth with other people on the same box. If one person on the box is abusing their power, and hogs up the CPU and network bandwidth, this can cause your system to come to a complete lock. Turned out someone on the box was going through 8 TB of transfer ( you are only allowed 400 GB ) they moved this person off the box and my system came back to normal. But this error started to pop up again. So either someone else on the box is also hogging CPU and bandwidth or there is something else. I have my hosting
company looking into if someone on the box is abusing it again. I am pretty sure its somone of the box as I have several other VPS
units running the same framework of helper functions and none of them have this issue.
Hope this helps anyone having this problem.
UPDATE 12/15/2008 5:00PM
After moving to a new VPS
the problem was still there. After talking with the tech, it would seem that they didn't physically move me. They only logically assigned me to a different node running on the same box. This allowed me to keep the same IPs I had before and not have to change the DNS or anything else. But the problem was still there. I finally broke down and fully uninstalled MySQL
server 5.0 and then installed MySQL server 5.1
( latest stable release ). I don't think there is any major difference between the two versions, but I do suspect that some of the files may have become corrupt due to hitting virtual memory on the server. Hopefully a fresh install of MySQL
will do the trick. Its been running for 10 mintues now with no issues. I will post back in a few days to update everyone on if a reinstall solves the problem
UPDATE 12/15/2008 11:15PM
As of right now I haven't had a single error on the server since the reinstall of MySQL
. I think it might be safe to say that the problem was a corrupt DB server. If you are every presented with this problem backup your data, then completely uninstall and reinstall MySQL
. This fix the aborted connection errors for MySQL
and probably also fix most of your ASP.NET