Bug #76703 [Com]: MYSQLI_OPT_READ_TIMEOUT cannot be changed after connection established

Edit report at https://bugs.php.net/bug.php?id=76703&edit=1 ID: 76703 Comment by: rtrtrtrtrt at dfdfdfdf dot dfd Reported by: php at darkain dot com Summary: MYSQLI_OPT_READ_TIMEOUT cannot be changed after connection established Status: Verified Type: Bug Package: MySQLi related Operating System: Debian 9 PHP Version: 7.2.0 Block user comment: N Private report: N New Comment: > 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); Previous Comments: ------------------------------------------------------------------------ [2020-12-11 23:33:09] 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:12:13] 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:05:35] 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. ------------------------------------------------------------------------ [2018-08-07 07:44:47] 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. ------------------------------------------------------------------------ [2018-08-03 23:02:14] 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'); ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=76703&edit=1