Migration fails: Cannot drop index 'unique_with_index': needed in a foreign key constraint

[herr-mhet]

Environment

• Python version: 3.9.2
• Nautobot version: 1.4.2
• nautobot-plugin-firewall-model version: 1.0.0
• MariaDB version: 10.5.15

Expected Behavior

Problemless nautobot-server post_upgrade

Observed Behavior

Applying nautobot_firewall_models.0008_renaming_part3...Traceback (most recent call last):
  File "/opt/nautobot/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
  File "/opt/nautobot/lib/python3.9/site-packages/django/db/backends/mysql/base.py", line 73, in execute
return self.cursor.execute(query, args)
  File "/opt/nautobot/lib/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute
res = self._query(query)
  File "/opt/nautobot/lib/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query
db.query(q)
  File "/opt/nautobot/lib/python3.9/site-packages/MySQLdb/connections.py", line 254, in query
_mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1553, "Cannot drop index 'unique_with_index': needed in a foreign key constraint")

Steps to Reproduce

1. Have Nautobot with MariaDB
2. Install plugin
3. run nautobot-server post_upgrade

Manual fix

After manually lifting the foreign key constraint on the nautobot_firewall_models_policyrulem2m table, I was able to let the post_upgrade migration successfully finish. Sadly, I was not able to figure out how to rewrite the offending migration so this error would not occur. I can confirm, though, that installing this firewall plugin on MariaDB from scratch also leads to this error. So this is a breaking bug for users of MariaDB.

These were my manual SQL statements:

MariaDB [nautobot]> show create table nautobot_firewall_models_policyrulem2m;
+----------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                                  | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+----------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| nautobot_firewall_models_policyrulem2m | CREATE TABLE `nautobot_firewall_models_policyrulem2m` (
  `id` char(32) NOT NULL,
  `index` smallint(5) unsigned DEFAULT NULL CHECK (`index` >= 0),
  `policy_id` char(32) NOT NULL,
  `rule_id` char(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_with_index` (`policy_id`,`rule_id`,`index`),
  KEY `nautobot_firewall_mo_rule_id_4fdd7827_fk_nautobot_` (`rule_id`),
  CONSTRAINT `nautobot_firewall_mo_policy_id_9591fe9b_fk_nautobot_` FOREIGN KEY (`policy_id`) REFERENCES `nautobot_firewall_models_policy` (`id`),
  CONSTRAINT `nautobot_firewall_mo_rule_id_4fdd7827_fk_nautobot_` FOREIGN KEY (`rule_id`) REFERENCES `nautobot_firewall_models_policyrule` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+----------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

MariaDB [nautobot]> alter table nautobot_firewall_models_policyrulem2m drop constraint nautobot_firewall_mo_policy_id_9591fe9b_fk_nautobot_;
Query OK, 0 rows affected (0.022 sec)
Records: 0  Duplicates: 0  Warnings: 0