Bug 284850: checksetup should rename indexes to conform to the new standard

Patch By Max Kanat-Alexander <mkanat@bugzilla.org> r=jouni, a=myk

4 files changed, 337 insertions, 77 deletions

diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm
index 28eb9eed9..b0106314e 100644
--- a/Bugzilla/DB.pm
+++ b/Bugzilla/DB.pm
@@ -305,10 +305,7 @@ sub bz_setup_database {
     my ($self) = @_;
 
     # Get a list of the existing tables (if any) in the database
-    my $table_sth = $self->table_info(undef, undef, undef, "TABLE");
-    my @current_tables =
-        @{$self->selectcol_arrayref($table_sth, { Columns => [3] })};
-
+    my @current_tables = $self->bz_table_list_real();
     my @desired_tables = $self->_bz_schema->get_table_list();
 
     foreach my $table_name (@desired_tables) {
@@ -650,6 +647,39 @@ sub bz_table_exists ($) {
 }
 
 #####################################################################
+# Protected "Real Database" Schema Information Methods
+#####################################################################
+
+# Only Bugzilla::DB and subclasses should use these methods.
+# If you need a method that does the same thing as one of these
+# methods, use the version without _real on the end.
+
+# bz_table_columns_real($table)
+#
+# Description: Returns a list of columns on a given table
+#              as the table actually is, on the disk.
+# Params:      $table - Name of the table.
+# Returns:     An array of column names.
+#
+sub bz_table_columns_real {
+    my ($self, $table) = @_;
+    my $sth = $self->column_info(undef, undef, $table, '%');
+    return @{ $self->selectcol_arrayref($sth, {Columns => [4]}) };
+}
+
+# bz_table_list_real()
+#
+# Description: Gets a list of tables in the current
+#              database, directly from the disk.
+# Params:      none
+# Returns:     An array containing table names.
+sub bz_table_list_real {
+    my ($self) = @_;
+    my $table_sth = $self->table_info(undef, undef, undef, "TABLE");
+    return @{$self->selectcol_arrayref($table_sth, { Columns => [3] })};
+}
+
+#####################################################################
 # Transaction Methods
 #####################################################################
 
diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm
index 7b2a7b2c2..1d10838e9 100644
--- a/Bugzilla/DB/Mysql.pm
+++ b/Bugzilla/DB/Mysql.pm
@@ -230,7 +230,226 @@ sub bz_setup_database {
         print "\nISAM->MyISAM table conversion done.\n\n";
     }
 
+    # Versions of Bugzilla before the existence of Bugzilla::DB::Schema did 
+    # not provide explicit names for the table indexes. This means
+    # that our upgrades will not be reliable, because we look for the name
+    # of the index, not what fields it is on, when doing upgrades.
+    # (using the name is much better for cross-database compatibility 
+    # and general reliability). It's also very important that our
+    # Schema object be consistent with what is on the disk.
+    #
+    # While we're at it, we also fix some inconsistent index naming
+    # from the original checkin of Bugzilla::DB::Schema.
+
+    # We check for the existence of a particular "short name" index that
+    # has existed at least since Bugzilla 2.8, and probably earlier.
+    # For fixing the inconsistent naming of Schema indexes,
+    # we also check for one of those inconsistently-named indexes.
+    my @tables = $self->bz_table_list_real();
+    if ( scalar(@tables) && 
+         ($self->bz_index_info_real('bugs', 'assigned_to') ||
+          $self->bz_index_info_real('flags', 'flags_bidattid_idx')) )
+    {
+        my $bug_count = $self->selectrow_array("SELECT COUNT(*) FROM bugs");
+        # We estimate one minute for each 3000 bugs, plus 3 minutes just
+        # to handle basic MySQL stuff.
+        my $rename_time = int($bug_count / 3000) + 3;
+        # If we're going to take longer than 5 minutes, we let the user know
+        # and allow them to abort.
+        if ($rename_time > 5) {
+            print "\nWe are about to rename old indexes.\n"
+                  . "The estimated time to complete renaming is "
+                  . "$rename_time minutes.\n"
+                  . "You cannot interrupt this action once it has begun.\n"
+                  . "If you would like to cancel, press Ctrl-C now..."
+                  . " (Waiting 45 seconds...)\n\n";
+            # Wait 45 seconds for them to respond.
+            sleep(45);
+        }
+        print "Renaming indexes...\n";
+
+        # We can't be interrupted, because of how the "if"
+        # works above.
+        local $SIG{INT}  = 'IGNORE';
+        local $SIG{TERM} = 'IGNORE';
+        local $SIG{PIPE} = 'IGNORE';
+
+        # Certain indexes had names in Schema that did not easily conform
+        # to a standard. We store those names here, so that they
+        # can be properly renamed.
+        my $bad_names = {
+            bugs_activity => ('bugs_activity_bugid_idx',
+                'bugs_activity_bugwhen_idx'),
+            longdescs => ('longdescs_bugid_idx',
+               'longdescs_bugwhen_idx'),
+            flags => ('flags_bidattid_idx'),
+            flaginclusions => ('flaginclusions_tpcid_idx'),
+            flagexclusions => ('flagexclusions_tpc_id_idx'),
+            profiles_activity => ('profiles_activity_when_idx'),
+            group_control_map => ('group_control_map_gid_idx')
+            # series_categories is dealt with below, not here.
+        };
+
+        # The series table is broken and needs to have one index
+        # dropped before we begin the renaming, because it had a
+        # useless index on it that would cause a naming conflict here.
+        if (grep($_ eq 'series', @tables)) {
+            my $dropname;
+            # This is what the bad index was called before Schema.
+            if ($self->bz_index_info_real('series', 'creator_2')) {
+                $dropname = 'creator_2';
+            }
+            # This is what the bad index is called in Schema.
+            elsif ($self->bz_index_info_real('series', 'series_creator_idx')) {
+                    $dropname = 'series_creator_idx';
+            }
+
+            if ($dropname) {
+                print "Removing the useless index $dropname on the"
+                      . " series table...\n";
+                my @drop = $self->_bz_schema->get_drop_index_ddl(
+                    'series', $dropname);
+                foreach my $sql (@drop) {
+                    $self->do($sql);
+                }
+            }
+        }
+
+        # The email_setting table also had the same problem.
+        if( grep($_ eq 'email_setting', @tables) 
+            && $self->bz_index_info_real('email_setting', 
+                                         'email_settings_user_id_idx') ) 
+        {
+            print "Removing the useless index email_settings_user_id_idx\n"
+                  . "  on the email_setting table...\n";
+            my @drop = $self->_bz_schema->get_drop_index_ddl('email_setting',
+                'email_settings_user_id_idx');
+            $self->do($_) foreach (@drop);
+        }
+
+        # Go through all the tables.
+        foreach my $table (@tables) {
+            # And go through all the columns on each table.
+            my @columns = $self->bz_table_columns_real($table);
+
+            # We also want to fix the silly naming of unique indexes
+            # that happened when we first checked-in Bugzilla::DB::Schema.
+            if ($table eq 'series_categories') {
+                # The series_categories index had a nonstandard name.
+                push(@columns, 'series_cats_unique_idx');
+            } 
+            elsif ($table eq 'email_setting') { 
+                # The email_setting table had a similar problem.
+                push(@columns, 'email_settings_unique_idx');
+            }
+            else {
+                push(@columns, "${table}_unique_idx");
+            }
+            # And this is how we fix the other inconsistent Schema naming.
+            push(@columns, $bad_names->{$table})
+                if (exists $bad_names->{$table});
+            foreach my $column (@columns) {
+                # If we have an index named after this column, it's an 
+                # old-style-name index.
+                # This will miss PRIMARY KEY indexes, but that's OK 
+                # because we aren't renaming them.
+                if (my $index = $self->bz_index_info_real($table, $column)) {
+                    # Fix the name to fit in with the new naming scheme.
+                    my $new_name = $table . "_" .
+                                   $index->{FIELDS}->[0] . "_idx";
+                    print "Renaming index $column to to $new_name...\n";
+                    # Unfortunately, MySQL has no way to rename an index. :-(
+                    # So we have to drop and recreate the indexes.
+                    my @drop = $self->_bz_schema->get_drop_index_ddl(
+                        $table, $column);
+                    my @add = $self->_bz_schema->get_add_index_ddl(
+                        $table, $new_name, $index);
+                    $self->do($_) foreach (@drop);
+                    $self->do($_) foreach (@add);
+                } # if
+            } # foreach column
+        } # foreach table
+    } # if old-name indexes
+
     $self->SUPER::bz_setup_database();
 }
 
+
+#####################################################################
+# MySQL-specific Database-Reading Methods
+#####################################################################
+
+=begin private
+
+=head 1 MYSQL-SPECIFIC DATABASE-READING METHODS
+
+These methods read information about the database from the disk,
+instead of from a Schema object. They are only reliable for MySQL 
+(see bug 285111 for the reasons why not all DBs use/have functions
+like this), but that's OK because we only need them for 
+backwards-compatibility anyway, for versions of Bugzilla before 2.20.
+
+=over 4
+
+=item C<bz_index_info_real($table, $index)>
+
+ Description: Returns information about an index on a table in the database.
+ Params:      $table = name of table containing the index
+              $index = name of an index
+ Returns:     An abstract index definition, always in hashref format.
+              If the index does not exist, the function returns undef.
+=cut
+sub bz_index_info_real {
+    my ($self, $table, $index) = @_;
+
+    my $sth = $self->prepare("SHOW INDEX FROM $table");
+    $sth->execute;
+
+    my @fields;
+    my $index_type;
+    # $raw_def will be an arrayref containing the following information:
+    # 0 = name of the table that the index is on
+    # 1 = 0 if unique, 1 if not unique
+    # 2 = name of the index
+    # 3 = seq_in_index (The order of the current field in the index).
+    # 4 = Name of ONE column that the index is on
+    # 5 = 'Collation' of the index. Usually 'A'.
+    # 6 = Cardinality. Either a number or undef.
+    # 7 = sub_part. Usually undef. Sometimes 1.
+    # 8 = "packed". Usually undef.
+    # MySQL 3
+    # -------
+    # 9 = comments. Usually an empty string. Sometimes 'FULLTEXT'.
+    # MySQL 4
+    # -------
+    # 9 = Null. Sometimes undef, sometimes 'YES'.
+    # 10 = Index_type. The type of the index. Usually either 'BTREE' or 'FULLTEXT'
+    # 11 = 'Comment.' Usually undef.
+    my $is_mysql3 = ($self->bz_server_version() =~ /^3/);
+    my $index_type_loc = $is_mysql3 ? 9 : 10;
+    while (my $raw_def = $sth->fetchrow_arrayref) {
+        if ($raw_def->[2] eq $index) {
+            push(@fields, $raw_def->[4]);
+            # No index can be both UNIQUE and FULLTEXT, that's why
+            # this is written this way.
+            $index_type = $raw_def->[1] ? '' : 'UNIQUE';
+            $index_type = $raw_def->[$index_type_loc] eq 'FULLTEXT'
+                ? 'FULLTEXT' : $index_type;
+        }
+    }
+
+    my $retval;
+    if (scalar(@fields)) {
+        $retval = {FIELDS => \@fields, TYPE => $index_type};
+    }
+    return $retval;
+}
+
 1;
+
+__END__
+
+=back
+
+=end private
+
diff --git a/Bugzilla/DB/Schema.pm b/Bugzilla/DB/Schema.pm
index 054080772..3d008a09b 100644
--- a/Bugzilla/DB/Schema.pm
+++ b/Bugzilla/DB/Schema.pm
@@ -191,7 +191,7 @@ use constant ABSTRACT_SCHEMA => {
             alias               => {TYPE => 'varchar(20)'},
         ],
         INDEXES => [
-            bugs_unique_idx           => {FIELDS => ['alias'],
+            bugs_alias_idx            => {FIELDS => ['alias'],
                                           TYPE => 'UNIQUE'},
             bugs_assigned_to_idx      => ['assigned_to'],
             bugs_creation_ts_idx      => ['creation_ts'],
@@ -224,9 +224,9 @@ use constant ABSTRACT_SCHEMA => {
             removed   => {TYPE => 'TINYTEXT'},
         ],
         INDEXES => [
-            bugs_activity_bugid_idx   => ['bug_id'],
+            bugs_activity_bug_id_idx  => ['bug_id'],
             bugs_activity_who_idx     => ['who'],
-            bugs_activity_bugwhen_idx => ['bug_when'],
+            bugs_activity_bug_when_idx => ['bug_when'],
             bugs_activity_fieldid_idx => ['fieldid'],
         ],
     },
@@ -237,7 +237,7 @@ use constant ABSTRACT_SCHEMA => {
             who    => {TYPE => 'INT3', NOTNULL => 1},
         ],
         INDEXES => [
-            cc_unique_idx => {FIELDS => [qw(bug_id who)],
+            cc_bug_id_idx => {FIELDS => [qw(bug_id who)],
                               TYPE => 'UNIQUE'},
             cc_who_idx    => ['who'],
         ],
@@ -257,9 +257,9 @@ use constant ABSTRACT_SCHEMA => {
                                 DEFAULT => 'FALSE'},
         ],
         INDEXES => [
-            longdescs_bugid_idx   => ['bug_id'],
+            longdescs_bug_id_idx   => ['bug_id'],
             longdescs_who_idx     => ['who'],
-            longdescs_bugwhen_idx => ['bug_when'],
+            longdescs_bug_when_idx => ['bug_when'],
             longdescs_thetext_idx => {FIELDS => ['thetext'],
                                       TYPE => 'FULLTEXT'},
         ],
@@ -308,6 +308,7 @@ use constant ABSTRACT_SCHEMA => {
         INDEXES => [
             attachments_bug_id_idx => ['bug_id'],
             attachments_creation_ts_idx => ['creation_ts'],
+            attachments_submitter_id_idx => ['submitter_id'],
         ],
     },
 
@@ -330,7 +331,7 @@ use constant ABSTRACT_SCHEMA => {
             description => {TYPE => 'MEDIUMTEXT'},
         ],
         INDEXES => [
-            keyworddefs_unique_idx => {FIELDS => ['name'],
+            keyworddefs_name_idx   => {FIELDS => ['name'],
                                        TYPE => 'UNIQUE'},
         ],
     },
@@ -341,7 +342,7 @@ use constant ABSTRACT_SCHEMA => {
             keywordid => {TYPE => 'INT2', NOTNULL => 1},
         ],
         INDEXES => [
-            keywords_unique_idx    => {FIELDS => [qw(bug_id keywordid)],
+            keywords_bug_id_idx    => {FIELDS => [qw(bug_id keywordid)],
                                        TYPE => 'UNIQUE'},
             keywords_keywordid_idx => ['keywordid'],
         ],
@@ -367,7 +368,7 @@ use constant ABSTRACT_SCHEMA => {
                                   DEFAULT => 'TRUE'},
         ],
         INDEXES => [
-            flags_bidattid_idx     => [qw(bug_id attach_id)],
+            flags_bug_id_idx       => [qw(bug_id attach_id)],
             flags_setter_id_idx    => ['setter_id'],
             flags_requestee_id_idx => ['requestee_id'],
         ],
@@ -408,7 +409,7 @@ use constant ABSTRACT_SCHEMA => {
             component_id => {TYPE => 'INT2'},
         ],
         INDEXES => [
-            flaginclusions_tpcid_idx =>
+            flaginclusions_type_id_idx =>
                 [qw(type_id product_id component_id)],
         ],
     },
@@ -420,7 +421,7 @@ use constant ABSTRACT_SCHEMA => {
             component_id => {TYPE => 'INT2'},
         ],
         INDEXES => [
-            flagexclusions_tpc_id_idx =>
+            flagexclusions_type_id_idx =>
                 [qw(type_id product_id component_id)],
         ],
     },
