Handle timezone mismatch between webserver and DB (MySQL) server

Bug #1187212 reported by Craig Miskell on 2013-06-04
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Aaron Wells

Bug Description

If the timezone of the MySQL DB server doesn't match the timezone of the webserver (e.g. you're in AWS, where the DB server is always unchangeably UTC, and you want the webserver to be in a reasonable timezone for your users), then you get odd offset issues in, for example, the timestamps on forum posts.

The webserver takes "now" in it's local timezone, formatted as a date/time (text), and puts it into the INSERT statement. The DB sees that, interprets it as the formatted date/time, but in it's time zone (UTC), which is some number of hours offset from the actual time. It stores that as a unix epoch time (seconds from 00:00:00 1-1-1970). When this is sent back to the client, the epoch time is converted back to the local timezone, and it displays as some number of hours offset.

The simplest solution is to set the timezone on the MySQL connection, forcing the text date/time values to be interpreted as being in that timezone; they are stored correctly in epoch format, which will then display properly on viewing. It's a simple patch:
--- lib/dml.php.orig 2013-05-22 22:33:27.229452915 -0400
+++ lib/dml.php 2013-05-22 22:35:44.403280333 -0400
@@ -1474,6 +1474,9 @@

     if (is_mysql()) {
         $db->Execute("SET SQL_MODE='POSTGRESQL'");
+ if(!empty($CFG->dbtimezone)) {
+ $db->Execute("SET time_zone='$CFG->dbtimezone'");
+ }

It requires you to set dbtimezone in config.php, to the timezone of the server (so maybe it's a poorly named option; I'm open to alternative suggsions).

Aaron Wells (u-aaronw) on 2013-06-04
Changed in mahara:
milestone: none → 1.8.0rc1
assignee: nobody → Aaron Wells (u-aaronw)
importance: Undecided → Medium
status: New → Triaged
status: Triaged → In Progress
Aaron Wells (u-aaronw) wrote :

Loaded into gerrit: https://reviews.mahara.org/2257

I think Moodle avoids this whole issue by converting dates to Unix epochs and then storing them as integers.

Since this is only like to happen in power-user situations, it may be enough to just add a $cfg option (and document it in config-defaults.php). According to the MySQL manual, you can provide the timezone as a UTC offset. So perhaps we could auto-detect the web server's timezone in PHP, and then decide what timezone to send based on that?

Craig Miskell (3-crjig-7) wrote :

Auto-detecting sounds nice; in the default case (TZ's the same on web and DB), it's a no-op, but in the odd case, it just quietly handles the difference in the way the user would expect. A power user could still set a config option manually if they know better.

Using the GMT offset avoids all the issues with Mysql's known timezones differing from PHP's. Actual code: I had a quick look, and it's ugly, and I don't feel confident writing something to share with the world :)

Aaron Wells (u-aaronw) on 2013-09-30
Changed in mahara:
milestone: 1.8rc1 → 1.8.0
Aaron Wells (u-aaronw) on 2013-10-22
Changed in mahara:
milestone: 1.8.0 → 1.9.0
Robert Lyon (robertl-9) wrote :

I tested this with mysql:

I set the $cfg->dbtimezone to '-5:00' and things look ok.

To test I added to index.php:
$res = get_record_sql('SELECT @@session.time_zone');

and it returned the correct value.

However, if I set it to 'Europe/Rome' I got an error

   mysqli error: [1298: Unknown or incorrect time zone: 'Europe/Rome'] in EXECUTE("SET time_zone='Europe/Rome'")

For testing with postgres:

Both '-5:00' and 'Europe/Rome' worked.
To test I added to index.php:
   $res = get_record_sql("SELECT current_setting('TIMEZONE')");

Aaron Wells (u-aaronw) wrote :

Yes, the example time zone formats in config-defaults.php do not both work with MySQL. But that's fine, because we clearly state above it to check your manual for legal values.

I considered listing all legal timezone formats for Postgres & MySQL, but in MySQL the legal timezone formats are OS-dependent, and I figured that tracking those down would be too much work for a feature that most sites should not be using anyway.

Reviewed: https://reviews.mahara.org/2257
Committed: http://gitorious.org/mahara/mahara/commit/ae9f8fdc8daef19e4ae959c93174b6010546582c
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit ae9f8fdc8daef19e4ae959c93174b6010546582c
Author: Craig Miskell <email address hidden>
Date: Tue Jun 4 14:28:20 2013 +1200

Allow database server timezone override


Change-Id: If268ee5144eed83f8ec6ce3610b7488bb1dc9edd
Signed-off-by: Aaron Wells <email address hidden>

Aaron Wells (u-aaronw) wrote :

Congrats, Craig! After 7 months, this one's finally made it into upstream. :)

Changed in mahara:
status: In Progress → Fix Committed
tags: added: nominatedfeature
Robert Lyon (robertl-9) on 2014-04-22
Changed in mahara:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers