Migrating Bugzilla between different time zones - problem

[glauber ribeiro]

Hello,

I have a time zone problem, which i hope isn't too hard to fix. Here's my situation:

I am migrating a Bugzilla 4.2.3 setup where the OS and MySQL database is running on the 'America/Chicago' timezone. To simplify, let's say it's GMT minus 5.

The destination system will be Bugzilla 4.2.3 and there, the OS and MySQL database are running on UTC.

So here's what happens: i dump the MySQL data on the source system, and load it into the destination system. The time stamps on the database seem to be "naked" (no Time Zone) and they are in Chicago time. I run checksetup,pl to convert the database to the new format. This doesn't seem to affect the dates on the database.

But then, when i run Bugzilla on this new database, all dates appear 5 hours off. For example, stuff that happened at 8AM Chicago time, displays as 3AM Chicago time. New records have correct times, it's only the pre-existing ones that are wrong.

Bugzilla seems to be assuming that the times in the database are on UTC, but they are on Chicago Time. So it converts from UTC to Chicago Time, by subtracting the proper offset.

What am i missing? I don't think there is a way to convert the times to UTC when dumping or load the MySQL data (but if there is and you know it, i would love to know). Is there a Bugzilla parameter that i'm missing, which tells Bugzilla what time zone is used in the database?

Many thanks,

glauber

[glauber ribeiro]

Correction (sorry): old system is 4.2.3; new system is 5.0.6

glauber

[Thorsten Schöning]

Guten Tag glauber ribeiro,
am Mittwoch, 30. September 2020 um 20:01 schrieben Sie:

> Bugzilla seems to be assuming that the times in the database are on
> UTC, but they are on Chicago Time. So it converts from UTC to
> Chicago Time, by subtracting the proper offset.

Bugzilla does store UTC normally, so what it does is correct. You most
likely restored using wrong time zone settings in your client and have
wrong timestamps in the DB for old data now. That's why newly added
timestamps work.

In Postgres, Bugzilla uses "timestamp without time zone" for
timestamps, which means whoever creates rows needs to provide UTC on
itself! If you forwarded the local timestamps of your dump 1:1,
that's wrong and Bugzilla assumes it has stored UTC itself.

Not sure how MySQL handles this, but found the following. That is how
Postgres handles things using "timestamp with time zone".

> Internally a MySQL timestamp column is stored as UTC but when
> selecting a date MySQL will automatically convert it to the current
> session timezone.

> When storing a date in a timestamp, MySQL will assume that the date
> is in the current session timezone and convert it to UTC for
> storage.

https://stackoverflow.com/a/19075291/2055163

> What am i missing? I don't think there is a way to convert the
> times to UTC when dumping or load the MySQL data (but if there is
> and you know it, i would love to know).

You should be able to set the time zone per session, depending on how
MySQL works I guess to UTC during dumps and then import those new
timestamps. You should easily spot a difference if it works in your
dump and Bugzilla.

> SET time_zone = 'Europe/Helsinki';
> SET time_zone = "+00:00";
> SET @@session.time_zone = "+00:00";

https://stackoverflow.com/a/19069310/2055163

> Is there a Bugzilla
> parameter that i'm missing, which tells Bugzilla what time zone is
> used in the database?

Not that I'm aware of, because UTC is assumed. The only setting is per
user, but that would only cover your wrong timestamps.

Mit freundlichen Grüßen,

Thorsten Schöning

--
Thorsten Schöning E-Mail: Thorsten....@AM-SoFT.de
AM-SoFT IT-Systeme http://www.AM-SoFT.de/

Telefon...........05151- 9468- 55
Fax...............05151- 9468- 88
Mobil..............0178-8 9468- 04

AM-SoFT GmbH IT-Systeme, Brandenburger Str. 7c, 31789 Hameln
AG Hannover HRB 207 694 - Geschäftsführer: Andreas Muchow

[glauber ribeiro]

Gutten Tag, Thorsten,

unfortunately, despite my name, i don't speak German (only Portuguese, English, Esperanto or Spanish).

Your message was very useful, thank you. Part of the problem here is my lack of MySQL proficiency. So far, no amount of googling has revealed a way to change the time zone for a database dump. I wish this database were PostgreSQL.

One thing that could be a clue: on both sides, the user preferences for time zone say "(Site Default) America/Chicago'. I would think the new server should say UTC. Where does Bugzilla get that site default time zone from?

When i look into my database, the time stamps seem to be stored in Chicago time, not UTC, but Bugzilla is handling them fine, including timezone conversion if i change my preference.

This is one of those problems that will either be something very simple to fix, or impossible. If i have to live with bad time stamps, c'est la vie. But i want to try a little longer to get it right.

Danken,

glauber

[Thorsten Schöning]

