1 # -*- Mode: perl; indent-tabs-mode: nil -*-
3 # The contents of this file are subject to the Mozilla Public
4 # License Version 1.1 (the "License"); you may not use this file
5 # except in compliance with the License. You may obtain a copy of
6 # the License at http://www.mozilla.org/MPL/
8 # Software distributed under the License is distributed on an "AS
9 # IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
10 # implied. See the License for the specific language governing
11 # rights and limitations under the License.
13 # The Original Code is the Bugzilla Bug Tracking System.
15 # The Initial Developer of the Original Code is Netscape Communications
16 # Corporation. Portions created by Netscape are
17 # Copyright (C) 1998 Netscape Communications Corporation. All
20 # Contributor(s): Dave Miller <davem00@aol.com>
21 # Gayathri Swaminath <gayathrik00@aol.com>
22 # Jeroen Ruigrok van der Werven <asmodai@wxs.nl>
23 # Dave Lawrence <dkl@redhat.com>
24 # Tomas Kopal <Tomas.Kopal@altap.cz>
25 # Max Kanat-Alexander <mkanat@bugzilla.org>
26 # Lance Larsh <lance.larsh@oracle.com>
30 Bugzilla::DB::Mysql - Bugzilla database compatibility layer for MySQL
34 This module overrides methods of the Bugzilla::DB module with MySQL specific
35 implementation. It is instantiated by the Bugzilla::DB module and should never
38 For interface details see L<Bugzilla::DB> and L<DBI>.
42 package Bugzilla::DB::Mysql;
46 use Bugzilla::Constants;
47 use Bugzilla::Install::Util qw(install_string);
50 use Bugzilla::DB::Schema::Mysql;
52 use List::Util qw(max);
54 # This is how many comments of MAX_COMMENT_LENGTH we expect on a single bug.
55 # In reality, you could have a LOT more comments than this, because
56 # MAX_COMMENT_LENGTH is big.
57 use constant MAX_COMMENTS => 50;
59 # This module extends the DB interface via inheritance
60 use base qw(Bugzilla::DB);
63 my ($class, $user, $pass, $host, $dbname, $port, $sock) = @_;
65 # construct the DSN from the parameters we got
66 my $dsn = "DBI:mysql:host=$host;database=$dbname";
67 $dsn .= ";port=$port" if $port;
68 $dsn .= ";mysql_socket=$sock" if $sock;
70 my $attrs = { mysql_enable_utf8 => Bugzilla->params->{'utf8'} };
72 my $self = $class->db_new($dsn, $user, $pass, $attrs);
74 # This makes sure that if the tables are encoded as UTF-8, we
75 # return their data correctly.
76 $self->do("SET NAMES utf8") if Bugzilla->params->{'utf8'};
78 # all class local variables stored in DBI derived class needs to have
79 # a prefix 'private_'. See DBI documentation.
80 $self->{private_bz_tables_locked} = "";
82 bless ($self, $class);
84 # Bug 321645 - disable MySQL strict mode, if set
85 my ($var, $sql_mode) = $self->selectrow_array(
86 "SHOW VARIABLES LIKE 'sql\\_mode'");
89 # STRICT_TRANS_TABLE or STRICT_ALL_TABLES enable MySQL strict mode,
90 # causing bug 321645. TRADITIONAL sets these modes (among others) as
91 # well, so it has to be stipped as well
93 join(",", grep {$_ !~ /^STRICT_(?:TRANS|ALL)_TABLES|TRADITIONAL$/}
94 split(/,/, $sql_mode));
96 if ($sql_mode ne $new_sql_mode) {
97 $self->do("SET SESSION sql_mode = ?", undef, $new_sql_mode);
101 # Allow large GROUP_CONCATs (largely for inserting comments
102 # into bugs_fulltext).
103 $self->do('SET SESSION group_concat_max_len = 128000000');
108 # when last_insert_id() is supported on MySQL by lowest DBI/DBD version
109 # required by Bugzilla, this implementation can be removed.
113 my ($last_insert_id) = $self->selectrow_array('SELECT LAST_INSERT_ID()');
115 return $last_insert_id;
118 sub sql_group_concat {
119 my ($self, $column, $separator) = @_;
122 $sep_sql = " SEPARATOR $separator";
124 return "GROUP_CONCAT($column$sep_sql)";
128 my ($self, $expr, $pattern) = @_;
130 return "$expr REGEXP $pattern";
134 my ($self, $expr, $pattern) = @_;
136 return "$expr NOT REGEXP $pattern";
140 my ($self, $limit, $offset) = @_;
142 if (defined($offset)) {
143 return "LIMIT $offset, $limit";
145 return "LIMIT $limit";
149 sub sql_string_concat {
150 my ($self, @params) = @_;
152 return 'CONCAT(' . join(', ', @params) . ')';
155 sub sql_fulltext_search {
156 my ($self, $column, $text) = @_;
158 # Add the boolean mode modifier if the search string contains
160 my $mode = ($text =~ /[+\-<>()~*"]/ ? "IN BOOLEAN MODE" : "");
162 # quote the text for use in the MATCH AGAINST expression
163 $text = $self->quote($text);
165 # untaint the text, since it's safe to use now that we've quoted it
168 return "MATCH($column) AGAINST($text $mode)";
172 my ($self, $string) = @_;
178 my ($self, $days) = @_;
180 return "FROM_DAYS($days)";
184 my ($self, $date) = @_;
186 return "TO_DAYS($date)";
189 sub sql_date_format {
190 my ($self, $date, $format) = @_;
192 $format = "%Y.%m.%d %H:%i:%s" if !$format;
194 return "DATE_FORMAT($date, " . $self->quote($format) . ")";
198 my ($self, $interval, $units) = @_;
200 return "INTERVAL $interval $units";
204 my ($self, $fragment, $text) = @_;
205 return "INSTR($text, $fragment)";
209 my ($self, $fragment, $text) = @_;
211 return "INSTR(CAST($text AS BINARY), CAST($fragment AS BINARY))";
215 my ($self, $needed_columns, $optional_columns) = @_;
217 # MySQL allows you to specify the minimal subset of columns to get
218 # a unique result. While it does allow specifying all columns as
219 # ANSI SQL requires, according to MySQL documentation, the fewer
220 # columns you specify, the faster the query runs.
221 return "GROUP BY $needed_columns";
225 sub _bz_get_initial_schema {
227 return $self->_bz_build_schema_from_disk();
230 #####################################################################
232 #####################################################################
234 sub bz_setup_database {
237 # The "comments" field of the bugs_fulltext table could easily exceed
238 # MySQL's default max_allowed_packet. Also, MySQL should never have
239 # a max_allowed_packet smaller than our max_attachment_size. So, we
240 # warn the user here if max_allowed_packet is too small.
241 my $min_max_allowed = MAX_COMMENTS * MAX_COMMENT_LENGTH;
242 my (undef, $current_max_allowed) = $self->selectrow_array(
243 q{SHOW VARIABLES LIKE 'max\_allowed\_packet'});
244 # This parameter is not yet defined when the DB is being built for
245 # the very first time. The code below still works properly, however,
246 # because the default maxattachmentsize is smaller than $min_max_allowed.
247 my $max_attachment = (Bugzilla->params->{'maxattachmentsize'} || 0) * 1024;
248 my $needed_max_allowed = max($min_max_allowed, $max_attachment);
249 if ($current_max_allowed < $needed_max_allowed) {
250 warn install_string('max_allowed_packet',
251 { current => $current_max_allowed,
252 needed => $needed_max_allowed }) . "\n";
255 # Make sure the installation has InnoDB turned on, or we're going to be
256 # doing silly things like making foreign keys on MyISAM tables, which is
257 # hard to fix later. We do this up here because none of the code below
258 # works if InnoDB is off. (Particularly if we've already converted the
260 my ($innodb_on) = @{$self->selectcol_arrayref(
261 q{SHOW VARIABLES LIKE '%have_innodb%'}, {Columns=>[2]})};
262 if ($innodb_on ne 'YES') {
264 InnoDB is disabled in your MySQL installation.
265 Bugzilla requires InnoDB to be enabled.
266 Please enable it and then re-run checksetup.pl.
273 # Figure out if any existing tables are of type ISAM and convert them
274 # to type MyISAM if so. ISAM tables are deprecated in MySQL 3.23,
275 # which Bugzilla now requires, and they don't support more than 16
276 # indexes per table, which Bugzilla needs.
277 my $table_status = $self->selectall_arrayref("SHOW TABLE STATUS");
279 foreach my $row (@$table_status) {
280 my ($name, $type) = @$row;
281 push(@isam_tables, $name) if $type eq "ISAM";
284 if(scalar(@isam_tables)) {
285 print "One or more of the tables in your existing MySQL database are\n"
286 . "of type ISAM. ISAM tables are deprecated in MySQL 3.23 and\n"
287 . "don't support more than 16 indexes per table, which \n"
288 . "Bugzilla needs.\n Converting your ISAM tables to type"
290 foreach my $table (@isam_tables) {
291 print "Converting table $table... ";
292 $self->do("ALTER TABLE $table TYPE = MYISAM");
295 print "\nISAM->MyISAM table conversion done.\n\n";
298 my ($sd_index_deleted, $longdescs_index_deleted);
299 my @tables = $self->bz_table_list_real();
300 # We want to convert tables to InnoDB, but it's possible that they have
301 # fulltext indexes on them, and conversion will fail unless we remove
303 if (grep($_ eq 'bugs', @tables)) {
304 if ($self->bz_index_info_real('bugs', 'short_desc')) {
305 $self->bz_drop_index_raw('bugs', 'short_desc');
307 if ($self->bz_index_info_real('bugs', 'bugs_short_desc_idx')) {
308 $self->bz_drop_index_raw('bugs', 'bugs_short_desc_idx');
309 $sd_index_deleted = 1; # Used for later schema cleanup.
312 if (grep($_ eq 'longdescs', @tables)) {
313 if ($self->bz_index_info_real('longdescs', 'thetext')) {
314 $self->bz_drop_index_raw('longdescs', 'thetext');
316 if ($self->bz_index_info_real('longdescs', 'longdescs_thetext_idx')) {
317 $self->bz_drop_index_raw('longdescs', 'longdescs_thetext_idx');
318 $longdescs_index_deleted = 1; # For later schema cleanup.
322 # Upgrade tables from MyISAM to InnoDB
324 foreach my $row (@$table_status) {
325 my ($name, $type) = @$row;
326 if ($type =~ /^MYISAM$/i
327 && !grep($_ eq $name, Bugzilla::DB::Schema::Mysql::MYISAM_TABLES))
329 push(@myisam_tables, $name) ;
332 if (scalar @myisam_tables) {
333 print "Bugzilla now uses the InnoDB storage engine in MySQL for",
334 " most tables.\nConverting tables to InnoDB:\n";
335 foreach my $table (@myisam_tables) {
336 print "Converting table $table... ";
337 $self->do("ALTER TABLE $table TYPE = InnoDB");
342 $self->_after_table_status(\@tables);
344 # Versions of Bugzilla before the existence of Bugzilla::DB::Schema did
345 # not provide explicit names for the table indexes. This means
346 # that our upgrades will not be reliable, because we look for the name
347 # of the index, not what fields it is on, when doing upgrades.
348 # (using the name is much better for cross-database compatibility
349 # and general reliability). It's also very important that our
350 # Schema object be consistent with what is on the disk.
352 # While we're at it, we also fix some inconsistent index naming
353 # from the original checkin of Bugzilla::DB::Schema.
355 # We check for the existence of a particular "short name" index that
356 # has existed at least since Bugzilla 2.8, and probably earlier.
357 # For fixing the inconsistent naming of Schema indexes,
358 # we also check for one of those inconsistently-named indexes.
359 if (grep($_ eq 'bugs', @tables)
360 && ($self->bz_index_info_real('bugs', 'assigned_to')
361 || $self->bz_index_info_real('flags', 'flags_bidattid_idx')) )
364 # This is a check unrelated to the indexes, to see if people are
365 # upgrading from 2.18 or below, but somehow have a bz_schema table
366 # already. This only happens if they have done a mysqldump into
367 # a database without doing a DROP DATABASE first.
368 # We just do the check here since this check is a reliable way
369 # of telling that we are upgrading from a version pre-2.20.
370 if (grep($_ eq 'bz_schema', $self->bz_table_list_real())) {
371 die("\nYou are upgrading from a version before 2.20, but the"
372 . " bz_schema\ntable already exists. This means that you"
373 . " restored a mysqldump into\nthe Bugzilla database without"
374 . " first dropping the already-existing\nBugzilla database,"
375 . " at some point. Whenever you restore a Bugzilla\ndatabase"
376 . " backup, you must always drop the entire database first.\n\n"
377 . "Please drop your Bugzilla database and restore it from a"
378 . " backup that\ndoes not contain the bz_schema table. If for"
379 . " some reason you cannot\ndo this, you can connect to your"
380 . " MySQL database and drop the bz_schema\ntable, as a last"
384 my $bug_count = $self->selectrow_array("SELECT COUNT(*) FROM bugs");
385 # We estimate one minute for each 3000 bugs, plus 3 minutes just
386 # to handle basic MySQL stuff.
387 my $rename_time = int($bug_count / 3000) + 3;
388 # And 45 minutes for every 15,000 attachments, per some experiments.
389 my ($attachment_count) =
390 $self->selectrow_array("SELECT COUNT(*) FROM attachments");
391 $rename_time += int(($attachment_count * 45) / 15000);
392 # If we're going to take longer than 5 minutes, we let the user know
393 # and allow them to abort.
394 if ($rename_time > 5) {
395 print "\nWe are about to rename old indexes.\n"
396 . "The estimated time to complete renaming is "
397 . "$rename_time minutes.\n"
398 . "You cannot interrupt this action once it has begun.\n"
399 . "If you would like to cancel, press Ctrl-C now..."
400 . " (Waiting 45 seconds...)\n\n";
401 # Wait 45 seconds for them to respond.
402 sleep(45) unless Bugzilla->installation_answers->{NO_PAUSE};
404 print "Renaming indexes...\n";
406 # We can't be interrupted, because of how the "if"
408 local $SIG{INT} = 'IGNORE';
409 local $SIG{TERM} = 'IGNORE';
410 local $SIG{PIPE} = 'IGNORE';
412 # Certain indexes had names in Schema that did not easily conform
413 # to a standard. We store those names here, so that they
414 # can be properly renamed.
415 # Also, sometimes an old mysqldump would incorrectly rename
416 # unique indexes to "PRIMARY", so we address that here, also.
418 # 'when' is a possible leftover from Bugzillas before 2.8
419 bugs_activity => ['when', 'bugs_activity_bugid_idx',
420 'bugs_activity_bugwhen_idx'],
422 longdescs => ['longdescs_bugid_idx',
423 'longdescs_bugwhen_idx'],
424 flags => ['flags_bidattid_idx'],
425 flaginclusions => ['flaginclusions_tpcid_idx'],
426 flagexclusions => ['flagexclusions_tpc_id_idx'],
427 keywords => ['PRIMARY'],
428 milestones => ['PRIMARY'],
429 profiles_activity => ['profiles_activity_when_idx'],
430 group_control_map => ['group_control_map_gid_idx', 'PRIMARY'],
431 user_group_map => ['PRIMARY'],
432 group_group_map => ['PRIMARY'],
433 email_setting => ['PRIMARY'],
434 bug_group_map => ['PRIMARY'],
435 category_group_map => ['PRIMARY'],
436 watch => ['PRIMARY'],
437 namedqueries => ['PRIMARY'],
438 series_data => ['PRIMARY'],
439 # series_categories is dealt with below, not here.
442 # The series table is broken and needs to have one index
443 # dropped before we begin the renaming, because it had a
444 # useless index on it that would cause a naming conflict here.
445 if (grep($_ eq 'series', @tables)) {
447 # This is what the bad index was called before Schema.
448 if ($self->bz_index_info_real('series', 'creator_2')) {
449 $dropname = 'creator_2';
451 # This is what the bad index is called in Schema.
452 elsif ($self->bz_index_info_real('series', 'series_creator_idx')) {
453 $dropname = 'series_creator_idx';
455 $self->bz_drop_index_raw('series', $dropname) if $dropname;
458 # The email_setting table also had the same problem.
459 if( grep($_ eq 'email_setting', @tables)
460 && $self->bz_index_info_real('email_setting',
461 'email_settings_user_id_idx') )
463 $self->bz_drop_index_raw('email_setting',
464 'email_settings_user_id_idx');
467 # Go through all the tables.
468 foreach my $table (@tables) {
469 # Will contain the names of old indexes as keys, and the
470 # definition of the new indexes as a value. The values
471 # include an extra hash key, NAME, with the new name of
474 # And go through all the columns on each table.
475 my @columns = $self->bz_table_columns_real($table);
477 # We also want to fix the silly naming of unique indexes
478 # that happened when we first checked-in Bugzilla::DB::Schema.
479 if ($table eq 'series_categories') {
480 # The series_categories index had a nonstandard name.
481 push(@columns, 'series_cats_unique_idx');
483 elsif ($table eq 'email_setting') {
484 # The email_setting table had a similar problem.
485 push(@columns, 'email_settings_unique_idx');
488 push(@columns, "${table}_unique_idx");
490 # And this is how we fix the other inconsistent Schema naming.
491 push(@columns, @{$bad_names->{$table}})
492 if (exists $bad_names->{$table});
493 foreach my $column (@columns) {
494 # If we have an index named after this column, it's an
495 # old-style-name index.
496 if (my $index = $self->bz_index_info_real($table, $column)) {
497 # Fix the name to fit in with the new naming scheme.
498 $index->{NAME} = $table . "_" .
499 $index->{FIELDS}->[0] . "_idx";
500 print "Renaming index $column to "
501 . $index->{NAME} . "...\n";
502 $rename_indexes{$column} = $index;
506 my @rename_sql = $self->_bz_schema->get_rename_indexes_ddl(
507 $table, %rename_indexes);
508 $self->do($_) foreach (@rename_sql);
511 } # if old-name indexes
513 # If there are no tables, but the DB isn't utf8 and it should be,
514 # then we should alter the database to be utf8. We know it should be
515 # if the utf8 parameter is true or there are no params at all.
516 # This kind of situation happens when people create the database
517 # themselves, and if we don't do this they will get the big
518 # scary WARNING statement about conversion to UTF8.
519 if ( !$self->bz_db_is_utf8 && !@tables
520 && (Bugzilla->params->{'utf8'} || !scalar keys %{Bugzilla->params}) )
522 $self->_alter_db_charset_to_utf8();
525 # And now we create the tables and the Schema object.
526 $self->SUPER::bz_setup_database();
528 if ($sd_index_deleted) {
529 $self->_bz_real_schema->delete_index('bugs', 'bugs_short_desc_idx');
530 $self->_bz_store_real_schema;
532 if ($longdescs_index_deleted) {
533 $self->_bz_real_schema->delete_index('longdescs',
534 'longdescs_thetext_idx');
535 $self->_bz_store_real_schema;
538 # The old timestamp fields need to be adjusted here instead of in
539 # checksetup. Otherwise the UPDATE statements inside of bz_add_column
540 # will cause accidental timestamp updates.
541 # The code that does this was moved here from checksetup.
543 # 2002-08-14 - bbaetz@student.usyd.edu.au - bug 153578
544 # attachments creation time needs to be a datetime, not a timestamp
545 my $attach_creation =
546 $self->bz_column_info("attachments", "creation_ts");
547 if ($attach_creation && $attach_creation->{TYPE} =~ /^TIMESTAMP/i) {
548 print "Fixing creation time on attachments...\n";
550 my $sth = $self->prepare("SELECT COUNT(attach_id) FROM attachments");
552 my ($attach_count) = $sth->fetchrow_array();
554 if ($attach_count > 1000) {
555 print "This may take a while...\n";
559 # This isn't just as simple as changing the field type, because
560 # the creation_ts was previously updated when an attachment was made
561 # obsolete from the attachment creation screen. So we have to go
562 # and recreate these times from the comments..
563 $sth = $self->prepare("SELECT bug_id, attach_id, submitter_id " .
567 # Restrict this as much as possible in order to avoid false
568 # positives, and keep the db search time down
569 my $sth2 = $self->prepare("SELECT bug_when FROM longdescs
570 WHERE bug_id=? AND who=?
572 ORDER BY bug_when " . $self->sql_limit(1));
573 while (my ($bug_id, $attach_id, $submitter_id)
574 = $sth->fetchrow_array())
576 $sth2->execute($bug_id, $submitter_id,
577 "Created an attachment (id=$attach_id)%");
578 my ($when) = $sth2->fetchrow_array();
580 $self->do("UPDATE attachments " .
581 "SET creation_ts='$when' " .
582 "WHERE attach_id=$attach_id");
584 print "Warning - could not determine correct creation"
585 . " time for attachment $attach_id on bug $bug_id\n";
588 print "Converted $i of $attach_count attachments\n" if !($i % 1000);
590 print "Done - converted $i attachments\n";
592 $self->bz_alter_column("attachments", "creation_ts",
593 {TYPE => 'DATETIME', NOTNULL => 1});
596 # 2004-08-29 - Tomas.Kopal@altap.cz, bug 257303
597 # Change logincookies.lastused type from timestamp to datetime
598 my $login_lastused = $self->bz_column_info("logincookies", "lastused");
599 if ($login_lastused && $login_lastused->{TYPE} =~ /^TIMESTAMP/i) {
600 $self->bz_alter_column('logincookies', 'lastused',
601 { TYPE => 'DATETIME', NOTNULL => 1});
604 # 2005-01-17 - Tomas.Kopal@altap.cz, bug 257315
605 # Change bugs.delta_ts type from timestamp to datetime
606 my $bugs_deltats = $self->bz_column_info("bugs", "delta_ts");
607 if ($bugs_deltats && $bugs_deltats->{TYPE} =~ /^TIMESTAMP/i) {
608 $self->bz_alter_column('bugs', 'delta_ts',
609 {TYPE => 'DATETIME', NOTNULL => 1});
612 # 2005-09-24 - bugreport@peshkin.net, bug 307602
613 # Make sure that default 4G table limit is overridden
614 my $row = $self->selectrow_hashref("SHOW TABLE STATUS LIKE 'attach_data'");
615 if ($$row{'Create_options'} !~ /MAX_ROWS/i) {
616 print "Converting attach_data maximum size to 100G...\n";
617 $self->do("ALTER TABLE attach_data
618 AVG_ROW_LENGTH=1000000,
622 # Convert the database to UTF-8 if the utf8 parameter is on.
623 # We check if any table isn't utf8, because lots of crazy
624 # partial-conversion situations can happen, and this handles anything
625 # that could come up (including having the DB charset be utf8 but not
626 # the table charsets.
627 my $utf_table_status =
628 $self->selectall_arrayref("SHOW TABLE STATUS", {Slice=>{}});
629 $self->_after_table_status([map($_->{Name}, @$utf_table_status)]);
630 my @non_utf8_tables = grep($_->{Collation} !~ /^utf8/, @$utf_table_status);
632 if (Bugzilla->params->{'utf8'} && scalar @non_utf8_tables) {
635 WARNING: We are about to convert your table storage format to UTF8. This
636 allows Bugzilla to correctly store and sort international characters.
637 However, if you have any non-UTF-8 data in your database,
638 it ***WILL BE DELETED*** by this process. So, before
639 you continue with checksetup.pl, if you have any non-UTF-8
640 data (or even if you're not sure) you should press Ctrl-C now
641 to interrupt checksetup.pl, and run contrib/recode.pl to make all
642 the data in your database into UTF-8. You should also back up your
643 database before continuing. This will affect every single table
644 in the database, even non-Bugzilla tables.
646 If you ever used a version of Bugzilla before 2.22, we STRONGLY
647 recommend that you stop checksetup.pl NOW and run contrib/recode.pl.
651 if (!Bugzilla->installation_answers->{NO_PAUSE}) {
652 if (Bugzilla->installation_mode ==
653 INSTALLATION_MODE_NON_INTERACTIVE)
656 Re-run checksetup.pl in interactive mode (without an 'answers' file)
662 print " Press Enter to continue or Ctrl-C to exit...";
667 print "Converting table storage format to UTF-8. This may take a",
669 foreach my $table ($self->bz_table_list_real) {
670 my $info_sth = $self->prepare("SHOW FULL COLUMNS FROM $table");
671 $info_sth->execute();
672 while (my $column = $info_sth->fetchrow_hashref) {
673 # Our conversion code doesn't work on enum fields, but they
674 # all go away later in checksetup anyway.
675 next if $column->{Type} =~ /enum/i;
677 # If this particular column isn't stored in utf-8
678 if ($column->{Collation}
679 && $column->{Collation} ne 'NULL'
680 && $column->{Collation} !~ /utf8/)
682 my $name = $column->{Field};
684 # The code below doesn't work on a field with a FULLTEXT
685 # index. So we drop it, which we'd do later anyway.
686 if ($table eq 'longdescs' && $name eq 'thetext') {
687 $self->bz_drop_index('longdescs',
688 'longdescs_thetext_idx');
690 if ($table eq 'bugs' && $name eq 'short_desc') {
691 $self->bz_drop_index('bugs', 'bugs_short_desc_idx');
694 if ($table eq 'bugs_fulltext') {
695 %ft_indexes = $self->_bz_real_schema->get_indexes_on_column_abstract(
696 'bugs_fulltext', $name);
697 foreach my $index (keys %ft_indexes) {
698 $self->bz_drop_index('bugs_fulltext', $index);
702 print "Converting $table.$name to be stored as UTF-8...\n";
704 $self->bz_column_info_real($table, $name);
706 # CHANGE COLUMN doesn't take PRIMARY KEY
707 delete $col_info->{PRIMARYKEY};
709 my $sql_def = $self->_bz_schema->get_type_ddl($col_info);
710 # We don't want MySQL to actually try to *convert*
711 # from our current charset to UTF-8, we just want to
712 # transfer the bytes directly. This is how we do that.
714 # The CHARACTER SET part of the definition has to come
715 # right after the type, which will always come first.
716 my ($binary, $utf8) = ($sql_def, $sql_def);
717 my $type = $self->_bz_schema->convert_type($col_info->{TYPE});
718 $binary =~ s/(\Q$type\E)/$1 CHARACTER SET binary/;
719 $utf8 =~ s/(\Q$type\E)/$1 CHARACTER SET utf8/;
720 $self->do("ALTER TABLE $table CHANGE COLUMN $name $name
722 $self->do("ALTER TABLE $table CHANGE COLUMN $name $name
725 if ($table eq 'bugs_fulltext') {
726 foreach my $index (keys %ft_indexes) {
727 $self->bz_add_index('bugs_fulltext', $index,
728 $ft_indexes{$index});
734 $self->do("ALTER TABLE $table DEFAULT CHARACTER SET utf8");
735 } # foreach my $table (@tables)
738 # Sometimes you can have a situation where all the tables are utf8,
739 # but the database isn't. (This tends to happen when you've done
740 # a mysqldump.) So we have this change outside of the above block,
741 # so that it just happens silently if no actual *table* conversion
743 if (Bugzilla->params->{'utf8'} && !$self->bz_db_is_utf8) {
744 $self->_alter_db_charset_to_utf8();
748 # There is a bug in MySQL 4.1.0 - 4.1.15 that makes certain SELECT
749 # statements fail after a SHOW TABLE STATUS:
750 # http://bugs.mysql.com/bug.php?id=13535
751 # This is a workaround, a dummy SELECT to reset the LAST_INSERT_ID.
752 sub _after_table_status {
753 my ($self, $tables) = @_;
754 if (grep($_ eq 'bugs', @$tables)
755 && $self->bz_column_info_real("bugs", "bug_id"))
757 $self->do('SELECT 1 FROM bugs WHERE bug_id IS NULL');
761 sub _alter_db_charset_to_utf8 {
763 my $db_name = Bugzilla->localconfig->{db_name};
764 $self->do("ALTER DATABASE $db_name CHARACTER SET utf8");
769 my $db_collation = $self->selectrow_arrayref(
770 "SHOW VARIABLES LIKE 'character_set_database'");
771 # First column holds the variable name, second column holds the value.
772 return $db_collation->[1] =~ /utf8/ ? 1 : 0;
776 sub bz_enum_initial_values {
778 my %enum_values = %{$self->ENUM_DEFAULTS};
779 # Get a complete description of the 'bugs' table; with DBD::MySQL
780 # there isn't a column-by-column way of doing this. Could use
781 # $dbh->column_info, but it would go slower and we would have to
782 # use the undocumented mysql_type_name accessor to get the type
784 my $sth = $self->prepare("DESCRIBE bugs");
786 # Look for the particular columns we are interested in.
787 while (my ($thiscol, $thistype) = $sth->fetchrow_array()) {
788 if (defined $enum_values{$thiscol}) {
789 # this is a column of interest.
791 if ($thistype and ($thistype =~ /^enum\(/)) {
792 # it has an enum type; get the set of values.
793 while ($thistype =~ /'([^']*)'(.*)/) {
794 push(@value_list, $1);
799 # record the enum values found.
800 $enum_values{$thiscol} = \@value_list;
805 return \%enum_values;
808 #####################################################################
809 # MySQL-specific Database-Reading Methods
810 #####################################################################
814 =head1 MYSQL-SPECIFIC DATABASE-READING METHODS
816 These methods read information about the database from the disk,
817 instead of from a Schema object. They are only reliable for MySQL
818 (see bug 285111 for the reasons why not all DBs use/have functions
819 like this), but that's OK because we only need them for
820 backwards-compatibility anyway, for versions of Bugzilla before 2.20.
824 =item C<bz_column_info_real($table, $column)>
826 Description: Returns an abstract column definition for a column
827 as it actually exists on disk in the database.
828 Params: $table - The name of the table the column is on.
829 $column - The name of the column you want info about.
830 Returns: An abstract column definition.
831 If the column does not exist, returns undef.
835 sub bz_column_info_real {
836 my ($self, $table, $column) = @_;
838 # DBD::mysql does not support selecting a specific column,
839 # so we have to get all the columns on the table and find
841 my $info_sth = $self->column_info(undef, undef, $table, '%');
843 # Don't use fetchall_hashref as there's a Win32 DBI bug (292821)
845 while ($col_data = $info_sth->fetchrow_hashref) {
846 last if $col_data->{'COLUMN_NAME'} eq $column;
849 if (!defined $col_data) {
852 return $self->_bz_schema->column_info_to_column($col_data);
855 =item C<bz_index_info_real($table, $index)>
857 Description: Returns information about an index on a table in the database.
858 Params: $table = name of table containing the index
859 $index = name of an index
860 Returns: An abstract index definition, always in hashref format.
861 If the index does not exist, the function returns undef.
863 sub bz_index_info_real {
864 my ($self, $table, $index) = @_;
866 my $sth = $self->prepare("SHOW INDEX FROM $table");
871 # $raw_def will be an arrayref containing the following information:
872 # 0 = name of the table that the index is on
873 # 1 = 0 if unique, 1 if not unique
874 # 2 = name of the index
875 # 3 = seq_in_index (The order of the current field in the index).
876 # 4 = Name of ONE column that the index is on
877 # 5 = 'Collation' of the index. Usually 'A'.
878 # 6 = Cardinality. Either a number or undef.
879 # 7 = sub_part. Usually undef. Sometimes 1.
880 # 8 = "packed". Usually undef.
881 # 9 = Null. Sometimes undef, sometimes 'YES'.
882 # 10 = Index_type. The type of the index. Usually either 'BTREE' or 'FULLTEXT'
883 # 11 = 'Comment.' Usually undef.
884 while (my $raw_def = $sth->fetchrow_arrayref) {
885 if ($raw_def->[2] eq $index) {
886 push(@fields, $raw_def->[4]);
887 # No index can be both UNIQUE and FULLTEXT, that's why
888 # this is written this way.
889 $index_type = $raw_def->[1] ? '' : 'UNIQUE';
890 $index_type = $raw_def->[10] eq 'FULLTEXT'
891 ? 'FULLTEXT' : $index_type;
896 if (scalar(@fields)) {
897 $retval = {FIELDS => \@fields, TYPE => $index_type};
902 =item C<bz_index_list_real($table)>
904 Description: Returns a list of index names on a table in
905 the database, as it actually exists on disk.
906 Params: $table - The name of the table you want info about.
907 Returns: An array of index names.
911 sub bz_index_list_real {
912 my ($self, $table) = @_;
913 my $sth = $self->prepare("SHOW INDEX FROM $table");
914 # Column 3 of a SHOW INDEX statement contains the name of the index.
915 return @{ $self->selectcol_arrayref($sth, {Columns => [3]}) };
918 #####################################################################
919 # MySQL-Specific "Schema Builder"
920 #####################################################################
924 =head1 MYSQL-SPECIFIC "SCHEMA BUILDER"
926 MySQL needs to be able to read in a legacy database (from before
927 Schema existed) and create a Schema object out of it. That's what
934 # This sub itself is actually written generically, but the subroutines
935 # that it depends on are database-specific. In particular, the
936 # bz_column_info_real function would be very difficult to create
937 # properly for any other DB besides MySQL.
938 sub _bz_build_schema_from_disk {
941 print "Building Schema object from database...\n";
943 my $schema = $self->_bz_schema->get_empty_schema();
945 my @tables = $self->bz_table_list_real();
946 foreach my $table (@tables) {
947 $schema->add_table($table);
948 my @columns = $self->bz_table_columns_real($table);
949 foreach my $column (@columns) {
950 my $type_info = $self->bz_column_info_real($table, $column);
951 $schema->set_column($table, $column, $type_info);
954 my @indexes = $self->bz_index_list_real($table);
955 foreach my $index (@indexes) {
956 unless ($index eq 'PRIMARY') {
957 my $index_info = $self->bz_index_info_real($table, $index);
958 ($index_info = $index_info->{FIELDS})
959 if (!$index_info->{TYPE});
960 $schema->set_index($table, $index, $index_info);