Error after upgrading to jdbc-mysql 8.0: ActiveRecord::JDBCError: HOUR_OF_DAY: 2 -> 3 #1091
Comments
|
Just a little bump on this, it seems like some relevant fixes were made in Connector/J 8.0.22 and especially 8.0.23: in 8.0.23:
in 8.0.22:
|
mohamedhafez
changed the title
mohamedhafez
changed the title
|
(Note: i made some edits to the title of the bug and the original description of the bug to reflect that this error has nothing to do with going from activerecord-jdbcmysql-adapter 60.0 to 60.1, 60.1 works just fine if you make it use jdbc-mysql 5.1.49. Its only when when we upgrade jdbc-mysql from 5.1.* to 8.0.17 or 8.0.20 that I see the issue) |
|
I just tested out with jdbc-mysql 8.0.27, and unfortunately the issue persists. I tried setting |
|
seems like a Connector/J bug to me - AR-JDBC uses roughly the same JDBC code when retrieving timestamps. there seems to be no capacity atm to look into driver specific issue esp. since the existing code works with the old one as well as MariaDB driver. happy to review PRs though, should not be hard to detetc the 8.0 driver being used. the bug report is useful - thanks for that, any chance we'll get an isolated (AR) reproducer? (including your time-zone info) |
|
Ok found an example of where this is happening! Time Zone info: When I just connect to mysql via the command line (with the session time_zone going to the default of UTC), I get the following result for this given row: mysql> SELECT created_at FROM accounts WHERE id = 1996;
+---------------------+
| created_at |
+---------------------+
| 2013-03-10 02:21:54 |
+---------------------+With jdbc-mysql 5.1.49, I get the following: >> Account.where(id: 1996).pluck(:created_at)
=> [Sat, 09 Mar 2013 19:21:54.000000000 PST -08:00]NOTE THAT THIS IS INCORRECT!! With jdbc-mysql 8.0.27, I get the following error: >> Account.where(id: 1996).pluck(:created_at)
Traceback (most recent call last):
1: from (irb):12:in `evaluate'
ActiveRecord::StatementInvalid (ActiveRecord::JDBCError: HOUR_OF_DAY: 2 -> 3)On March 10, 2013 at 2am, America/Los_Angeles was supposed to spring forward to 3am. That's the "HOUR_OF_DAY: 2 -> 3" error. It seems like it is trying to interpret that UTC time stamp as being an America/Los_Angeles timestamp for some reason, and complaining that its an invalid time since there should be no times between 2:00am and 2:59am on that day in that timezone. Doing As per the suggestion in #897 (comment), I also tried setting |
|
If I start jruby with Perhaps there is a more elegant way of doing that that won't affect other parts of JRuby? Since there are issues with both jdbc-mysql 5.1.49 and 8.0.27 I'm hoping it merits a fix... |
|
We at eazyBI faced a similar error and, in the end, created our fork to fix it by catching the exception. I made a new PR with our solution - #1160. Here are my findings about possible solutions after more investigation and conversation with @headius:
With the patched AR JDBC JAR file, I compared results with the C Ruby in a sample test app, and they matched:
From the PR test results, I see that not only is the time zone set in the code important, but the environment time zone is also taken into account. |
|
There's lots of posts online asking about this same issue, some with suggested fixes such as: Suggested fixes here include fixing data in the database (the time entered is not valid and so should be fixed), configuring the connection for UTC, and setting the server timezone to UTC. @mohamedhafez suggested a similar workaround in #1091 (comment). I'm guessing that in @jbaiza's case we may be talking about databases we do not control, such as those set up by users of EazyBI? That certainly limits our options. This one also suggests bulk scrubbing the data using a stored procedure that adjusts all The fix in #1160 is not terrible but obviously it would be nice to avoid the error altogether. I'm wondering if that's really possible though. @jbaiza A few more questions for you (and @mohamedhafez if you have some input):
I don't know what the right answer is here but getting a not-great fix in place for now would seem better than leaving it broken until we have a perfect solution (which may not exist if this is indeed bad data). |
|
@headius, I can imagine two cases when the error could occur in eazyBI, and in both cases, the data stored in the database are valid:
|
and others
I'm getting the following error after upgrading from jdbc-mysql from the 5.1.* series to 8.0.20:
ActiveRecord::JDBCError: HOUR_OF_DAY: 2 -> 3It looks like its due to upgrading Connector/J from the 5.1 series to the 8.0 series. It seems like someone reported the the bug to the Connector/J bug tracker here, and that it was marked as a possible duplicate of this bug that was resolved in Connector/J 8.0.22.
Currently, jdbc-mysql is using Connector/J 8.0.20, could we get an update to the latest version in the hopes of getting a fix to this bug?
Here's the stack trace I'm getting btw:
The text was updated successfully, but these errors were encountered: