activerecord-jdbc-adapter: new error in 51.1: The server time zone value 'PDT' is unrecognized

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


                                       initialize at arjdbc/jdbc/RubyJdbcConnection.java:551
                                       initialize at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-jdbc-adapter-51.1-java/lib/arjdbc/abstract/core.rb:19
                                       initialize at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-5.1.6/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:69
                                       initialize at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-jdbc-adapter-51.1-java/lib/arjdbc/abstract/statement_cache.rb:21
                                       initialize at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-jdbc-adapter-51.1-java/lib/arjdbc/mysql/adapter.rb:35
                                              new at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-jdbc-adapter-51.1-java/lib/arjdbc/jdbc/callbacks.rb:12
                                  jdbc_connection at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-jdbc-adapter-51.1-java/lib/arjdbc/jdbc/connection_methods.rb:14
                                 mysql_connection at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-jdbc-adapter-51.1-java/lib/arjdbc/mysql/connection_methods.rb:91
                                   new_connection at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-5.1.6/lib/active_record/connection_adapters/abstract/connection_pool.rb:759
                          checkout_new_connection at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-5.1.6/lib/active_record/connection_adapters/abstract/connection_pool.rb:803
                   try_to_checkout_new_connection at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-5.1.6/lib/active_record/connection_adapters/abstract/connection_pool.rb:782
                               acquire_connection at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-5.1.6/lib/active_record/connection_adapters/abstract/connection_pool.rb:743
                                         checkout at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-5.1.6/lib/active_record/connection_adapters/abstract/connection_pool.rb:500
                                       connection at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-5.1.6/lib/active_record/connection_adapters/abstract/connection_pool.rb:374
                              retrieve_connection at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-5.1.6/lib/active_record/connection_adapters/abstract/connection_pool.rb:931
                              retrieve_connection at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-5.1.6/lib/active_record/connection_handling.rb:116
                                       connection at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-5.1.6/lib/active_record/connection_handling.rb:88
                                       connection at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-5.1.6/lib/active_record/relation/delegation.rb:44
                                 bound_attributes at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-5.1.6/lib/active_record/relation/query_methods.rb:92
                                     exec_queries at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-5.1.6/lib/active_record/relation.rb:678
                                             load at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-5.1.6/lib/active_record/relation.rb:546
                                          records at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-5.1.6/lib/active_record/relation.rb:255
                                             each at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activerecord-5.1.6/lib/active_record/relation/delegation.rb:39
                                              map at org/jruby/RubyEnumerable.java:830
                           load_problem_proxy_set at /Users/mohamed/rubyprojects/substitutealert/config/initializers/proxy_list.rb:70
                                           <main> at /Users/mohamed/rubyprojects/substitutealert/config/initializers/proxy_list.rb:418
                                             load at org/jruby/RubyKernel.java:974
                                  block in (root) at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activesupport-5.1.6/lib/active_support/dependencies.rb:1
                                             load at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activesupport-5.1.6/lib/active_support/dependencies.rb:286
                                  load_dependency at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activesupport-5.1.6/lib/active_support/dependencies.rb:258
                                    block in load at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activesupport-5.1.6/lib/active_support/dependencies.rb:286
                          load_config_initializer at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/railties-5.1.6/lib/rails/engine.rb:655
                                       instrument at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activesupport-5.1.6/lib/active_support/notifications.rb:168
                 block in load_config_initializer at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/railties-5.1.6/lib/rails/engine.rb:654
                                             each at org/jruby/RubyArray.java:1735
                                  block in Engine at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/railties-5.1.6/lib/rails/engine.rb:612
                                    instance_exec at org/jruby/RubyBasicObject.java:1728
                                           Engine at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/railties-5.1.6/lib/rails/engine.rb:611
                                     block in run at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/railties-5.1.6/lib/rails/initializable.rb:30
                        block in run_initializers at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/railties-5.1.6/lib/rails/initializable.rb:59
                              block in tsort_each at /Users/mohamed/.rvm/rubies/jruby-9.1.17.0/lib/ruby/stdlib/tsort.rb:228
       block in each_strongly_connected_component at /Users/mohamed/.rvm/rubies/jruby-9.1.17.0/lib/ruby/stdlib/tsort.rb:350
           each_strongly_connected_component_from at /Users/mohamed/.rvm/rubies/jruby-9.1.17.0/lib/ruby/stdlib/tsort.rb:422
  block in each_strongly_connected_component_from at /Users/mohamed/.rvm/rubies/jruby-9.1.17.0/lib/ruby/stdlib/tsort.rb:431
                                             each at org/jruby/RubyArray.java:1735
           each_strongly_connected_component_from at /Users/mohamed/.rvm/rubies/jruby-9.1.17.0/lib/ruby/stdlib/tsort.rb:421
                                             call at org/jruby/RubyMethod.java:115
                                 tsort_each_child at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/railties-5.1.6/lib/rails/initializable.rb:48
  block in each_strongly_connected_component_from at /Users/mohamed/.rvm/rubies/jruby-9.1.17.0/lib/ruby/stdlib/tsort.rb:415
                                             each at org/jruby/RubyArray.java:1735
                                             call at org/jruby/RubyMethod.java:127
                each_strongly_connected_component at /Users/mohamed/.rvm/rubies/jruby-9.1.17.0/lib/ruby/stdlib/tsort.rb:349
                each_strongly_connected_component at /Users/mohamed/.rvm/rubies/jruby-9.1.17.0/lib/ruby/stdlib/tsort.rb:347
                                       tsort_each at /Users/mohamed/.rvm/rubies/jruby-9.1.17.0/lib/ruby/stdlib/tsort.rb:226
                                       tsort_each at /Users/mohamed/.rvm/rubies/jruby-9.1.17.0/lib/ruby/stdlib/tsort.rb:205
                                 run_initializers at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/railties-5.1.6/lib/rails/initializable.rb:58
                                      public_send at org/jruby/RubyKernel.java:1830
                                      initialize! at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/railties-5.1.6/lib/rails/application.rb:353
                                           <main> at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/railties-5.1.6/lib/rails/railtie.rb:185
                                          require at org/jruby/RubyKernel.java:956
                                  block in (root) at /Users/mohamed/rubyprojects/substitutealert/config/environment.rb:28
                                           (root) at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activesupport-5.1.6/lib/active_support/dependencies.rb:1
                                          require at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activesupport-5.1.6/lib/active_support/dependencies.rb:292
                         block in load_dependency at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activesupport-5.1.6/lib/active_support/dependencies.rb:258
                                    instance_eval at org/jruby/RubyBasicObject.java:1691
                                          require at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/activesupport-5.1.6/lib/active_support/dependencies.rb:292
                                           <eval> at config.ru:3
                                             eval at org/jruby/RubyKernel.java:995
                                       initialize at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/rack-2.0.5/lib/rack/builder.rb:55
                                  new_from_string at config.ru:1
                                  new_from_string at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/rack-2.0.5/lib/rack/builder.rb:49
                                       parse_file at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/rack-2.0.5/lib/rack/builder.rb:40
                build_app_and_options_from_config at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/rack-2.0.5/lib/rack/server.rb:319
                                              app at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/rack-2.0.5/lib/rack/server.rb:219
                                              app at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/railties-5.1.6/lib/rails/commands/server/server_command.rb:24
                                      wrapped_app at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/rack-2.0.5/lib/rack/server.rb:354
                           block in log_to_stdout at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/railties-5.1.6/lib/rails/commands/server/server_command.rb:80
                                              tap at org/jruby/RubyKernel.java:1765
                                            start at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/railties-5.1.6/lib/rails/commands/server/server_command.rb:42
                                          perform at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/railties-5.1.6/lib/rails/commands/server/server_command.rb:135
                                          perform at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/railties-5.1.6/lib/rails/commands/server/server_command.rb:130
                                              run at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/thor-0.20.0/lib/thor/command.rb:27
                                   invoke_command at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/thor-0.20.0/lib/thor/invocation.rb:126
                                         dispatch at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/thor-0.20.0/lib/thor.rb:387
                                           <main> at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/railties-5.1.6/lib/rails/command/base.rb:63
                                          require at org/jruby/RubyKernel.java:956
                                           <main> at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/railties-5.1.6/lib/rails/command.rb:44
                                             load at org/jruby/RubyKernel.java:974
                                           <main> at /Users/mohamed/.rvm/gems/jruby-9.1.17.0/gems/railties-5.1.6/lib/rails/commands.rb:16

About this issue

Original URL
State: closed
Created 6 years ago
Comments: 21 (9 by maintainers)

Commits related to this issue

Explicitly set timezone on database adapter (see jruby/activerecord-jdbc-adapter#897) — committed to sosol/sosol by ryanfb 3 years ago

Most upvoted comments

Thinking about it some more, personally my two cents would be that trying to fix this with useLegacyDatetimeCode might be the wrong way to go. I think the real issue is why is the code trying to set the server time zone to PDT, even when Rails.application.config.time_zone is 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 ?

mohamedhafez on Oct 7, 2018

So 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_Angeles but 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?

mohamedhafez on Jul 17, 2018

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, put default-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

mohamedhafez on Jul 17, 2018

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

m-andreas on Aug 20, 2018