Asked  12 Months ago    Answers:  5   Viewed   14 times

I have a MySQL stored procedure that is executed from Python (wrapped in Django). I get the error "commands out of sync; you can't run this command now" when I try to execute the second statement. I cannot commit the transaction at this point. This is only an issue when I call a procedure. What to do?

cursor.callproc('my_mysql_procedure', [some_id,]) 
result = cursor.fetchall()
for r in result:
    do something

cursor.execute("select * from some_table")
result = cursor.fetchall()

EDIT: I've been asked to post the MySQL procedure. I have made it super-simple and I still see the same problem

delimiter $$
create procedure my_mysql_procedure(p_page_id int)
    begin

        select 1
        from dual; 

    end$$
delimiter ;

 Answers

4

Thanks to JoshuaBoshi for his answer, which solved the problem. After calling the procedure, I had to close the cursor and open it again before using it to execute another statement:

cursor.close() 

cursor = connection.cursor() 

The cursor can be closed immediately after fetchall(). The result set still remains and can be looped through.

Wednesday, June 30, 2021
 
Elxx
 
4

DB-API tries to handle transactions on its own, starting a transaction on the first command and having its own API call to commit it, so:

cursor.execute( "CREATE TABLE t1 ( t1_id INT PRIMARY KEY AUTO_INCREMENT )" )
cursor.commit()
cursor.execute( "CREATE TABLE t2 ( t2_id INT PRIMARY KEY AUTO_INCREMENT )" )
cursor.commit()

In my opinion, this is a serious, glaring design error of Python's DB-API, making it a serious hassle to execute commands outside of transactions and to have proper control over transactions, eg. to use things like SQLite's BEGIN EXCLUSIVE TRANSACTION. It's as if someone with no real database experience was allowed to design the API...

Thursday, July 29, 2021
 
Raef
 
5

A possible solution involves subclassing the MySQLCursor class like this:

class MySQLCursorDict(mysql.connector.cursor.MySQLCursor):
    def _row_to_python(self, rowdata, desc=None):
        row = super(MySQLCursorDict, self)._row_to_python(rowdata, desc)
        if row:
            return dict(zip(self.column_names, row))
        return None

db = mysql.connector.connect(user='root', database='test')

cursor = db.cursor(cursor_class=MySQLCursorDict)

Now the _row_to_python() method returns a dictionary instead of a tuple.

I found this on the mysql forum, and I believe it was posted by the mysql developers themselves. I hope they add it to the mysql connector package some day.

I tested this and it does work.

UPDATE: As mentioned below by Karl M.W... this subclass is no longer needed in v2 of the mysql.connector. The mysql.connector has been updated and now you can use the following option to enable a dictionary cursor.

cursor = db.cursor(dictionary=True)
Friday, August 13, 2021
 
5

I was just asking myself similar question and I think I found the "fresh" answer. Here is info from two weeks ago: https://github.com/pypa/warehouse/issues/5537

It seems that currently a long-inactive person is owner of the mysql-connector name, hence MySQL developers release under name mysql-connector-python.

Friday, September 17, 2021
 
5

Finally, I fixed this issue. My app has multithread to use the same connection, it seems is not a proper way to access mysql, so when I do not share connection, the issue is gone.

Under 'threadSafety' in the MySQLdb User Guide:

The MySQL protocol can not handle multiple threads using the same connection at once. Some earlier versions of MySQLdb utilized locking to achieve a threadsafety of 2. While this is not terribly hard to accomplish using the standard Cursor class (which uses mysql_store_result()), it is complicated by SSCursor (which uses mysql_use_result(); with the latter you must ensure all the rows have been read before another query can be executed. It is further complicated by the addition of transactions, since transactions start when a cursor execute a query, but end when COMMIT or ROLLBACK is executed by the Connection object. Two threads simply cannot share a connection while a transaction is in progress, in addition to not being able to share it during query execution. This excessively complicated the code to the point where it just isn't worth it.

The general upshot of this is: Don't share connections between threads. It's really not worth your effort or mine, and in the end, will probably hurt performance, since the MySQL server runs a separate thread for each connection. You can certainly do things like cache connections in a pool, and give those connections to one thread at a time. If you let two threads use a connection simultaneously, the MySQL client library will probably upchuck and die. You have been warned.

Friday, October 15, 2021
 
david.s
 
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share