@@ -441,7 +442,7 @@ use constant ABSTRACT_SCHEMA => {
                             DEFAULT => 'FALSE'},
         ],
         INDEXES => [
-            fielddefs_unique_idx  => {FIELDS => ['name'],
+            fielddefs_name_idx    => {FIELDS => ['name'],
                                       TYPE => 'UNIQUE'},
             fielddefs_sortkey_idx => ['sortkey'],
         ],
@@ -464,8 +465,8 @@ use constant ABSTRACT_SCHEMA => {
             sortkey    => {TYPE => 'INT2', NOTNULL => 1},
         ],
         INDEXES => [
-            milestones_unique_idx => {FIELDS => [qw(product_id value)],
-                                      TYPE => 'UNIQUE'},
+            milestones_product_id_idx => {FIELDS => [qw(product_id value)],
+                                          TYPE => 'UNIQUE'},
         ],
     },
 
@@ -482,7 +483,7 @@ use constant ABSTRACT_SCHEMA => {
                          DEFAULT => 'TRUE'},
         ],
         INDEXES => [
-            bug_status_unique_idx  => {FIELDS => ['value'],
+            bug_status_value_idx  => {FIELDS => ['value'],
                                        TYPE => 'UNIQUE'},
             bug_status_sortkey_idx => ['sortkey', 'value'],
         ],
@@ -498,7 +499,7 @@ use constant ABSTRACT_SCHEMA => {
                          DEFAULT => 'TRUE'},
         ],
         INDEXES => [
-            resolution_unique_idx  => {FIELDS => ['value'],
+            resolution_value_idx   => {FIELDS => ['value'],
                                        TYPE => 'UNIQUE'},
             resolution_sortkey_idx => ['sortkey', 'value'],
         ],
@@ -514,7 +515,7 @@ use constant ABSTRACT_SCHEMA => {
                          DEFAULT => 'TRUE'},
         ],
         INDEXES => [
-            bug_severity_unique_idx  => {FIELDS => ['value'],
+            bug_severity_value_idx   => {FIELDS => ['value'],
                                          TYPE => 'UNIQUE'},
             bug_severity_sortkey_idx => ['sortkey', 'value'],
         ],
@@ -530,7 +531,7 @@ use constant ABSTRACT_SCHEMA => {
                          DEFAULT => 'TRUE'},
         ],
         INDEXES => [
-            priority_unique_idx  => {FIELDS => ['value'],
+            priority_value_idx   => {FIELDS => ['value'],
                                      TYPE => 'UNIQUE'},
             priority_sortkey_idx => ['sortkey', 'value'],
         ],
@@ -546,7 +547,7 @@ use constant ABSTRACT_SCHEMA => {
                          DEFAULT => 'TRUE'},
         ],
         INDEXES => [
-            rep_platform_unique_idx  => {FIELDS => ['value'],
+            rep_platform_value_idx   => {FIELDS => ['value'],
                                          TYPE => 'UNIQUE'},
             rep_platform_sortkey_idx => ['sortkey', 'value'],
         ],
@@ -562,7 +563,7 @@ use constant ABSTRACT_SCHEMA => {
                          DEFAULT => 'TRUE'},
         ],
         INDEXES => [
-            op_sys_unique_idx  => {FIELDS => ['value'],
+            op_sys_value_idx   => {FIELDS => ['value'],
                                    TYPE => 'UNIQUE'},
             op_sys_sortkey_idx => ['sortkey', 'value'],
         ],
@@ -588,8 +589,8 @@ use constant ABSTRACT_SCHEMA => {
             extern_id      => {TYPE => 'varchar(64)'},
         ],
         INDEXES => [
-            profiles_unique_idx => {FIELDS => ['login_name'],
-                                    TYPE => 'UNIQUE'},
+            profiles_login_name_idx => {FIELDS => ['login_name'],
+                                        TYPE => 'UNIQUE'},
         ],
     },
 
