1

Possible Duplicate:
MySQL server has gone away - in exactly 60 seconds

We're using the mysql_connect method in PHP to create database handles for a daemon. The problem is, that connection might not be used for more than 8 hours (sometimes for more than a few weeks.)

We're running into issues where MySQL will end the session because the wait_timeout is reached. http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

We don't want to increase this value for ALL connections. Is there a way to increase the timeout for that handle only through PHP? Through MySQL?

Community
  • 1
  • 1
Dan Bough
  • 499
  • 1
  • 10
  • 23
  • Why can't you check if there is a connection and renew, if it timed out? – Peon Jan 17 '13 at 14:31
  • 2
    Just a comment: You should not use PHP mysql_connect because is deprecated and will be removed in the future. – FerCa Jan 17 '13 at 14:33
  • @FerCa is correct. All the `mysql_xxx()` functions are deprecated. Please switch to mysqli or PDO as soon as possible. See also http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php for more info. – SDC Jan 17 '13 at 14:36
  • @ManseUK Thank you! Do you know if that affects all connections or just the connections in the calling script? – Dan Bough Jan 17 '13 at 14:39
  • @DainisAbols - The script is called from init.d so it runs as a daemon. I have to do some more research, but checking the connection hasn't worked for me. – Dan Bough Jan 17 '13 at 14:41
  • @FerCa - I agree. This is for an enterprise app that is slow to move. We're in the works on that though, thank you. – Dan Bough Jan 17 '13 at 14:41
  • 1
    @DanB. according to the [doc](http://php.net/manual/en/function.ini-set.php) *The configuration option will keep this new value during the script's execution, and will be restored at the script's ending* but you could check other scripts executing at the same time using [`ini_get`](http://php.net/manual/en/function.ini-get.php) to confirm – Manse Jan 17 '13 at 14:41
  • what is your mysql version? – Shiplu Mokaddim Jan 17 '13 at 14:53

2 Answers2

1

It's not good to hang onto DB connections for long periods because the DB only provides a fixed number of connections at any one time; if you're using one up for ages it means your DB has less capacity to deal with other requests, even if you're not actually doing anything with that connection.

I suggest dropping the connection if the program is finished using it for the time being, and re-connect when the time comes to do more DB work.

In addition, this solution will protect your program from possible database downtime, ie if you need to reboot your DB server (it happens, even in the best supported network). If you keep the connection alive (ie a DB ping as per the other answers), then an event like that will leave you with exactly the same problem you have now. With a properly managed connection that is dropped when not needed, you can safely leave your daemon running even if you have planned downtime on your DB; as long as it remains idle for the duration, it needn't be any the wiser.

(as an aside, I'd also question the wisdom of writing a PHP program that runs continuously; PHP is designed for short duration web requested. It may be capable of running long term daemon programs, but there are better tools for the job)

SDC
  • 14,192
  • 2
  • 35
  • 48
0

For this problem, the best way is use mysql_ping();

Check it out on manual PHP mysql_ping()

Steven V
  • 16,357
  • 3
  • 63
  • 76
Dawid Sajdak
  • 3,064
  • 2
  • 23
  • 37
  • mysql_ping doesn't work in MySQL 5.5. – Dan Bough Jan 17 '13 at 14:43
  • @DanB, do you have a source for that? I can't find anything about ping not working in MySQL 5.5, and it seems highly unlikely that it would have been removed. Autoconnect is off by default since MySQL 5.0.3, but that is not the same as ping not working. – Animism Jan 28 '14 at 02:43
  • @Animism - Looks like it's "just" deprecated in 5.5. It will still work. – Dan Bough Jan 28 '14 at 11:39