Exact relationship between default_socket_timeout and mysql.connect_timeout?

Question

There are many articles on the web and answers here on StackExchange that recommend using lines like these (or even longer timeouts) to avoid errors with MySQL timeouts during long-running processes (such, but not limited to, as "MySQL server has gone away"):

ini_set('mysql.connect_timeout', 300);
ini_set('default_socket_timeout', 300);

However, I cannot find a good explanation of exactly how these solve the problems. In many cases they seem to be cargo cult programming without a clear basis. I'm curious about how precisely these settings affect the MySQL connection lifecycle: the official documentation is vague about when they apply and are measured.

From what I can tell mysql.connect_timeout only relates to the original connection to MySQL. Thus this setting seems irrelevant to connection failure issues after a successful connection has initially been made. The setting default_socket_timeout may be more relevant to later connection issues, but it's not clear exactly what would trigger the timeout. Is it also only relevant at connection, or is it a timeout for the whole socket lifetime? What triggers the timeout? Lack of traffic? Lack of a specific type of traffic?

Understanding exactly how these interact with the MySQL connection is important to be able to reason soundly about error behavior.

Simply put, mysql.connect_timeout applies only to establishing a connection to mySQL, while default_socket_timeout applies to all subsequent communication after the connection has been established in english, if you already have an established connection, the connect_timeout parameter will be ignored once you send your query. But if you do send the query and the server takes longer than X to acknowledge, the socket_timeout parameter will be triggered — Javier Larroulet
– Javier Larroulet, Commented Aug 24, 2018 at 16:23
@JavierLarroulet Thanks. Just to parse a little closer: what does it specifically mean for the "server to take longer than X to acknowledge"? Is this protocol-aware (e.g., maybe timing out waiting for a TCP ACK packet)? Or is it just looking for a silent socket with no data transfer for X time period (e.g., SO_RCVTIMEO at the C level)? And furthermore, does the timeout pay attention only to the receiving side (input), or the sending side too (output)? — Scott Buchanan
– Scott Buchanan, Commented Aug 24, 2018 at 19:57

beberlei · Accepted Answer · 2018-08-24 18:57:01Z

1

Both settings have nothing to with getting "mysql has gone away" in long running processes. MySQL historically had no way to restrict how long queries run maximum, as such they could run forever and because phps max execution time does not get checked during I/O events, it wouldn't stop that.

To restrict SQL queries runtime, you can use a new MySQL feature though with 5.7:

SELECT /*+ MAX_EXECUTION_TIME(1000) */ status, count(*) FROM articles GROUP BY status ORDER BY status;
SET SESSION MAX_EXECUTION_TIME=2000;
SET GLOBAL MAX_EXECUTION_TIME=2000;

I wrote a blog post about that here with details:

https://tideways.com/profiler/blog/use-timeouts-to-prevent-long-running-select-queries-from-taking-down-your-mysql

But to fix MySQL has gone away, you need a point in your long running script where you know it didnt do sometihng for a while and add a "ping".

try {
    $pdo->query("SELECT 1");
} catch (PDOException $e) {
    if (stripos($pdo->getMessage(), "mysql gone away")) {
        // reconnect
    }
}

This way you can reconnect whenever mysql gone away.

answered Aug 24, 2018 at 18:57

beberlei

2022 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Scott Buchanan Over a year ago

Simply pinging to reconnect would of course work, but I'm trying to figure out what' causing the connection to be lost in the first place. According to the manual this can be caused by a "timeout from the TCP/IP connection on the client side", which is where I expected default_socket_timeout might come in. dev.mysql.com/doc/refman/8.0/en/gone-away.html

beberlei Over a year ago

Oh, its caused by not using the socket / tcp connection for around a minute, and then the MySQL server kills them for maybe being a Zombie.

Scott Buchanan Over a year ago

Thanks @WilsonHauck. Appreciate the offer for help, but my goal here was really to get an answer to the exact behavior of these PHP settings rather than to diagnose my particular situation.

Wilson Hauck Over a year ago

@ScottBuchanan Posting B) and C) would be helpful in looking at your timeout difficulties related to PHP settings. Thanks

Collectives™ on Stack Overflow

Exact relationship between default_socket_timeout and mysql.connect_timeout?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related