@@ -604,7 +605,7 @@ use constant ABSTRACT_SCHEMA => {
         ],
         INDEXES => [
             profiles_activity_userid_idx  => ['userid'],
-            profiles_activity_when_idx    => ['profiles_when'],
+            profiles_activity_profiles_when_idx => ['profiles_when'],
             profiles_activity_fieldid_idx => ['fieldid'],
         ],
     },
@@ -616,8 +617,7 @@ use constant ABSTRACT_SCHEMA => {
             event        => {TYPE => 'INT1', NOTNULL => 1},
         ],
         INDEXES => [
-            email_settings_user_id_idx => ['user_id'],
-            email_settings_unique_idx  =>
+            email_setting_user_id_idx  =>
                                     {FIELDS => [qw(user_id relationship event)],
                                      TYPE => 'UNIQUE'},
         ],
@@ -629,7 +629,7 @@ use constant ABSTRACT_SCHEMA => {
             watched => {TYPE => 'INT3', NOTNULL => 1},
         ],
         INDEXES => [
-            watch_unique_idx  => {FIELDS => [qw(watcher watched)],
+            watch_watcher_idx => {FIELDS => [qw(watcher watched)],
                                   TYPE => 'UNIQUE'},
             watch_watched_idx => ['watched'],
         ],
@@ -643,7 +643,7 @@ use constant ABSTRACT_SCHEMA => {
             query        => {TYPE => 'MEDIUMTEXT', NOTNULL => 1},
         ],
         INDEXES => [
-            namedqueries_unique_idx => {FIELDS => [qw(userid name)],
+            namedqueries_userid_idx => {FIELDS => [qw(userid name)],
                                         TYPE => 'UNIQUE'},
         ],
     },
@@ -697,7 +697,7 @@ use constant ABSTRACT_SCHEMA => {
                              DEFAULT => 'TRUE'},
         ],
         INDEXES => [
-            groups_unique_idx => {FIELDS => ['name'], TYPE => 'UNIQUE'},
+            groups_name_idx => {FIELDS => ['name'], TYPE => 'UNIQUE'},
         ],
     },
 