Guten Tag glauber ribeiro,
am Mittwoch, 30. September 2020 um 21:45 schrieben Sie:

> Your message was very useful, thank you. Part of the problem here
> is my lack of MySQL proficiency. So far, no amount of googling has
> revealed a way to change the time zone for a database dump. I wish this database were PostgreSQL.

How do you create the dump? The SET-stuff I linked should work.

> One thing that could be a clue: on both sides, the user preferences
> for time zone say "(Site Default) America/Chicago'. I would think
> the new server should say UTC. Where does Bugzilla get that site
> default time zone from?

From your server somewhere, so which time zone is your server in? That
is NOT necessarily the same for MySQL, but most likely, it is NOT
necessary the same for your sessions when connecting to MySQL and it
is VERY likely not the time zone used for actual values in the
database of Bugzilla.

> When i look into my database, the time stamps seem to be stored in
> Chicago time, not UTC,

Because of the text I quoted: MySQL knows its UTC, or assumes it or
whatever, and converts the timestamp to the time of your client in the
session you look at the data with. Using the SET-statements you can
change that, SELECT the data again and should see changes. Otherwise
things heavily depend on the tools you are using to look at the
database.

> but Bugzilla is handling them fine, including
> timezone conversion if i change my preference.

Which only makes sense if it properly stores UTC for new records,
doesn't it?

> This is one of those problems that will either be something very
> simple to fix, or impossible. If i have to live with bad time
> stamps, c'est la vie. But i want to try a little longer to get it right.

https://dba.stackexchange.com/questions/121715/mysqldump-between-servers-with-different-timezones-tz-utc

[glauber ribeiro]

On Wednesday, September 30, 2020 at 3:37:46 PM UTC-5, Thorsten Schöning wrote:
> Guten Tag glauber ribeiro,
> am Mittwoch, 30. September 2020 um 21:45 schrieben Sie:
> > Your message was very useful, thank you. Part of the problem here
> > is my lack of MySQL proficiency. So far, no amount of googling has
> > revealed a way to change the time zone for a database dump. I wish this database were PostgreSQL.
> How do you create the dump? The SET-stuff I linked should work.

mysqldump \
--host=$DB_HOST \
--user=$DB_USER \
--password=$DB_PW \
--add-locks \
--add-drop-database \
--add-drop-table \
--routines \
--triggers \
--single-transaction \
--tz-utc=false \
--default-character-set=utf8 \
--databases $DB \
--hex-blob \
--verbose >[dumpfile]

I have tried with "--tz-utc" and "--tz-utc=false" (which i found in the Stack Overflow URL you quoted).

> > One thing that could be a clue: on both sides, the user preferences
> > for time zone say "(Site Default) America/Chicago'. I would think
> > the new server should say UTC. Where does Bugzilla get that site
> > default time zone from?
> From your server somewhere, so which time zone is your server in? That

UTC, i think

$ date
Wed Sep 30 21:05:21 UTC 2020

Apache agrees (maybe)

HTTP/1.1 200 OK
Date: Wed, 30 Sep 2020 20:50:34 GMT
Server: Apache/2.4.6 (Red Hat Enterprise Linux) OpenSSL/1.0.2k-fips

This is on AWS (Amazon), but i don't think that changes anything

> is NOT necessarily the same for MySQL, but most likely, it is NOT
> necessary the same for your sessions when connecting to MySQL and it
> is VERY likely not the time zone used for actual values in the
> database of Bugzilla.
> > When i look into my database, the time stamps seem to be stored in
> > Chicago time, not UTC,
> Because of the text I quoted: MySQL knows its UTC, or assumes it or
> whatever, and converts the timestamp to the time of your client in the
> session you look at the data with.

No changes. This is in my local (old) MySQL database.

mysql> select @@session.time_zone;
+---------------------+
| @@session.time_zone |
+---------------------+
| SYSTEM |
+---------------------+
1 row in set (0.00 sec)

mysql> select creation_ts from bugs where bug_id = 8948;
+---------------------+
| creation_ts |
+---------------------+
| 2020-09-30 08:05:33 |
+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = "+04:00";
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.time_zone;
+---------------------+
| @@session.time_zone |
+---------------------+
| +04:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select creation_ts from bugs where bug_id = 8948;
+---------------------+
| creation_ts |
+---------------------+
| 2020-09-30 08:05:33 |
+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone = "-04:00";
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.time_zone;
+---------------------+
| @@session.time_zone |
+---------------------+
| -04:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select creation_ts from bugs where bug_id = 8948;
+---------------------+
| creation_ts |
+---------------------+
| 2020-09-30 08:05:33 |
+---------------------+
1 row in set (0.00 sec)


All this stuff is reminding me why i have never become a MySQL fan.

