diff options
Diffstat (limited to 'Bugzilla/DB/Mysql.pm')
| -rw-r--r-- | Bugzilla/DB/Mysql.pm | 1603 |
1 files changed, 815 insertions, 788 deletions
diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm index d0915f1e6..a58d88df4 100644 --- a/Bugzilla/DB/Mysql.pm +++ b/Bugzilla/DB/Mysql.pm @@ -37,258 +37,265 @@ use List::Util qw(max); use Text::ParseWords; # This is how many comments of MAX_COMMENT_LENGTH we expect on a single bug. -# In reality, you could have a LOT more comments than this, because +# In reality, you could have a LOT more comments than this, because # MAX_COMMENT_LENGTH is big. use constant MAX_COMMENTS => 50; use constant FULLTEXT_OR => '|'; sub new { - my ($class, $params) = @_; - my ($user, $pass, $host, $dbname, $port, $sock) = - @$params{qw(db_user db_pass db_host db_name db_port db_sock)}; - - # construct the DSN from the parameters we got - my $dsn = "dbi:mysql:host=$host;database=$dbname"; - $dsn .= ";port=$port" if $port; - $dsn .= ";mysql_socket=$sock" if $sock; - - my %attrs = ( - mysql_enable_utf8 => Bugzilla->params->{'utf8'}, - # Needs to be explicitly specified for command-line processes. - mysql_auto_reconnect => 1, - ); - - # MySQL SSL options - my ($ssl_ca_file, $ssl_ca_path, $ssl_cert, $ssl_key) = - @$params{qw(db_mysql_ssl_ca_file db_mysql_ssl_ca_path - db_mysql_ssl_client_cert db_mysql_ssl_client_key)}; - if ($ssl_ca_file || $ssl_ca_path || $ssl_cert || $ssl_key) { - $attrs{'mysql_ssl'} = 1; - $attrs{'mysql_ssl_ca_file'} = $ssl_ca_file if $ssl_ca_file; - $attrs{'mysql_ssl_ca_path'} = $ssl_ca_path if $ssl_ca_path; - $attrs{'mysql_ssl_client_cert'} = $ssl_cert if $ssl_cert; - $attrs{'mysql_ssl_client_key'} = $ssl_key if $ssl_key; + my ($class, $params) = @_; + my ($user, $pass, $host, $dbname, $port, $sock) + = @$params{qw(db_user db_pass db_host db_name db_port db_sock)}; + + # construct the DSN from the parameters we got + my $dsn = "dbi:mysql:host=$host;database=$dbname"; + $dsn .= ";port=$port" if $port; + $dsn .= ";mysql_socket=$sock" if $sock; + + my %attrs = ( + mysql_enable_utf8 => Bugzilla->params->{'utf8'}, + + # Needs to be explicitly specified for command-line processes. + mysql_auto_reconnect => 1, + ); + + # MySQL SSL options + my ($ssl_ca_file, $ssl_ca_path, $ssl_cert, $ssl_key) = @$params{ + qw(db_mysql_ssl_ca_file db_mysql_ssl_ca_path + db_mysql_ssl_client_cert db_mysql_ssl_client_key) + }; + if ($ssl_ca_file || $ssl_ca_path || $ssl_cert || $ssl_key) { + $attrs{'mysql_ssl'} = 1; + $attrs{'mysql_ssl_ca_file'} = $ssl_ca_file if $ssl_ca_file; + $attrs{'mysql_ssl_ca_path'} = $ssl_ca_path if $ssl_ca_path; + $attrs{'mysql_ssl_client_cert'} = $ssl_cert if $ssl_cert; + $attrs{'mysql_ssl_client_key'} = $ssl_key if $ssl_key; + } + + my $self = $class->db_new( + {dsn => $dsn, user => $user, pass => $pass, attrs => \%attrs}); + + # This makes sure that if the tables are encoded as UTF-8, we + # return their data correctly. + $self->do("SET NAMES utf8") if Bugzilla->params->{'utf8'}; + + # all class local variables stored in DBI derived class needs to have + # a prefix 'private_'. See DBI documentation. + $self->{private_bz_tables_locked} = ""; + + # Needed by TheSchwartz + $self->{private_bz_dsn} = $dsn; + + bless($self, $class); + + # Check for MySQL modes. + my ($var, $sql_mode) + = $self->selectrow_array("SHOW VARIABLES LIKE 'sql\\_mode'"); + + # Disable ANSI and strict modes, else Bugzilla will crash. + if ($sql_mode) { + + # STRICT_TRANS_TABLE or STRICT_ALL_TABLES enable MySQL strict mode, + # causing bug 321645. TRADITIONAL sets these modes (among others) as + # well, so it has to be stipped as well + my $new_sql_mode = join(",", + grep { $_ !~ /^(?:ANSI|STRICT_(?:TRANS|ALL)_TABLES|TRADITIONAL)$/ } + split(/,/, $sql_mode)); + + if ($sql_mode ne $new_sql_mode) { + $self->do("SET SESSION sql_mode = ?", undef, $new_sql_mode); } + } - my $self = $class->db_new({ dsn => $dsn, user => $user, - pass => $pass, attrs => \%attrs }); - - # This makes sure that if the tables are encoded as UTF-8, we - # return their data correctly. - $self->do("SET NAMES utf8") if Bugzilla->params->{'utf8'}; - - # all class local variables stored in DBI derived class needs to have - # a prefix 'private_'. See DBI documentation. - $self->{private_bz_tables_locked} = ""; - - # Needed by TheSchwartz - $self->{private_bz_dsn} = $dsn; + # Allow large GROUP_CONCATs (largely for inserting comments + # into bugs_fulltext). + $self->do('SET SESSION group_concat_max_len = 128000000'); - bless ($self, $class); + # MySQL 5.5.2 and older have this variable set to true, which causes + # trouble, see bug 870369. + $self->do('SET SESSION sql_auto_is_null = 0'); - # Check for MySQL modes. - my ($var, $sql_mode) = $self->selectrow_array( - "SHOW VARIABLES LIKE 'sql\\_mode'"); - - # Disable ANSI and strict modes, else Bugzilla will crash. - if ($sql_mode) { - # STRICT_TRANS_TABLE or STRICT_ALL_TABLES enable MySQL strict mode, - # causing bug 321645. TRADITIONAL sets these modes (among others) as - # well, so it has to be stipped as well - my $new_sql_mode = - join(",", grep {$_ !~ /^(?:ANSI|STRICT_(?:TRANS|ALL)_TABLES|TRADITIONAL)$/} - split(/,/, $sql_mode)); - - if ($sql_mode ne $new_sql_mode) { - $self->do("SET SESSION sql_mode = ?", undef, $new_sql_mode); - } - } - - # Allow large GROUP_CONCATs (largely for inserting comments - # into bugs_fulltext). - $self->do('SET SESSION group_concat_max_len = 128000000'); - - # MySQL 5.5.2 and older have this variable set to true, which causes - # trouble, see bug 870369. - $self->do('SET SESSION sql_auto_is_null = 0'); - - return $self; + return $self; } # when last_insert_id() is supported on MySQL by lowest DBI/DBD version # required by Bugzilla, this implementation can be removed. sub bz_last_key { - my ($self) = @_; + my ($self) = @_; - my ($last_insert_id) = $self->selectrow_array('SELECT LAST_INSERT_ID()'); + my ($last_insert_id) = $self->selectrow_array('SELECT LAST_INSERT_ID()'); - return $last_insert_id; + return $last_insert_id; } sub sql_group_concat { - my ($self, $column, $separator, $sort, $order_by) = @_; - $separator = $self->quote(', ') if !defined $separator; - $sort = 1 if !defined $sort; - if ($order_by) { - $column .= " ORDER BY $order_by"; - } - elsif ($sort) { - my $sort_order = $column; - $sort_order =~ s/^DISTINCT\s+//i; - $column = "$column ORDER BY $sort_order"; - } - return "GROUP_CONCAT($column SEPARATOR $separator)"; + my ($self, $column, $separator, $sort, $order_by) = @_; + $separator = $self->quote(', ') if !defined $separator; + $sort = 1 if !defined $sort; + if ($order_by) { + $column .= " ORDER BY $order_by"; + } + elsif ($sort) { + my $sort_order = $column; + $sort_order =~ s/^DISTINCT\s+//i; + $column = "$column ORDER BY $sort_order"; + } + return "GROUP_CONCAT($column SEPARATOR $separator)"; } sub sql_regexp { - my ($self, $expr, $pattern, $nocheck, $real_pattern) = @_; - $real_pattern ||= $pattern; + my ($self, $expr, $pattern, $nocheck, $real_pattern) = @_; + $real_pattern ||= $pattern; - $self->bz_check_regexp($real_pattern) if !$nocheck; + $self->bz_check_regexp($real_pattern) if !$nocheck; - return "$expr REGEXP $pattern"; + return "$expr REGEXP $pattern"; } sub sql_not_regexp { - my ($self, $expr, $pattern, $nocheck, $real_pattern) = @_; - $real_pattern ||= $pattern; + my ($self, $expr, $pattern, $nocheck, $real_pattern) = @_; + $real_pattern ||= $pattern; - $self->bz_check_regexp($real_pattern) if !$nocheck; + $self->bz_check_regexp($real_pattern) if !$nocheck; - return "$expr NOT REGEXP $pattern"; + return "$expr NOT REGEXP $pattern"; } sub sql_limit { - my ($self, $limit, $offset) = @_; - - if (defined($offset)) { - return "LIMIT $offset, $limit"; - } else { - return "LIMIT $limit"; - } + my ($self, $limit, $offset) = @_; + + if (defined($offset)) { + return "LIMIT $offset, $limit"; + } + else { + return "LIMIT $limit"; + } } sub sql_string_concat { - my ($self, @params) = @_; - - return 'CONCAT(' . join(', ', @params) . ')'; + my ($self, @params) = @_; + + return 'CONCAT(' . join(', ', @params) . ')'; } sub sql_fulltext_search { - my ($self, $column, $text) = @_; - - # Add the boolean mode modifier if the search string contains - # boolean operators at the start or end of a word. - my $mode = ''; - if ($text =~ /(?:^|\W)[+\-<>~"()]/ || $text =~ /[()"*](?:$|\W)/) { - $mode = 'IN BOOLEAN MODE'; - - my @terms = split(quotemeta(FULLTEXT_OR), $text); - foreach my $term (@terms) { - # quote un-quoted compound words - my @words = quotewords('[\s()]+', 'delimiters', $term); - foreach my $word (@words) { - # match words that have non-word chars in the middle of them - if ($word =~ /\w\W+\w/ && $word !~ m/"/) { - $word = '"' . $word . '"'; - } - } - $term = join('', @words); + my ($self, $column, $text) = @_; + + # Add the boolean mode modifier if the search string contains + # boolean operators at the start or end of a word. + my $mode = ''; + if ($text =~ /(?:^|\W)[+\-<>~"()]/ || $text =~ /[()"*](?:$|\W)/) { + $mode = 'IN BOOLEAN MODE'; + + my @terms = split(quotemeta(FULLTEXT_OR), $text); + foreach my $term (@terms) { + + # quote un-quoted compound words + my @words = quotewords('[\s()]+', 'delimiters', $term); + foreach my $word (@words) { + + # match words that have non-word chars in the middle of them + if ($word =~ /\w\W+\w/ && $word !~ m/"/) { + $word = '"' . $word . '"'; } - $text = join(FULLTEXT_OR, @terms); + } + $term = join('', @words); } + $text = join(FULLTEXT_OR, @terms); + } - # quote the text for use in the MATCH AGAINST expression - $text = $self->quote($text); + # quote the text for use in the MATCH AGAINST expression + $text = $self->quote($text); - # untaint the text, since it's safe to use now that we've quoted it - trick_taint($text); + # untaint the text, since it's safe to use now that we've quoted it + trick_taint($text); - return "MATCH($column) AGAINST($text $mode)"; + return "MATCH($column) AGAINST($text $mode)"; } sub sql_istring { - my ($self, $string) = @_; - - return $string; + my ($self, $string) = @_; + + return $string; } sub sql_from_days { - my ($self, $days) = @_; + my ($self, $days) = @_; - return "FROM_DAYS($days)"; + return "FROM_DAYS($days)"; } sub sql_to_days { - my ($self, $date) = @_; + my ($self, $date) = @_; - return "TO_DAYS($date)"; + return "TO_DAYS($date)"; } sub sql_date_format { - my ($self, $date, $format) = @_; + my ($self, $date, $format) = @_; + + $format = "%Y.%m.%d %H:%i:%s" if !$format; - $format = "%Y.%m.%d %H:%i:%s" if !$format; - - return "DATE_FORMAT($date, " . $self->quote($format) . ")"; + return "DATE_FORMAT($date, " . $self->quote($format) . ")"; } sub sql_date_math { - my ($self, $date, $operator, $interval, $units) = @_; - - return "$date $operator INTERVAL $interval $units"; + my ($self, $date, $operator, $interval, $units) = @_; + + return "$date $operator INTERVAL $interval $units"; } sub sql_iposition { - my ($self, $fragment, $text) = @_; - return "INSTR($text, $fragment)"; + my ($self, $fragment, $text) = @_; + return "INSTR($text, $fragment)"; } sub sql_position { - my ($self, $fragment, $text) = @_; + my ($self, $fragment, $text) = @_; - return "INSTR(CAST($text AS BINARY), CAST($fragment AS BINARY))"; + return "INSTR(CAST($text AS BINARY), CAST($fragment AS BINARY))"; } sub sql_group_by { - my ($self, $needed_columns, $optional_columns) = @_; + my ($self, $needed_columns, $optional_columns) = @_; - # MySQL allows you to specify the minimal subset of columns to get - # a unique result. While it does allow specifying all columns as - # ANSI SQL requires, according to MySQL documentation, the fewer - # columns you specify, the faster the query runs. - return "GROUP BY $needed_columns"; + # MySQL allows you to specify the minimal subset of columns to get + # a unique result. While it does allow specifying all columns as + # ANSI SQL requires, according to MySQL documentation, the fewer + # columns you specify, the faster the query runs. + return "GROUP BY $needed_columns"; } sub bz_explain { - my ($self, $sql) = @_; - my $sth = $self->prepare("EXPLAIN $sql"); - $sth->execute(); - my $columns = $sth->{'NAME'}; - my $lengths = $sth->{'mysql_max_length'}; - my $format_string = '|'; - my $i = 0; - foreach my $column (@$columns) { - # Sometimes the column name is longer than the contents. - my $length = max($lengths->[$i], length($column)); - $format_string .= ' %-' . $length . 's |'; - $i++; - } - - my $first_row = sprintf($format_string, @$columns); - my @explain_rows = ($first_row, '-' x length($first_row)); - while (my $row = $sth->fetchrow_arrayref) { - my @fixed = map { defined $_ ? $_ : 'NULL' } @$row; - push(@explain_rows, sprintf($format_string, @fixed)); - } - - return join("\n", @explain_rows); + my ($self, $sql) = @_; + my $sth = $self->prepare("EXPLAIN $sql"); + $sth->execute(); + my $columns = $sth->{'NAME'}; + my $lengths = $sth->{'mysql_max_length'}; + my $format_string = '|'; + my $i = 0; + foreach my $column (@$columns) { + + # Sometimes the column name is longer than the contents. + my $length = max($lengths->[$i], length($column)); + $format_string .= ' %-' . $length . 's |'; + $i++; + } + + my $first_row = sprintf($format_string, @$columns); + my @explain_rows = ($first_row, '-' x length($first_row)); + while (my $row = $sth->fetchrow_arrayref) { + my @fixed = map { defined $_ ? $_ : 'NULL' } @$row; + push(@explain_rows, sprintf($format_string, @fixed)); + } + + return join("\n", @explain_rows); } sub _bz_get_initial_schema { - my ($self) = @_; - return $self->_bz_build_schema_from_disk(); + my ($self) = @_; + return $self->_bz_build_schema_from_disk(); } ##################################################################### @@ -296,493 +303,503 @@ sub _bz_get_initial_schema { ##################################################################### sub bz_check_server_version { - my $self = shift; + my $self = shift; - my $lc = Bugzilla->localconfig; - if (lc(Bugzilla->localconfig->{db_name}) eq 'mysql') { - die "It is not safe to run Bugzilla inside a database named 'mysql'.\n" - . " Please pick a different value for \$db_name in localconfig.\n"; - } + my $lc = Bugzilla->localconfig; + if (lc(Bugzilla->localconfig->{db_name}) eq 'mysql') { + die "It is not safe to run Bugzilla inside a database named 'mysql'.\n" + . " Please pick a different value for \$db_name in localconfig.\n"; + } - $self->SUPER::bz_check_server_version(@_); + $self->SUPER::bz_check_server_version(@_); } sub bz_setup_database { - my ($self) = @_; - - # The "comments" field of the bugs_fulltext table could easily exceed - # MySQL's default max_allowed_packet. Also, MySQL should never have - # a max_allowed_packet smaller than our max_attachment_size. So, we - # warn the user here if max_allowed_packet is too small. - my $min_max_allowed = MAX_COMMENTS * MAX_COMMENT_LENGTH; - my (undef, $current_max_allowed) = $self->selectrow_array( - q{SHOW VARIABLES LIKE 'max\_allowed\_packet'}); - # This parameter is not yet defined when the DB is being built for - # the very first time. The code below still works properly, however, - # because the default maxattachmentsize is smaller than $min_max_allowed. - my $max_attachment = (Bugzilla->params->{'maxattachmentsize'} || 0) * 1024; - my $needed_max_allowed = max($min_max_allowed, $max_attachment); - if ($current_max_allowed < $needed_max_allowed) { - warn install_string('max_allowed_packet', - { current => $current_max_allowed, - needed => $needed_max_allowed }) . "\n"; + my ($self) = @_; + + # The "comments" field of the bugs_fulltext table could easily exceed + # MySQL's default max_allowed_packet. Also, MySQL should never have + # a max_allowed_packet smaller than our max_attachment_size. So, we + # warn the user here if max_allowed_packet is too small. + my $min_max_allowed = MAX_COMMENTS * MAX_COMMENT_LENGTH; + my (undef, $current_max_allowed) + = $self->selectrow_array(q{SHOW VARIABLES LIKE 'max\_allowed\_packet'}); + + # This parameter is not yet defined when the DB is being built for + # the very first time. The code below still works properly, however, + # because the default maxattachmentsize is smaller than $min_max_allowed. + my $max_attachment = (Bugzilla->params->{'maxattachmentsize'} || 0) * 1024; + my $needed_max_allowed = max($min_max_allowed, $max_attachment); + if ($current_max_allowed < $needed_max_allowed) { + warn install_string('max_allowed_packet', + {current => $current_max_allowed, needed => $needed_max_allowed}) + . "\n"; + } + + # Make sure the installation has InnoDB turned on, or we're going to be + # doing silly things like making foreign keys on MyISAM tables, which is + # hard to fix later. We do this up here because none of the code below + # works if InnoDB is off. (Particularly if we've already converted the + # tables to InnoDB.) + my %engines = @{$self->selectcol_arrayref('SHOW ENGINES', {Columns => [1, 2]})}; + if (!$engines{InnoDB} || $engines{InnoDB} !~ /^(YES|DEFAULT)$/) { + die install_string('mysql_innodb_disabled'); + } + + + my ($sd_index_deleted, $longdescs_index_deleted); + my @tables = $self->bz_table_list_real(); + + # We want to convert tables to InnoDB, but it's possible that they have + # fulltext indexes on them, and conversion will fail unless we remove + # the indexes. + if (grep($_ eq 'bugs', @tables) and !grep($_ eq 'bugs_fulltext', @tables)) { + if ($self->bz_index_info_real('bugs', 'short_desc')) { + $self->bz_drop_index_raw('bugs', 'short_desc'); } - - # Make sure the installation has InnoDB turned on, or we're going to be - # doing silly things like making foreign keys on MyISAM tables, which is - # hard to fix later. We do this up here because none of the code below - # works if InnoDB is off. (Particularly if we've already converted the - # tables to InnoDB.) - my %engines = @{$self->selectcol_arrayref('SHOW ENGINES', {Columns => [1,2]})}; - if (!$engines{InnoDB} || $engines{InnoDB} !~ /^(YES|DEFAULT)$/) { - die install_string('mysql_innodb_disabled'); + if ($self->bz_index_info_real('bugs', 'bugs_short_desc_idx')) { + $self->bz_drop_index_raw('bugs', 'bugs_short_desc_idx'); + $sd_index_deleted = 1; # Used for later schema cleanup. } - - - my ($sd_index_deleted, $longdescs_index_deleted); - my @tables = $self->bz_table_list_real(); - # We want to convert tables to InnoDB, but it's possible that they have - # fulltext indexes on them, and conversion will fail unless we remove - # the indexes. - if (grep($_ eq 'bugs', @tables) - and !grep($_ eq 'bugs_fulltext', @tables)) - { - if ($self->bz_index_info_real('bugs', 'short_desc')) { - $self->bz_drop_index_raw('bugs', 'short_desc'); - } - if ($self->bz_index_info_real('bugs', 'bugs_short_desc_idx')) { - $self->bz_drop_index_raw('bugs', 'bugs_short_desc_idx'); - $sd_index_deleted = 1; # Used for later schema cleanup. - } + } + if (grep($_ eq 'longdescs', @tables) and !grep($_ eq 'bugs_fulltext', @tables)) + { + if ($self->bz_index_info_real('longdescs', 'thetext')) { + $self->bz_drop_index_raw('longdescs', 'thetext'); } - if (grep($_ eq 'longdescs', @tables) - and !grep($_ eq 'bugs_fulltext', @tables)) - { - if ($self->bz_index_info_real('longdescs', 'thetext')) { - $self->bz_drop_index_raw('longdescs', 'thetext'); - } - if ($self->bz_index_info_real('longdescs', 'longdescs_thetext_idx')) { - $self->bz_drop_index_raw('longdescs', 'longdescs_thetext_idx'); - $longdescs_index_deleted = 1; # For later schema cleanup. - } + if ($self->bz_index_info_real('longdescs', 'longdescs_thetext_idx')) { + $self->bz_drop_index_raw('longdescs', 'longdescs_thetext_idx'); + $longdescs_index_deleted = 1; # For later schema cleanup. } - - # Upgrade tables from MyISAM to InnoDB - my $db_name = Bugzilla->localconfig->{db_name}; - my $myisam_tables = $self->selectcol_arrayref( - 'SELECT TABLE_NAME FROM information_schema.TABLES - WHERE TABLE_SCHEMA = ? AND ENGINE = ?', - undef, $db_name, 'MyISAM'); - foreach my $should_be_myisam (Bugzilla::DB::Schema::Mysql::MYISAM_TABLES) { - @$myisam_tables = grep { $_ ne $should_be_myisam } @$myisam_tables; + } + + # Upgrade tables from MyISAM to InnoDB + my $db_name = Bugzilla->localconfig->{db_name}; + my $myisam_tables = $self->selectcol_arrayref( + 'SELECT TABLE_NAME FROM information_schema.TABLES + WHERE TABLE_SCHEMA = ? AND ENGINE = ?', undef, $db_name, 'MyISAM' + ); + foreach my $should_be_myisam (Bugzilla::DB::Schema::Mysql::MYISAM_TABLES) { + @$myisam_tables = grep { $_ ne $should_be_myisam } @$myisam_tables; + } + + if (scalar @$myisam_tables) { + print "Bugzilla now uses the InnoDB storage engine in MySQL for", + " most tables.\nConverting tables to InnoDB:\n"; + foreach my $table (@$myisam_tables) { + print "Converting table $table... "; + $self->do("ALTER TABLE $table ENGINE = InnoDB"); + print "done.\n"; } - - if (scalar @$myisam_tables) { - print "Bugzilla now uses the InnoDB storage engine in MySQL for", - " most tables.\nConverting tables to InnoDB:\n"; - foreach my $table (@$myisam_tables) { - print "Converting table $table... "; - $self->do("ALTER TABLE $table ENGINE = InnoDB"); - print "done.\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. + if ( + grep($_ eq 'bugs', @tables) + && ( $self->bz_index_info_real('bugs', 'assigned_to') + || $self->bz_index_info_real('flags', 'flags_bidattid_idx')) + ) + { + + # This is a check unrelated to the indexes, to see if people are + # upgrading from 2.18 or below, but somehow have a bz_schema table + # already. This only happens if they have done a mysqldump into + # a database without doing a DROP DATABASE first. + # We just do the check here since this check is a reliable way + # of telling that we are upgrading from a version pre-2.20. + if (grep($_ eq 'bz_schema', $self->bz_table_list_real())) { + die install_string('bz_schema_exists_before_220'); } - - # 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. - if (grep($_ eq 'bugs', @tables) - && ($self->bz_index_info_real('bugs', 'assigned_to') - || $self->bz_index_info_real('flags', 'flags_bidattid_idx')) ) - { - # This is a check unrelated to the indexes, to see if people are - # upgrading from 2.18 or below, but somehow have a bz_schema table - # already. This only happens if they have done a mysqldump into - # a database without doing a DROP DATABASE first. - # We just do the check here since this check is a reliable way - # of telling that we are upgrading from a version pre-2.20. - if (grep($_ eq 'bz_schema', $self->bz_table_list_real())) { - die install_string('bz_schema_exists_before_220'); - } + my $bug_count = $self->selectrow_array("SELECT COUNT(*) FROM bugs"); - 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; - # And 45 minutes for every 15,000 attachments, per some experiments. - my ($attachment_count) = - $self->selectrow_array("SELECT COUNT(*) FROM attachments"); - $rename_time += int(($attachment_count * 45) / 15000); - # 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 "\n", install_string('mysql_index_renaming', - { minutes => $rename_time }); - # Wait 45 seconds for them to respond. - sleep(45) unless Bugzilla->installation_answers->{NO_PAUSE}; - } - 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. - # Also, sometimes an old mysqldump would incorrectly rename - # unique indexes to "PRIMARY", so we address that here, also. - my $bad_names = { - # 'when' is a possible leftover from Bugzillas before 2.8 - bugs_activity => ['when', 'bugs_activity_bugid_idx', - 'bugs_activity_bugwhen_idx'], - cc => ['PRIMARY'], - longdescs => ['longdescs_bugid_idx', - 'longdescs_bugwhen_idx'], - flags => ['flags_bidattid_idx'], - flaginclusions => ['flaginclusions_tpcid_idx'], - flagexclusions => ['flagexclusions_tpc_id_idx'], - keywords => ['PRIMARY'], - milestones => ['PRIMARY'], - profiles_activity => ['profiles_activity_when_idx'], - group_control_map => ['group_control_map_gid_idx', 'PRIMARY'], - user_group_map => ['PRIMARY'], - group_group_map => ['PRIMARY'], - email_setting => ['PRIMARY'], - bug_group_map => ['PRIMARY'], - category_group_map => ['PRIMARY'], - watch => ['PRIMARY'], - namedqueries => ['PRIMARY'], - series_data => ['PRIMARY'], - # 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'; - } - $self->bz_drop_index_raw('series', $dropname) if $dropname; - } + # 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; - # 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') ) - { - $self->bz_drop_index_raw('email_setting', - 'email_settings_user_id_idx'); - } - - # Go through all the tables. - foreach my $table (@tables) { - # Will contain the names of old indexes as keys, and the - # definition of the new indexes as a value. The values - # include an extra hash key, NAME, with the new name of - # the index. - my %rename_indexes; - # 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. - if (my $index = $self->bz_index_info_real($table, $column)) { - # Fix the name to fit in with the new naming scheme. - $index->{NAME} = $table . "_" . - $index->{FIELDS}->[0] . "_idx"; - print "Renaming index $column to " - . $index->{NAME} . "...\n"; - $rename_indexes{$column} = $index; - } # if - } # foreach column - - my @rename_sql = $self->_bz_schema->get_rename_indexes_ddl( - $table, %rename_indexes); - $self->do($_) foreach (@rename_sql); - - } # foreach table - } # if old-name indexes - - # If there are no tables, but the DB isn't utf8 and it should be, - # then we should alter the database to be utf8. We know it should be - # if the utf8 parameter is true or there are no params at all. - # This kind of situation happens when people create the database - # themselves, and if we don't do this they will get the big - # scary WARNING statement about conversion to UTF8. - if ( !$self->bz_db_is_utf8 && !@tables - && (Bugzilla->params->{'utf8'} || !scalar keys %{Bugzilla->params}) ) - { - $self->_alter_db_charset_to_utf8(); - } + # And 45 minutes for every 15,000 attachments, per some experiments. + my ($attachment_count) + = $self->selectrow_array("SELECT COUNT(*) FROM attachments"); + $rename_time += int(($attachment_count * 45) / 15000); - # And now we create the tables and the Schema object. - $self->SUPER::bz_setup_database(); + # 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 "\n", install_string('mysql_index_renaming', {minutes => $rename_time}); - if ($sd_index_deleted) { - $self->_bz_real_schema->delete_index('bugs', 'bugs_short_desc_idx'); - $self->_bz_store_real_schema; + # Wait 45 seconds for them to respond. + sleep(45) unless Bugzilla->installation_answers->{NO_PAUSE}; } - if ($longdescs_index_deleted) { - $self->_bz_real_schema->delete_index('longdescs', - 'longdescs_thetext_idx'); - $self->_bz_store_real_schema; + 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. + # Also, sometimes an old mysqldump would incorrectly rename + # unique indexes to "PRIMARY", so we address that here, also. + my $bad_names = { + + # 'when' is a possible leftover from Bugzillas before 2.8 + bugs_activity => + ['when', 'bugs_activity_bugid_idx', 'bugs_activity_bugwhen_idx'], + cc => ['PRIMARY'], + longdescs => ['longdescs_bugid_idx', 'longdescs_bugwhen_idx'], + flags => ['flags_bidattid_idx'], + flaginclusions => ['flaginclusions_tpcid_idx'], + flagexclusions => ['flagexclusions_tpc_id_idx'], + keywords => ['PRIMARY'], + milestones => ['PRIMARY'], + profiles_activity => ['profiles_activity_when_idx'], + group_control_map => ['group_control_map_gid_idx', 'PRIMARY'], + user_group_map => ['PRIMARY'], + group_group_map => ['PRIMARY'], + email_setting => ['PRIMARY'], + bug_group_map => ['PRIMARY'], + category_group_map => ['PRIMARY'], + watch => ['PRIMARY'], + namedqueries => ['PRIMARY'], + series_data => ['PRIMARY'], + + # 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'; + } + $self->bz_drop_index_raw('series', $dropname) if $dropname; } - # The old timestamp fields need to be adjusted here instead of in - # checksetup. Otherwise the UPDATE statements inside of bz_add_column - # will cause accidental timestamp updates. - # The code that does this was moved here from checksetup. - - # 2002-08-14 - bbaetz@student.usyd.edu.au - bug 153578 - # attachments creation time needs to be a datetime, not a timestamp - my $attach_creation = - $self->bz_column_info("attachments", "creation_ts"); - if ($attach_creation && $attach_creation->{TYPE} =~ /^TIMESTAMP/i) { - print "Fixing creation time on attachments...\n"; + # 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')) + { + $self->bz_drop_index_raw('email_setting', 'email_settings_user_id_idx'); + } - my $sth = $self->prepare("SELECT COUNT(attach_id) FROM attachments"); - $sth->execute(); - my ($attach_count) = $sth->fetchrow_array(); + # Go through all the tables. + foreach my $table (@tables) { - if ($attach_count > 1000) { - print "This may take a while...\n"; - } - my $i = 0; - - # This isn't just as simple as changing the field type, because - # the creation_ts was previously updated when an attachment was made - # obsolete from the attachment creation screen. So we have to go - # and recreate these times from the comments.. - $sth = $self->prepare("SELECT bug_id, attach_id, submitter_id " . - "FROM attachments"); - $sth->execute(); - - # Restrict this as much as possible in order to avoid false - # positives, and keep the db search time down - my $sth2 = $self->prepare("SELECT bug_when FROM longdescs - WHERE bug_id=? AND who=? - AND thetext LIKE ? - ORDER BY bug_when " . $self->sql_limit(1)); - while (my ($bug_id, $attach_id, $submitter_id) - = $sth->fetchrow_array()) - { - $sth2->execute($bug_id, $submitter_id, - "Created an attachment (id=$attach_id)%"); - my ($when) = $sth2->fetchrow_array(); - if ($when) { - $self->do("UPDATE attachments " . - "SET creation_ts='$when' " . - "WHERE attach_id=$attach_id"); - } else { - print "Warning - could not determine correct creation" - . " time for attachment $attach_id on bug $bug_id\n"; - } - ++$i; - print "Converted $i of $attach_count attachments\n" if !($i % 1000); - } - print "Done - converted $i attachments\n"; + # Will contain the names of old indexes as keys, and the + # definition of the new indexes as a value. The values + # include an extra hash key, NAME, with the new name of + # the index. + my %rename_indexes; + + # 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. + if (my $index = $self->bz_index_info_real($table, $column)) { + + # Fix the name to fit in with the new naming scheme. + $index->{NAME} = $table . "_" . $index->{FIELDS}->[0] . "_idx"; + print "Renaming index $column to " . $index->{NAME} . "...\n"; + $rename_indexes{$column} = $index; + } # if + } # foreach column + + my @rename_sql + = $self->_bz_schema->get_rename_indexes_ddl($table, %rename_indexes); + $self->do($_) foreach (@rename_sql); + + } # foreach table + } # if old-name indexes + + # If there are no tables, but the DB isn't utf8 and it should be, + # then we should alter the database to be utf8. We know it should be + # if the utf8 parameter is true or there are no params at all. + # This kind of situation happens when people create the database + # themselves, and if we don't do this they will get the big + # scary WARNING statement about conversion to UTF8. + if ( !$self->bz_db_is_utf8 + && !@tables + && (Bugzilla->params->{'utf8'} || !scalar keys %{Bugzilla->params})) + { + $self->_alter_db_charset_to_utf8(); + } + + # And now we create the tables and the Schema object. + $self->SUPER::bz_setup_database(); + + if ($sd_index_deleted) { + $self->_bz_real_schema->delete_index('bugs', 'bugs_short_desc_idx'); + $self->_bz_store_real_schema; + } + if ($longdescs_index_deleted) { + $self->_bz_real_schema->delete_index('longdescs', 'longdescs_thetext_idx'); + $self->_bz_store_real_schema; + } + + # The old timestamp fields need to be adjusted here instead of in + # checksetup. Otherwise the UPDATE statements inside of bz_add_column + # will cause accidental timestamp updates. + # The code that does this was moved here from checksetup. + + # 2002-08-14 - bbaetz@student.usyd.edu.au - bug 153578 + # attachments creation time needs to be a datetime, not a timestamp + my $attach_creation = $self->bz_column_info("attachments", "creation_ts"); + if ($attach_creation && $attach_creation->{TYPE} =~ /^TIMESTAMP/i) { + print "Fixing creation time on attachments...\n"; + + my $sth = $self->prepare("SELECT COUNT(attach_id) FROM attachments"); + $sth->execute(); + my ($attach_count) = $sth->fetchrow_array(); - $self->bz_alter_column("attachments", "creation_ts", - {TYPE => 'DATETIME', NOTNULL => 1}); + if ($attach_count > 1000) { + print "This may take a while...\n"; } + my $i = 0; - # 2004-08-29 - Tomas.Kopal@altap.cz, bug 257303 - # Change logincookies.lastused type from timestamp to datetime - my $login_lastused = $self->bz_column_info("logincookies", "lastused"); - if ($login_lastused && $login_lastused->{TYPE} =~ /^TIMESTAMP/i) { - $self->bz_alter_column('logincookies', 'lastused', - { TYPE => 'DATETIME', NOTNULL => 1}); - } + # This isn't just as simple as changing the field type, because + # the creation_ts was previously updated when an attachment was made + # obsolete from the attachment creation screen. So we have to go + # and recreate these times from the comments.. + $sth = $self->prepare( + "SELECT bug_id, attach_id, submitter_id " . "FROM attachments"); + $sth->execute(); - # 2005-01-17 - Tomas.Kopal@altap.cz, bug 257315 - # Change bugs.delta_ts type from timestamp to datetime - my $bugs_deltats = $self->bz_column_info("bugs", "delta_ts"); - if ($bugs_deltats && $bugs_deltats->{TYPE} =~ /^TIMESTAMP/i) { - $self->bz_alter_column('bugs', 'delta_ts', - {TYPE => 'DATETIME', NOTNULL => 1}); + # Restrict this as much as possible in order to avoid false + # positives, and keep the db search time down + my $sth2 = $self->prepare( + "SELECT bug_when FROM longdescs + WHERE bug_id=? AND who=? + AND thetext LIKE ? + ORDER BY bug_when " . $self->sql_limit(1) + ); + while (my ($bug_id, $attach_id, $submitter_id) = $sth->fetchrow_array()) { + $sth2->execute($bug_id, $submitter_id, + "Created an attachment (id=$attach_id)%"); + my ($when) = $sth2->fetchrow_array(); + if ($when) { + $self->do("UPDATE attachments " + . "SET creation_ts='$when' " + . "WHERE attach_id=$attach_id"); + } + else { + print "Warning - could not determine correct creation" + . " time for attachment $attach_id on bug $bug_id\n"; + } + ++$i; + print "Converted $i of $attach_count attachments\n" if !($i % 1000); } - - # 2005-09-24 - bugreport@peshkin.net, bug 307602 - # Make sure that default 4G table limit is overridden - my $attach_data_create = $self->selectrow_array( - 'SELECT CREATE_OPTIONS FROM information_schema.TABLES - WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?', - undef, $db_name, 'attach_data'); - if ($attach_data_create !~ /MAX_ROWS/i) { - print "Converting attach_data maximum size to 100G...\n"; - $self->do("ALTER TABLE attach_data + print "Done - converted $i attachments\n"; + + $self->bz_alter_column("attachments", "creation_ts", + {TYPE => 'DATETIME', NOTNULL => 1}); + } + + # 2004-08-29 - Tomas.Kopal@altap.cz, bug 257303 + # Change logincookies.lastused type from timestamp to datetime + my $login_lastused = $self->bz_column_info("logincookies", "lastused"); + if ($login_lastused && $login_lastused->{TYPE} =~ /^TIMESTAMP/i) { + $self->bz_alter_column('logincookies', 'lastused', + {TYPE => 'DATETIME', NOTNULL => 1}); + } + + # 2005-01-17 - Tomas.Kopal@altap.cz, bug 257315 + # Change bugs.delta_ts type from timestamp to datetime + my $bugs_deltats = $self->bz_column_info("bugs", "delta_ts"); + if ($bugs_deltats && $bugs_deltats->{TYPE} =~ /^TIMESTAMP/i) { + $self->bz_alter_column('bugs', 'delta_ts', {TYPE => 'DATETIME', NOTNULL => 1}); + } + + # 2005-09-24 - bugreport@peshkin.net, bug 307602 + # Make sure that default 4G table limit is overridden + my $attach_data_create = $self->selectrow_array( + 'SELECT CREATE_OPTIONS FROM information_schema.TABLES + WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?', undef, $db_name, 'attach_data' + ); + if ($attach_data_create !~ /MAX_ROWS/i) { + print "Converting attach_data maximum size to 100G...\n"; + $self->do( + "ALTER TABLE attach_data AVG_ROW_LENGTH=1000000, - MAX_ROWS=100000"); - } - - # Convert the database to UTF-8 if the utf8 parameter is on. - # We check if any table isn't utf8, because lots of crazy - # partial-conversion situations can happen, and this handles anything - # that could come up (including having the DB charset be utf8 but not - # the table charsets. - # - # TABLE_COLLATION IS NOT NULL prevents us from trying to convert views. - my $non_utf8_tables = $self->selectrow_array( - "SELECT 1 FROM information_schema.TABLES + MAX_ROWS=100000" + ); + } + + # Convert the database to UTF-8 if the utf8 parameter is on. + # We check if any table isn't utf8, because lots of crazy + # partial-conversion situations can happen, and this handles anything + # that could come up (including having the DB charset be utf8 but not + # the table charsets. + # + # TABLE_COLLATION IS NOT NULL prevents us from trying to convert views. + my $non_utf8_tables = $self->selectrow_array( + "SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_COLLATION IS NOT NULL AND TABLE_COLLATION NOT LIKE 'utf8%' - LIMIT 1", undef, $db_name); - - if (Bugzilla->params->{'utf8'} && $non_utf8_tables) { - print "\n", install_string('mysql_utf8_conversion'); - - if (!Bugzilla->installation_answers->{NO_PAUSE}) { - if (Bugzilla->installation_mode == - INSTALLATION_MODE_NON_INTERACTIVE) - { - die install_string('continue_without_answers'), "\n"; - } - else { - print "\n " . install_string('enter_or_ctrl_c'); - getc; - } - } - - print "Converting table storage format to UTF-8. This may take a", - " while.\n"; - foreach my $table ($self->bz_table_list_real) { - my $info_sth = $self->prepare("SHOW FULL COLUMNS FROM $table"); - $info_sth->execute(); - my (@binary_sql, @utf8_sql); - while (my $column = $info_sth->fetchrow_hashref) { - # Our conversion code doesn't work on enum fields, but they - # all go away later in checksetup anyway. - next if $column->{Type} =~ /enum/i; - - # If this particular column isn't stored in utf-8 - if ($column->{Collation} - && $column->{Collation} ne 'NULL' - && $column->{Collation} !~ /utf8/) - { - my $name = $column->{Field}; - - print "$table.$name needs to be converted to UTF-8...\n"; - - # These will be automatically re-created at the end - # of checksetup. - $self->bz_drop_related_fks($table, $name); - - my $col_info = - $self->bz_column_info_real($table, $name); - # CHANGE COLUMN doesn't take PRIMARY KEY - delete $col_info->{PRIMARYKEY}; - my $sql_def = $self->_bz_schema->get_type_ddl($col_info); - # We don't want MySQL to actually try to *convert* - # from our current charset to UTF-8, we just want to - # transfer the bytes directly. This is how we do that. - - # The CHARACTER SET part of the definition has to come - # right after the type, which will always come first. - my ($binary, $utf8) = ($sql_def, $sql_def); - my $type = $self->_bz_schema->convert_type($col_info->{TYPE}); - $binary =~ s/(\Q$type\E)/$1 CHARACTER SET binary/; - $utf8 =~ s/(\Q$type\E)/$1 CHARACTER SET utf8/; - push(@binary_sql, "MODIFY COLUMN $name $binary"); - push(@utf8_sql, "MODIFY COLUMN $name $utf8"); - } - } # foreach column - - if (@binary_sql) { - my %indexes = %{ $self->bz_table_indexes($table) }; - foreach my $index_name (keys %indexes) { - my $index = $indexes{$index_name}; - if ($index->{TYPE} and $index->{TYPE} eq 'FULLTEXT') { - $self->bz_drop_index($table, $index_name); - } - else { - delete $indexes{$index_name}; - } - } - - print "Converting the $table table to UTF-8...\n"; - my $bin = "ALTER TABLE $table " . join(', ', @binary_sql); - my $utf = "ALTER TABLE $table " . join(', ', @utf8_sql, - 'DEFAULT CHARACTER SET utf8'); - $self->do($bin); - $self->do($utf); - - # Re-add any removed FULLTEXT indexes. - foreach my $index (keys %indexes) { - $self->bz_add_index($table, $index, $indexes{$index}); - } - } - else { - $self->do("ALTER TABLE $table DEFAULT CHARACTER SET utf8"); - } - - } # foreach my $table (@tables) + LIMIT 1", undef, $db_name + ); + + if (Bugzilla->params->{'utf8'} && $non_utf8_tables) { + print "\n", install_string('mysql_utf8_conversion'); + + if (!Bugzilla->installation_answers->{NO_PAUSE}) { + if (Bugzilla->installation_mode == INSTALLATION_MODE_NON_INTERACTIVE) { + die install_string('continue_without_answers'), "\n"; + } + else { + print "\n " . install_string('enter_or_ctrl_c'); + getc; + } } - # Sometimes you can have a situation where all the tables are utf8, - # but the database isn't. (This tends to happen when you've done - # a mysqldump.) So we have this change outside of the above block, - # so that it just happens silently if no actual *table* conversion - # needs to happen. - if (Bugzilla->params->{'utf8'} && !$self->bz_db_is_utf8) { - $self->_alter_db_charset_to_utf8(); - } + print "Converting table storage format to UTF-8. This may take a", " while.\n"; + foreach my $table ($self->bz_table_list_real) { + my $info_sth = $self->prepare("SHOW FULL COLUMNS FROM $table"); + $info_sth->execute(); + my (@binary_sql, @utf8_sql); + while (my $column = $info_sth->fetchrow_hashref) { + + # Our conversion code doesn't work on enum fields, but they + # all go away later in checksetup anyway. + next if $column->{Type} =~ /enum/i; + + # If this particular column isn't stored in utf-8 + if ( $column->{Collation} + && $column->{Collation} ne 'NULL' + && $column->{Collation} !~ /utf8/) + { + my $name = $column->{Field}; - $self->_fix_defaults(); + print "$table.$name needs to be converted to UTF-8...\n"; - # Bug 451735 highlighted a bug in bz_drop_index() which didn't - # check for FKs before trying to delete an index. Consequently, - # the series_creator_idx index was considered to be deleted - # despite it was still present in the DB. That's why we have to - # force the deletion, bypassing the DB schema. - if (!$self->bz_index_info('series', 'series_category_idx')) { - if (!$self->bz_index_info('series', 'series_creator_idx') - && $self->bz_index_info_real('series', 'series_creator_idx')) - { - foreach my $column (qw(creator category subcategory name)) { - $self->bz_drop_related_fks('series', $column); - } - $self->bz_drop_index_raw('series', 'series_creator_idx'); + # These will be automatically re-created at the end + # of checksetup. + $self->bz_drop_related_fks($table, $name); + + my $col_info = $self->bz_column_info_real($table, $name); + + # CHANGE COLUMN doesn't take PRIMARY KEY + delete $col_info->{PRIMARYKEY}; + my $sql_def = $self->_bz_schema->get_type_ddl($col_info); + + # We don't want MySQL to actually try to *convert* + # from our current charset to UTF-8, we just want to + # transfer the bytes directly. This is how we do that. + + # The CHARACTER SET part of the definition has to come + # right after the type, which will always come first. + my ($binary, $utf8) = ($sql_def, $sql_def); + my $type = $self->_bz_schema->convert_type($col_info->{TYPE}); + $binary =~ s/(\Q$type\E)/$1 CHARACTER SET binary/; + $utf8 =~ s/(\Q$type\E)/$1 CHARACTER SET utf8/; + push(@binary_sql, "MODIFY COLUMN $name $binary"); + push(@utf8_sql, "MODIFY COLUMN $name $utf8"); } + } # foreach column + + if (@binary_sql) { + my %indexes = %{$self->bz_table_indexes($table)}; + foreach my $index_name (keys %indexes) { + my $index = $indexes{$index_name}; + if ($index->{TYPE} and $index->{TYPE} eq 'FULLTEXT') { + $self->bz_drop_index($table, $index_name); + } + else { + delete $indexes{$index_name}; + } + } + + print "Converting the $table table to UTF-8...\n"; + my $bin = "ALTER TABLE $table " . join(', ', @binary_sql); + my $utf + = "ALTER TABLE $table " . join(', ', @utf8_sql, 'DEFAULT CHARACTER SET utf8'); + $self->do($bin); + $self->do($utf); + + # Re-add any removed FULLTEXT indexes. + foreach my $index (keys %indexes) { + $self->bz_add_index($table, $index, $indexes{$index}); + } + } + else { + $self->do("ALTER TABLE $table DEFAULT CHARACTER SET utf8"); + } + + } # foreach my $table (@tables) + } + + # Sometimes you can have a situation where all the tables are utf8, + # but the database isn't. (This tends to happen when you've done + # a mysqldump.) So we have this change outside of the above block, + # so that it just happens silently if no actual *table* conversion + # needs to happen. + if (Bugzilla->params->{'utf8'} && !$self->bz_db_is_utf8) { + $self->_alter_db_charset_to_utf8(); + } + + $self->_fix_defaults(); + + # Bug 451735 highlighted a bug in bz_drop_index() which didn't + # check for FKs before trying to delete an index. Consequently, + # the series_creator_idx index was considered to be deleted + # despite it was still present in the DB. That's why we have to + # force the deletion, bypassing the DB schema. + if (!$self->bz_index_info('series', 'series_category_idx')) { + if (!$self->bz_index_info('series', 'series_creator_idx') + && $self->bz_index_info_real('series', 'series_creator_idx')) + { + foreach my $column (qw(creator category subcategory name)) { + $self->bz_drop_related_fks('series', $column); + } + $self->bz_drop_index_raw('series', 'series_creator_idx'); } + } } # When you import a MySQL 3/4 mysqldump into MySQL 5, columns that @@ -792,100 +809,109 @@ sub bz_setup_database { # looks like. So we remove defaults from columns that aren't supposed # to have them sub _fix_defaults { - my $self = shift; - my $maj_version = substr($self->bz_server_version, 0, 1); - return if $maj_version < 5; - - # The oldest column that could have this problem is bugs.assigned_to, - # so if it doesn't have the problem, we just skip doing this entirely. - my $assi_def = $self->_bz_raw_column_info('bugs', 'assigned_to'); - my $assi_default = $assi_def->{COLUMN_DEF}; - # This "ne ''" thing is necessary because _raw_column_info seems to - # return COLUMN_DEF as an empty string for columns that don't have - # a default. - return unless (defined $assi_default && $assi_default ne ''); - - my %fix_columns; - foreach my $table ($self->_bz_real_schema->get_table_list()) { - foreach my $column ($self->bz_table_columns($table)) { - my $abs_def = $self->bz_column_info($table, $column); - # BLOB/TEXT columns never have defaults - next if $abs_def->{TYPE} =~ /BLOB|TEXT/i; - if (!defined $abs_def->{DEFAULT}) { - # Get the exact default from the database without any - # "fixing" by bz_column_info_real. - my $raw_info = $self->_bz_raw_column_info($table, $column); - my $raw_default = $raw_info->{COLUMN_DEF}; - if (defined $raw_default) { - if ($raw_default eq '') { - # Only (var)char columns can have empty strings as - # defaults, so if we got an empty string for some - # other default type, then it's bogus. - next unless $abs_def->{TYPE} =~ /char/i; - $raw_default = "''"; - } - $fix_columns{$table} ||= []; - push(@{ $fix_columns{$table} }, $column); - print "$table.$column has incorrect DB default: $raw_default\n"; - } - } - } # foreach $column - } # foreach $table - - print "Fixing defaults...\n"; - foreach my $table (reverse sort keys %fix_columns) { - my @alters = map("ALTER COLUMN $_ DROP DEFAULT", - @{ $fix_columns{$table} }); - my $sql = "ALTER TABLE $table " . join(',', @alters); - $self->do($sql); - } + my $self = shift; + my $maj_version = substr($self->bz_server_version, 0, 1); + return if $maj_version < 5; + + # The oldest column that could have this problem is bugs.assigned_to, + # so if it doesn't have the problem, we just skip doing this entirely. + my $assi_def = $self->_bz_raw_column_info('bugs', 'assigned_to'); + my $assi_default = $assi_def->{COLUMN_DEF}; + + # This "ne ''" thing is necessary because _raw_column_info seems to + # return COLUMN_DEF as an empty string for columns that don't have + # a default. + return unless (defined $assi_default && $assi_default ne ''); + + my %fix_columns; + foreach my $table ($self->_bz_real_schema->get_table_list()) { + foreach my $column ($self->bz_table_columns($table)) { + my $abs_def = $self->bz_column_info($table, $column); + + # BLOB/TEXT columns never have defaults + next if $abs_def->{TYPE} =~ /BLOB|TEXT/i; + if (!defined $abs_def->{DEFAULT}) { + + # Get the exact default from the database without any + # "fixing" by bz_column_info_real. + my $raw_info = $self->_bz_raw_column_info($table, $column); + my $raw_default = $raw_info->{COLUMN_DEF}; + if (defined $raw_default) { + if ($raw_default eq '') { + + # Only (var)char columns can have empty strings as + # defaults, so if we got an empty string for some + # other default type, then it's bogus. + next unless $abs_def->{TYPE} =~ /char/i; + $raw_default = "''"; + } + $fix_columns{$table} ||= []; + push(@{$fix_columns{$table}}, $column); + print "$table.$column has incorrect DB default: $raw_default\n"; + } + } + } # foreach $column + } # foreach $table + + print "Fixing defaults...\n"; + foreach my $table (reverse sort keys %fix_columns) { + my @alters = map("ALTER COLUMN $_ DROP DEFAULT", @{$fix_columns{$table}}); + my $sql = "ALTER TABLE $table " . join(',', @alters); + $self->do($sql); + } } sub _alter_db_charset_to_utf8 { - my $self = shift; - my $db_name = Bugzilla->localconfig->{db_name}; - $self->do("ALTER DATABASE $db_name CHARACTER SET utf8"); + my $self = shift; + my $db_name = Bugzilla->localconfig->{db_name}; + $self->do("ALTER DATABASE $db_name CHARACTER SET utf8"); } sub bz_db_is_utf8 { - my $self = shift; - my $db_collation = $self->selectrow_arrayref( - "SHOW VARIABLES LIKE 'character_set_database'"); - # First column holds the variable name, second column holds the value. - return $db_collation->[1] =~ /utf8/ ? 1 : 0; + my $self = shift; + my $db_collation + = $self->selectrow_arrayref("SHOW VARIABLES LIKE 'character_set_database'"); + + # First column holds the variable name, second column holds the value. + return $db_collation->[1] =~ /utf8/ ? 1 : 0; } sub bz_enum_initial_values { - my ($self) = @_; - my %enum_values = %{$self->ENUM_DEFAULTS}; - # Get a complete description of the 'bugs' table; with DBD::MySQL - # there isn't a column-by-column way of doing this. Could use - # $dbh->column_info, but it would go slower and we would have to - # use the undocumented mysql_type_name accessor to get the type - # of each row. - my $sth = $self->prepare("DESCRIBE bugs"); - $sth->execute(); - # Look for the particular columns we are interested in. - while (my ($thiscol, $thistype) = $sth->fetchrow_array()) { - if (defined $enum_values{$thiscol}) { - # this is a column of interest. - my @value_list; - if ($thistype and ($thistype =~ /^enum\(/)) { - # it has an enum type; get the set of values. - while ($thistype =~ /'([^']*)'(.*)/) { - push(@value_list, $1); - $thistype = $2; - } - } - if (@value_list) { - # record the enum values found. - $enum_values{$thiscol} = \@value_list; - } + my ($self) = @_; + my %enum_values = %{$self->ENUM_DEFAULTS}; + + # Get a complete description of the 'bugs' table; with DBD::MySQL + # there isn't a column-by-column way of doing this. Could use + # $dbh->column_info, but it would go slower and we would have to + # use the undocumented mysql_type_name accessor to get the type + # of each row. + my $sth = $self->prepare("DESCRIBE bugs"); + $sth->execute(); + + # Look for the particular columns we are interested in. + while (my ($thiscol, $thistype) = $sth->fetchrow_array()) { + if (defined $enum_values{$thiscol}) { + + # this is a column of interest. + my @value_list; + if ($thistype and ($thistype =~ /^enum\(/)) { + + # it has an enum type; get the set of values. + while ($thistype =~ /'([^']*)'(.*)/) { + push(@value_list, $1); + $thistype = $2; } + } + if (@value_list) { + + # record the enum values found. + $enum_values{$thiscol} = \@value_list; + } } + } - return \%enum_values; + return \%enum_values; } ##################################################################### @@ -916,29 +942,29 @@ backwards-compatibility anyway, for versions of Bugzilla before 2.20. =cut sub bz_column_info_real { - my ($self, $table, $column) = @_; - my $col_data = $self->_bz_raw_column_info($table, $column); - return $self->_bz_schema->column_info_to_column($col_data); + my ($self, $table, $column) = @_; + my $col_data = $self->_bz_raw_column_info($table, $column); + return $self->_bz_schema->column_info_to_column($col_data); } sub _bz_raw_column_info { - my ($self, $table, $column) = @_; - - # DBD::mysql does not support selecting a specific column, - # so we have to get all the columns on the table and find - # the one we want. - my $info_sth = $self->column_info(undef, undef, $table, '%'); - - # Don't use fetchall_hashref as there's a Win32 DBI bug (292821) - my $col_data; - while ($col_data = $info_sth->fetchrow_hashref) { - last if $col_data->{'COLUMN_NAME'} eq $column; - } - - if (!defined $col_data) { - return undef; - } - return $col_data; + my ($self, $table, $column) = @_; + + # DBD::mysql does not support selecting a specific column, + # so we have to get all the columns on the table and find + # the one we want. + my $info_sth = $self->column_info(undef, undef, $table, '%'); + + # Don't use fetchall_hashref as there's a Win32 DBI bug (292821) + my $col_data; + while ($col_data = $info_sth->fetchrow_hashref) { + last if $col_data->{'COLUMN_NAME'} eq $column; + } + + if (!defined $col_data) { + return undef; + } + return $col_data; } =item C<bz_index_info_real($table, $index)> @@ -952,42 +978,43 @@ sub _bz_raw_column_info { =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. - # 9 = Null. Sometimes undef, sometimes 'YES'. - # 10 = Index_type. The type of the index. Usually either 'BTREE' or 'FULLTEXT' - # 11 = 'Comment.' Usually undef. - 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->[10] eq 'FULLTEXT' - ? 'FULLTEXT' : $index_type; - } + 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. + # 9 = Null. Sometimes undef, sometimes 'YES'. + # 10 = Index_type. The type of the index. Usually either 'BTREE' or 'FULLTEXT' + # 11 = 'Comment.' Usually undef. + 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->[10] eq 'FULLTEXT' ? 'FULLTEXT' : $index_type; } + } - my $retval; - if (scalar(@fields)) { - $retval = {FIELDS => \@fields, TYPE => $index_type}; - } - return $retval; + my $retval; + if (scalar(@fields)) { + $retval = {FIELDS => \@fields, TYPE => $index_type}; + } + return $retval; } =item C<bz_index_list_real($table)> @@ -1000,10 +1027,11 @@ sub bz_index_info_real { =cut sub bz_index_list_real { - my ($self, $table) = @_; - my $sth = $self->prepare("SHOW INDEX FROM $table"); - # Column 3 of a SHOW INDEX statement contains the name of the index. - return @{ $self->selectcol_arrayref($sth, {Columns => [3]}) }; + my ($self, $table) = @_; + my $sth = $self->prepare("SHOW INDEX FROM $table"); + + # Column 3 of a SHOW INDEX statement contains the name of the index. + return @{$self->selectcol_arrayref($sth, {Columns => [3]})}; } ##################################################################### @@ -1027,34 +1055,33 @@ this code does. # bz_column_info_real function would be very difficult to create # properly for any other DB besides MySQL. sub _bz_build_schema_from_disk { - my ($self) = @_; - - my $schema = $self->_bz_schema->get_empty_schema(); - - my @tables = $self->bz_table_list_real(); - if (@tables) { - print "Building Schema object from database...\n"; + my ($self) = @_; + + my $schema = $self->_bz_schema->get_empty_schema(); + + my @tables = $self->bz_table_list_real(); + if (@tables) { + print "Building Schema object from database...\n"; + } + foreach my $table (@tables) { + $schema->add_table($table); + my @columns = $self->bz_table_columns_real($table); + foreach my $column (@columns) { + my $type_info = $self->bz_column_info_real($table, $column); + $schema->set_column($table, $column, $type_info); } - foreach my $table (@tables) { - $schema->add_table($table); - my @columns = $self->bz_table_columns_real($table); - foreach my $column (@columns) { - my $type_info = $self->bz_column_info_real($table, $column); - $schema->set_column($table, $column, $type_info); - } - my @indexes = $self->bz_index_list_real($table); - foreach my $index (@indexes) { - unless ($index eq 'PRIMARY') { - my $index_info = $self->bz_index_info_real($table, $index); - ($index_info = $index_info->{FIELDS}) - if (!$index_info->{TYPE}); - $schema->set_index($table, $index, $index_info); - } - } + my @indexes = $self->bz_index_list_real($table); + foreach my $index (@indexes) { + unless ($index eq 'PRIMARY') { + my $index_info = $self->bz_index_info_real($table, $index); + ($index_info = $index_info->{FIELDS}) if (!$index_info->{TYPE}); + $schema->set_index($table, $index, $index_info); + } } + } - return $schema; + return $schema; } 1; |