[CONPY-206] mariadb connector loses server connection during RETURNING query Created: 2022-06-02  Updated: 2022-07-31  Resolved: 2022-07-31

Status: Closed
Project: MariaDB Connector/Python
Component/s: Generic
Affects Version/s: 1.0.11
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Mike Bayer Assignee: Georg Richter
Resolution: Fixed Votes: 0
Labels: None
Environment:

Python 3.10.0 (default, Nov 5 2021, 17:23:47) [GCC 11.2.1 20210728 (Red Hat 11.2.1-1)] on linux

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.5.13-MariaDB MariaDB Server

Issue Links:
Problem/Incident
causes MDEV-28740 crash in INSERT RETURNING subquery in... Closed

 Description   

the following test runs an INSERT..RETURNING that retrieves a value from a related "sequence" table. on mariadb-connector, it crashes with mariadb.InterfaceError: Lost connection to MySQL server during query. The same tests succeeds with most other major MySQL drivers, mysqlclient, pymysql (this is for SQLAlchemy). The test case below runs first on mysqlclient as a demo and then on mariadb-connector where it crashes.

import mariadb
 
import MySQLdb
 
 
 
 
 
def run_test(conn, p):
 
    cursor = conn.cursor()
 
 
 
    cursor.execute("DROP TABLE IF EXISTS t2")
 
    cursor.execute("DROP TABLE IF EXISTS t1")
 
 
 
    cursor.execute(
 
        """
 
    CREATE TABLE t2 (
 
        nextid INTEGER
 
    )ENGINE=MyISAM
 
    """
 
    )
 
 
 
    cursor.execute(
 
        """
 
    CREATE TABLE t1 (
 
        id INTEGER NOT NULL,
 
        data VARCHAR(30),
 
        PRIMARY KEY (id)
 
    )ENGINE=MyISAM
 
    """
 
    )
 
 
 
    cursor.execute(f"""INSERT INTO t2 (nextid) VALUES ({p})""", (1,))
 
 
 
    cursor.execute(
 
        f"""
 
        INSERT INTO t1 (id, data) VALUES
 
        ((SELECT max(t2.nextid) AS max_1 FROM t2), {p}) RETURNING t1.id""",
 
        ("hi",),
 
    )
 
    rows = cursor.fetchall()
 
    cursor.close()
 
 
 
    conn.close()
 
 
 
    print(f"Connection {conn} succeeded")
 
 
 
 
 
mysqldb_conn = MySQLdb.connect(
 
    user="scott", password="tiger", host="localhost", db="test"
 
)
 
mariadb_conn = mariadb.connect(
 
    user="scott", password="tiger", host="localhost", db="test"
 
)
 
 
 
 
 
run_test(mysqldb_conn, "%s")
 
run_test(mariadb_conn, "?")

output:

Connection <_mysql.connection closed at 0xc00b40> succeeded
 
Traceback (most recent call last):
 
  File "/home/classic/dev/sqlalchemy/test3.py", line 54, in <module>
 
    run_test(mariadb_conn, "?")
 
  File "/home/classic/dev/sqlalchemy/test3.py", line 31, in run_test
 
    cursor.execute(
 
mariadb.InterfaceError: Lost connection to MySQL server during query



 Comments   
Comment by Mike Bayer [ 2022-06-02 ]

seems to be any subquery, not just with a separate table , it needs to have parameters present

import mariadb
 
import MySQLdb
 
 
 
 
 
def run_test(conn, p):
 
    cursor = conn.cursor()
 
 
 
    cursor.execute("DROP TABLE IF EXISTS t1")
 
 
 
    cursor.execute(
 
        """
 
    CREATE TABLE t1 (
 
        id INTEGER NOT NULL,
 
        data VARCHAR(30),
 
        PRIMARY KEY (id)
 
    )ENGINE=MyISAM
 
    """
 
    )
 
 
 
    cursor.execute(
 
        f"""
 
        INSERT INTO t1 (id, data) VALUES
 
        ((SELECT CAST(1 AS SIGNED INTEGER) AS anon_1), {p}) RETURNING t1.id""",
 
        ("hi",),
 
    )
 
    rows = cursor.fetchall()
 
    cursor.close()
 
 
 
    conn.close()
 
 
 
    print(f"Connection {conn} succeeded")
 
 
 
 
 
mysqldb_conn = MySQLdb.connect(
 
    user="scott", password="tiger", host="localhost", db="test"
 
)
 
mariadb_conn = mariadb.connect(
 
    user="scott", password="tiger", host="localhost", db="test"
 
)
 
 
 
 
 
run_test(mysqldb_conn, "%s")
 
run_test(mariadb_conn, "?")

same result

Comment by Daniel Black [ 2022-06-03 ]

Thanks zzzeek, put MDEV-28740 as the server crashing bug, but leaving this open in case there's something incorrect in the python implementation.

Comment by Georg Richter [ 2022-06-03 ]

Server crashes only with 1.0 branch, not with 1.1. The difference between these branches is, that 1.0 uses binary protocol, while 1.1 uses text protocol (since there are no parameters to substitute).

Comment by Mike Bayer [ 2022-06-03 ]

ah I didnt realize the server is actually where the bug is, great thanks for looking into it.

Comment by Georg Richter [ 2022-07-31 ]

Closed, since this issue affects Server (MDEV).

Generated at Thu Feb 08 03:31:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.