824346 - The flaginclusions and flagexclusions DB tables have no UNIQUE index

Status: RESOLVED FIXED

(Bugzilla :: Database, enhancement)

Description

[Frédéric Buclin]

The flaginclusions and flagexclusions DB tables have no UNIQUE index and so it's possible to insert duplicated rows. We already have an index for both tables, they should simply include UNIQUE in them. (The audit_log table also has no such index, but that's another story.)

Comment 1

[David Lawrence [:dkl]]

Attachment: Patch to update to unique index on flag clusion tables (v1)

Comment 2

[Frédéric Buclin]

Comment on attachment 709777 [details] [diff] [review]
Patch to update to unique index on flag clusion tables (v1)

>=== modified file 'Bugzilla/Install/DB.pm'

>+    foreach my $table ('flaginclusions', 'flagexclusions') {
>+        my $idx = $dbh->bz_index_info($table, $table . '_type_id_idx');

Instead of writing |$table . '_type_id_idx'| 3 times, please define:

  my $index = $table . '_type_id_idx';

right before $idx and use it where appropriate.


r=LpSolit with this fix (make sure to retest your patch to not break DB upgrades).

Comment 3

[David Lawrence [:dkl]]

Attachment: Patch to update to unique index on flag clusion tables (v2)

Sorry to need another review but I felt it better to go ahead and extend this patch to also remove any possible duplicates in the flaginclusions/flagexclusions table right before updating the indexes. This will keep a nasty DB error from happening when a Bugzilla already has some duplicates in one or more of those tables. I did test that scenario. Please review.

dkl

Comment 4

[Frédéric Buclin]

Comment on attachment 713931 [details] [diff] [review]
Patch to update to unique index on flag clusion tables (v2)

>=== modified file 'Bugzilla/Install/DB.pm'

>+            say "Removing duplicated entries from the '$table' table...\n" if @$dupes;

Remove \n. say() already includes it. r=LpSolit

Comment 5

[Frédéric Buclin]

Added to relnotes for 4.4rc2. Please commit asap.

Comment 6

[David Lawrence [:dkl]]

Committing to: bzr+ssh://[e-mail]/bugzilla/trunk
modified Bugzilla/Install/DB.pm
modified Bugzilla/DB/Schema.pm                        
Committed revision 8583.

Committing to: bzr+ssh://[e-mail]/bugzilla/4.4         
modified Bugzilla/Install/DB.pm
modified Bugzilla/DB/Schema.pm
Committed revision 8522.  

dkl