Red Hat Bugzilla – Bug 1260827
Migrate times in the DB from EST to UTC
Last modified: 2016-08-05 04:19:26 EDT
Description of problem:
Bugzilla database has no timezone information, and all timestamps are in EST/EDT.
This means that the DB server and the web server also need to be in EST/EDT which is a very bad server configuration (as timestamps go backwards during the switch back from daylight savings)
Version-Release number of selected component (if applicable):
How reproducible: Entirely
Steps to Reproduce:
1. Change web and DB servers to be non EST/EDT
2. Create a new bug or comment
3. See that the creation time does not match the actual time
Timestamps don't reflect reality
Web and DB server can be in any arbitrary timezone and timezone information is appropriately converted at storage and display time
Notes for Development and QE:
* The Errata tool is very sensitive to timestamps in Bugzilla. The syncing process for bug data asks Bugzilla for a list of bug ID's for bugs that have changed since the last successful sync. ET then fetches the detail of those bugs. If the timestamps are not handled correctly, ET will either sync no bugs (which will block advisories from progressing in their lifecycle) or will sync far too many bugss (which will likely cause severe performance issues for ET).
* Bugzilla propagates bug data to Jira and Salesforce (SFDC). Any timestamps in that data must be verified to propagate correctly.
* The yet-to-be-activated message bus integration include a timestamp in every message. Once that code is switched on in production, ET will consume changes from the message bus rather than polling Bugzilla periodically. Inaccurate timestamps in those messages may have a similar impact on ET as described above.
* Other users run saved searches and RPC queries to ask for various types of changes that have occurred within a specific time window. Some of those query results are used to drive critical business processes, such as triaging of security issues and RHEL bugs, so again those search results must be accurate.
* The rules engine makes use of timestamps in both displays and in the rollback function. These must be verified to work correctly.
* Timestamps must also be verified to propagate to Teiid correctly.
Note for SysOps:
* Any change to the system timezone of Bugzilla should be accompanied by a review of the start times of cron jobs. Some of these were originally chosen to run outside peak usage periods to lessen their impact on performance.
*** Bug 1260489 has been marked as a duplicate of this bug. ***
Suggest moving this to 5.x as it's an intrusive change and is likely to complicate the migration.
The task here is to change the timestamps from EST +4 to UTC, the web and database servers must all be using UTC as part o this change.
QA environment(bzperfweb01.app.qa) with version(4.4.10042-1, DB: psql)
1.Execute the scripts of this bug fix,
2.Both change the timezone to 'UTC' in web and db server.
==>Check some bugs's creation_ts and comment_time keep same as before from web UI, I compare it with current production.
==>Create a new bug, add some comments and do some update to this bug. check that all operation time record right.
How long did the script take to run in the QE environment? This information will be useful for planning the deployment of this change.
(In reply to Jason McDonald from comment #7)
> How long did the script take to run in the QE environment? This information
> will be useful for planning the deployment of this change.
I execute this script in bzperweb01 which db dumped from production in Fed 2015, it cost around 45 mins, I will try it in the rdu environment which is closer to current production, and leave comment then.
Hi Rony, did you get the times from RDU?
(In reply to Jeff Fearn from comment #10)
> Hi Rony, did you get the times from RDU?
On Rdu, It cost 48m49s.
I shut down all web servers.
Moving this back to assigned as it's not been tested as part of the BZ 5 upgrade, or integrated in to the BZ 5 code base.
The server must be using UTC tiem zone, if you used the bugzilla 5 ansible repo to configure the server this should already be done.
# ls -l /etc/localtime
lrwxrwxrwx. 1 root root 23 May 12 02:34 /etc/localtime -> /usr/share/zoneinfo/UTC
Script to run is bz5-ET2UTC.pl
This script cost almost 3 hours in qe test env(bz-web), and changed all time to 4 hours later, please confirm that this script mustn't applied to current BZ4.4 yet.
This script will only execute when BZ5 upgrade.