ActiveRecord not setting/checking MySQL session variable time_zone

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

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

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?

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.

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

louis vuitton watches