410873 - Dropping indexes sometimes needs to drop Foreign Keys

Status: RESOLVED FIXED

(Bugzilla :: Database, defect, P1)

Description

[Albert Ting]

+++ This bug was initially created as a clone of Bug #328904 +++

Bug 328904, comment #44 highlights a problem with dropping an index.  MySQL won't allow this until the foreign key is drop and it doesn't sound like MySQL supports this automatically.  Some details can be found at the MySQL ticket system, http://bugs.mysql.com/bug.php?id=21395.

For example, if I want to delete profiles_activity_fieldid_idx, I have to do something like this.  

        $dbh->do('ALTER TABLE DROP FOREIGN KEY 
                  fk_profiles_activity_fieldid_fielddefs_id');
        $dbh->bz_drop_index('profiles_activity',
                            'profiles_activity_fieldid_idx');

So I think Bugzilla has to do this until MySQL does this automatically.

Comment 1

[Max Kanat-Alexander]

Bugzilla already supports dropping FKs, but this is a difficult circumstance, because we don't know what index the FKs are depending on.

In the case where an FK requires an index but Bugzilla itself no longer requires that index, we should drop the index, then drop and re-create the FK, so that it gets its default index name.

Comment 2

[Frédéric Buclin]

Bugzilla 3.2 is restricted to security bugs only. Moreover, this bug is either assigned to nobody or got no traction for several months now. Rather than retargetting it at each new release, I'm clearing the target milestone and the bug will be retargetted to some sensible release when someone starts fixing this bug for real (Bugzilla 3.8 more likely).

Comment 3

[Frédéric Buclin]

Fixed in Bugzilla 4.2 by bug 451735.