loading notifications raise OperationalError MySQL join limit

[truhlik]

Hi,

during my development after couple dozens of notification suddenly I was getting this exception. Have you ever met with this problem?

Method: GET
Request URL: http://localhost:8000/backend/estates/tour/

Django Version: 1.9.6
Python Version: 2.7.11

Template error:
In template /home/truhlik/Dropbox/web/reality/templates/base_list.html, error at line 0
   1116   1 : {% extends request.is_ajax|yesno:"base_ajax.html,base.html" %}
   2 : 
   3 : {% load i18n permissions django_tables2 common_tags %}
   4 : 
   5 : {% block body %}
   6 :     {% render_table list_table %}
   7 : {% endblock %}

Traceback:

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/core/handlers/base.py" in get_response
  174.                     response = self.process_exception_by_middleware(e, request)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/core/handlers/base.py" in get_response
  172.                     response = response.render()

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/template/response.py" in render
  160.             self.content = self.rendered_content

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/template/response.py" in rendered_content
  137.         content = template.render(context, self._request)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/template/backends/django.py" in render
  95.             return self.template.render(context)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/template/base.py" in render
  206.                     return self._render(context)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/template/base.py" in _render
  197.         return self.nodelist.render(context)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/template/base.py" in render
  992.                 bit = node.render_annotated(context)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/template/base.py" in render_annotated
  959.             return self.render(context)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/template/loader_tags.py" in render
  173.         return compiled_parent._render(context)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/template/base.py" in _render
  197.         return self.nodelist.render(context)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/template/base.py" in render
  992.                 bit = node.render_annotated(context)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/template/base.py" in render_annotated
  959.             return self.render(context)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/template/loader_tags.py" in render
  69.                 result = block.nodelist.render(context)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/template/base.py" in render
  992.                 bit = node.render_annotated(context)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/template/base.py" in render_annotated
  959.             return self.render(context)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/notify/templatetags/notification_tags.py" in render
  150.                 return self.generate_html(notifications)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/notify/templatetags/notification_tags.py" in generate_html
  48.         for nf in notifications:

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/db/models/query.py" in __iter__
  258.         self._fetch_all()

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/notify/models.py" in _fetch_all
  36.                 prefetch_relations(self)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/notify/utils.py" in prefetch_relations
  102.         models = prefetch_relations(model_class.objects.filter(pk__in=object_ids).select_related())

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/notify/utils.py" in prefetch_relations
  86.     for weak_model in weak_queryset:

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/db/models/query.py" in __iter__
  258.         self._fetch_all()

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/db/models/query.py" in _fetch_all
  1074.             self._result_cache = list(self.iterator())

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/db/models/query.py" in __iter__
  52.         results = compiler.execute_sql()

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py" in execute_sql
  848.             cursor.execute(sql, params)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/db/backends/utils.py" in execute
  79.             return super(CursorDebugWrapper, self).execute(sql, params)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/db/backends/utils.py" in execute
  64.                 return self.cursor.execute(sql, params)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/db/utils.py" in __exit__
  95.                 six.reraise(dj_exc_type, dj_exc_value, traceback)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/db/backends/utils.py" in execute
  64.                 return self.cursor.execute(sql, params)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/django/db/backends/mysql/base.py" in execute
  112.             return self.cursor.execute(query, args)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/MySQLdb/cursors.py" in execute
  205.             self.errorhandler(self, exc, value)

File "/home/truhlik/.virtualenvs/reality/local/lib/python2.7/site-packages/MySQLdb/connections.py" in defaulterrorhandler
  36.     raise errorclass, errorvalue

Exception Type: OperationalError at /backend/estates/tour/
Exception Value: (1116, 'Too many tables; MySQL can only use 61 tables in a join')

[v1k45]

Hi @truhlik , This appears to be an issue related to prefetch_relations function which we are using to reduce the number of queries. It is trying to prefetch more than 61 inter-related tables.

A possible fix would be to either not use prefetched queries or somehow limit the number of tables prefetched.

Both of them sounds a reasonable fix to me. What do you think? What approach should be followed?

cc: @fgmacedo

[truhlik]

limit the number of tables prefetched

... seems to me like better approach, but also more complicated. So if you figure out how it could be acomplished, then +1 from me.

not use prefetched queries

Tmp fix until above will be done...?

[v1k45]

Yes, it is little complicated but doable.

I am not the original author of that function but from what i can see, stopping select_related() from being added to the queryset after a limit of tables specified in DB specs is hit will fix the issue.

I'll push a temporary fix where you can turnoff prefetched queries using a setting as soon as i get time. And will work on a permanent fix for that as well.

Thanks.

[truhlik]

Great thank you. Let me know if I can help you with anything.

[fgmacedo]

@truhlik Please include the code where your are using the template tag in the base.html template. I think that you are using the user_notifications template tag, that is a shortcut to the more generic render_notifications template tag, so you can specify a the queryset to be used. The user_notifications templatetag uses this query: user.notifications.active().prefetch().

You can replace a call to the template tag {% user_notifications %}, by using something like:

{% render_notifications using user.notifications.active() %}

Please note that you may still try to do some sort of prefetch, as so many joins will slow down your render time doing many queries to fetch related objects.

[v1k45]

@fgmacedo 's solution is relevant too.

BTW, @fgmacedo should I still push a commit to let users to decide whether to prefetch by default or not? I think adding that option would be better because we use prefetch() by default in our views and templatetags.

[fgmacedo]

@v1k45 I don't think that It's an issue. @truhlik just reached the limit of his database backend, PostgreSQL for example, does not have this limit. We can point this on docs, but I think that the workaround above should work.

[v1k45]

Thanks @fgmacedo . I'll keep this issue open to take @truhlik 's input.

[truhlik]

Give me a little time please. I deleted all records from DB. The issue is not appearing now (with original code). When it appears I will try to change the code. I let you know. Thank you.

[v1k45]

Nice. I'll close this issue now. Feel free to comment further if you see this issue again.

[truhlik]

Hi guys,

I found related problem with view "notifications" which should display all notifications. There is notification_list = request.user.notifications.active().prefetch() I can't obviously fix this in the template. Do you have any idea how to work around this? Temporarly I created my own function but it is not good solution to the future. Any update of your app will affect my project.

Thanks for advice.

[v1k45]

I think a simple switch for turning off prefetchs will do. I'll push a fix as soon as i get time.

PS: ignore the old comment, I didn't read your comment completely.