MySQL Bugs: #115788: Option `connection_timeout` is overwritten and works as "query" timeout instead

Bug #115788	Option `connection_timeout` is overwritten and works as "query" timeout instead
Submitted:	6 Aug 2024 19:27	Modified:	8 Jan 21:57
Reporter:	Oscar Pacheco	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / Python	Severity:	S2 (Serious)
Version:	8.4.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
After opening the connection, the connector sets the connection timeout to None (see https://github.com/mysql/mysql-connector-python/blob/trunk/mysql-connector-python/lib/mysq...

As a result, the connection_timeout specified when creating the connection isn't enforced - basically
https://github.com/mysql/mysql-connector-python/blob/dc71cebe53615110ff00dbb8b629f5457ece1...
is overwritten.

Furthermore, the connection option connection_timeout isn't working as a "connection timeout" but as a "query timeout".

* connection_timeout refers to the time taken to establish a connection between the server and Connector/Python.
* query_timeout refers to the time taken to execute a query by Connector/Python inside an established connection.

How to repeat:
Consider the following (pure-python is used):
```
import mysql.connector

# connection_timeout is in seconds
with mysql.connector.connect(user="root", use_pure=True, connection_timeout=2) as cnx:
    with cnx.cursor() as cur:
        cur.execute("SELECT SLEEP(10)")    # mysql.connector.errors.OperationalError will happen
        print(cur.fetchall())
```

The query lasts 10 seconds, which means we'll get an exception since the connection timeout (which, by the way, is wrongly acting as a query timeout) is set to 2 seconds.

Suggested fix:
Firstly, remove line 380.

Then, you should fix/modify the current "timeout feature" such that we have two timeout options:

* connection_timeout
* query_timeout

You must ensure `connection_timeout` is controlling the time taken to establish a connection between the server and Connector/Python, and query_timeout is controlling the time taken to execute a query by
Connector/Python inside an established connection.

Even though this issue is reported for the pure-python implementation, you must enforce the same for the cext (sync) and the aio implementations.

There is an acceptable workaround for the pure-python implementation to control a query timeout. For instance, while using the "non-async" connector, you could access the protected attribute `socket` and set the wanted timeout, which will impact directly on the queries' timeout.

```
>>> cnx = mysql.connector.connec(**config)
>>> cnx._socket.set_connection_timeout(1)
>>> cnx.query('SELECT SLEEP(10);') # creates a cursor and executes the query
...
mysql.connector.errors.OperationalError: 2055: Lost connection to MySQL server at '127.0.0.1:7306', system error: The read operation timed out
```

Regards.

Improving the snippet, it should be:

```
config - {...} # connection options
with mysql.connector.connec(**config) as cnx:
    cnx._socket.set_connection_timeout(1)  # workaround
    with cnx.cusor() as cur:
        cur.execute('SELECT SLEEP(10);') # executes the query
        _ = cur.fetchone()
```

Posted by developer:
 
The "connection_timeout" option is only going to be used as a timeout in seconds before which the connection with Connector/Python and the MySQL Server should be established. We've added the support for "read_timeout" and "write_timeout" to handle the query timeout scenarios instead.

The option "read_timeout" will be used as the number of seconds up to which the connector's underlying socket should wait for the server to reply back before raising an `ReadTimeoutError`.

The option "write_timeout" will be used as the number of seconds up to which the connector's underlying socket should spend to send data to the server before raising an `WriteTimeoutError`.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/Python 9.2.0 release, and here's the proposed changelog entry from the documentation team:

Added two new connection options: read_timeout (time limit to receive a
response from the server) and write_timeout (time limit to send data to
the server). Both options default to None, which sets the timeout to
indefinitely.

Thank you for the bug report.