3

So, I'm getting the following error:

Error Number: 2006
MySQL server has gone away

I can all but guarantee this is because the script takes a bazillion minutes to run, and then more queries are run in the middle of the script. Considering this is an admin-only, huge file-writing script, this is to be expected. (and is perfectly acceptable) I think the MySQL connection is getting closed because PHP / MySQL / something isn't holding it open. However, I can't for the life of me figure out how to stop this timeout from occurring!

I have the following at the top of my PHP script, but it doesn't appear to be helping.

ini_set('default_socket_timeout', -1);
ini_set('max_execution_time', -1);
ini_set('memory_limit', -1);
ini_set('mysql.connect_timeout', -1);

Any idea how I can fix this problem? Thanks!

Nathanael
  • 6,893
  • 5
  • 33
  • 54
  • 1
    You can check for a connection and then reconnect if needed. – diolemo May 17 '12 at 16:52
  • How would I do that? :O That sounds like a pretty great solution. – Nathanael May 17 '12 at 16:59
  • 1
    You can ping the server (over the connection) http://www.php.net/manual/en/function.mysql-ping.php The return value indicates whether the connection is still active. Some versions will auto reconnect (you need to check that). – diolemo May 17 '12 at 17:01

1 Answers1

7

I think a viable option would be to mysql_connect everytime before you call mysql_query. This ensures that there will be a live connection before every query.

But a much better approach is to first check for the connection status, and only then reconnect if necessary. mysql_ping is useful for checking the connection status, so you can use this to check if re-connection is required. Example code present in @galador's answer.

Thanks @diolemo and @galador.

Community
  • 1
  • 1
Unos
  • 1,293
  • 2
  • 14
  • 35
  • 2
    This would introduce too much overhead. You should check whether the connection is still available at points where it is possible to have gone away. If you have 10 fast queries in a row then it is unlikely to have gone away over that time. But if you are waiting 5 minutes on a file transfer then reconnection would be expected. – diolemo May 17 '12 at 17:09
  • Agreed. did not know about mysql_ping, which would be the perfect solution for this problem. updating the answer! – Unos May 17 '12 at 17:16
  • 1
    `mysql_ping()` does **not** reconnect automatically since MySQL 5.0.14. See [this answer](http://stackoverflow.com/questions/6231285/fastest-way-to-insert-134675-values-in-remote-database/6231453#6231453). – bhamby May 17 '12 at 17:57