> > but Bugzilla is handling them fine, including
> > timezone conversion if i change my preference.
> Which only makes sense if it properly stores UTC for new records,
> doesn't it?

Yes, unless the storage of time stamps changed between Bugzilla version 4.2.3 and current versions.


> https://dba.stackexchange.com/questions/121715/mysqldump-between-servers-with-different-timezones-tz-utc

I found this Stack Overflow page too, but it doesn't say anything useful. That's where i got the idea to try --tz-utc=false

[Thorsten Schöning]

Guten Tag glauber ribeiro,
am Mittwoch, 30. September 2020 um 23:08 schrieben Sie:

> All this stuff is reminding me why i have never become a MySQL fan.

My last suggestion for today is maybe things work better with
PostgreSQL for you:

https://www.bugzilla.org/docs/4.4/en/html/api/contrib/bzdbcopy.html

I recently switched myself for other reasons, might be worth a try if
you have easy access to a Postgres-installation.

[glauber ribeiro]

On Wednesday, September 30, 2020 at 4:42:16 PM UTC-5, Thorsten Schöning wrote:
> Guten Tag glauber ribeiro,
> am Mittwoch, 30. September 2020 um 23:08 schrieben Sie:
> > All this stuff is reminding me why i have never become a MySQL fan.
> My last suggestion for today is maybe things work better with
> PostgreSQL for you:
>
> https://www.bugzilla.org/docs/4.4/en/html/api/contrib/bzdbcopy.html
>
> I recently switched myself for other reasons, might be worth a try if
> you have easy access to a Postgres-installation.
> Mit freundlichen Grüßen,

Hmmm, yes, this could well be "the nuclear option".

So i would make a copy of my Bugzilla directory, then in the copy edit localconfig to point to an empty Postgres database, and run checksetup,pl to create the database structures. Then this script would copy from the MySQL to the Postgres. Both databases need to be accessible to the script at the same time.

Is the above paragraph correct?

Thanks,

g

[Emmanuel Seyman]

* glauber ribeiro [01/10/2020 06:25] :

>
> So i would make a copy of my Bugzilla directory, then in the copy edit
> localconfig to point to an empty Postgres database, and run checksetup,pl to
> create the database structures. Then this script would copy from the MySQL to
> the Postgres. Both databases need to be accessible to the script at the same
> time.
>
> Is the above paragraph correct?

Specifing the database information is done at the top of the bzdbcopy.pl
script (you need both databases to be configured and you can only have one
in localconfig). Other than that, you are correct.

I would also update your mysql database to the 5.0.6 schema before copying
to postgres so that you are just porting from one database to another
instead of porting and updating.

Emmanuel

[Thorsten Schöning]

Guten Tag glauber ribeiro,
am Donnerstag, 1. Oktober 2020 um 15:25 schrieben Sie:

> So i would make a copy of my Bugzilla directory, then in the copy
> edit localconfig to point to an empty Postgres database, and run
> checksetup,pl to create the database structures. Then this script
> would copy from the MySQL to the Postgres. Both databases need to be
> accessible to the script at the same time.

You don't necessarily need to copy the whole Bugzilla in theory, but
you would be safer that way of course. Otherwise things sound correct,
yes. I'm not too sure anymore what I did exactly, but can't remember
any relevant problems anymore as well, so things have been easy.

Depending on your setup and stuff, you could easily use that approach
to migrate your old Bugzilla to the new server as well. Postgres would
just need to be reachable by the source-Bugzilla. Afterwards you could
upgrade Bugzilla at the new server and wouldn't need to use dump/load.

But in general, your former approach for MySQL should work as well. I
just don't see any obvious error, so you might want to have another
look at that. Looking at timestamps and time zones is the correct
thing most likely.

[glauber ribeiro]

I'm still very suspicious of the default site timezone detection.

I haven't yet been able to understand why Bugzilla thinks the default timezone is 'America/Chicago'

At this point, i'm supposing Bugzilla uses Datetime::TimeZone to determine it. But if i do

PERL5LIB="./lib/" perl -wle 'use DateTime; print DateTime::TimeZone->new(name => "local")'

i get

DateTime::TimeZone::UTC=HASH(0x2cf7848)

as expected. If i run that as the "apache" user (which is what runs Bugzilla), i get the same.

So maybe this means Bugzilla doesn't use Perl to determine the local timezone?

g

[Thorsten Schöning]

Guten Tag glauber ribeiro,
am Donnerstag, 1. Oktober 2020 um 19:35 schrieben Sie:

> So maybe this means Bugzilla doesn't use Perl to determine the local timezone?

I don't see any other code for system wide defaults than the
following in "Bugzilla.pm":

> sub local_timezone {
> return $_[0]->process_cache->{local_timezone}
> ||= DateTime::TimeZone->new(name => 'local');
> }