@@ -711,9 +711,9 @@ use constant ABSTRACT_SCHEMA => {
             canedit       => {TYPE => 'BOOLEAN', NOTNULL => 1},
         ],
         INDEXES => [
-            group_control_map_unique_idx =>
+            group_control_map_product_id_idx =>
             {FIELDS => [qw(product_id group_id)], TYPE => 'UNIQUE'},
-            group_control_map_gid_idx    => ['group_id'],
+            group_control_map_group_id_idx    => ['group_id'],
         ],
     },
 
@@ -734,7 +734,7 @@ use constant ABSTRACT_SCHEMA => {
                            DEFAULT => '0'},
         ],
         INDEXES => [
-            user_group_map_unique_idx =>
+            user_group_map_user_id_idx =>
                 {FIELDS => [qw(user_id group_id grant_type isbless)],
                  TYPE => 'UNIQUE'},
         ],
@@ -755,7 +755,7 @@ use constant ABSTRACT_SCHEMA => {
                            DEFAULT => '0'},
         ],
         INDEXES => [
-            group_group_map_unique_idx =>
+            group_group_map_member_id_idx =>
                 {FIELDS => [qw(member_id grantor_id grant_type)],
                  TYPE => 'UNIQUE'},
         ],
@@ -769,7 +769,7 @@ use constant ABSTRACT_SCHEMA => {
             group_id => {TYPE => 'INT3', NOTNULL => 1},
         ],
         INDEXES => [
-            bug_group_map_unique_idx   =>
+            bug_group_map_bug_id_idx   =>
                 {FIELDS => [qw(bug_id group_id)], TYPE => 'UNIQUE'},
             bug_group_map_group_id_idx => ['group_id'],
         ],
