Comment and media count crashing admin

[edwardgalligan]

I'm receiving this error in the GeoKey admin when visiting the main project overview page:

Request Method: GET
Request URL: https://geokey.localhost/admin/projects/3/
Django Version: 1.9.13
Exception Type: ProgrammingError
Exception Value: more than one row returned by a subquery used as an expression
Exception Location: /home/ubuntu/www/geokey/virtualenv/local/lib/python2.7/site-packages/django/db/backends/utils.py in execute, line 64
Python Executable: /home/ubuntu/www/geokey/virtualenv/bin/uwsgi
Python Version: 2.7.12
Python Path: ['.',  '',  '/home/ubuntu/www/geokey/virtualenv/lib/python2.7',  '/home/ubuntu/www/geokey/virtualenv/lib/python2.7/plat-x86_64-linux-gnu',  '/home/ubuntu/www/geokey/virtualenv/lib/python2.7/lib-tk',  '/home/ubuntu/www/geokey/virtualenv/lib/python2.7/lib-old',  '/home/ubuntu/www/geokey/virtualenv/lib/python2.7/lib-dynload',  '/usr/lib/python2.7',  '/usr/lib/python2.7/plat-x86_64-linux-gnu',  '/usr/lib/python2.7/lib-tk',  '/home/ubuntu/www/geokey/virtualenv/local/lib/python2.7/site-packages']

The issue appears to be with the following lines so I've put in place this temporary fix for now:

diff --git a/geokey/projects/views.py b/geokey/projects/views.py
index df144aa..d3d2eaf 100644
--- a/geokey/projects/views.py
+++ b/geokey/projects/views.py
@@ -168,10 +168,10 @@ class ProjectOverview(LoginRequiredMixin, ProjectContext, TemplateView):
         if project:
             contributions = project.observations.all()
             project.contributions_count = len(contributions)
-            project.comments_count = Comment.objects.filter(
-                commentto=contributions).count()
-            project.media_count = MediaFile.objects.filter(
-                contribution=contributions).count()
+            project.comments_count = 'fixme' #Comment.objects.filter(
+            #    commentto=contributions).count()
+            project.media_count = 'fixme' #MediaFile.objects.filter(
+            #    contribution=contributions).count()

         return context

It's not really a feature I'd use/rely on, but I thought it would be worth reporting as a curiosity. Also worth noting, this crash does not occur on projects with 0 contributions. It also does not seem to affect the contribution count, only the comment and media count, so it's likely something related to joins on foreign keys with those tables.

I'll submit a PR if I can figure out the problem, but I'm not very familiar with Django so I've not been able to solve it sofar.

[edwardgalligan]

Follow-up - in case it helps, here's the exception environment copypasta:

Environment:


Request Method: GET
Request URL: https://geokey.localhost/admin/projects/1/

Django Version: 1.9.13
Python Version: 2.7.12
Installed Applications:
('django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.staticfiles',
 'django.contrib.gis',
 'django.contrib.sites',
 'django_hstore',
 'oauth2_provider',
 'easy_thumbnails',
 'allauth',
 'allauth.account',
 'allauth.socialaccount',
 'rest_framework',
 'rest_framework_gis',
 'geokey.core',
 'geokey.projects',
 'geokey.categories',
 'geokey.contributions',
 'geokey.users',
 'geokey.applications',
 'geokey.superusertools',
 'geokey.extensions',
 'geokey.subsets',
 'django_nose',
 'debug_toolbar',
 'allauth.socialaccount.providers.auth0',
 'allauth.socialaccount.providers.facebook',
 'allauth.socialaccount.providers.google',
 'allauth.socialaccount.providers.instagram',
 'allauth.socialaccount.providers.linkedin',
 'allauth.socialaccount.providers.linkedin_oauth2',
 'allauth.socialaccount.providers.openid',
 'allauth.socialaccount.providers.pinterest',
 'allauth.socialaccount.providers.reddit',
 'allauth.socialaccount.providers.soundcloud',
 'allauth.socialaccount.providers.spotify',
 'allauth.socialaccount.providers.tumblr',
 'allauth.socialaccount.providers.twitter',
 'allauth.socialaccount.providers.vimeo',
 'allauth.socialaccount.providers.windowslive',
 'geokey_dataimports')
Installed Middleware:
('django.middleware.common.CommonMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'geokey.core.middleware.DisableCSRF',
 'geokey.core.middleware.XsSharing',
 'geokey.core.middleware.RequestProvider')



Traceback:

File "/home/ubuntu/www/geokey/virtualenv/local/lib/python2.7/site-packages/django/core/handlers/base.py" in get_response
  149.                     response = self.process_exception_by_middleware(e, request)

File "/home/ubuntu/www/geokey/virtualenv/local/lib/python2.7/site-packages/django/core/handlers/base.py" in get_response
  147.                     response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "/home/ubuntu/www/geokey/virtualenv/local/lib/python2.7/site-packages/django/views/generic/base.py" in view
  68.             return self.dispatch(request, *args, **kwargs)

File "/home/ubuntu/www/geokey/virtualenv/local/lib/python2.7/site-packages/braces/views/_access.py" in dispatch
  102.             request, *args, **kwargs)

File "/home/ubuntu/www/geokey/virtualenv/local/lib/python2.7/site-packages/django/views/generic/base.py" in dispatch
  88.         return handler(request, *args, **kwargs)

File "/home/ubuntu/www/geokey/virtualenv/local/lib/python2.7/site-packages/django/views/generic/base.py" in get
  157.         context = self.get_context_data(**kwargs)

File "./geokey/projects/views.py" in get_context_data
  172.                 commentto=contributions).count()

File "/home/ubuntu/www/geokey/virtualenv/local/lib/python2.7/site-packages/django/db/models/query.py" in count
  371.         return self.query.get_count(using=self.db)

File "/home/ubuntu/www/geokey/virtualenv/local/lib/python2.7/site-packages/django/db/models/sql/query.py" in get_count
  483.         number = obj.get_aggregation(using, ['__count'])['__count']

File "/home/ubuntu/www/geokey/virtualenv/local/lib/python2.7/site-packages/django/db/models/sql/query.py" in get_aggregation
  464.         result = compiler.execute_sql(SINGLE)

File "/home/ubuntu/www/geokey/virtualenv/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py" in execute_sql
  848.             cursor.execute(sql, params)

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

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

File "/home/ubuntu/www/geokey/virtualenv/local/lib/python2.7/site-packages/django/db/utils.py" in __exit__
  95.                 six.reraise(dj_exc_type, dj_exc_value, traceback)

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

Exception Type: ProgrammingError at /admin/projects/1/
Exception Value: more than one row returned by a subquery used as an expression

[josokinas]

Please try downgrading Django to version 1.8 (with latest patch).

[edwardgalligan]

Hey @josokinas thanks for the suggestion.

As I said above, this isn't a feature we use/need so I'm happier with the patch above than going through a downgrade. Only reporting here for posterity and in the off-chance I may be able to help the project.

Do you know - is it a bug in Django 1.9 or in GeoKey?

Could this be something relevant to #379

[josokinas]

Difficult to tell without replicating the situation. If changing Django version fixes the problem - most likely there's either bug in Django, or some change which was not reflected on GeoKey. Replicating with a test case would be ideal, as it could help to solve the issue.

[edwardgalligan]

OK, I'll try and look into downgrading to 1.8 as soon as I can, and report back with results.

[edwardgalligan]

Just to report this has been working for me on since pulling in later commits in this repo, so I guess this can be closed (original cause still unknown).

[josokinas]

@edwardgalligan I think with latest GeoKey update the issue should be solved as it now supports later Django version.