Unavoidable commands out of sync on interrupt during execute

[charettes]

Describe the bug

It is well known that MySQL bindings require that results associated with an executed query must all be fetched or discarded before a second query is attempted otherwise the connection will be stuck in a Commands out sync; you can't run this command now state.

In other words, calling a procedure without fetching all associated results or calling execute with multiple statements separated with a ; and trying to reuse the connection for follow up queries will reproduce the state described above. This is fine and expected to be handled by mysqlclient users.

One thing that the library fails to account for though is the fact that an interrupt signal that relies on exceptions (think of CTRL + C which results in KeyboardInterupt or any other signal handler could happen during the blocking db.query call and break the symmetry of the _do_get_result call leaving the connection in an unusable state.

When this occurs one might be tempted to write code along the lines of

with connection.cursor() as cursor():
    try:
        cursor.execute(query)
    except BaseException:
        cursor.fetchall()  # or event cursor._discard()
        raise
    results = cursor.fetchall()

To keep the connection in a reusable state but that's not possible as .fetchall() cannot be called before _do_get_result and self._executed is assigned.

So my question to you all, is there something that can be done at the Cursor level to prevent connections from getting into this state when a non-database exception is raised before _do_get_result is called or is the only solution to terminate the connection?

• Note that the usage of the MySQL sleep function and KeyboardInterrupt are solely for demonstration and the local reproducibility purposes of this report. In a real life scenario the query could be any query that happens to run at the time an interrupt is signalled by a supervisor in charge of managing Python processes (e.g. think of container orchestration or Celery + Billiard soft limit handling via SoftTimeLimitExceeded)

Environment

No response

How did you install libmysqlclient libraries?

brew

What version of mysqlclient do you use?

2.20

Docker command to start MySQL server

docker run -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -p 3306:3306 --rm --name mysql mysql:8.0

Minimum but complete code to reproduce

# Write Python code here.
import MySQLdb

conn = MySQLdb.connect(host='127.0.0.1', port=3306, user='root')
try:
    with conn.cursor() as cursor:
        print(
            "About to sleep, issue a (CTRL | CMD) + C now and notice "
            "how its blocked by query execution"
        )
        cursor.execute("SELECT sleep(10)")  
        cursor.fetchall()
except Exception as exc:
    print(f"Query failed: {exc}")
# Even if the query completed successfully, the fact `KeyboardInterrupt` was raised
# after db.query(q) but before _do_get_result leaves the connection in a state where
# it has dangling results by the time `cursor.close()` is called and thus it become
# unusable.
conn.ping() # ProgrammingError: (2014, "Commands of of sync; you can't run this command now")
...

Schema and initial data required to reproduce.

No response

Commands, and any other step required to reproduce your issue.

Run the above Python code as standalone script on in a Python terminal and issue an interrupt to notice how the connection is left in an unusable state.

[methane]

is the only solution to terminate the connection?

Yes. it is the only solution.
It is too difficult to write program that keeps in consistent state even when the interrupt happens at arbitrary timing. I can not write/test/maintain such programs.

[charettes]

Far enough, I got a sense that this was possible given PyMySQL/PyMySQL doesn't not exhibit this behaviour.

For anyone who want to keep the connection open at all cost I've managed to maintain the successful query / result fetching symmetry by doing

diff --git a/src/MySQLdb/cursors.py b/src/MySQLdb/cursors.py
index 70fbeea..3fdb57d 100644
--- a/src/MySQLdb/cursors.py
+++ b/src/MySQLdb/cursors.py
@@ -327,7 +327,15 @@ def _query(self, q):
         self._result = None
         self.rowcount = None
         self.lastrowid = None
-        db.query(q)
+        try:
+            db.query(q)
+        except BaseException as exc:
+            # Maintain successful query / result fetching in-sync
+            # to allow further connection usage when an unexpected
+            # exception is encountered (e.g. a system interrupt).
+            if not isinstance(exc, self.Error):
+                self._do_get_result(db)
+            raise
         self._do_get_result(db)
         self._post_get_result()
         self._executed = q