From 956e50863d553ed397f15c4c640d07aecf4c8cc5 Mon Sep 17 00:00:00 2001 From: Max Kanat-Alexander Date: Tue, 19 Oct 2010 16:45:47 -0700 Subject: Bug 605663: For MySQL, use information_schema instead of SHOW TABLE STATUS to get table information during checksetup, because it's much faster. This also bumps our MySQL requirement to 5.0.15. r=mkanat, a=mkanat (module owner) --- Bugzilla/Constants.pm | 8 ++++-- Bugzilla/DB/Mysql.pm | 76 ++++++++++++++++----------------------------------- 2 files changed, 28 insertions(+), 56 deletions(-) diff --git a/Bugzilla/Constants.pm b/Bugzilla/Constants.pm index 14c3f2b26..9ce2c9f94 100644 --- a/Bugzilla/Constants.pm +++ b/Bugzilla/Constants.pm @@ -479,14 +479,16 @@ use constant INSTALLATION_MODE_NON_INTERACTIVE => 1; # Data about what we require for different databases. use constant DB_MODULE => { - 'mysql' => {db => 'Bugzilla::DB::Mysql', db_version => '4.1.2', + # MySQL 5.0.15 was the first production 5.0.x release. + 'mysql' => {db => 'Bugzilla::DB::Mysql', db_version => '5.0.15', dbd => { package => 'DBD-mysql', module => 'DBD::mysql', # Disallow development versions blacklist => ['_'], - # For UTF-8 support - version => '4.00', + # For UTF-8 support. 4.001 makes sure that blobs aren't + # marked as UTF-8. + version => '4.001', }, name => 'MySQL'}, 'pg' => {db => 'Bugzilla::DB::Pg', db_version => '8.03.0000', diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm index cdc23287d..b9143d7b6 100644 --- a/Bugzilla/DB/Mysql.pm +++ b/Bugzilla/DB/Mysql.pm @@ -320,27 +320,6 @@ sub bz_setup_database { } - my %table_status = @{ $self->selectcol_arrayref("SHOW TABLE STATUS", - {Columns=>[1,2]}) }; - my @isam_tables; - foreach my $name (keys %table_status) { - push(@isam_tables, $name) if (defined($table_status{$name}) && $table_status{$name} eq "ISAM"); - } - - if(scalar(@isam_tables)) { - print "One or more of the tables in your existing MySQL database are\n" - . "of type ISAM. ISAM tables are deprecated in MySQL 3.23 and\n" - . "don't support more than 16 indexes per table, which \n" - . "Bugzilla needs.\n Converting your ISAM tables to type" - . " MyISAM:\n\n"; - foreach my $table (@isam_tables) { - print "Converting table $table... "; - $self->do("ALTER TABLE $table TYPE = MYISAM"); - print "done.\n"; - } - print "\nISAM->MyISAM table conversion done.\n\n"; - } - 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 @@ -370,27 +349,25 @@ sub bz_setup_database { } # Upgrade tables from MyISAM to InnoDB - my @myisam_tables; - foreach my $name (keys %table_status) { - if (defined($table_status{$name}) - && $table_status{$name} =~ /^MYISAM$/i - && !grep($_ eq $name, Bugzilla::DB::Schema::Mysql::MYISAM_TABLES)) - { - push(@myisam_tables, $name) ; - } + 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) { + + 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) { + foreach my $table (@$myisam_tables) { print "Converting table $table... "; $self->do("ALTER TABLE $table ENGINE = InnoDB"); print "done.\n"; } } - $self->_after_table_status(\@tables); - # 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 @@ -647,8 +624,11 @@ sub bz_setup_database { # 2005-09-24 - bugreport@peshkin.net, bug 307602 # Make sure that default 4G table limit is overridden - my $row = $self->selectrow_hashref("SHOW TABLE STATUS LIKE 'attach_data'"); - if ($$row{'Create_options'} !~ /MAX_ROWS/i) { + 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, @@ -660,12 +640,15 @@ sub bz_setup_database { # 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. - my $utf_table_status = - $self->selectall_arrayref("SHOW TABLE STATUS", {Slice=>{}}); - $self->_after_table_status([map($_->{Name}, @$utf_table_status)]); - my @non_utf8_tables = grep(defined($_->{Collation}) && $_->{Collation} !~ /^utf8/, @$utf_table_status); + # + # 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'} && scalar @non_utf8_tables) { + if (Bugzilla->params->{'utf8'} && $non_utf8_tables) { print "\n", install_string('mysql_utf8_conversion'); if (!Bugzilla->installation_answers->{NO_PAUSE}) { @@ -839,19 +822,6 @@ sub _fix_defaults { } } -# There is a bug in MySQL 4.1.0 - 4.1.15 that makes certain SELECT -# statements fail after a SHOW TABLE STATUS: -# http://bugs.mysql.com/bug.php?id=13535 -# This is a workaround, a dummy SELECT to reset the LAST_INSERT_ID. -sub _after_table_status { - my ($self, $tables) = @_; - if (grep($_ eq 'bugs', @$tables) - && $self->bz_column_info_real("bugs", "bug_id")) - { - $self->do('SELECT 1 FROM bugs WHERE bug_id IS NULL'); - } -} - sub _alter_db_charset_to_utf8 { my $self = shift; my $db_name = Bugzilla->localconfig->{db_name}; -- cgit v1.2.3-24-g4f1b