How to fix mysql server has gone away error

→ Are you a new visitor? Please visit the page guidance for new visitors ←

How to fix mysql server has gone away error

Today I thought about right something about this particular error which pops up in certain specific cases

Mysql server has gone away

I’ve seen this a couple of times on some of my clients and they all thought that this issue was a direct cause of how the mysql server has been configured. I do not want to say that they are completely wrong, but in that specific case, unfortunately they were. I had a bit of work to explain the cause and why it would happen, in the end they did understood.

What can cause this error?

Normally this error is directly linked to timeout settings, especially when you are using a remote mysql connection. It can be one of:

  • tcp connection timeout
  • mysql settings timeout
  • php mysql connection timeout

But you can also get this error if the administrator issued the kill command, or the query you are sending has exceeded the maximum allowed packets to be sent to your mysql server.

Anyways, from what I’ve seen and read about this, those are the most specific cases where you will stumble this error.

How to fix the mysql error?

Well you as a client, you will need to make sure that your php mysql connection timeout and tcp connection timeout is high enough for the query you are running to complete. You can do this by adding in your script the following:

This will increase the timeout to 300 seconds. If you need more, you can increase it, but 300 seconds I believe is more than enough, else try splitting the query.

Then for administrator, you would normally set the mysql settings timeout, you would fiddle with the variable

You can also view the mysql variables by issuing command:

Another option you would look after is the max_allowed_packet, if the amount of query is higher than this value, you will also see this error once again. The default value is set to 1MB, keep in mind you would also need to update this value on the client side too, for eg:

For mysql server and client you will also have to add the same value to my.cnf file, so do not forget about that.

The last cause would be the TCP connection timeout, normally this would only happen on Windows where in some cases you do not get an error from your OS when trying to write to your TCP/IP connection.

The solution for this is to use mysql_ping() on the connection to check if connection is active, this would then have the connection re-established and this error would then go away.

More information here:

http://www.php.net/manual/en/mysqli.ping.php

Well that’s about it I believe, if I remember something else, I’ll be sure to add here. Happy coding!

Request an article ←