Bug #110641 MySQL Connection not available after a procedure call

• Submitted: 9 Apr 2023 6:31
• Modified: 10 Apr 2023 11:56
• Reporter: Wenqian Deng
• Status: Verified
• Category: Connector / Python
• Severity: S3 (Non-critical)
• Version: 8.0.32
• OS: Any
• CPU Architecture: Any

[9 Apr 2023 6:31] Wenqian Deng

Description:
When I used cursor.execute to call a stored procedure in the database, a confusing phenomenon occurred: the connection between the client and MySQL seemed to be disconnected, and an mysql.connector.errors.OperationalError was thrown in the subsequent execution.

How to repeat:
def execute(con, sql):
    try:
        cursor = con.cursor()
        cursor.execute(sql)
        cursor.close()
    except Exception as e:
        print(e)

conn_params = {
    "user": "user",
    "password": password",
    "host": "127.0.0.1",
    "port": 3306
}

con = mysql.connector.connect(**conn_params)
execute(con, "DROP DATABASE IF EXISTS test")
execute(con, "CREATE DATABASE test")
execute(con, "USE test")
execute(con, "CREATE TABLE t0(c0 FLOAT UNIQUE)");
execute(con, "CREATE PROCEDURE `t0_select_all`() BEGIN SELECT * FROM t0; END;");
execute(con, "INSERT INTO t0 VALUES (1234)")

# execute call
cursor = con.cursor()
cursor.execute("call `t0_select_all`()")
for row in cursor:
    print(*row, sep=' * ')
cursor.close()

# mysql.connector.errors.OperationalError: MySQL Connection not available.
cursor1 = con.cursor()

con.commit()
con.close()

[10 Apr 2023 11:56] MySQL Verification Team

Hello Wenqian Deng,

Thank you for the report and test case.

regards,
Umesh

[12 Apr 2023 11:57] Nuno Mariz

Posted by developer:
 
Thank you for your bug report.
The recommended way of calling a stored procedure is using `cursor.callproc()`.

Using `cursor.execute()` your implementation will not work, it should be something like:

  for res in cursor.execute("CALL t0_select_all()", multi=True):
      if res.with_rows:
          print(res.fetchall())

Although the above code works, this is the recommended way of calling stored procedures:

  cursor.callproc("t0_select_all")
  for res in cursor.stored_results():
      print(res.fetchall())

Nonetheless, when getting a new cursor it shouldn't raise any error, and needs investigation on our side.