While writing a simple Python script to access and process data in a database, I came across an error that said:
1 | Error 2014: Commands out of sync; you can't run this command now |
After quite a bit of Googling and with very little findings, I had to dive in a little and try to figure out what was going on. The whole error looked like this:
1 2 3 4 5 6 7 8 9 10 11 | [elubow@web7 scripts]$ ./2014_test.py Traceback (most recent call last): File "build/bdist.linux-x86_64/egg/MySQLdb/cursors.py", line 173, in execute self.errorhandler(self, exc, value) File "build/bdist.linux-x86_64/egg/MySQLdb/connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue ProgrammingError: (2014, "Commands out of sync; you can't run this command now") Error 2014: Commands out of sync; you can't run this command now Exception _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 'test_table' at line 1") in <bound method Cursor.__del__ of <mySQLdb.cursors.Cursor object at 0x2aac002f9d90>> ignored </bound> |
The issue turned out to be a very simple one to fix. Apparently MySQLdb doesn’t support compound MySQL statements. That is to say that this won’t work:
1 | cursor.execute('SELECT COUNT(*) FROM people; SHOW TABLES LIKE addresses;') |
But this will work:
1 2 3 4 | cursor.execute("SELECT COUNT(*) FROM people") cursor.close() cursor = conn.cursor() cursor.execute("SHOW TABLES LIKE addresses") |
Make sure you break up your SQL statements into separate execute statements and you should avoid this MySQL 2014 error.