@@ -781,7 +781,7 @@ use constant ABSTRACT_SCHEMA => {
             group_id    => {TYPE => 'INT3', NOTNULL => 1},
         ],
         INDEXES => [
-            category_group_map_unique_idx =>
+            category_group_map_category_id_idx =>
                 {FIELDS => [qw(category_id group_id)], TYPE => 'UNIQUE'},
         ],
     },
@@ -798,7 +798,7 @@ use constant ABSTRACT_SCHEMA => {
             description => {TYPE => 'MEDIUMTEXT'},
         ],
         INDEXES => [
-            classifications_unique_idx => {FIELDS => ['name'],
+            classifications_name_idx => {FIELDS => ['name'],
                                            TYPE => 'UNIQUE'},
         ],
     },
@@ -821,7 +821,7 @@ use constant ABSTRACT_SCHEMA => {
                                   NOTNULL => 1, DEFAULT => "'---'"},
         ],
         INDEXES => [
-            products_unique_idx => {FIELDS => ['name'],
+            products_name_idx   => {FIELDS => ['name'],
                                     TYPE => 'UNIQUE'},
         ],
     },
@@ -837,8 +837,8 @@ use constant ABSTRACT_SCHEMA => {
             description      => {TYPE => 'MEDIUMTEXT', NOTNULL => 1},
         ],
         INDEXES => [
-            components_unique_idx => {FIELDS => [qw(product_id name)],
-                                      TYPE => 'UNIQUE'},
+            components_product_id_idx => {FIELDS => [qw(product_id name)],
+                                          TYPE => 'UNIQUE'},
             components_name_idx   => ['name'],
         ],
     },
