Migration failure ("Foreign key constraint is incorrectly formed") generated by incidents.0008_add_authz_structure

[rolinh]

Hi,

When trying to upgrade a FIR instance, the migration incidents/migrations/0008_add_authz_structure.py does not apply successfully. This one was introduced with commit 45061a329513fcfa0c81c0792bc2c17b7fc3ce53. When checking out to the commit just before this one, all migrations apply successfully. Since this migration script was never edited after the initial revision, this error occurs with latest master as well.

django.db.utils.OperationalError: (1005, 'Can\'t create table `fir`.`#sql-842_c2` (errno: 150 "Foreign key constraint is incorrectly formed")')

Any help appreciated.

[gcrahay]

Not sure about this, but can you try to migrate "contrib.auth" first?

(fir-env) $ python manage.py migrate auth
(fir-env) $ python manage.py migrate 

[rolinh]

There is nothing to migrate from "auth":

$ ./manage.py migrate --settings fir.config.production auth
No module named hooks for plugins
No module named hooks for artifacts
No module named hooks for alerting
No module named hooks for todos
No module named hooks for api
Operations to perform:
  Apply all migrations: auth
Running migrations:
  No migrations to apply.

Here is the status of the migrations:

$ ./manage.py migrate --settings fir.config.production -l
No module named hooks for plugins
No module named hooks for artifacts
No module named hooks for alerting
No module named hooks for todos
No module named hooks for api
admin
 [X] 0001_initial
 [X] 0002_logentry_remove_auto_add
auth
 [X] 0001_initial
 [X] 0002_alter_permission_name_max_length
 [X] 0003_alter_user_email_max_length
 [X] 0004_alter_user_username_opts
 [X] 0005_alter_user_last_login_null
 [X] 0006_require_contenttypes_0002
 [X] 0007_alter_validators_add_error_messages
authtoken
 [X] 0001_initial
 [X] 0002_auto_20160226_1747
contenttypes
 [X] 0001_initial
 [X] 0002_remove_content_type_name
fir_alerting
 [X] 0001_initial
 [ ] 0002_add_helptext_to_body
fir_artifacts
 [X] 0001_initial
 [X] 0002_create_artifacts
 [X] 0003_auto_20160119_1131
 [X] 0002_artifactwhitelistitem
 [X] 0004_merge
 [X] 0005_delete_artifactwhitelistitem
 [ ] 0006_auto_20170110_1415
fir_nuggets
 [X] 0001_initial
fir_todos
 [X] 0001_initial
 [X] 0002_auto_20160110_0223
 [X] 0003_todolisttemplate_todolist
 [X] 0004_auto_20160112_1310
 [X] 0005_auto_20160119_1131
incidents
 [X] 0001_initial
 [X] 0002_link_incidents_to_artifacts
 [X] 0003_copy_file_artifact_data
 [X] 0004_delete_old_artifact_file
 [X] 0002_auto_20150907_1147
 [X] 0005_merge
 [X] 0003_auto_20160119_1021
 [X] 0006_merge
 [ ] 0007_change_bl_base_class
 [ ] 0008_add_authz_structure
 [ ] 0009_add_incicent_permissions
sessions
 [X] 0001_initial
sites
 [X] 0001_initial
 [X] 0002_alter_domain_unique

[rolinh]

@gcrahay Any other idea?

[gcrahay]

I tried to reproduce this bug, but it ran without error.

The 0007_change_bl_base_class migration is not applied in your migrate -l output. Do you have any problem to apply it?

[rolinh]

Nope, 0007_change_bl_base_class applies just fine. Only the 0008_add_authz_structure migration won't apply. I've been playing with this issue a bit today, including trying to re-generate the migration and it appears that the same migration gets generated (not really a surprise). At this point, I really don't know what is going on especially since I don't see why the foreign key constraint would be violated.

[gaelmuller]

Are you still having this issue ? What RDBMS are you using ?

[rolinh]

Yes, still having the issue, using MariaDB 10.0 series as the RDBMS.

[gcrahay]

I tried to reproduce this issue with a backup of my production data, MariaDB from Debian Jessie. No error with migrate.

Here is the complete log:

