Sudden "MySQL server has gone away" error in PHP site


Question

Today one of my websites started showing

Error Number: 2006
MySQL server has gone away

It's a low-traffic client site running under Apache 2.2.9 (Debian), PHP 5.2.6-1+lenny3 (using CodeIgniter 1.7.1 framework) and MySQL 5.0.51a. I obviously reasearched about the error but all the possible solutions imply that there are big queries going on that may time out and reset the connection, or hit the packet limits. However, this is not the case, it's a small database processed with the simplest queries. To be sure about this I made up a few queries to return one row, still the same error.

Database credentials are fine, I can even login directly into mysql, run some of the site's queries and get the right data instantly. There are several other sites on the same server and connections to the database, much larger sites, and they all have no problems.

I tried:

  • Restarting MySQL
  • Restarting the whole server
  • Looking for errors in the logs (both Apache and MySQL, none)
  • Checking db user permissions
  • Changing mysql.connect_timeout and default_socket_timeout in PHP
  • Changing max_allowed_packet in MySQL
  • Reading the official docs, forum and everything in SO that says "MySQL server has gone away"

New:

  • Disabling persistent connections in PHP
  • Changing wait_timeout and connect_timeout in MySQL

Update:

It seems to be related to the execution time of my script: it retrieves some info using the Facebook PHP client and this call seems to be failing randomly today, so I either have no data from Facebook or the MySQL error. But to my surprise, none of the given solutions seems to deal with the timeout.

Any ideas? thank you for your time!

1
14
12/10/2009 10:28:00 PM

Accepted Answer

As I said in my update, I concluded that the problem with MySQL arises when the link to Facebook takes longer than the maximum connection time with the DB. None of the suggestions could beat this limitation, so I decided to work around it and reconnect every time I presumed the link maybe gone.

So after each call to Facebook, I used to following code:

$this->load->database();
$this->db->reconnect();

This is the particular solution when using CodeIgniter, and AFAIK the db->reconnect() function is only available since version 1.7.2 so I updated it in order to work.

Thanks everyone for your answers!

11
12/11/2009 3:13:14 AM

It's probably a connection time out affecting your persistent connections in PHP. I used to see them all of the time. The timeout parameter is within MySQL itself.

Your options include: - not using persistent connections - turning off idle timeout on the MySQL server - trapping the error

I always wrap reconnection into my own PDO class, so I can't even remember if PHP reconnects or not. In any case, it's an easy fix. On query, catch & reconnect.

I have "generated" this error in the past with InnoDB. If you're using that engine, what's the output of SHOW ENGINE INNODB STATUS after a failure?


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon