Python ", commands are not synchronized, you cannot run this command now"

Question

I have a MySQL stored procedure that runs from Python (wrapped in Django). I get the error "commands from synchronization, you cannot run this command now" when I try to execute the second statement. I cannot complete a transaction at this time. This is only a problem 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 was asked to submit a MySQL procedure. I 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 Joshua Boshi for his answer that solved the problem. After calling the procedure, I had to close the cursor and reopen it 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.

Answer 2

The main reason for this is the results that are not taken from the cursor before a new request is made. There may be several result sets.

To stop the error, you have to make sure that every time you use the result set with .nextset. If it creates several result sets, you may need to make several 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 could be an indicator of other problems in sql queries that are not selected as python errors, until a subsequent request is made. They created several 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 that was confused with the code) leads to this semicolon and then to a new statement - this leads to many sets of results. This will not result in an error for this statement, but for the next one, which is trying to run a command over the cursor.

I did a github repo - https://github.com/odmsolutions/mysql_python_out_of_sync_demo - to demonstrate and verify 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 three lines of code:

The minimum case to reproduce this is: (suppose you have an empty db, and only created a connection to 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") 

This is the bad syntax that was generated, and due to an error, I could not say that it was a problem.

Try to study the last query or procedure, as Bukzor suggests, and try to run it in a raw mysql client to see the real problem.

Answer 3

I encountered this error while running the following Python code (2.7):

  def _execute(self, query, params, is_destructive): try: cursor = self.con.cursor() cursor.execute(query, params) if is_destructive: self.con.commit() return cursor.fetchall() finally: cursor.close() 

The problem is with calling self.con.commit (). We were protective and encoded to perform destructive operations (operations) before closing the cursor. MySql did not like this. Removing the commit () method resolved the issue, and the script was still running using cursor.fetchall ()

Answer 4

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

Something kills your connection between statements. This is probably a mistake in your procedure. Check mysql error log.