(fir-env) FIR$ git checkout 5568bfb6efabd7819ab367291c87171a16901151
[...]
(fir-env) FIR$ sudo apt install mariadb-server libmariadb-client-lgpl-dev libmysqlclient-dev
# Create fir database, adjust fir DATABASES settings
(fir-env) FIR$ pip install mysqlclient
(fir-env) FIR$ pip install -r requirements.txt
(fir-env) FIR$ ./manage.py migrate
[...]
(fir-env) FIR$ ./manage.py migrate -l
admin
 [X] 0001_initial
 [X] 0002_logentry_remove_auto_add
auth
 [X] 0001_initial
 [X] 0002_alter_permission_name_max_length
 [X] 0003_alter_user_email_max_length
 [X] 0004_alter_user_username_opts
 [X] 0005_alter_user_last_login_null
 [X] 0006_require_contenttypes_0002
 [X] 0007_alter_validators_add_error_messages
authtoken
 [X] 0001_initial
 [X] 0002_auto_20160226_1747
contenttypes
 [X] 0001_initial
 [X] 0002_remove_content_type_name
fir_artifacts
 [X] 0001_initial
 [X] 0002_create_artifacts
 [X] 0003_auto_20160119_1131
 [X] 0002_artifactwhitelistitem
 [X] 0004_merge
fir_nuggets
 [X] 0001_initial
fir_todos
 [X] 0001_initial
 [X] 0002_auto_20160110_0223
 [X] 0003_todolisttemplate_todolist
 [X] 0004_auto_20160112_1310
 [X] 0005_auto_20160119_1131
incidents
 [X] 0001_initial
 [X] 0002_link_incidents_to_artifacts
 [X] 0003_copy_file_artifact_data
 [X] 0004_delete_old_artifact_file
 [X] 0002_auto_20150907_1147
 [X] 0005_merge
 [X] 0003_auto_20160119_1021
 [X] 0006_merge
sessions
 [X] 0001_initial
sites
 [X] 0001_initial
 [X] 0002_alter_domain_unique
(fir-env) FIR$ ./manage.py loaddata fir_backup_20160120.json
Installed 2394 object(s) from 1 fixture(s)
(fir-env) FIR$ git checkout master
(fir-env) FIR$ pip install -r requirements.txt # New requirements needed
(fir-env) FIR$ ./manage.py migrate -l
admin
 [X] 0001_initial
 [X] 0002_logentry_remove_auto_add
auth
 [X] 0001_initial
 [X] 0002_alter_permission_name_max_length
 [X] 0003_alter_user_email_max_length
 [X] 0004_alter_user_username_opts
 [X] 0005_alter_user_last_login_null
 [X] 0006_require_contenttypes_0002
 [X] 0007_alter_validators_add_error_messages
authtoken
 [X] 0001_initial
 [X] 0002_auto_20160226_1747
contenttypes
 [X] 0001_initial
 [X] 0002_remove_content_type_name
fir_artifacts
 [X] 0001_initial
 [X] 0002_create_artifacts
 [X] 0003_auto_20160119_1131
 [X] 0002_artifactwhitelistitem
 [X] 0004_merge
 [ ] 0005_delete_artifactwhitelistitem
 [ ] 0006_auto_20170110_1415
fir_nuggets
 [X] 0001_initial
fir_todos
 [X] 0001_initial
 [X] 0002_auto_20160110_0223
 [X] 0003_todolisttemplate_todolist
 [X] 0004_auto_20160112_1310
 [X] 0005_auto_20160119_1131
incidents
 [X] 0001_initial
 [X] 0002_link_incidents_to_artifacts
 [X] 0003_copy_file_artifact_data
 [X] 0004_delete_old_artifact_file
 [X] 0002_auto_20150907_1147
 [X] 0005_merge
 [X] 0003_auto_20160119_1021
 [X] 0006_merge
 [ ] 0007_change_bl_base_class
 [ ] 0008_add_authz_structure
 [ ] 0009_add_incicent_permissions
 [ ] 0010_auto_20170122_1208
sessions
 [X] 0001_initial
sites
 [X] 0001_initial
 [X] 0002_alter_domain_unique
(fir-env) FIR$ ./manage.py migrate -v 3
No module named hooks for plugins
No module named hooks for artifacts
No module named hooks for email
No module named hooks for todos
Operations to perform:
  Apply all migrations: authtoken, fir_nuggets, fir_artifacts, sessions, admin, sites, auth, fir_todos, contenttypes, incidents
