From 35df2cd5bc44b1f369ca6d32dfe1c600c1608dc4 Mon Sep 17 00:00:00 2001 From: David Lawrence Date: Sat, 14 May 2016 14:09:38 +0000 Subject: Bug 232193 - bmo's systems (webheads, database, etc) should use UTC natively for o/s timezone and date storage --- Bugzilla/DB/Mysql.pm | 74 --------------------------------------------- Bugzilla/DB/Schema/Mysql.pm | 23 +++++++++++++- 2 files changed, 22 insertions(+), 75 deletions(-) (limited to 'Bugzilla/DB') 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; -- cgit v1.2.3-24-g4f1b