MySQL Got an error reading communication packets


MySQL Got an error reading communication packets

Posted on Dec 10, 2008 by Paul White

The Problem


Aborted connection 147 to db: 'mydatabase' user: 'myuser' host: 'localhost' (Got an error reading communication packets)



The Result

Lots of MySQL Warnings in your Event Viewer along with lots of ASP.NET warrnings



Quick and Dirty Answer


MySQL or ASP.NET 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 or dedicated 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
event viewer windows

So we open one of the entries to see the details

event properties windows
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

text editor
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 errors too.

Permalink
7998 Visitors
13717 Views

Categories associated with MySQL Got an error reading communication packets

Discussion

Weber | May 28, 2009 1:25 PM
Do you think this issue may causes some perfomance problem?
paul | May 28, 2009 4:38 PM
Weber, This issue from what I can tell is the result of a corrupt MySQL installation. If DB connections never get closed, this leaves you with less available connections. I would recommend you fully uninstall mySQL, and reinstall with the latest build.
name
Email Needed to confirm comment, but not made public.
Website
 
Type Code
Security Check
 
When you Post your Comment, you'll be sent a confirmation link. Once you click this link your thoughts will be made public.. Posts that are considered spam will be deleted, Please keep your thoughts and links relavent to this Article