django.db.utils.OperationalError: (1054, "Unknown column

Question

An interesting issue. Getting Unknown column exception -- Please find the stack trace

I try to get new leads list and responded leads. I merge them. When I merge them there is an exception.

After debugging its found that new_leads method has exclude of two fields collection and delivery . If we make it one exclude all is well . I mean dont check the other, if we include both the filters we have an issue.

I tried using filter/ exclude etc. but it didnt work.

Query Set contains following method

    def all_leads_related_to_user(self, user):
    """ User new and past leads
    Use this queryset for performing lead search.
    """
    new_leads = self.new_leads_for_user(user)
    responded_leads = self.leads_responded_by_user(user)
    all_leads = (new_leads | responded_leads).distinct() <= Issue is here.
    return all_leads

    def new_leads_for_user(self, user):
     ....

    # User's location filter
    if user.sub_region_excluded_list:
        sub_region_exclude_list = [10, 12]
        qs = qs.exclude( Q(collection_point__sub_region_id__in=sub_region_exclude_list) |
                        Q(delivery_point__sub_region_id__in=sub_region_exclude_list)) 

                   # <== Make it just one exclude it works.


Model 

class Suburb(models.Model):
    state = models.ForeignKey(State, blank=False)
    sub_region = models.ForeignKey(SubRegion, blank=False)
    postcode = models.CharField(_('postcode'), blank=False, max_length=10)
    name = models.CharField(_('suburb name'), blank=False, max_length=200)

class Load(models.Model):
.....

collection_point = models.ForeignKey(Suburb, related_name='collection_point', on_delete=models.SET_NULL, null=True)
delivery_point = models.ForeignKey(Suburb, related_name='delivery_point', on_delete=models.SET_NULL, null=True)

Stack Trace:-

    >>> Load.objects.all_leads_related_to_user(User.objects.all()[0])
Load.objects.all_leads_related_to_user(User.objects.all()[0])
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/data/fq/venv/lib/python3.4/site-packages/django/db/models/query.py", line 226, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/data/fq/venv/lib/python3.4/site-packages/django/db/models/query.py", line 250, in __iter__
    self._fetch_all()
  File "/data/fq/venv/lib/python3.4/site-packages/django/db/models/query.py", line 1103, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/data/fq/venv/lib/python3.4/site-packages/django/db/models/query.py", line 53, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch)
  File "/data/fq/venv/lib/python3.4/site-packages/django/db/models/sql/compiler.py", line 886, in execute_sql
    raise original_exception
  File "/data/fq/venv/lib/python3.4/site-packages/django/db/models/sql/compiler.py", line 876, in execute_sql
    cursor.execute(sql, params)
  File "/data/fq/venv/lib/python3.4/site-packages/django/db/backends/utils.py", line 80, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/data/fq/venv/lib/python3.4/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/data/fq/venv/lib/python3.4/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/data/fq/venv/lib/python3.4/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/data/fq/venv/lib/python3.4/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/data/fq/venv/lib/python3.4/site-packages/django/db/backends/mysql/base.py", line 101, in execute
    return self.cursor.execute(query, args)
  File "/data/fq/venv/lib/python3.4/site-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/data/fq/venv/lib/python3.4/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/data/fq/venv/lib/python3.4/site-packages/MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "/data/fq/venv/lib/python3.4/site-packages/MySQLdb/cursors.py", line 411, in _query
    rowcount = self._do_query(q)
  File "/data/fq/venv/lib/python3.4/site-packages/MySQLdb/cursors.py", line 374, in _do_query
    db.query(q)
  File "/data/fq/venv/lib/python3.4/site-packages/MySQLdb/connections.py", line 292, in query
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: (1054, "Unknown column 'locations_suburb.sub_region_id' in 'having clause'")
>>> 

I'm using MySqlDB

Note:-

All migrations are applied and Db is in right state

Update

Issue is related to MYSQL madating columns to be available in select statements when its creating a Having clause . Refer to - Unknown column in 'having clause'. Django in this instance is not adding as the column to be selected. hence the error. I need to some how find a way to add this in the select clause with other params.

Answer 1

This usually pops up when you haven't made or applied migrations. Specifically, when you modify the fields any model in Django (or any ORM), you need to inform the SQL server so it can reflect it in its tables. Modern Django implements this by a series of migrations, so that if you have data from any time in the life of your project, you can run it on code from any time in history by simply running the migrations forwards or backwards.

Long story short, MySQL claims the sub_region field doesn't exist. You need to sync the tables to reflect your models.

There are two steps, making the migrations and running them on your server, shown below for your locations app. Take a backup before running the second command, especially on MySQL or SQLite!

$ python manage.py makemigrations locations
$ python manage.py migrate locations 

This will cause the database server to create the column, and you should no longer get an OperationalError.

Answer 2

It is not a migration issue.

When having a HAVING Clause MYSQL mandates those parameters to be in selected, if not selected it will throw an exception. Therefore I had to force Django to include both the fields by:

qs = qs.annotate(collection_point__sub_region_id = F("collection_point__sub_region_id"),
                 delivery_point__sub_region_id = F("delivery_point__sub_region_id"))

Answer 3

This is the answer to this issue:

Step 1. You need to fake the migrations

python manage.py migrate --fake

Step 2. Migrate again

python manage.py migrate

Step 3. Comment out the column for which the error comes up e.g. "Can't DROP 'address'

Step 4. Make migrations and migrate again

python manage.py makemigrations
python manage.py migrate