activerecord-jdbcmysql-adapter reads timestamps incorrectly

[pushups]

It appears that activerecord-jdbcmysql-adapter is reading timestamps incorrectly. It is converting them to the local timezone and then interpreting them as a UTC time instead of keeping them in a UTC timezone.

activerecord-jdbcmysql-adapter version: 52.2

activerecord-jdbc-adapter (52.2-java)
      activerecord (~> 5.2.0)
    activerecord-jdbcmysql-adapter (52.2-java)
      activerecord-jdbc-adapter (= 52.2)
      jdbc-mysql (~> 5.1.36)

Rails version: 5.2.3

Jruby version:

jruby 9.1.17.0 (2.3.3) 2018-04-20 d8b1ff9 Java HotSpot(TM) 64-Bit Server VM 25.202-b08 on 1.8.0_202-b08 +jit [darwin-x86_64]

Reproduction Steps:

jruby-9.1.17.0 :001 > thing = Thing.create!
   (1.8ms)  SET  @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'),  @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
   (1.0ms)  BEGIN TRANSACTION
  Thing Create (2.4ms)  INSERT INTO `things` (`created_at`, `updated_at`) VALUES ('2019-04-11 00:41:45', '2019-04-11 00:41:45')
   (11.3ms)  COMMIT TRANSACTION
 => #<Thing id: 1, created_at: "2019-04-11 00:41:45", updated_at: "2019-04-11 00:41:45">
jruby-9.1.17.0 :002 > thing_read = Thing.find(thing.id)
  Thing Load (2.9ms)  SELECT  `things`.* FROM `things` WHERE `things`.`id` = 1 LIMIT 1
 => #<Thing id: 1, created_at: "2019-04-10 17:41:45", updated_at: "2019-04-10 17:41:45">

Result: The created_at and updated_at timestamps after reading are the local time interpreted as a UTC time.

Expected result: Reading from the database doesn't change a timestamp.

I've also made an example repo where you can see this bug with a failing test: https://github.com/pushups/ar_jdbc_mysql

Some other things to note: The object is stored in the database correctly with UTC time:

mysql> select * from things;
+----+---------------------+---------------------+
| id | created_at          | updated_at          |
+----+---------------------+---------------------+
|  1 | 2019-04-11 00:41:45 | 2019-04-11 00:41:45 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

Timezones are set explicitly in the database:

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| +00:00             | +00:00              |
+--------------------+---------------------+
1 row in set (0.00 sec)

[dr-itz]

It's a MySQL jdbc configuration thing. I tried your example and I can't even create/migrate the database because MySQL is complaining about timezone issues. I get

Java::JavaSql::SQLException: The server time zone value 'CEST' 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.

So I added this to the datatabase.yml:

diff --git a/config/database.yml b/config/database.yml
index 5ae466a..dd6d54c 100644
--- a/config/database.yml
+++ b/config/database.yml
@@ -7,6 +7,8 @@
 default: &default
   adapter: mysql2
   pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
+  properties:
+    serverTimezone: <%= java.util.TimeZone.getDefault.getID %>

 development:
   <<: *default

DB is created, migrated...and the test is passing. If I change serverTimezone to UTC, it fails. For me:

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+

So I don't know the right value for you, but probably UTC. Also I don't really know much about MySQL these days, haven't used it in many years. The only reason I have it around is to test activerecord-jdbc-adapter ;)

[reidmorrison]

Thank you @dr-itz we could not get our application to connect at all to local MySQL servers when running JRuby. After adding the serverTimezone option you supplied above it suddenly started connecting to MySQL. Looks like if we have other properties in the configuration that instead of the exception you saw above it just fails to connect.

[dr-itz]

This is now in the README as well: https://github.com/jruby/activerecord-jdbc-adapter#mysql-specific-notes

[zacheryph]

So... How does one go about passing config[:properties] via DATABASE_URL?

I have tried ?serverTimezone=, ?properties.serverTimezone=, ?properties[serverTimezone]= and nothing seems to work.