Running pre-migrate handlers for application auth
Running pre-migrate handlers for application contenttypes
Running pre-migrate handlers for application sessions
Running pre-migrate handlers for application sites
Running pre-migrate handlers for application admin
Running pre-migrate handlers for application rest_framework
Running pre-migrate handlers for application authtoken
Running pre-migrate handlers for application fir_plugins
Running pre-migrate handlers for application incidents
Running pre-migrate handlers for application fir_artifacts
Running pre-migrate handlers for application treebeard
Running pre-migrate handlers for application fir_todos
Running pre-migrate handlers for application fir_nuggets
Running migrations:
  Rendering model states... DONE (0.040s)
  Applying fir_artifacts.0005_delete_artifactwhitelistitem... OK (0.217s)
  Applying fir_artifacts.0006_auto_20170110_1415... OK (1.049s)
  Applying incidents.0007_change_bl_base_class... OK (3.737s)
  Applying incidents.0008_add_authz_structure... OK (7.085s)
  Applying incidents.0009_add_incicent_permissions... OK (0.091s)
  Applying incidents.0010_auto_20170122_1208... OK (2.669s)
Running post-migrate handlers for application auth
Running post-migrate handlers for application contenttypes
Running post-migrate handlers for application sessions
Running post-migrate handlers for application sites
Running post-migrate handlers for application admin
Running post-migrate handlers for application rest_framework
Running post-migrate handlers for application authtoken
Running post-migrate handlers for application fir_plugins
Running post-migrate handlers for application incidents
Adding permission 'incidents | access control entry | Can add access control entry'
Adding permission 'incidents | access control entry | Can change access control entry'
Adding permission 'incidents | access control entry | Can delete access control entry'
Adding permission 'incidents | incident | Can report events'
Adding permission 'incidents | incident | Can view incidents'
Running post-migrate handlers for application fir_artifacts
Running post-migrate handlers for application treebeard
Running post-migrate handlers for application fir_todos
Running post-migrate handlers for application fir_nuggets
(fir-env) FIR$ ./manage.py migrate -l
admin
 [X] 0001_initial
 [X] 0002_logentry_remove_auto_add
auth
 [X] 0001_initial
 [X] 0002_alter_permission_name_max_length
 [X] 0003_alter_user_email_max_length
 [X] 0004_alter_user_username_opts
 [X] 0005_alter_user_last_login_null
 [X] 0006_require_contenttypes_0002
 [X] 0007_alter_validators_add_error_messages
authtoken
 [X] 0001_initial
 [X] 0002_auto_20160226_1747
contenttypes
 [X] 0001_initial
 [X] 0002_remove_content_type_name
fir_artifacts
 [X] 0001_initial
 [X] 0002_create_artifacts
 [X] 0003_auto_20160119_1131
 [X] 0002_artifactwhitelistitem
 [X] 0004_merge
 [X] 0005_delete_artifactwhitelistitem
 [X] 0006_auto_20170110_1415
fir_nuggets
 [X] 0001_initial
fir_todos
 [X] 0001_initial
 [X] 0002_auto_20160110_0223
 [X] 0003_todolisttemplate_todolist
 [X] 0004_auto_20160112_1310
 [X] 0005_auto_20160119_1131
incidents
 [X] 0001_initial
 [X] 0002_link_incidents_to_artifacts
 [X] 0003_copy_file_artifact_data
 [X] 0004_delete_old_artifact_file
 [X] 0002_auto_20150907_1147
 [X] 0005_merge
 [X] 0003_auto_20160119_1021
 [X] 0006_merge
 [X] 0007_change_bl_base_class
 [X] 0008_add_authz_structure
 [X] 0009_add_incicent_permissions
 [X] 0010_auto_20170122_1208
sessions
 [X] 0001_initial
sites
 [X] 0001_initial
 [X] 0002_alter_domain_unique

[ghost]

Potentially worth checking when this occurs: are the MySQL / MariaDB storage engines the same or different across the tables referenced in the foreign key fields and the created AccessControlEntry model?

[Augustin-FL]

As mentioned by ghost, this is likely due to all tables not using the same engine (eg, MyISAM vs InnoDB). @rolinh, Are you still having this issue? If yes, could you SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'fir'; and confirm what engine(s) each table is using?

[rolinh]

This issue is over 6 years old at this point and I'm not using FIR anymore. I'd say it's time to close the issue.