Rails Ruby on Rails

Comments and changes to this ticket

• You flagged this item as spam.
  

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

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

• 

  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.

• You flagged this item as spam.
  

  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 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.

• You flagged this item as spam.
  

  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 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 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.

• You flagged this item as spam.
  

  bingbing March 26th, 2011 @ 02:10 AM

  louis vuitton watches