MySQL Bugs: #62070: SO_KEEPALIVE socket option not set by mysqlnd

Bug #62070	SO_KEEPALIVE socket option not set by mysqlnd
Submitted:	3 Aug 2011 11:35	Modified:	2 May 2016 10:41
Reporter:	Mark Robson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connectors: mysqlnd ( PHP )	Severity:	S3 (Non-critical)
Version:	PHP 5.3.6, PHP 5.4.4 mysqlnd 5.0.10	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The SO_KEEPALIVE flag is not set on the client-socket, by the php mysqlnd library. The C client library always sets this flag, which means that a server which goes away during a query eventually gives an error

How to repeat:
1. Connect to the server using mysqlnd
2. Turn off the server VM
3. Turn on the server VM
4. Wait...

Expected result:

Eventual error

Actual result:

Hangs forever, even after the server comes back.

Suggested fix:
Enable SO_KEEPALIVE on the socket.

You probably need to be executing a query when the server goes away... try SELECT SLEEP(60)

Thank you for the report.

In my case PHP application does not hang, but immediately finish. Please send example of problem PHP script which hangs.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

We have observed such problem many times in production. It never happened before we started using mysqlnd. The case is: connection runs multiple transactions, but is killed in the middle by our application (we kill too long transactions automatically). PHP doesn't get the error and just hangs.
However, I still don't have a repeatable testcase.

Vojtech,

thank you for the feedback.

How do you kill transactions? Do you use mysql command KILL <mysql_thread_id> or some other method?

Connections are killed using a stored procedure run using event scheduler. The kill goes this way:

 SET @KILL_STMT_STR := CONCAT('KILL ', _thread_id);
 PREPARE kill_stmt FROM @KILL_STMT_STR;
 EXECUTE kill_stmt;
 DEALLOCATE PREPARE kill_stmt;

I tried to reproduce that by creating/killing connections in a loop, but didn't succeed. However I think someone from mysqlnd developers should review the information that Mark Robson wrote in his initial report.

Thank you for the feedback.

I finally managed to repeat this behavior with PHP 5.4.4., mysqlnd 5.0.10

See also Bug #65658

This has been fixed upstream at PHP as bug #70456
See https://bugs.php.net/bug.php?id=70456