mysqli::real_connect(): MySQL server has gone away

sneakyimp

I've got a dev version of a site up on a rackspace Cloud Server. The site is built on CodeIgniter and we occasionally get the error mysqli::real_connect(): MySQL server has gone away. I have not yet been able to determine any pattern to when this happens, but it seems to happen first thing in the morning after an extended period where no one is accessing the server. I do NOT think this error is happening in connection with any length query. This seems really fishy to me and I'm wondering if maybe CodeIgniter is to blame for it. Would [man]mysqli_connect[/man] ever return this error under any conditions? I'm wondering if maybe the db server might get swapped out of memory or something while no one is accessing it at night and then when we hit it first thing in the morning, that first query attempt may trigger a sizeable disk access or something that triggers a timeout.

Or alternatively, it may be that the db server (which is a separate, dedicated machine from the web server running PHP) might have somehow disconnected.

More detail on the error.
http://dev.mysql.com/doc/refman/5.5/en/gone-away.html

NogDog

I'm wondering if CI is setting up a persistent connection, and the DB server is killing it after x amount if idle time? If so, the quick fix would be to tell CI not to use a persistent connection. I haven't used CI in awhile, so don't have a config file hanging around to see if that's an option that can be turned off -- but I'm guessing a bit of googling would find it. 🙂 (Historically, I've had more problems with persistent MySQL connections than whatever little bit of performance boost they may have provided.)

dalecosp

mysql.com/doc/refman/5.0/en/gone-away.html wrote:
By default, the server closes the connection after eight hours if nothing has happened.

I see this error every morning that I've left a terminal open to one of our Linux boxes while running the CLI client. As the page notes, the CLI client is configured to automatically re-issue the query, so it matter little to me and my terminal.

I can see where it would be rather nasty in a web app, though ;-)

I suppose you could try adjusting wait_timeout in my.cnf upwards.

A really dirty hack would be a cronjob that loads your page every couple of hours overnight ....

sneakyimp

It looks like my CI database config is in fact configured to use persistent connections, so I'll be changing that because yes I agree that persistent connections cause more annoyance than performance boost. for reference this is what the CI database config looks like:

$db['default'] = array(
	'dsn'	=> '',
	'hostname' => 'localhost',
	'username' => 'dbuserxxx',
	'password' => 'dbpassxxx',
	'database' => 'my_db_name',
	'dbdriver' => 'mysqli',
	'dbprefix' => '',
	'pconnect' => TRUE, // change this to false
	'db_debug' => TRUE,
	'cache_on' => FALSE,
	'cachedir' => '',
	'char_set' => 'utf8',
	'dbcollat' => 'utf8_general_ci',
	'swap_pre' => '',
	'autoinit' => TRUE,
	'encrypt' => FALSE,
	'compress' => FALSE,
	'stricton' => FALSE,
	'failover' => array(),
	'save_queries' => TRUE
);

How do the mechanics of these connections work? The mysql command-line client seems pretty simple to me. You connect and authenticate and it listens to you until at some point you sit idle long enough and it disconnects you and closes the socket.

But how does it work when you have Apache running numerous threads, each of which might be connecting to the db? Are persistent connections maintained between subsequent page requests? How do the Apache/PHP processes interact with some pool of connections (if any such pool exists)?

I've always wondered exactly what it means to user a persistent database connection but haven't really seen a thorough explanation. This gets close, but I still feel like it could be described more clearly.

NogDog

I think a connection has a process ID of some sort, which I presume the PHP Apache module keeps in memory until it needs it next. (?)