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

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.

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.

This is not a python issue, but a mysql issue. Do the same from perl and you will get the same error. In general, I see that message when I run a mysql console, then kill it from another console, then try to run a query from the killed console.

Something is killing your connection between statements. It's likely an error in your procedure. Check your mysql error log.