PHP MySQL set Connection Timeout

Question

There are certain posts on MySQL connection set time out from PHP using mysql.connect_timeout. I want to know if this set timeout from PHP just time out the initial connection to MySQL or valid for a particular query to database?

My case here is that, I have a page with connection to MySQL on top and then I am executing say 3-4 queries to MySQL one after the another. 1st and 2nd query taken only 1-2 seconds to execute where as 3rd query takes 20 seconds. Now, in cases when 3rd query is taking more than 20 seconds, I want to call time out. So, the question here is that, setting this time out from PHP is applicable to initial connection to database or it is applicable to every subsequent query as well (independently). If later is the case, then how I can set it to timeout after 20 seconds for 3rd query?

Sueannsuede

Answer 1

The connect_timeout parameter is only valid at connection time. It's useful to check if your DB server is reachable in 20 seconds or so. Once connected the specified timeout is no longer valid.

I don't find any query timeout parameter on official mysql manual page: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html so I don't think this is possibile.

Esurient

Answer 2

see it:

set_time_limit(0);   
ini_set('mysql.connect_timeout','0');   
ini_set('max_execution_time', '0');   

Nursery

max_execution_time only affects only cpu usage - querying a database does no cpu usage from php process – Service

set_time_limit(0); is useless outside a loop – Sinhalese

Answer 3

sigalarm is your friend. You can set a alarm, and use a signal handler to detect the alarm.

http://php.net/manual/en/function.pcntl-alarm.php

Eagan

looks interesting, can you give an example? – Service

Answer 4

Use MySQL version 5.7.4 or above and set appropriate "max_statement_time" variable, query will fail if timeout is reached

see http://mysqlserverteam.com/server-side-select-statement-timeouts/

Vigil

Answer 5

It is in your php configuration file. The location of this file is different from one system to the other. On Ubuntu and Debian it is located in /etc/php5/apache2/php.ini

mysql.connect_timeout = 60 /* the default value is 60 seconds */

Mcgehee

any explanation for why I got a down vote on a working answer? – Mcgehee

Because it changes a wide system configuration that may affects to another application. – Sclerometer

Answer 6

You would have to fork a child process, the parent process will kill the child process if some condition is not met within 20 seconds.

Or use a language like node.js that doesn't block the current thread for an IO operation like a query.

Lyric