Real error masked somewhere in the library with 2014

[dannystaple]

I've found a reproducible error case where an error that should be simple SQL syntax is masked and made very hard to spot by giving a rather mysterious:

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")"

What I expected:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
_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")

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")

I realise this is bad syntax, but it was generated and from the error I couldn’t tell that that was the problem.

[farcepest]

The real problem here is, you're using the low-level interface and not the DB-API interface. When using the low-level interface, you're responsible for fetching the result set after each query.

Read PEP-249 and try again.

[dannystaple]

I'd reduced it to the simplest possible reproducing code. I'll set up a local environment on my current machine, and see if I can reproduce it using a cursor and execute - if I recall correctly, I did see it with using that method too.

[farcepest]

This was simpler than the simplest possible example.

Your queries don't need statement terminators either, e.g. the trailing semicolon.

[dannystaple]

Ok - I have confirmed - I can reproduce the same behaviour using a cursor.

cu = conn.cursor()
cu.execute("create table foo(bar int(11))")
cu.execute("insert into foo values (1););")
cu.execute("insert into foo values (2););")

Will also result in _mysql_exceptions.ProgrammingError: (2014, "Commands out of sync; you can't run this command now").

[farcepest]

No, it's working correctly. Your semicolons are creating multiple statements, so multiple result sets are returned. Your first insert works. Never mind that insert returns no rows: Every MySQL statement returns (at least) one result set; in this case, an empty (zero rows) result set. Internally, MySQLdb retrieves a result set after each .execute () call. This query returns two result sets, but the second is not retrieved; .nextset () does this. When the second insert statement is encountered, there is still a pending result set; thus the error which arises from the MySQL C API.

Try disabling multi statement support and you'll get the expected result.

[dannystaple]

My sample could be simpler - the last query doesn't need to do anything other than "select 1".
It seems that this could be a trap for someone parsing and altering large mysql dump outputs.