This project is archived and is in readonly mode.

#3579 ✓stale
Joachim Büchse

ActiveRecord not setting/checking MySQL session variable time_zone

Reported by Joachim Büchse | December 16th, 2009 @ 09:39 AM

Rails current timezone support implementation creates problems with MySQL servers that have not/cannot be configured to use system/global time_zone=UTC.

The mysql connection adapter does not check or set the mysql session variable time_zone to be in sync with config.active_record.default_timezone. This leads to time shifts for columns of type Timestamp.

The default setup for MySQL on OSX and Debian is to use the system time zone.

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

mysql> SHOW VARIABLES LIKE '%zone%'; 
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CET    | 
| time_zone        | SYSTEM | 
+------------------+--------+
2 rows in set (0.00 sec)

This makes sense as most simple applications are not timezone aware (ie they are not capable of converting to a users time zone) but rely on the values retrieved from the DB to be "correct" without conversion.

MySQL internally stores columns of type Timestamp as UTC value. However those UTC timestamps are transfered as values in the session timezone. When Timestamp values are retrieved or stored they are converted to/from the session timezone. Applications which are timezone aware must (or at least usually do) set the mysql session variable time_zone to UTC or GMT (in order to access a linear, non-interrupted time axis).

The rails mysql adapter offers no database.yml configuration option to set a specific session time_zone (or other mysql session variables), neither does it check the environment.rb setting of config.active_record.default_timezone to be in sync with the servers global or session time_zone.

Ugly workaround (patching MysqlAdapter in environment.rb):

# We need to patch the mysql connection adaptor to always use the UTC timezone
require 'active_record/connection_adapters/mysql_adapter'
module ActiveRecord
  module ConnectionAdapters
    class MysqlAdapter
      private
        alias old_connect connect
        def connect
          old_connect
          execute("SET SESSION time_zone=UTC")
        end
    end
  end
end

Clean solution:

The are several ways this problem could be solved.

1) At the very least, the MysqlAdapter should check whether mysqls session time_zone is the same as config.active_record.default_timezone and generate a warning if it is not. This warning doesn't actually solve the problem for the majority of mysql users, but it does solve it for those that can change mysqls global time zone or the system time zone of the DB server.

2) The problem could be solved by allowing more configuration options in database.yml, ie

development:
  adapter: mysql
  database: test
  username: test
  password: test
  session_variables: time_zone=UTC,someVar=someValue

which are to be set at the end of connect() in MysqlAdapter (this is the approach of Connector/J). While this can help solve the problem (and adds additional flexibility) it is not really DRY... we have already specified the time_zone we want to use in config.active_record.default_timezone.

3) In my opinion the cleanest solution would be if MysqlAdapter uses the setting of config.active_record.default_timezone=xxxx and issues a "SET SESSION time_zone=xxxx" at the end of the connect() method. However, this will fail if the time_zone* tables have not been created/populated in mysql.

Comments and changes to this ticket

  • Joachim Büchse

    Joachim Büchse December 16th, 2009 @ 09:42 AM

    Sorry for the formatting! A preview or edit would be great.

  • Geoff Buesing

    Geoff Buesing December 16th, 2009 @ 03:33 PM

    @Joachim: thanks for the thorough writeup.

    The issue described here with Mysql doing time zone conversions on TIMESTAMP columns. Note that TIMESTAMP columns aren't generated by ActiveRecord schema migrations -- the timestamps method adds Mysql DATETIME columns, which aren't subject to Mysql time zone conversions. So, this particular case is somewhat off the beaten path.

    Because this is a rare, off-the-Rails-way case, I don't think we can justify maintaining infrastructure necessary for solutions #1 or #3 proposed here (furthermore, I don't think these solutions could actually be implemented correctly, given that the only valid settings for config.active_record.default_timezone are :utc and :local. How would we check that the Mysql SYSTEM setting was the same as :local in the Ruby process? How would we SET SESSION correctly with :local?)

    A good solution here is to give developers an expansion point that doesn't involve monkey patching, i.e. your solution #2. With this option, nothing is implied about specific time zone support within the framework for database-side time zone conversions.

    I'm not familiar enough with the Mysql connection specifics to know of any potential issues with a session_variables config, but I think it's worth pursuing this concept further, via a patch, and/or a discussion on the Rails Core mailing list.

  • Joachim Büchse

    Joachim Büchse December 16th, 2009 @ 06:53 PM

    We use rails as a viewer on a database that's been written too by a Java server application (ie not an island by itself).

    I'm not sure how DATETIME columns are handled as we don't use them. I would not be surprised if for the aspect mentioned (timezone conversion) DATETIME behaves exactly like TIMESTAMP. I'll do a little test to check this.

    Checking if the mysql session time_zone corresponds to :utc is pretty simple.

    SHOW VARIABLES LIKE 'time_zone'

    would have to be UTC, GMT or SYSTEM. If it is SYSTEM then

    SHOW VARIABLES LIKE 'system_time_zone'

    must be UTC or GMT.

    Checking if the mysql session time_zone corresponds to :local is more tricky I agree, its the same variables but potentially not a 1-to-1 match. It might be easier to just do a

    SELECT now()

    and see if the time offset matches (this would require ignoring small deviations). It would not catch the corner case where DB + RAILS are in the same TZ but only one of them switches on DST.

    Maybe the solution for this would actually be to add another option besides :utc and :local called :matchdb?

  • Geoff Buesing

    Geoff Buesing December 16th, 2009 @ 07:31 PM

    To answer your question about Mysql DATETIME columns: they're not affected by the Mysql time zone. From the Mysql documentation:

    "The current time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting from TIMESTAMP values." http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html

    IMO the cleanest solution is the one you presented before (#2), which allows you to pass custom session variables for the connection.

  • Joachim Büchse

    Joachim Büchse December 16th, 2009 @ 10:12 PM

    Yep, (unfortunately for me;-) you are right DATETIMEs seem to be stored "like strings". The current implementation of MysqlAdapter.connect() is

            def connect
              encoding = @config[:encoding]
              if encoding
                @connection.options(Mysql::SET_CHARSET_NAME, encoding) rescue nil
              end
              @connection.ssl_set(@config[:sslkey], @config[:sslcert], @config[:sslca], @config[:sslcapath], @config[:sslcipher]) if @config[:sslkey]
              @connection.real_connect(*@connection_options)
              execute("SET NAMES '#{encoding}'") if encoding
            end
    

    If the execute("SET NAMES ...") statement does what it is supposed to, then there should be no problem with a execute(SET SESSION time_zone=...). Not sure which other session variables somebody would like to change maybe lc_time_names but that should hardly be relevant for a rails application.

    Well I guess it's time to hack up a patch...

  • Geoff Buesing

    Geoff Buesing January 28th, 2010 @ 03:25 AM

    • State changed from “new” to “incomplete”

    Relevant to this discussion, please see this change to the Postgres adapter: http://github.com/rails/rails/commit/c5b652f3d25ef92ae0f67551464fb0...

    I'm assuming that we couldn't use the same approach for MySql, because of the need to build time zone tables mentioned above.

  • Rohit Arondekar

    Rohit Arondekar October 9th, 2010 @ 03:14 AM

    • State changed from “incomplete” to “stale”
    • Importance changed from “” to “Low”

    Marking ticket as stale. If this is still an issue please leave a comment with suggested changes, creating a patch with tests, rebasing an existing patch or just confirming the issue on a latest release or master/branches.

  • bingbing

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

Pages