php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #76703 MYSQLI_OPT_READ_TIMEOUT cannot be changed after connection established
Submitted: 2018-08-03 23:02 UTC Modified: 2020-12-11 23:33 UTC
From: php at darkain dot com Assigned:
Status: Verified Package: MySQLi related
PHP Version: 7.2.0 OS: Debian 9
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2018-08-03 23:02 UTC] php at darkain dot com
Description:
------------
The MYSQLI_OPT_READ_TIMEOUT option cannot be changed after a connection is established. This means if it is set to a low value to ensure a stable initial connection to the MySQL server, it cannot be raised back up again before executing long running queries.

Test script:
---------------
<?php
$connection = mysqli_init();

//ensure we receive a reply from mysql in under 1 second
$connection->options(MYSQLI_OPT_READ_TIMEOUT, 1);

//connect to the mysql server
$connection->real_connect($server, $username, $password, $database);

//change timeout to 1 hour so we can run a long query
$connection->options(MYSQLI_OPT_READ_TIMEOUT, 60*60);

//attempt to pull results from a very large table, something that would exceed the initial 1 second timeout value, but not the 1 hour timeout value
$connection->query('SELECT * FROM `huge_table`');


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2018-08-07 07:44 UTC] liyang1025 at gmail dot com
I'm searching the PHP-src source code and find some clue, the MYSQLI_OPT_READ_TIMEOUT option will be only assigned to the php_stream is at the establish connection and then reassign the option value but the php_stream's option is not will be reassigned.
 [2020-12-11 23:05 UTC] dharman@php.net
-Status: Open +Status: Verified -PHP Version: 7.2.8 +PHP Version: 7.2.0
 [2020-12-11 23:05 UTC] dharman@php.net
I can verify that you can only change this before opening the connection. This is the documented behaviour and I am not sure if we are ever going to change it. PRs are welcome if someone has an idea on how to improve it.
 [2020-12-11 23:12 UTC] rtrtrtrtrt at dfdfdfdf dot dfd
expected behavior

//ensure we receive a reply from mysql in under 1 second
$connection->options(MYSQLI_OPT_READ_TIMEOUT, 1);

this makes no sense at all before connect
MYSQLI_OPT_CONNECT_TIMEOUT maybe makes

---------------

https://www.php.net/manual/en/mysqli.options.php

This function may be called multiple times to set several options

mysqli_options() should be called after mysqli_init() and before mysqli_real_connect()
 [2020-12-11 23:33 UTC] php at darkain dot com
Setting this value *BEFORE* connection and then changing it does indeed have a very specific and practical real-world use case.

Having it set BEFORE connect ensures that we have a maximum time-to-first-byte from the server. The MYSQLI_OPT_CONNECT_TIMEOUT option only measures the TCP connection itself, not the traffic going over it. If a TCP connection to a server is established quickly, but then lags significantly sending the first byte of data (or never sends data at all), PHP will hang until data is received, indefinitely.

But after that first byte is received, we may need to relax the timeout if we know we're about to intentionally issue a more lengthy query.

This exact scenario of MySQL not returning data at initial connection, but accepting the TCP connection lead to a DoS of an entire e-commerce platform, because PHP would open connections to the MySQL backend, which established TCP fine (so no connect timeout), but then never returned any bytes sent. PHP hung indefinitely, and as a result, we had a 100% total resource exhaustion due to PHP never closing. This is why I submitted the series of bug reports in the first place.

Alternatively, MYSQLI_OPT_CONNECT_TIMEOUT should be updated to ensure there is the HELLO message from MySQL, not just the initial TCP connection.
 [2020-12-11 23:42 UTC] rtrtrtrtrt at dfdfdfdf dot dfd
> Alternatively, MYSQLI_OPT_CONNECT_TIMEOUT should be 
> updated to ensure there is the HELLO message from MySQL, 
> not just the initial TCP connection

that's the only valid point i would say while it would be interesting how msqli is built (myslqnd or libmysql and if they bahve differently)

i would simply expect that MYSQLI_OPT_CONNECT_TIMEOUT have that hehavior

and the we are here which makes sense:
$connection = mysqli_init();
$connection->options(MYSQLI_OPT_CONNECT_TIMEOUT, 1);
$connection->options(MYSQLI_OPT_READ_TIMEOUT, 60*60);
$connection->real_connect($server, $username, $password, $database);
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Sun Apr 18 19:01:24 2021 UTC