Foreign key error when running DB schema update script

[mtb3]

I encountered this error when trying to run the updateSchemaFromIlios2toIlios3.sql script (per the upgrade instructions) to upgrade from 2.4.8 to 3.12.0.

I first attempted this using a DB copy on MySQL 5.1, then then same error occurred with 5.6.

ERROR 1833 (HY000) at line 486: Cannot change column 'group_id': used in a foreign key constraint 'fkey_group_default_instructor_group_id' of table 'omi_ilios_d.group_default_instructor'

I believe a similar error occurs for other tables that are subsequently dropped.

[mtb3]

Several tables are dropped starting on line 648 of the script:

https://github.com/ilios/ilios/blob/v3.12.0/app/Resources/updateSchemaFromIlios2toIlios3.sql#L648

DROP TABLE IF EXISTS `group_default_instructor`;
DROP TABLE IF EXISTS `curriculum_inventory_program`;
DROP TABLE IF EXISTS `ilm_session_facet_learner`;
DROP TABLE IF EXISTS `ilm_session_facet_instructor`;
DROP TABLE IF EXISTS `offering_instructor`;
DROP TABLE IF EXISTS `offering_learner`;
DROP TABLE IF EXISTS `report_po_value`;
DROP TABLE IF EXISTS `group_x_group`;

Of these tables, only report_po_value is directly referenced/used earlier in the file.

I was able to work around the problem reported in this issue by dropping these tables (except report_po_value) before executing the script.

Perhaps moving the DROP TABLE statements (except for report_po_value) to the top of the script is a reasonbly quick fix.

[thecoolestguy]

We've recently noticed a few issues like this surfacing when people run the migrations, and for many the issue seems to be that they are using MySQL 5.6 which has changed the way it handles schema updates on columns that are part of a Foreign Key Constraint (see issue #1449 for more info about this 'feature change' in MySQL). Not sure why you experienced the issue with MySQL 5.1 or why you are having the additional problems you listed, but we will look into it.

That being said, we've had 100% success running https://github.com/ilios/ilios/blob/v3.12.0/app/Resources/updateSchemaFromIlios2toIlios3.sql for upgrades as recently as yesterday, and had no errors with that part at all, so there may be some data values in your db that are missing or something that may be causing the problem. You are definitely running the file from the 3.12.0 branch, correct?

[mtb3]

I'm definitely running the file from the 3.12.0 branch. And I saw the error occur with both MySQL 5.1 and 5.6 (I don't have immediate access to try other versions in between).

I realized it may be an atypical case having the group_default_instructor table present in the database (and similarly for other tables mentioned above). This table was replaced/deprecated via a migration a long time ago; see the following commit:

3f23462

I'm not sure of the exact history of why this table (and the others mentioned above) is present in our DB, but the schema update script does drop this table and the others.

[jrjohnson]

I'm going to attempt to move the drop table stuff up further in the script and see if that breaks anything else. Seems like this is easily doable and should present no problems.

[mtb3]

I agree that should be a good approach.