Database Migration course_overviews `courseoverviewtab` Foreign Key Constraint Issue

Zachary_Trabookis · April 14, 2022, 4:45pm

We’re receiving this issue when trying to migrate from Ironwood database to Juniper.

Applying course_overviews.0019_improve_courseoverviewtab...Traceback (most recent call last):
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/MySQLdb/connections.py", line 239, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (`edxapp`.`#sql-38bd_c7`, CONSTRAINT `course_overviews_cou_course_overview_id_71fa6321_fk_course_ov` FOREIGN KEY (`course_overview_id`) REFERENCES `course_overviews_courseoverview` (`id`))')

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

Traceback (most recent call last):
  File "./manage.py", line 123, in <module>
    execute_from_command_line([sys.argv[0]] + django_args)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/django/core/management/__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/django/core/management/base.py", line 323, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/django/core/management/base.py", line 364, in execute
    output = self.handle(*args, **options)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/django/core/management/base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/django/core/management/commands/migrate.py", line 234, in handle
    fake_initial=fake_initial,
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/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 "/edx/app/edxapp/venvs/edxapp/lib/python3.5/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 "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/django/db/migrations/executor.py", line 245, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/django/db/migrations/migration.py", line 124, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/django/db/migrations/operations/fields.py", line 249, in database_forwards
    schema_editor.alter_field(from_model, from_field, to_field)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/django/db/backends/base/schema.py", line 535, in alter_field
    old_db_params, new_db_params, strict)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/django/db/backends/base/schema.py", line 758, in _alter_field
    self.execute(self._create_fk_sql(model, new_field, "_fk_%(to_table)s_%(to_column)s"))
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/django/db/backends/base/schema.py", line 137, in execute
    cursor.execute(sql, params)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/edx/app/edxapp/venvs/edxapp/lib/python3.5/site-packages/MySQLdb/connections.py", line 239, in query
    _mysql.connection.query(self, query)
**django.db.utils.IntegrityError: (1452, 'Cannot add or update a child row: a foreign key constraint fails (`edxapp`.`#sql-38bd_c7`, CONSTRAINT `course_overviews_cou_course_overview_id_71fa6321_fk_course_ov` FOREIGN KEY (`course_overview_id`) REFERENCES `course_overviews_courseoverview` (`id`))')**

The original foreign key was setup with this migration.

github.com

openedx/edx-platform/blob/open-release/juniper.master/openedx/core/djangoapps/content/course_overviews/migrations/0001_initial.py#L57

      
        
                        ('enrollment_domain', models.TextField(null=True)),
                        ('invitation_only', models.BooleanField(default=False)),
                        ('max_student_enrollments_allowed', models.IntegerField(null=True)),
                    ],
                ),
                migrations.CreateModel(
                    name='CourseOverviewTab',
                    fields=[
                        ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
                        ('tab_id', models.CharField(max_length=50)),
                        ('course_overview', models.ForeignKey(related_name='tabs', to='course_overviews.CourseOverview', on_delete=models.CASCADE)),
                    ],
                ),
            ]

Then the foreign key was altered with this migration change. This is where the error occurs above.

github.com

openedx/edx-platform/blob/open-release/juniper.master/openedx/core/djangoapps/content/course_overviews/migrations/0019_improve_courseoverviewtab.py#L34

      
        
                    field=models.TextField(null=True),
                ),
                migrations.AddField(
                    model_name='courseoverviewtab',
                    name='type',
                    field=models.CharField(max_length=50, null=True),
                ),
                migrations.AlterField(
                    model_name='courseoverviewtab',
                    name='course_overview',
                    field=models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, related_name='tab_set', to='course_overviews.CourseOverview'),
                ),
            ]

It appears that the Django model.ForeignKey related_name was updated.

Can anyone help us understand why this is happening?

Should we execute the following to prevent foreign keys from issuing a warning then run Django migrations for this app?

-- MySQL
use edxapp;
SET FOREIGN_KEY_CHECKS=0;

# Terminal (LMS)
./manage.py lms --settings=production migrate course_overviews

cc: @braden @jill @traek728 @becdavid @jmbowman @dave

dave · April 15, 2022, 2:00pm

So with older versions of Django, changing the related name would cause the foreign key constraint to be dropped and recreated, even though it’s not an operation that actually requires database changes. I don’t get why it would fail while doing the re-creation though. Is it possible that the collation changed between something that was and wasn’t case sensitive? If you check the values in courseoverviewtab.course_overview_id vs. course_overviews_courseoverview.id, do you get some that differ by case? Or maybe a value so large it exceeds the 255 char limit on that field and gets truncated?

Zachary_Trabookis · April 15, 2022, 2:37pm

@dave Thanks for looking into this for us. Here is what I find.

I don’t think the length of the course_overviews_id or course_overviews_courseoverviewtab are large enough to be truncated.

-- CourseOverview
select distinct id, length(id) as coid_length
from edxapp.course_overviews_courseoverview
order by 2 desc, 1 asc limit 10;

--CourseOverviewTab
select distinct course_overview_id, length(course_overview_id) as coid_length
from edxapp.course_overviews_courseoverviewtab
order by 2 desc, 1 asc limit 10;

I do have one entry in CourseOverviewTabs that doesn’t exist in CourseOverview. Left side is CourseOverview and right side is CourseOverviewTabs. Not sure how that happened if the foreign key constraint was in place for course_overview for CourseOverviewTabs.

Just confirming that CourseOverview is missing that 'course-v1:CUCWD+TEST101+TEMPLATE' course_id.

-- CourseOverview for 'course-v1:CUCWD+TEST101+TEMPLATE'
select *
from edxapp.course_overviews_courseoverviewtab
where id in ('course-v1:CUCWD+TEST101+TEMPLATE');

-- CourseOverviewTab for 'course-v1:CUCWD+TEST101+TEMPLATE'
select *
from edxapp.course_overviews_courseoverviewtab
where course_overview_id in ('course-v1:CUCWD+TEST101+TEMPLATE');

I deleted this course using the delete_course Django command. Maybe I should just clean up the foreign key course_overview_courseoverview.id records?

cc: @traek728 @becdavid

dave · April 15, 2022, 8:35pm

Yeah, that sounds best. Course deletion has always been kinda dodgy as a feature. I still don’t get why the foreign key constraint would have allowed it in the first place, but I’m guessing it should resolve your issue when trying to recreate the constraint.

Edit:

Er… actually, shouldn’t it be the reference in course_overviews_courseoverviewtab that gets deleted?

system · July 14, 2022, 8:35pm

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Migration from Hawthorn to Ironwood Site Operations Help	5	893	February 1, 2024
Automated database migration from Ironwood to Juniper Site Operations Help	3	1022	December 8, 2022
Juniper upgrade, getting error while restoring the data Site Operations Help juniper	2	554	October 27, 2020
MySQL Migration from Hawthorn to Ironwood Site Operations Help	2	514	December 26, 2019
Error in Discovery after Upgrading from Iromwood to Juinper Site Operations Help api , online , how-to , discussion , juniper	2	566	October 6, 2021

Database Migration course_overviews `courseoverviewtab` Foreign Key Constraint Issue

Related topics