Repository navigation
new error in 51.1: The server time zone value 'PDT' is unrecognized #897
Description
Activity
I’m also seeing the same issue using v51.1 & Rails 5.1.6 & JRuby 9.1.10.0. Incidentally, I’m also seeing the same issue with v50.1 & Rails 5.0.7 & JRuby 9.1.10.0 as well.
Perhaps related, but it seems like this was a bug introduced introduced in
mysql-connector-javasomewhere between version5.1.33and5.1.38. The issue seems to be fixed in version5.1.39according to https://bugs.mysql.com/bug.php?id=79343 and https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-1-39.htmlWith some Tomcat web applications, when Connector/J connects to the server with useLegacyDatetimeCode=false without setting serverTimeZone, a NullPointerException was returned. This was because the timezone property file for Connector/J was loaded by the bootstrap class loader, which did not know the location of the property file and thus failed to load it. This fix avoids the problem by making Connector/J use the same class loader for both the property file and the Connector/J classes. (Bug #22353759, Bug #79343)
I see that
activerecord-jdbc-adapter/jdbc-mysqlis using version5.1.44inv51.1, and the activerecord-jdbcmysql-adapter gemspec seems to be configured to pull that in. Perhaps some changes are required to make use of the bug fixes that were introduced in mysql-connector-java 5.1.39?Upgraded my mysql version to 5.7.22 to see if that was the issue, but still getting the same error...
believe this is the result of MySQL (and its official driver) trying to have accurate TZ info (from DB).
had the issue locally and I simply set the appropriate time-zone value at my.cnf, this is a won't fix.Perhaps some changes are required to make use of the bug fixes that were introduced in mysql-connector-java 5.1.39?
nope its a separate standalone gem - just
bundle update jdbc-mysqlSo I got things working correctly by having in my.cnf
[mysqld] default-time-zone='-7:00'however that utc offset changes with daylight savings, which will make it a pain in development and not usuable in production (or does this issue not affect Linux?)
I tried setting the value to a bunch of things like
America/Los_Angelesbut mysqld wouldn't start up, saying the timezone provided was invalid. Any ideas how to set the time zone in a way that won't cause problems with daylight savings?Also, the default timezone is usually
SYSTEM, meaning the DB should get it from the OS, so it should have a perfectly accurate value with the default value, no?Reacted by Jason Lunn, idaWHALE and lancedolanReacted by idaWHALE and lancedolanwell, ask for DB support elsewhere ... were pretty under utilized here :) basically, populate MySQL's tables with time-zone data than it will handle the TZ identifier, not sure about DST. its weird but this is what we got from the driver and we already use some of its related params. seems there's not much that can be done, or maybe there is if someone is into digging for a while ...
For those that also aren't super familiar with MySQL and need help getting this working, this is what worked for me:
-
run
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql. This will populate MySQL's internal list of timezones and other variables regarding how to calculate them. BE AWARE that you will need to re-run this command on a periodic basis, as timezone info and the info to calculate them changes apparently (not sure if thats just to take into account tiny countries changing their timezones all the time, or if its actually necessary for big stable timezones like America/Los_Angeles). Personally, I'm guessing to be safe re-run this command every time the tzdata package updates? (if you are using Linux) -
in your my.cnf, under the
[mysqld]group, putdefault-time-zone='America/Los_Angeles'(or whatever timezone you want). For example:
[mysqld] default-time-zone='America/Los_Angeles'DISCLOSURE: I have no idea what the hell I'm talking about. I just did some googling and this happens to be working for me, at the moment at least, which is pretty damn scary that that's what I'm relying on. Will this work with Daylight Savings Time changes? Will this continue to be accurate if you don't re-run that command to update MySQL's timezone date? How often do you need to re-run the command? I have no idea.
--
@kares considering this behavior is very different from running Ruby on Rails in MRI, and also previous versions of this gem, I'd argue this should at minimum be well documented somewhere as a behavior difference that everyone will have to take into account.
Also, this looks like the underlying issue perhaps?: https://bugs.mysql.com/bug.php?id=85816
Reacted by Tom Devine and Shenglin Qiu-
If PDT in rails means America/Los Angeles maybe someone can PR to Rails to have timezone= do that? We could possibly monkey patch this but this may be brittle in the future? I don't know if making that change will effect logging or something else
I think the Problem is not related to this timezone in particular.
I got the same issue with CEST.
I think its this issue:
https://stackoverflow.com/questions/26515700/mysql-jdbc-driver-5-1-33-time-zone-issueReacted by Hamza AlayedSo if we pass 'useLegacyDatetimeCode' in on jdbc connection string will these timezones continue to work?
@enebo it looks like this isn't going to be fixed for a while, if at all. Could there be an official wiki on how to set up MySQL for JRuby if that's going to be the case? I barely know what I'm doing with MySQL, but I've done my best just from googling in the comment above #897 (comment), but myself and I bet you a lot of others would be way more comfortable if someone who actually knew what they were doing looked it over and approved it and made an official post somewhere...
@mohamedhafez I think there definitely should be but I am not sure what those instructions are. Your comment seems more like a workaround for a specific timezone but maybe it is a good starting point. Maybe you can make a wiki page entry and link to that page from this issue.
Personally, I would like to make our out of box experience for pure-Rails greenfield users to be the same. One thing which confuses me is why mysql2 native adapter works? Do they populate their timezone data like your comment above? The problem really does just sound like it is how a db is setup. Maybe mysql2 has some bootstrapping we can use?
its mostly that server setup changed with versions (5.1 vs 5.5) and since they make the driver they're somehow enforcing practices (not working around them). although confusing it has a point. the stuff you did applies to MRI as well and you will be better off setting up the time-zone.
initially thought its "fixable", and maybe it is, but jdbc configuration did not seem to help and the Java folks seem to have gone with a proper server setup. one minor difference that AR-JDBC doesn't and shouldn't do - a bit gross - is that under MRI Rails adapter takes AR's time-zone and sends it with every query if I recall right. which simply felt like I might as well retire from Rails completely :)
yeah, should be documented and I also would very welcome it, but doing the work takes time to fully grasp that one can spent elsewhere ... esp. if one isn't even using the thing to document on a daily basis.
EDIT: I deleted the wiki page I talk about creating here, because there's a much easier workaround that requires no db setup, see #897 (comment) below
@kares @enebo I set up a wiki at https://github.com/jruby/activerecord-jdbc-adapter/wiki/MySQL-Timezone-Setup, its my best guess from googling around, but there's a number of unresolved questions I have about the process I put at the bottom of the wiki.
Its not a huge deal to have to do this even though you don't have to with MRI, as long as there's a clear, authoritative guide on how to do it. To that end, if one of you guys, or anyone else that knows what they are doing with MySQL (i have only rudimentary knowledge) could look over that wiki, address the questions I put there, and make sure that my solution won't cause problems, that would be great.
P.S. @enebo the process I described isn't for just a specific timezone, I just used PDT as an example. It should work for other timezones as well.
Also @enebo I tried setting
useLegacyDatetimeCodeto true, and that does indeed work! @kares would that be an acceptable fix to just change that, or does that have other effects?EDIT: From reading the description in the docs, it sounds like if we do turn this variable on there's going to be a few more variable values we may have to set:
Use code for DATE/TIME/DATETIME/TIMESTAMP handling in result sets and statements that consistently handles time zone conversions from client to server and back again, or use the legacy code for these datatypes that has been in the driver for backwards-compatibility? Setting this property to 'false' voids the effects of "useTimezone," "useJDBCCompliantTimezoneShift," "useGmtMillisForDatetimes," and "useFastDateParsing."
Thinking about it some more, personally my two cents would be that trying to fix this with
useLegacyDatetimeCodemight be the wrong way to go. I think the real issue is why is the code trying to set the server time zone toPDT, even whenRails.application.config.time_zoneis set to"America/Los_Angeles"?It turns out that if in my database.yml configuration I explicitly set serverTimezone to a valid value with
properties: {serverTimezone: "America/Los_Angeles"}, things work perfectly, without any extra db setup.For @m-andreas or anyone else held up by this bug, this is an easy workaround. But I do think fixing whatever code is converting valid timezone strings to invalid ones should be fixed, because I can imagine a lot of first time JRuby users getting discouraged and turning away. Until then though, may I have permission to put this basically required configuration in README.md under "Using ActiveRecord JDBC" @kares ?
Reacted by Evan Kuchar, Samuil Goranov, Praytic, Hao Tang, Michael Floering, Alexander Zubkov and wmeneReacted by Hao Tang and Michael Floeringwe can not handle AR setup cases properly, there's a chicken egg problem with default-time-zone configuration from Rails ... as already mentioned its unfortunate (even the fact that it might change any time) how its reflectem to server from native adapter.
basically, AR::Base.default_tz is being pushed as a server configuration while previously it was enough for ARJDBC to do the conversion of time values before handing results back to AR.
So it sounds like this would be difficult to fix outright - in that case do you foresee any issues with the workaround I proposed above of setting
serverTimezoneexplicitly in database.yml @kares? Like will it deal with DST changes correctly and all that?do not know - pls try setting those and running the full suite - it has been really hard to get TZ changing tests.
unfortunately at this point I do not remember the exact issue but I spent couple hours playing with properties.On my local machine I played around with manually changing time date and time to right before the upcoming DST change in the US, to see what would happen when the change occurs, and everything worked as expected with the
properties: {serverTimezone: "America/Los_Angeles"}setting, even though there was no timezone info set up in the dbIt looks like an official configuration that solves this issue has been put in the README at https://github.com/jruby/activerecord-jdbc-adapter#mysql-specific-notes, closing this issue
When I try to run my app using activerecord-jdbcmysql-adapter 51.1, I get the following error that I didn't in previous versions:
ActiveRecord::JDBCError: The server time zone value 'PDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.In my application.rb I just have the regular old
config.time_zone = 'Pacific Time (US & Canada)'that's always worked, and the string 'PDT' occurs nowhere in my code, so I'm guessing this is a regression?Here's the full stack trace, I'm using a Mac 10.13.4, MySQL 5.7.16, Rails 5.1.6, JRuby 9.1.17.0