Python Mysql, "commands out of sync; you can't run this command now"

user_984003 0
Viewed 70680

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 ;
mysql python
7 Answers

Since I have googled this few times now, landing on the same SO question (here), and none of the answers are helpful, here is protip for myself in the future:

this error is a source of unfetched response from MySQL. That is, we send request, and got something back but didn't care to read it.

req = "UPDATE data SET h1='qwertyuioasdasdasda' WHERE id=1;commit;"

In my case, after removing commit I was able to see this error:

Warning: (1265L, "Data truncated for column 'h1' at row 1")

It was raised because I was trying to write h1 which was too long for data type. However, commit prevents me from getting this information back.

user_28991680

I was getting this error when i used a query to select one column data.. yet did not receive the error when I was selecting multiple columns.

THIS ONE DID NOT WORK

mycursor.execute("SELECT PropID FROM for_sale WHERE redfin_rent_est is NULL")

THIS ONE DID WORK which did not create the commands out of sync error.

mycursor.execute("SELECT PropID, web_url FROM for_sale WHERE redfin_rent_est is NULL")

So apparently its caused my errors in syntax also? I'm still unsure on the cause.

user_153637480

As @Mark Gerolimatos mentioned in this thread`s comments, you cannot execute strings containing multiple sql commands, or even commands followed by a comment.

This will NOT work:

cursor.execute('SELECT 1; SELECT 2;')

cursor.execute('SELECT 1; -- myComment')

This is fine:

cursor.execute('SELECT 1;')
user_67383610
Related