Uploaded image for project: 'MariaDB Connector/Python'
  1. MariaDB Connector/Python
  2. CONPY-206

mariadb connector loses server connection during RETURNING query

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 1.0.11
    • N/A
    • Generic
    • None

    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
      

      Attachments

        Issue Links

          Activity

            zzzeek Mike Bayer added a comment -

            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

            zzzeek Mike Bayer added a comment - 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
            danblack Daniel Black added a comment -

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

            danblack Daniel Black added a comment - Thanks zzzeek , put MDEV-28740 as the server crashing bug, but leaving this open in case there's something incorrect in the python implementation.
            georg Georg Richter added a comment -

            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).

            georg Georg Richter added a comment - 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).
            zzzeek Mike Bayer added a comment -

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

            zzzeek Mike Bayer added a comment - ah I didnt realize the server is actually where the bug is, great thanks for looking into it.
            georg Georg Richter added a comment -

            Closed, since this issue affects Server (MDEV).

            georg Georg Richter added a comment - Closed, since this issue affects Server (MDEV).

            People

              georg Georg Richter
              zzzeek Mike Bayer
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.