[CONPY-152] INSERT statement with VALUES(DEFAULT) not accepted by cursor.executemany() Created: 2021-04-13  Updated: 2023-06-28

Status: Open
Project: MariaDB Connector/Python
Component/s: DBAPI 2.0
Affects Version/s: 1.0.6
Fix Version/s: None

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

linux w Server version: 10.4.17-MariaDB MariaDB Server


 Description   

In SQLAlchemy we are attempting to expand support for inserting rows without any parameters; only the table defaults and/or AUTO_INCREMENT are invoked. mariadb seems to fail when the VALUES(DEFAULT) syntax is used with cursor.executemany() only. The script below illustrates the failure, contrasting to the pymysql driver which like all other drivers tested does not have this issue:

import pymysql
 
import mariadb
 
 
 
 
 
def run_test(conn):
 
    print("RUNNING AGAINST: %s" % conn)
 
    cursor = conn.cursor()
 
 
 
    cursor.execute("DROP TABLE IF EXISTS a")
 
 
 
    cursor.execute(
 
        "CREATE TABLE IF NOT EXISTS a(id INTEGER PRIMARY KEY AUTO_INCREMENT)"
 
    )
 
 
 
    # illustrate the syntax
 
    cursor.execute(
 
        "INSERT INTO a (id) VALUES (DEFAULT)",
 
        (),
 
    )
 
 
 
    # now run with executemany
 
    try:
 
        cursor.executemany(
 
            "INSERT INTO a (id) VALUES (DEFAULT)",
 
            [(), (), ()],
 
        )
 
    except Exception as err:
 
        print("FAILED! %s" % err)
 
    else:
 
        print("SUCCEEDED!")
 
 
 
    conn.rollback()
 
 
 
 
 
pconn = pymysql.connect(
 
    user="scott", password="tiger", host="localhost", db="test"
 
)
 
mconn = mariadb.connect(
 
    user="scott", password="tiger", host="localhost", db="test"
 
)
 
 
 
 
 
run_test(pconn)
 
 
 
run_test(mconn)

output:

RUNNING AGAINST: <pymysql.connections.Connection object at 0x7f2ce057fcd0>
 
SUCCEEDED!
 
RUNNING AGAINST: <mariadb.connection object at 0x7f2ce0493b40>
 
FAILED! Invalid number of parameters in row 1
 
 



 Comments   
Comment by Georg Richter [ 2021-04-14 ]

Hello Mike,

thank you for the report.

Currently both Connector/C and Connector/Python check if parameters are present and will report an error, if the number is zero.
Sending no parameters is not possible due to protocol restrictions (see Client-Server Protocol (COM_STMT_BULK_EXECUTE): since the client doesn't send any data, the server cannot determine the number of rows - unfortunately the iteration count field from COM_STMT_EXECUTE was removed in COM_STMT_BULK_EXECUTE.

I see 3 solutions for this problem:

a) Fixing client/server protocol, which will will likely not happen before 10.7

b) Adding a workaround in Connector/Python: In case the statement doesn't contain any parameter, it will be executed in a loop.

c) by rewriting the statement (I didn't check if or how it is possible in SQL alchemy dialect for MariaDB Connector/Python),e.g.

cursor.executemany("insert into t1 values (?)", [(INDICATOR.DEFAULT,), (INDICATOR.DEFAULT,), (INDICATOR.DEFAULT,)])

or executing in a loop (as mention in b.)

Comment by Mike Bayer [ 2021-04-14 ]

this test script works with mysqlclient as well which is using the MySQL C api, supported by Mariadb.

Is mariadbconnector using a different C api, and if so shouldn't this C api be a superset of the original MySQL C api at least as far as SQL understood by the database?

Comment by Georg Richter [ 2021-04-15 ]

For executemany() MariaDB Connector/Python uses binary protocol, which is depending on the connection type (unix_socket,wire) up to 8 times faster. While in text protocol an insert statement can be rewritten by using a multi value syntax, update and delete statements have to be executed in a loop. In MariaDB binary protocol executemany() always sends one packet together will all data to the server.

Comment by Mike Bayer [ 2021-04-15 ]

we have no urgent need for this issue to move forward, so if the protocol could eventually be improved that would be best.

Comment by markus makela [ 2023-06-28 ]

This could be fixed by using the feature added in CONC-533 and sending an empty COM_STMT_EXECUTE for each empty tuple.

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