@@ -861,10 +861,9 @@ use constant ABSTRACT_SCHEMA => {
                             DEFAULT => 'FALSE'},
         ],
         INDEXES => [
-            series_unique_idx  =>
+            series_creator_idx  =>
                 {FIELDS => [qw(creator category subcategory name)],
                  TYPE => 'UNIQUE'},
-            series_creator_idx => ['creator'],
         ],
     },
 
@@ -875,7 +874,7 @@ use constant ABSTRACT_SCHEMA => {
             series_value => {TYPE => 'INT3', NOTNULL => 1},
         ],
         INDEXES => [
-            series_data_unique_idx =>
+            series_data_series_id_idx =>
                 {FIELDS => [qw(series_id series_date)],
                  TYPE => 'UNIQUE'},
         ],
@@ -888,8 +887,8 @@ use constant ABSTRACT_SCHEMA => {
             name => {TYPE => 'varchar(64)', NOTNULL => 1},
         ],
         INDEXES => [
-            series_cats_unique_idx => {FIELDS => ['name'],
-                                       TYPE => 'UNIQUE'},
+            series_categories_name_idx => {FIELDS => ['name'],
+                                           TYPE => 'UNIQUE'},
         ],
     },
 
diff --git a/checksetup.pl b/checksetup.pl
index 0e2af1608..8decb119f 100755
--- a/checksetup.pl
+++ b/checksetup.pl
@@ -1905,7 +1905,10 @@ $dbh->bz_rename_field('bugs_activity', 'when', 'bug_when');
 # a new table format which will allow 32 indices.)
 
 $dbh->bz_drop_field('bugs', 'area');
-$dbh->bz_add_field('bugs',     'votes',        'mediumint not null, add index (votes)');
+if (!$dbh->bz_get_field_def('bugs', 'votes')) {
+    $dbh->bz_add_field('bugs', 'votes', 'mediumint not null');
+    $dbh->do('CREATE INDEX bugs_votes_idx ON bugs(votes)');
+}
 $dbh->bz_add_field('products', 'votesperuser', 'mediumint not null');
 
 
