diff options
| -rw-r--r-- | Bugzilla/DB/Mysql.pm | 74 | ||||
| -rw-r--r-- | Bugzilla/DB/Schema/Mysql.pm | 23 | ||||
| -rw-r--r-- | extensions/BMO/lib/Reports/ReleaseTracking.pm | 4 | ||||
| -rw-r--r-- | extensions/BMO/lib/Reports/UserActivity.pm | 16 | ||||
| -rw-r--r-- | scripts/convert_datetime.pl | 125 |
5 files changed, 159 insertions, 83 deletions
diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm index bf8854c9c..8a545bd87 100644 --- a/Bugzilla/DB/Mysql.pm +++ b/Bugzilla/DB/Mysql.pm @@ -566,80 +566,6 @@ sub bz_setup_database { $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(); - - 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"; - - $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( diff --git a/Bugzilla/DB/Schema/Mysql.pm b/Bugzilla/DB/Schema/Mysql.pm index b0a40586f..bcc663e83 100644 --- a/Bugzilla/DB/Schema/Mysql.pm +++ b/Bugzilla/DB/Schema/Mysql.pm @@ -119,7 +119,7 @@ sub _initialize { LONGBLOB => 'longblob', - DATETIME => 'datetime', + DATETIME => 'timestamp', DATE => 'date', }; @@ -396,4 +396,25 @@ sub get_rename_column_ddl { return ("ALTER TABLE $table CHANGE COLUMN $old_name $new_name $def"); } +sub get_type_ddl { + my $self = shift; + my $type_ddl = $self->SUPER::get_type_ddl(@_); + + # TIMESTAMPS as of 5.6.6 still default to + # 'NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP' + # unless explicitly setup in the table definition. This will change in future releases + # and can be disabled by using 'explicit_defaults_for_timestamp = 1' in my.cnf. + # So instead, we explicitly setup TIMESTAMP types to not be automatic. + if ($type_ddl =~ /^timestamp/i) { + if ($type_ddl !~ /NOT NULL/) { + $type_ddl .= ' NULL DEFAULT NULL'; + } + if ($type_ddl =~ /NOT NULL/ && $type_ddl !~ /DEFAULT/) { + $type_ddl .= ' DEFAULT CURRENT_TIMESTAMP'; + } + } + + return $type_ddl; +} + 1; diff --git a/extensions/BMO/lib/Reports/ReleaseTracking.pm b/extensions/BMO/lib/Reports/ReleaseTracking.pm index 0d9a51583..8894a5169 100644 --- a/extensions/BMO/lib/Reports/ReleaseTracking.pm +++ b/extensions/BMO/lib/Reports/ReleaseTracking.pm @@ -367,9 +367,9 @@ sub report { push @where, "(a.fieldid = ?)"; push @params, $q->{field_id}; - push @where, "(a.bug_when >= ?)"; + push @where, "(CONVERT_TZ(a.bug_when, 'UTC', 'America/Los_Angeles') >= ?)"; push @params, $q->{start_date} . ' 00:00:00'; - push @where, "(a.bug_when <= ?)"; + push @where, "(CONVERT_TZ(a.bug_when, 'UTC', 'America/Los_Angeles') <= ?)"; push @params, $q->{end_date} . ' 23:59:59'; push @where, "(a.added LIKE ?)"; diff --git a/extensions/BMO/lib/Reports/UserActivity.pm b/extensions/BMO/lib/Reports/UserActivity.pm index 04810c2ec..0a8e79e1f 100644 --- a/extensions/BMO/lib/Reports/UserActivity.pm +++ b/extensions/BMO/lib/Reports/UserActivity.pm @@ -126,7 +126,8 @@ sub report { INNER JOIN profiles ON profiles.userid = bugs_activity.who WHERE profiles.login_name IN ($who_bits) - AND bugs_activity.bug_when >= ? AND bugs_activity.bug_when <= ? + AND CONVERT_TZ(bugs_activity.bug_when, 'UTC', 'America/Los_Angeles') >= ? + AND CONVERT_TZ(bugs_activity.bug_when, 'UTC', 'America/Los_Angeles') <= ? $activity_where UNION ALL @@ -147,8 +148,8 @@ sub report { INNER JOIN profiles ON profiles.userid = longdescs_tags_activity.who WHERE profiles.login_name IN ($who_bits) - AND longdescs_tags_activity.bug_when >= ? - AND longdescs_tags_activity.bug_when <= ? + AND CONVERT_TZ(longdescs_tags_activity.bug_when, 'UTC', 'America/Los_Angeles') >= ? + AND CONVERT_TZ(longdescs_tags_activity.bug_when, 'UTC', 'America/Los_Angeles') <= ? $tags_activity_where UNION ALL @@ -167,7 +168,8 @@ sub report { INNER JOIN profiles ON profiles.userid = bugs.reporter WHERE profiles.login_name IN ($who_bits) - AND bugs.creation_ts >= ? AND bugs.creation_ts <= ? + AND CONVERT_TZ(bugs.creation_ts, 'UTC', 'America/Los_Angeles') >= ? + AND CONVERT_TZ(bugs.creation_ts, 'UTC', 'America/Los_Angeles') <= ? UNION ALL @@ -185,7 +187,8 @@ sub report { INNER JOIN profiles ON profiles.userid = longdescs.who WHERE profiles.login_name IN ($who_bits) - AND longdescs.bug_when >= ? AND longdescs.bug_when <= ? + AND CONVERT_TZ(longdescs.bug_when, 'UTC', 'America/Los_Angeles') >= ? + AND CONVERT_TZ(longdescs.bug_when, 'UTC', 'America/Los_Angeles') <= ? $comment_filter UNION ALL @@ -204,7 +207,8 @@ sub report { INNER JOIN profiles ON profiles.userid = attachments.submitter_id WHERE profiles.login_name IN ($who_bits) - AND attachments.creation_ts >= ? AND attachments.creation_ts <= ? + AND CONVERT_TZ(attachments.creation_ts, 'UTC', 'America/Los_Angeles') >= ? + AND CONVERT_TZ(attachments.creation_ts, 'UTC', 'America/Los_Angeles') <= ? $attachments_where ORDER BY $order "; diff --git a/scripts/convert_datetime.pl b/scripts/convert_datetime.pl new file mode 100644 index 000000000..b87e3561a --- /dev/null +++ b/scripts/convert_datetime.pl @@ -0,0 +1,125 @@ +#!/usr/bin/perl +# This Source Code Form is subject to the terms of the Mozilla Public +# License, v. 2.0. If a copy of the MPL was not distributed with this +# file, You can obtain one at http://mozilla.org/MPL/2.0/. +# +# This Source Code Form is "Incompatible With Secondary Licenses", as +# defined by the Mozilla Public License, v. 2.0. + +use 5.10.1; +use strict; +use warnings; + +use Bugzilla; +use Bugzilla::Constants; +use Bugzilla::DB; + +Bugzilla->usage_mode(USAGE_MODE_CMDLINE); + +my $bugs_dbh = Bugzilla->dbh; +my $localconfig = Bugzilla->localconfig; + +my $root_mysql_pw = shift; +defined $root_mysql_pw || die "MySQL root password required.\n"; + +my $mysql_dbh = Bugzilla::DB::_connect({ + db_driver => $localconfig->{db_driver}, + db_host => $localconfig->{db_host}, + db_name => 'mysql', + db_user => 'root', + db_pass => $root_mysql_pw +}); + +# Check that the mysql timezones are populated and up to date +my $mysql_tz_install + = "Please populate using instuctions at http://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html#time-zone-installation and re-run this script."; +my $mysql_tz_count = $mysql_dbh->selectrow_array("SELECT COUNT(*) FROM mysql.time_zone_name"); +$mysql_tz_count + || die "The timezone table mysql.time_zone_name has not been populated.\n$mysql_tz_install\n"; +my $mysql_tz_date1 = $mysql_dbh->selectrow_array("SELECT CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central')"); +my $mysql_tz_date2 = $mysql_dbh->selectrow_array("SELECT CONVERT_TZ('2007-03-11 3:00:00','US/Eastern','US/Central')"); +($mysql_tz_date1 eq $mysql_tz_date2) + || die "The timezone table mysql.time_zone_name needs to be updated.\n$mysql_tz_install\n"; + +my $rows = $mysql_dbh->selectall_arrayref( + "SELECT TABLE_NAME, COLUMN_NAME + FROM INFORMATION_SCHEMA.COLUMNS + WHERE TABLE_SCHEMA = ? + AND DATA_TYPE='datetime'", + undef, Bugzilla->localconfig->{db_name}); +my $total = scalar @$rows; + +if (!$total) { + print "No DATETIME columns found.\n"; + exit; +} + +print STDERR <<EOF; +About to convert $total DATETIME columns to TIMESTAMP columns and migrate their values from PST to UTC. + +Press <Ctrl-C> to stop or <Enter> to continue... +EOF +getc(); + +# Store any indexes we may need to drop/add later +my %indexes; +foreach my $row (@$rows) { + my ($table, $column) = @$row; + next if exists $indexes{$table} && exists $indexes{$table}{$column}; + my $table_info = $bugs_dbh->bz_table_info($table); + next if !exists $table_info->{INDEXES}; + my $table_indexes = $table_info->{INDEXES}; + for (my $i = 0; $i < @$table_indexes; $i++) { + my $name = $table_indexes->[$i]; + my $definition = $table_indexes->[$i+1]; + if ((ref $definition eq 'HASH' && grep($column eq $_, @{ $definition->{FIELDS} })) + || (ref $definition eq 'ARRAY' && grep($column eq $_, @$definition))) + { + $indexes{$table} ||= {}; + $indexes{$table}->{$column} = { name => $name, definition => $definition }; + last; + } + } +} + +my @errors; +foreach my $row (@$rows) { + my ($table, $column) = @$row; + + if (my $column_info = $bugs_dbh->bz_column_info($table, $column)) { + say "Converting $table.$column to TIMESTAMP..."; + + # Drop any indexes first + if (exists $indexes{$table} && exists $indexes{$table}->{$column}) { + my $index_name = $indexes{$table}->{$column}->{name}; + $bugs_dbh->bz_drop_index($table, $index_name); + } + + # Rename current column to PST + $bugs_dbh->bz_rename_column($table, $column, $column . "_pst"); + + # Create the new UTC column + $column_info->{TYPE} = 'TIMESTAMP'; + $column_info->{DEFAULT} = 'CURRENT_TIMESTAMP' if $column_info->{NOTNULL} && !$column_info->{DEFAULT}; + $bugs_dbh->bz_add_column($table, $column, $column_info); + + # Migrate the PST value to UTC + $bugs_dbh->do("UPDATE $table SET $column = CONVERT_TZ(" . $column . '_pst' . ", 'America/Los_Angeles', 'UTC')"); + + # Drop the old PST column + $bugs_dbh->bz_drop_column($table, $column . '_pst'); + + # And finally recreate the index if one existed for this column + if (exists $indexes{$table} && exists $indexes{$table}->{$column}) { + my $index_info = $indexes{$table}->{$column}; + $bugs_dbh->bz_add_index($table, $index_info->{name}, $index_info->{definition}); + } + } + else { + push(@errors, "$table.$column does not exist in bz_schema and will need to fixed manually."); + } +} + +if (@errors) { + print "Errors:\n" . join("\n", @errors) . "\n"; +} |