1054 Error only when running test

I’m running…
Python 3.8.1
Django 3.2.19
model-bakery 1.12.0
mysql database via MAMP PRO

The problem…

I have added two fields to a model mtmforms

class MTMForm(models.Model):
	NONE = 'NON'
	CANDIDATE = 'CAN'
	APPROVED = 'APP'
	SCHEDULED = 'SCH'
	OBFUSCATED = 'OBF'
	IGNORE = 'IGN'

	OBFUSCATE_CHOICES = [
		(NONE, '-----'),
		(CANDIDATE, 'Candidate'),
		(APPROVED, 'Approved'),
		(SCHEDULED, 'Scheduled'),
		(OBFUSCATED, 'Obfuscated'),
		(IGNORE, 'Do not obfuscate')
	]

	SUBMITTED = 'SUB'
	REVIEWING = 'REV'
	CHECKED = 'CHE'
	COMPLETE = 'COM'
	REJECTED = 'REJ'

	FORM_STATUS_CHOICES = [
		(SUBMITTED, 'Submitted'),
		(REVIEWING, 'Reviewing'),
		(CHECKED, 'Checked'),
		(COMPLETE, 'Complete'),
		(REJECTED, 'Rejected')
	]

	MTMFORM = 'M'
	APPFORM = 'A'

	FORM_TYPE_CHOICES = [
		(MTMFORM, 'Log Form'),
		(APPFORM, 'Application Form')
	]

	form_type = models.CharField(max_length=1, choices=FORM_TYPE_CHOICES, default=MTMFORM)
	status = models.CharField(max_length=3, choices=FORM_STATUS_CHOICES, default=SUBMITTED)
	form_template = models.ForeignKey(MTMFormTemplate, on_delete=models.PROTECT)
	relates_to_beneficiary = models.ForeignKey('people.Person', related_name='RelatesToBeneficiary',
	                                           on_delete=models.PROTECT,
	                                           blank=True, null=True)
	beneficiary_required = models.BooleanField()
	relates_to_volunteer = models.ForeignKey('people.Person', related_name='RelatesToVolunteer',
	                                         on_delete=models.PROTECT,
	                                         blank=True, null=True)
	volunteer_required = models.BooleanField()
	parent_task = models.ForeignKey('tasks.Task', on_delete=models.CASCADE, null=True, blank=True)
	parent_task_required = models.BooleanField()
	area = models.ForeignKey('people.Area', on_delete=models.PROTECT, null=True, blank=True)
	#  Add blank = true for when a publicly accessible form is submitted
	#  e.g. someone providing a character reference.
	submitted_by = models.ForeignKey('people.Person', related_name='SubmittedBy', on_delete=models.PROTECT, null=True,
	                                 blank=True)
	submitted_date = models.DateField(blank=True, null=True)
	# Activity date is populated by hoisting an dateOfActivity from the form.form_data, if the key is present.
	# Thereby significantly improving query performance e.g. in metrics
	# Issue #1347 refers
	activity_date = models.DateField(blank=True, null=True)
	# Audit fields
	created_at = models.DateTimeField(auto_now_add=True)
	updated_at = models.DateTimeField(auto_now=True)
	last_updated_by = models.ForeignKey('people.Person', related_name='LastUpdatedBy', on_delete=models.PROTECT,
	                                    null=True,
	                                    blank=True)
	name = models.CharField(max_length=60)
	description = models.TextField(blank=True)
	multiple_submissions_allowed = models.BooleanField(default=False)
	for_admin_use_only = models.BooleanField(default=True)
	always_review_submissions = models.BooleanField(default=True)
	form_schema = models.JSONField()
	form_uischema = models.JSONField()
	form_data = models.JSONField()
	service_group = models.ForeignKey('tasks.ServiceGroup', on_delete=models.PROTECT, null=True, blank=True)
	action_group = models.ForeignKey('tasks.ActionGroup', on_delete=models.PROTECT, null=True, blank=True)
	obfuscate_status = models.CharField(max_length=3, choices=OBFUSCATE_CHOICES, default='NON')
	obfuscate_date = models.DateField(blank=True, null=True)

Here is the migration

# Generated by Django 3.2.19 on 2023-06-21 16:46

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
        ('mtmforms', '0045_populate_ward_for_applications'),
    ]

    operations = [
        migrations.AddField(
            model_name='mtmform',
            name='obfuscate_date',
            field=models.DateField(blank=True, null=True),
        ),
        migrations.AddField(
            model_name='mtmform',
            name='obfuscate_status',
            field=models.CharField(choices=[('NON', '-----'), ('CAN', 'Candidate'), ('APP', 'Approved'), ('SCH', 'Scheduled'), ('OBF', 'Obfuscated'), ('IGN', 'Do not obfuscate')], default='NON', max_length=3),
        ),
    ]

The application works fine with runserver and I can see the fields in django admin
If I run test with --keepdb the migration is not shown in the test database and the columns are not present in the mtmforms table

I have had the same fields on another model Person, and that has tested fine for some time.

The Error I get when running test…