@@ -2114,8 +2117,9 @@ if ($dbh->bz_get_field_def('bugs', 'long_desc')) {
 # now has a pointer into that table instead of recording the name directly.
 
 if ($dbh->bz_get_field_def('bugs_activity', 'field')) {
-    $dbh->bz_add_field('bugs_activity', 'fieldid',
-             'mediumint not null, ADD INDEX (fieldid)');
+    $dbh->bz_add_field('bugs_activity', 'fieldid', 'mediumint not null');
+    $dbh->do('CREATE INDEX bugs_activity_fieldid_idx
+              ON bugs_activity(fieldid)');
     print "Populating new fieldid field ...\n";
 
     $dbh->bz_lock_tables('bugs_activity WRITE', 'fielddefs WRITE');
@@ -2200,8 +2204,9 @@ if ($dbh->bz_get_index_def('profiles', 'login_name')->[1]) {
     }
     print "OK, changing index type to prevent duplicates in the future ...\n";
     
-    $dbh->do("ALTER TABLE profiles DROP INDEX login_name");
-    $dbh->do("ALTER TABLE profiles ADD UNIQUE (login_name)");
+    $dbh->do("ALTER TABLE profiles DROP INDEX profiles_login_name_idx");
+    $dbh->do("CREATE UNIQUE INDEX profiles_login_name_idx"
+             . " ON profiles(login_name)");
 
 }    
 
@@ -2397,8 +2402,8 @@ if ( $dbh->bz_get_index_count('cc') != 3 ) {
     #
     print "Recreating indexes on cc table.\n";
     $dbh->bz_drop_table_indexes('cc');
-    $dbh->do("ALTER TABLE cc ADD UNIQUE (bug_id,who)");
-    $dbh->do("ALTER TABLE cc ADD INDEX (who)");
+    $dbh->do("CREATE UNIQUE INDEX cc_bug_id_idx ON cc(bug_id,who)");
+    $dbh->do("CREATE INDEX cc_who_idx ON cc(who)");
 }    
 
 if ( $dbh->bz_get_index_count('keywords') != 3 ) {
@@ -2407,8 +2412,9 @@ if ( $dbh->bz_get_index_count('keywords') != 3 ) {
     #
     print "Recreating indexes on keywords table.\n";
     $dbh->bz_drop_table_indexes('keywords');
-    $dbh->do("ALTER TABLE keywords ADD INDEX (keywordid)");
-    $dbh->do("ALTER TABLE keywords ADD UNIQUE (bug_id,keywordid)");
+    $dbh->do("CREATE UNIQUE INDEX keywords_bug_id_idx"
+             . " ON keywords(bug_id,keywordid)");
+    $dbh->do("CREATE INDEX keywords_keywordid_idx ON keywords(keywordid)");
 
 }    
 
@@ -2555,7 +2561,7 @@ ENDTEXT
 #   http://bugzilla.mozilla.org/show_bug.cgi?id=57350
 if (!defined $dbh->bz_get_index_def('longdescs','who')) {
     print "Adding index for who column in longdescs table...\n";
-    $dbh->do('ALTER TABLE longdescs ADD INDEX (who)');
+    $dbh->do('CREATE INDEX longdescs_who_idx ON longdescs(who)');
 }
 
 # 2001-06-15 kiko@async.com.br - Change bug:version size to avoid
@@ -2720,7 +2726,7 @@ $dbh->bz_add_field('attachments', 'isprivate', 'tinyint not null default 0');
 # in addition to ID.
 if (!$dbh->bz_get_field_def("bugs", "alias")) {
     $dbh->bz_add_field("bugs", "alias", "VARCHAR(20)");
-    $dbh->do("ALTER TABLE bugs ADD UNIQUE (alias)");
+    $dbh->do("CREATE UNIQUE INDEX bugs_alias_idx ON bugs(alias)");
 }
 
 # 2002-07-15 davef@tetsubo.com - bug 67950
@@ -2818,11 +2824,12 @@ if ($dbh->bz_get_field_def("products", "product")) {
     # Drop any indexes that may exist on the milestones table.
     $dbh->bz_drop_table_indexes('milestones');
 
-    $dbh->do("ALTER TABLE milestones ADD UNIQUE (product_id, value)");
-    $dbh->do("ALTER TABLE bugs DROP INDEX product");
-    $dbh->do("ALTER TABLE bugs ADD INDEX (product_id)");
-    $dbh->do("ALTER TABLE bugs DROP INDEX component");
-    $dbh->do("ALTER TABLE bugs ADD INDEX (component_id)");
+    $dbh->do("CREATE UNIQUE INDEX milestones_product_id_idx"
+             . " ON milestones(product_id, value)");
+    $dbh->do("ALTER TABLE bugs DROP INDEX bugs_product_idx");
+    $dbh->do("CREATE INDEX bugs_product_id_idx ON bugs(product_id)");
+    $dbh->do("ALTER TABLE bugs DROP INDEX bugs_component_idx");
+    $dbh->do("CREATE INDEX bugs_component_id_idx ON bugs(component_id)");
 
     print "Removing, renaming, and retyping old product and component fields.\n";
     $dbh->bz_drop_field("components", "program");
@@ -2837,9 +2844,10 @@ if ($dbh->bz_get_field_def("products", "product")) {
     $dbh->bz_change_field_type("components", "name", "varchar(64) not null");
 
     print "Adding indexes for products and components tables.\n";
-    $dbh->do("ALTER TABLE products ADD UNIQUE (name)");
-    $dbh->do("ALTER TABLE components ADD UNIQUE (product_id, name)");
-    $dbh->do("ALTER TABLE components ADD INDEX (name)");
+    $dbh->do("CREATE UNIQUE INDEX products_name_idx ON products(name)");
+    $dbh->do("CREATE UNIQUE INDEX component_product_id_idx"
+             . " ON components(product_id, name)");
+    $dbh->do("CREATE INDEX components_name_idx ON components(name)");
 }
 
 # 2002-08-14 - bbaetz@student.usyd.edu.au - bug 153578
@@ -2932,11 +2940,13 @@ if ($dbh->bz_get_field_def("profiles", "groupset")) {
     $dbh->bz_add_field('groups', 'last_changed', 'datetime not null');
     # Some mysql versions will promote any unique key to primary key
     # so all unique keys are removed first and then added back in
-    $dbh->do("ALTER TABLE groups DROP INDEX bit") if $dbh->bz_get_index_def("groups","bit");
-    $dbh->do("ALTER TABLE groups DROP INDEX name") if $dbh->bz_get_index_def("groups","name");
+    $dbh->do("ALTER TABLE groups DROP INDEX groups_bit_idx") 
+        if $dbh->bz_get_index_def("groups","bit");
+    $dbh->do("ALTER TABLE groups DROP INDEX groups_name_idx") 
+        if $dbh->bz_get_index_def("groups","name");
     $dbh->do("ALTER TABLE groups DROP PRIMARY KEY"); 
     $dbh->bz_add_field('groups', 'id', 'mediumint not null auto_increment primary key');
-    $dbh->do("ALTER TABLE groups ADD UNIQUE (name)");
+    $dbh->do("CREATE UNIQUE INDEX groups_name_idx ON groups(name)");
     $dbh->bz_add_field('profiles', 'refreshed_when', 'datetime not null');
 
     # Convert all existing groupset records to map entries before removing
@@ -3547,8 +3557,8 @@ if ($dbh->bz_get_field_def("user_group_map", "isderived")) {
               WHERE isbless = 0 AND grant_type != " . GRANT_DIRECT);
     $dbh->bz_drop_field("user_group_map", "isderived");
     $dbh->bz_drop_table_indexes("user_group_map");
-    $dbh->do("ALTER TABLE user_group_map 
-              ADD UNIQUE (user_id, group_id, grant_type, isbless)");
+    $dbh->do("CREATE UNIQUE INDEX user_group_map_user_id_idx
+              ON user_group_map(user_id, group_id, grant_type, isbless)");
     # Evaluate regexp-based group memberships
     my $sth = $dbh->prepare("SELECT profiles.userid, profiles.login_name,
                              groups.id, groups.userregexp 
@@ -3582,8 +3592,8 @@ if ($dbh->bz_get_field_def("group_group_map", "isbless")) {
                              GROUP_MEMBERSHIP . ")");
     $dbh->bz_drop_table_indexes("group_group_map");
     $dbh->bz_drop_field("group_group_map", "isbless");
-    $dbh->do("ALTER TABLE group_group_map 
-              ADD UNIQUE (member_id, grantor_id, grant_type)");
+    $dbh->do("CREATE UNIQUE INDEX group_group_map_member_id_idx
+              ON group_group_map(member_id, grantor_id, grant_type)");
 }    
 
 # Allow profiles to optionally be linked to a unique identifier in an outside
@@ -3653,11 +3663,12 @@ if (!$dbh->bz_get_field_def('fielddefs', 'obsolete')) {
 # Add fulltext indexes for bug summaries and descriptions/comments.
 if (!defined $dbh->bz_get_index_def('bugs', 'short_desc')) {
     print "Adding full-text index for short_desc column in bugs table...\n";
-    $dbh->do('ALTER TABLE bugs ADD FULLTEXT (short_desc)');
+    $dbh->do('CREATE FULLTEXT INDEX bugs_short_desc_idx ON bugs(short_desc)');
 }
 if (!defined $dbh->bz_get_index_def('longdescs', 'thetext')) {
     print "Adding full-text index for thetext column in longdescs table...\n";
-    $dbh->do('ALTER TABLE longdescs ADD FULLTEXT (thetext)');
+    $dbh->do('CREATE FULLTEXT INDEX longdescs_thetext_idx
+              ON longdescs(thetext)');
 }
 
 # 2002 November, myk@mozilla.org, bug 178841:
@@ -3775,11 +3786,12 @@ if (($fielddef = $dbh->bz_get_field_def("bugs", "delta_ts")) &&
 # 2005-02-12 bugreport@peshkin.net, bug 281787
 if (!defined $dbh->bz_get_index_def('attachments','submitter_id')) {
     print "Adding index for submitter_id column in attachments table...\n";
-    $dbh->do('ALTER TABLE attachments ADD INDEX (submitter_id)');
+    $dbh->do('CREATE INDEX attachments_submitter_id_idx'
+             . ' ON attachments(submitter_id)');
 }
 if (!defined $dbh->bz_get_index_def('bugs_activity','who')) {
     print "Adding index for who column in bugs_activity table...\n";
-    $dbh->do('ALTER TABLE bugs_activity ADD INDEX (who)');
+    $dbh->do('CREATE INDEX bugs_activity_who_idx ON bugs_activity(who)');
 }
 
 # This lastdiffed change and these default changes are unrelated,