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

Question

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 ; 

Answer 1

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.

Answer 2

The main cause of this is results that are not taken from the cursor before a new query is made. There may be multiple result sets.

To stop the error you must ensure you consume the result set each time with .nextset. If it produces multiple result sets- you may even need to do a few of them.

cursor.callproc('my_mysql_procedure', [some_id,])  result = cursor.fetchall() for r in result:     do something cursor.nextset() cursor.execute("select * from some_table") result = cursor.fetchall() 

In my case, I found that this can actually be an indicator of other problems in the sql queries that aren't picked out as python errors until a subsequent query is made. They produced multiple result sets.

Applying this to my example below (where I saw the same error),

>>> import MySQLdb >>> conn = MySQLdb.connect(passwd="root", db="test") >>> cur = conn.cursor() >>> cur.execute("insert into foo values (1););") 1L >>> cur.nextset() Traceback (most recent call last):   File "<stdin>", line 1, in <module>   File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 107, in nextset     nr = db.next_result() _mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 

Here - because a parsing error (some trusted input data, which was munged with code) lead to that semicolon and then a new statement - this produced multiple result sets. It will not produce an error for this statement, but for the next one that attempts to run a command on the cursor.

I've made a github repo - https://github.com/odmsolutions/mysql_python_out_of_sync_demo - to demonstrate and test this.

---

Original answer: Take a look at https://github.com/farcepest/MySQLdb1/issues/28 for details on how I was able to reliably reproduce this with 3 lines of code:

Minimal case to reproduce this: (assume you have a blank db, and have created only a connection to the db, called conn)

>>> conn.query("Create table foo(bar int(11))") >>> conn.query("insert into foo values (1););") >>> conn.query("insert into foo values (2););") Traceback (most recent call last):   File "<stdin>", line 1, in <module> _mysql_exceptions.ProgrammingError: (2014, "Commands out of sync; you can't run this command now") 

It is bad syntax which was generated and from the error I couldn’t tell that that was the problem.

Try examining the last query or procedure as Bukzor suggests - and try running it in a raw mysql client to see the real problem.