mysql 'commands out of sync' error when calling stored procedure

[candlerb]

django 1.3 running under Ubuntu 10.04 LTS x86_64 with python 2.6.5-1ubuntu6, python-mysqldb 1.2.2-10build1, mysql 5.1.41-3ubuntu12.10

Here is a trivial stored procedure:

mysql> delimiter $$
mysql> create procedure dummy()
    -> begin
    -> select 'Foo';
    -> end
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call dummy();
+-----+
| Foo |
+-----+
| Foo |
+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Now try to call it from a django view:

from django.db import connection 
def dummy(request):
    ctx = {}
    c = connection.cursor()
    c.callproc('dummy');
    # OR: c.execute('call dummy()');

    # doesn't get here:
    ctx['result'] = c.fetchall()

    return render_to_response('tools/dummy.html', ctx,
           context_instance=RequestContext(request))

Upon hitting this view I get:

ProgrammingError at /tools/dummy/
(2014, "Commands out of sync; you can't run this command now")

Traceback:

Environment:


Request Method: GET
Request URL: http://192.168.56.2:3000/tools/dummy/

Django Version: 1.3
Python Version: 2.6.5
Installed Applications:
['django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.staticfiles',
 'django.contrib.admin',
 'nup',
 'deploy']
Installed Middleware:
('django.middleware.common.CommonMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.middleware.transaction.TransactionMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware')


Traceback:
File "/usr/local/lib/python2.6/dist-packages/Django-1.3-py2.6.egg/django/core/handlers/base.py" in get_response
  117.                             response = middleware_method(request, e)
File "/usr/local/lib/python2.6/dist-packages/Django-1.3-py2.6.egg/django/middleware/transaction.py" in process_exception
  18.             transaction.rollback()
File "/usr/local/lib/python2.6/dist-packages/Django-1.3-py2.6.egg/django/db/transaction.py" in rollback
  151.     connection.rollback()
File "/usr/local/lib/python2.6/dist-packages/Django-1.3-py2.6.egg/django/db/backends/__init__.py" in rollback
  208.         self._rollback()
File "/usr/local/lib/python2.6/dist-packages/Django-1.3-py2.6.egg/django/db/backends/mysql/base.py" in _rollback
  331.             BaseDatabaseWrapper._rollback(self)
File "/usr/local/lib/python2.6/dist-packages/Django-1.3-py2.6.egg/django/db/backends/__init__.py" in _rollback
  50.             return self.connection.rollback()

Exception Type: ProgrammingError at /tools/dummy/
Exception Value: (2014, "Commands out of sync; you can't run this command now")

The error is clearly coming from within mysql - if I change the stored procedure body to say call nonexistent() then I get the error passed back. It's as soon as I invoke SELECT that I get the out-of-sync error. However, I don't get the error when calling the stored procedure from a django shell:

$ python manage.py shell
Python 2.6.5 (r265:79063, Apr 16 2010, 13:57:41) 
[GCC 4.4.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from django.db import connection
>>> c = connection.cursor()
>>> c.callproc('dummy')
()
>>> c.fetchall()
((u'Foo',),)
>>> 

This suggests that the problem is something to do with middleware. My app uses the following middleware:

MIDDLEWARE_CLASSES = (
    'django.middleware.common.CommonMiddleware',
    'django.contrib.sessions.middleware.SessionMiddleware',
    'django.middleware.csrf.CsrfViewMiddleware',
    'django.contrib.auth.middleware.AuthenticationMiddleware',
    'django.middleware.transaction.TransactionMiddleware',
    'django.contrib.messages.middleware.MessageMiddleware',
)

By experimentation, commenting out MessageMiddleware makes this test application work - but I don't understand why this should be.

[candlerb]

I think I have a solution which doesn't involve removing MessageMiddleware.

After c.fetchall() you must call c.nextset(), even if only one result set is returned. Or more generally:

more = True
while more:
    c.fetchall()
    # Do something with these results
    more = c.nextset()
c.close()

When this is done, the procedure call works as expected. It seems like MessageMiddleware is trying to send some additional commands to mysql while mysql thinks the result set hasn't been fully consumed.

[Aymeric Augustin]

This is a documented behavior of MySQLdb:

Compatibility note: It appears that the mere act of executing the CALL statement produces an empty result set, which appears after any result sets which might be generated by the stored procedure. Thus, you will always need to use nextset() to advance result sets.

(​http://mysql-python.sourceforge.net/MySQLdb.html)

So, even though an exception was raised in Django when it tried to use the database connection again, the bug was in your application code, and you found the correct fix.