django-pyodbc-azure SQL Server Parameter Limitation

Question

Django: 1.11

I am using django-pyodbc-azure as my Django backend. It is connecting to SQL Server 2012. SQL Server has a limit of 2,100 parameters. When I attempt to use prefetch_related on a queryset that will return more than 2,100 results...

def object_search_list(request):
    objects = MyModel.objects.filter(some_field='filter value').select_related('another_field').prefetch_related('a_third_field')
    print(objects)
    return render(request, 'objects/object_list.html', {'objects':objects})

...this error is returned:

Exception Value: ('07002', '[07002] [Microsoft][SQL Server Native Client 11.0]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')

It points to the pyodbc backend used by django-pyodbc-azure. When looking at the features (https://github.com/michiya/django-pyodbc-azure/blob/azure-2.1/sql_server/pyodbc/features.py), there is no max_query_params set. I attempted to manually add max_query_params = 2100. This resulted in the same error. Setting that value to 2000 and 1000 resulted in the same error also.

How do I adjust django-pyodbc-azure to automatically handle this SQL Server limitation?

The Oracle backend for Django has a feature like this: https://github.com/django/django/blob/master/django/db/backends/oracle/features.py

UPDATE:

I realized that the max_query_params name was new to Django 2.0. Therefore, I tried the max_limit_in_size to match Django 1.11. Is it simply that django-pyodbc-azure does not support controlling parameter counts and the base.py overrides Django's default base.py?

UPDATE 2:

I upgraded to Django 2.1 and upgraded django-pyodbc-azure to match. I then edited features.py to add max_query_params = 2000 as a feature. The other place where max_query_params appears in Django native backends is operations.py in this function (Oracle example):

def bulk_batch_size(self, fields, objs):
    """Oracle restricts the number of parameters in a query."""
    if fields:
        return self.connection.features.max_query_params // len(fields)
    return len(objs)

django-pyodbc-azure has this function also, and it looks like this:

def bulk_batch_size(self, fields, objs):
    """
    Returns the maximum allowed batch size for the backend. The fields
    are the fields going to be inserted in the batch, the objs contains
    all the objects to be inserted.
    """
    objs_len, fields_len, max_row_values = len(objs), len(fields), 1000
    if (objs_len * fields_len) <= max_row_values:
        size = objs_len
    else:
        size = max_row_values // fields_len
    return size

It seems like it would already take care of batching with max_row_values set to 1000. However, the same error persists. I also tried changing the 1000 to max_query_params to no avail.

Here's the full traceback:

Traceback:

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\backends\utils.py" in _execute
  85.                 return self.cursor.execute(sql, params)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\sql_server\pyodbc\base.py" in execute
  546.             return self.cursor.execute(sql, params)

The above exception (('07002', '[07002] [Microsoft][SQL Server Native Client 11.0]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')) was the direct cause of the following exception:

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\core\handlers\exception.py" in inner
  34.             response = get_response(request)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\core\handlers\base.py" in _get_response
  126.                 response = self.process_exception_by_middleware(e, request)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\core\handlers\base.py" in _get_response
  124.                 response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\contrib\auth\decorators.py" in _wrapped_view
  21.                 return view_func(request, *args, **kwargs)

File "C:\Users\user\djangoproject\app\views.py" in object_list
  486.     return render(request, 'objects/object_list.html', {'objects':objects})

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\shortcuts.py" in render
  36.     content = loader.render_to_string(template_name, context, request, using=using)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\loader.py" in render_to_string
  62.     return template.render(context, request)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\backends\django.py" in render
  61.             return self.template.render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\template_timings_panel\panels\TemplateTimings.py" in timing_hook
  139.         result = func(self, *args, **kwargs)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\base.py" in render
  171.                     return self._render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\test\utils.py" in instrumented_test_render
  96.     return self.nodelist.render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\base.py" in render
  937.                 bit = node.render_annotated(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\base.py" in render_annotated
  904.             return self.render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\loader_tags.py" in render
  150.             return compiled_parent._render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\test\utils.py" in instrumented_test_render
  96.     return self.nodelist.render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\base.py" in render
  937.                 bit = node.render_annotated(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\base.py" in render_annotated
  904.             return self.render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\template_timings_panel\panels\TemplateTimings.py" in timing_hook
  139.         result = func(self, *args, **kwargs)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\loader_tags.py" in render
  62.                 result = block.nodelist.render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\base.py" in render
  937.                 bit = node.render_annotated(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\base.py" in render_annotated
  904.             return self.render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\loader_tags.py" in render
  188.             return template.render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\template_timings_panel\panels\TemplateTimings.py" in timing_hook
  139.         result = func(self, *args, **kwargs)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\base.py" in render
  173.                 return self._render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\test\utils.py" in instrumented_test_render
  96.     return self.nodelist.render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\base.py" in render
  937.                 bit = node.render_annotated(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\base.py" in render_annotated
  904.             return self.render(context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\template\defaulttags.py" in render
  166.             len_values = len(values)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\models\query.py" in __len__
  250.         self._fetch_all()

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\models\query.py" in _fetch_all
  1188.             self._prefetch_related_objects()

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\models\query.py" in _prefetch_related_objects
  723.         prefetch_related_objects(self._result_cache, *self._prefetch_related_lookups)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\models\query.py" in prefetch_related_objects
  1569.                 obj_list, additional_lookups = prefetch_one_level(obj_list, prefetcher, lookup, level)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\models\query.py" in prefetch_one_level
  1699.     all_related_objects = list(rel_qs)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\models\query.py" in __iter__
  268.         self._fetch_all()

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\models\query.py" in _fetch_all
  1186.             self._result_cache = list(self._iterable_class(self))

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\models\query.py" in __iter__
  54.         results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\models\sql\compiler.py" in execute_sql
  1065.             cursor.execute(sql, params)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\debug_toolbar\panels\sql\tracking.py" in execute
  186.         return self._record(self.cursor.execute, sql, params)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\debug_toolbar\panels\sql\tracking.py" in _record
  124.             return method(sql, params)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\backends\utils.py" in execute
  100.             return super().execute(sql, params)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\backends\utils.py" in execute
  68.         return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\backends\utils.py" in _execute_with_wrappers
  77.         return executor(sql, params, many, context)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\backends\utils.py" in _execute
  85.                 return self.cursor.execute(sql, params)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\utils.py" in __exit__
  89.                 raise dj_exc_value.with_traceback(traceback) from exc_value

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\django\db\backends\utils.py" in _execute
  85.                 return self.cursor.execute(sql, params)

File "C:\Users\user\installed\anaconda\envs\django2\lib\site-packages\sql_server\pyodbc\base.py" in execute
  546.             return self.cursor.execute(sql, params)

Exception Type: Error at url
Exception Value: ('07002', '[07002] [Microsoft][SQL Server Native Client 11.0]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')

score 2 · Answer 1 · answered Dec 19 '18 at 22:52

2

It doesn't look like it has been added to django-pyodbc-azure:

https://github.com/michiya/django-pyodbc-azure/blob/azure-2.1/sql_server/pyodbc/features.py#L4

I've added it to my fork here:

https://github.com/FlipperPA/django-pyodbc-azure/blob/azure-2.1/sql_server/pyodbc/features.py#L33

Can you give it a test? You'll need to upgrade to Django 2.1, and then you should be able to pip install git+https://github.com/FlipperPA/django-pyodbc-azure.git@azure-2.1 to see if it is as straight forward as adding that setting. At a cursory glance of Django's source, it looks like that might be all it takes. If it works, I'll issue Michaya a pull request.

answered Dec 19 '18 at 22:52

FlipperPA

13,607
4
39
71

so I tried editing the `django-pyodbc-azure` files with the Django 1.11 version of this (`max_limit_in_size`) and it didn't work with that bit of editing. I'll work on upgrading to 2.1 and installing your fork (or just editing the `features.py` from Michaya's repo) – OverflowingTheGlass Dec 20 '18 at 17:15
This doesn't seem to work. I'm getting the same error after editing `features.py` to add the `max_query_params = 2000` line – OverflowingTheGlass Jan 02 '19 at 19:57
Check out my update to my question - added some detail – OverflowingTheGlass Jan 02 '19 at 20:07
At this point, I'd open it up as an issue on the `django-pyodbc-azure` site; since you have a working test case, it might help Michaya out. I'm guessing he may have coded this up but not had a working example or test case to test. Good luck! – FlipperPA Jan 03 '19 at 01:43
I posted this as an issue - it looks like a related issue was brought up last year, and it may be a limitation with Django's `prefetch_related` as well. On a side note, do you have any direct contact with Michaya? Fully understand if the time is not there, but issues are piling up, and Microsoft's PM for drivers posted an issue in January asking about the state of the project. I think there are a lot of people relying on this project - any insight you might have would be greatly appreciated! – OverflowingTheGlass Mar 25 '19 at 21:16
1

I don't, unfortunately, but several at Microsoft have his contact information. I've been close to making a fork a few times, but have been using PostgreSQL a lot more than SQL Server lately. Let's see if Michaya resurfaces when v2.2 pops up. – FlipperPA Mar 25 '19 at 23:20
You appear to be pretty active with this project on here and on GitHub, so I, selfishly, would love if you made a fork. I don't think I have the expertise to do much heavy lifting, but I would be up for assisting in any way I can . – OverflowingTheGlass Mar 27 '19 at 17:22
1

@OverflowingTheGlass We've started a fork here to bring this into Django core: https://github.com/FlipperPA/django-mssql-backend/issues Did you ever resolve this issue? I'd love to have support for `max_query_params` in the release. – FlipperPA Aug 11 '19 at 11:52
I did not - I ended up switching to server-side processing since my volume would have eventually been too big for `max_query_params` anyway. Great news on the fork! Really glad you are taking it on - I will take a look at the project and see if there's anything I could help with. – OverflowingTheGlass Aug 12 '19 at 14:25

django-pyodbc-azure SQL Server Parameter Limitation

1 Answers1