id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
17289	mysql 'commands out of sync' error when calling stored procedure	candlerb	nobody	"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."	Uncategorized	closed	Uncategorized	1.3	Normal	invalid			Unreviewed	0	0	0	0	0	0