Re: Migrating Bugzilla between different time zones - problem

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

_______________________________________________
support-bugzilla mailing list
[email protected]
https://lists.mozilla.org/listinfo/support-bugzilla
PLEASE put [email protected] in the To: field when you reply.