(venv) mtm-crm-patch % python manage.py test         
Creating test database for alias 'default'...
Got an error creating the test database: (1007, "Can't create database 'test_mtmdev'; database exists")
Type 'yes' if you would like to try deleting the test database 'test_mtmdev', or 'no' to cancel: yes
Destroying old test database for alias 'default'...
area Unassigned added
Traceback (most recent call last):
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
MySQLdb.OperationalError: (1054, "Unknown column 'mtmforms_mtmform.obfuscate_status' in 'field list'")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "manage.py", line 22, in <module>
    main()
  File "manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/core/management/commands/test.py", line 23, in run_from_argv
    super().run_from_argv(argv)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/core/management/commands/test.py", line 55, in handle
    failures = test_runner.run_tests(test_labels)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/test/runner.py", line 725, in run_tests
    old_config = self.setup_databases(aliases=databases)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/test/runner.py", line 643, in setup_databases
    return _setup_databases(
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/test/utils.py", line 179, in setup_databases
    connection.creation.create_test_db(
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/db/backends/base/creation.py", line 74, in create_test_db
    call_command(
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/core/management/__init__.py", line 181, in call_command
    return command.execute(*args, **defaults)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/core/management/base.py", line 89, in wrapped
    res = handle_func(*args, **kwargs)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/core/management/commands/migrate.py", line 244, in handle
    post_migrate_state = executor.migrate(
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/db/migrations/executor.py", line 227, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/db/migrations/migration.py", line 123, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/db/migrations/operations/special.py", line 190, in database_forwards
    self.code(from_state.apps, schema_editor)
  File "/Users/robertcollins/github/mtm-crm-patch/mtmforms/migrations/0045_populate_ward_for_applications.py", line 20, in create_locate_application_job
    for application in ApplicationForm.objects.filter(
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/db/models/query.py", line 280, in __iter__
    self._fetch_all()
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/db/models/query.py", line 1324, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/db/models/query.py", line 51, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1175, in execute_sql
    cursor.execute(sql, params)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/django/db/backends/mysql/base.py", line 73, in execute
    return self.cursor.execute(query, args)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/Users/robertcollins/github/mtm-crm-patch/venv/lib/python3.8/site-packages/MySQLdb/connections.py", line 254, in query
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: (1054, "Unknown column 'mtmforms_mtmform.obfuscate_status' in 'field list'")

Things I’ve tried…

  • resolutions to similar issues proposed on Stackoverflow - generally advocating running migrate --fake
  • creating a new main database and copying the data across from the old database using dumpdata and loaddata
  • doing the migrations and test outside of pycharm
  • staring out of the window :frowning:

Any pointers would be appreciated

I don’t know if this would help, but when I run test in debug I get a whole load of
2555
and then
3437
output to the console before the Traceback

OK - a bit more info… I’ve been able to sidestep the issue by removing the previous migration 'mtmforms', '0045_populate_ward_for_applications' which comprised of…

# Generated by Django 3.2.19 on 2023-06-20 18:18
import logging

from django.db import migrations

from mtmforms.models import ApplicationForm

logger = logging.getLogger(__name__)


def create_locate_application_job(apps, schema_editor):
    """
    For each Application Form where the applicant.ward is not populated,
    create a locate_applicant job
    """

    locate_job_count = 0
    error_count = 0
    no_postal_code = 0
    for application in ApplicationForm.objects.filter(
            applicant_ward__exact=''):
        if application.applicant_postal_code:
            try:
                application.initiate_locate_applicant_job()
                locate_job_count += 1
            except Exception as e:
                logger.warning(f'Cannot locate Application: {application.id}\n{e}')
                error_count += 1
        else:
            logger.warning(f'No postal code for Application: {application.id}')
            no_postal_code += 1

    logger.warning(f'\nCreated {locate_job_count} jobs\n'
          f'Could not create {error_count} jobs\n'
                   f'Ignored {no_postal_code} Applications without a Postal Code')

class Migration(migrations.Migration):
    dependencies = [
        ('mtmforms', '0044_applicationform_applicant_ward'),
    ]

    operations = [
        migrations.RunPython(create_locate_application_job)
    ]

migrating back to mtmforms 0044 and then re-running make migrations etc.
Note: ApplicationForm inherits from MTMForm

Finally figured it - read the section on Historical Models and how to access them when using RunPython.

My revised migration now looks like this…

# Generated by Django 3.2.19 on 2023-06-20 18:18
import logging

from django.db import migrations
from django_dbq.models import Job


logger = logging.getLogger(__name__)


def create_locate_application_job(apps, schema_editor):
    """
    For each Application Form where the applicant.ward is not populated,
    create a locate_applicant job
    """

    locate_job_count = 0
    error_count = 0
    # Get the historical model for ApplicationForm
    ApplicationForm = apps.get_model('mtmforms', 'ApplicationForm')
    db_alias = schema_editor.connection.alias
    for application in ApplicationForm.objects.values_list(
            'id', 'applicant_postal_code').using(db_alias).filter(
            applicant_ward='').exclude(applicant_postal_code=''):
        context = {
            'postal_code': application[1],
            'application_id': application[0]
        }
        try:
            Job.objects.create(
                name="locate_application_form_job",
                workspace=context,
                queue_name='mapit_queue'
            )
            locate_job_count += 1

        except Exception as e:
            logger.error(f'Cannot create background locate_application_form_job for application'
                         f':{application[0]}\n{e}')
            error_count += 1

    logger.warning(f'\nCreated {locate_job_count} jobs\n'
                   f'Could not create {error_count} jobs.')


class Migration(migrations.Migration):
    dependencies = [
        ('mtmforms', '0044_applicationform_applicant_ward'),
    ]

    operations = [
        migrations.RunPython(create_locate_application_job)
    ]

and now test applies all migrations successfully.

Moral: RTFM