But for some reason what DateTime does might differ? What does your
OS-tools say about the time zone, regardless of Perl? Something like
"dpkg-reconfigure tzdata" or whatever you need to do.

[glauber ribeiro]

On Thursday, October 1, 2020 at 1:41:52 PM UTC-5, Thorsten Schöning wrote:

> > sub local_timezone {
> > return $_[0]->process_cache->{local_timezone}
> > ||= DateTime::TimeZone->new(name => 'local');
> > }

So, i'm assuming that process_cache->{local_timezone} simply looks up to see if there is a value cached. In that case, the perl call to DateTime::TimeZone should be really what determines the local timezone.

> But for some reason what DateTime does might differ? What does your
> OS-tools say about the time zone, regardless of Perl? Something like
> "dpkg-reconfigure tzdata" or whatever you need to do.

In RedHat, i believe you just set this symlink:

$ ls -l /etc/localtime
lrwxrwxrwx. 1 root root 25 Jul 23 2019 /etc/localtime -> ../usr/share/zoneinfo/UTC


g

[glauber ribeiro]

One of the options for time zone in the user preferences in Bugzilla is "same as server". If i pick that, it sets it to UTC. So i don't know what "Site Default" is supposed to mean.

And tomorrow i'm going to lasso one of our DBAs to try to understand why changing the session time_zone has no effect on selecting a timestamp, despite documentation.

g

[Thorsten Schöning]

Guten Tag glauber ribeiro,
am Freitag, 2. Oktober 2020 um 00:04 schrieben Sie:

> One of the options for time zone in the user preferences in
> Bugzilla is "same as server". If i pick that, it sets it to UTC. So
> i don't know what "Site Default" is supposed to mean.

So, "America/Chicago" was a former user setting stored in the DB?
Because those time zone settings are per user and "same as server"
should be the default. It is for me.

Does changing your user back to UTC change anything regarding the
wrong times shown for old bugs? It should in theory, but at the same
time the times of the new bugs should be wrong now? Otherwise applying
"America/Chicago" per user wouldn't have any effect.

"Site Default" in that context really only means that NO user settings
are applied, that's why that term is missing from individual time
zones.

[glauber ribeiro]

On Friday, October 2, 2020 at 12:39:28 AM UTC-5, Thorsten Schöning wrote:
> Guten Tag glauber ribeiro,
> am Freitag, 2. Oktober 2020 um 00:04 schrieben Sie:
> > One of the options for time zone in the user preferences in
> > Bugzilla is "same as server". If i pick that, it sets it to UTC. So
> > i don't know what "Site Default" is supposed to mean.

Bah... "Site Default" is set in site-wide preferences. So much for that avenue of investigation.

g

[glauber ribeiro]

At this point, since i'm not convinced i want to take the additional risk of a MySQL to PostgreSQL migration, the state of this madness is i wrote a quick Perl script to translate each timestamp from local time (Chicago) to UTC, as it's being dumped. It seems to have worked...

Now to do some QA.


Here's the perl script - it's very naive, because (why not) i'm also rather limited in the number of modules i have available. I called it tconvert.pl.

#!/usr/bin/perl -w

use POSIX;
use Time::Local;

sub conv {
($Y,$M,$D,$h,$m,$s) = @_;
$time = timelocal($s,$m,$h,$D,$M - 1,$Y);

my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = gmtime($time);
$mon++;
$year += 1900;
my $gmt_ts = sprintf("'%04d-%02d-%02d %02d:%02d:%02d'",
$year, $mon, $mday, $hour, $min, $sec);
# print STDERR "$Y-$M-$D $h:$m:$s -> $gmt_ts\n";

return $gmt_ts;
}

while (<STDIN>) {
$_ =~ s/'(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})'/conv($1,$2,$3,$4,$5,$6)/ge;
print;
}


I put this in line, like this (simplified)

mysqldump [options] | tconvert.pl | bzip2 >dumpfile.sql.bz2



g

[Thorsten Schöning]

Guten Tag glauber ribeiro,
am Montag, 5. Oktober 2020 um 21:17 schrieben Sie:

> Bah... "Site Default" is set in site-wide preferences. So much for that avenue of investigation.

Sorry, I regularly forget about those and only looked into the
"parameters" of the admin-UI.

[glauber ribeiro]

On Monday, October 5, 2020 at 2:51:54 PM UTC-5, glauber ribeiro wrote:
> At this point, since i'm not convinced i want to take the additional risk of a MySQL to PostgreSQL migration, the state of this madness is i wrote a quick Perl script to translate each timestamp from local time (Chicago) to UTC, as it's being dumped. It seems to have worked...

So far this does seems to be working - i've checked bugs in standard and daylight-saving time.
I'll do a lot more testing, but it's looking good.