| Bug #93444 | LocalDateTime parameter values altered when client and server timezones differ | ||
|---|---|---|---|
| Submitted: | 2 Dec 2018 16:56 | Modified: | 25 Sep 2020 21:33 |
| Reporter: | Gord Thompson | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 8.0.13 | OS: | Any |
| Assigned to: | Filipe Silva | CPU Architecture: | Any |
[4 Dec 2018 13:06]
Filipe Silva
Hi Gord, Thank you for your interest in MySQL and Connector/J. You're right, this is a bug and should be fixed.
[6 Feb 2019 13:19]
Sam Price
This affects all Local* classes in the JDK. Any chance we could have a fix in 8.0.16, please? :) I'm happy to contribute a patch.
[20 May 2019 17:42]
OCA Admin
Contribution submitted via Github - Fix for #93444 - improve setObject behavior for LocalDate, LocalDateTime and Loc (*) Contribution by 巖 阿部 (Github harawata, mysql-connector-j/pull/36): This PR is an attempt to fix [#93444](https://bugs.mysql.com/bug.php?id=93444). i.e. - Inserted `LocalDate`, `LocalDateTime` and `LocalTime` values are altered when there is timezone difference between server and client. The basic idea is to eliminate involvement of the legacy date/time classes (e.g. `java.sql.Timestamp`, `java.util.Calendar`) during the process. Some notes: - On my environment, there is no new test failure caused by this change. - Commented-out lines in `StatementTest` are seemingly unsupported conversions. I left them for you to confirm that they all are expected. - I have sent my signed OCA a few days ago. - I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it. If there is anything you want me to change or clarify, please let me know.
Contribution: git_patch_280113759.txt (text/plain), 35.05 KiB.
[28 Oct 2019 14:09]
Gareth Smith
This is still an issue. Please can the fix be included in a new release of mysql-connector-java ?
[30 Jan 2020 9:10]
Ruslan Stelmachenko
It's still relevant. It's also related to #98436 (same problem, but with LocalDate).
[13 Feb 2020 14:00]
Alexander Soklakov
Bug#98468 is marked as a duplicate of this one.
[13 Feb 2020 14:18]
Alexander Soklakov
Bug#97240 is marked as a duplicate of this one.
[13 Feb 2020 15:42]
Alexander Soklakov
Bug#96276 is marked as a duplicate of this one.
[25 Sep 2020 21:33]
Daniel So
Posted by developer: Added the following entry to the Connector/J 8.0.22 changelog: "LocalDate, LocalDateTime, and LocalTime values set through Connector/J were altered when there was a timezone difference between the server and the client. This fix corrects the issue by handling the LocalDate, LocalTime, and LocalDateTime with no time zone conversion and no intermediate conversions to other date-time classes. Thanks to Iwao Abe for his contribution to the fix."
[20 Oct 2020 6:52]
Frederic Descamps
Thank you for your contribution that has been added to 8.0.22: https://lefred.be/content/mysql-8-0-22-thank-you-for-the-contributions/
[3 Sep 2021 11:37]
Vojtech Knyttl
The problem with this still persists for me when using LocalDate (version 8.0.26). I created a new issue for this: https://bugs.mysql.com/bug.php?id=104822&thanks=4
[19 Feb 2022 14:30]
Roman Zinchuk
I have the same bug, but with LocalDateTime. I pass "2022-02-14 00:00" as query param in java, but got "2022-02-13 22:00" in query. i'm using mysql-connector-java:8.0.27
Description: java.time.LocalDateTime parameter values are being altered when the client timezone and server timezone are different. How to repeat: Repro code: package myjdbcbug; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.time.LocalDateTime; import java.util.TimeZone; public class MyJdbcBugMain { public static void main(String[] args) { TimeZone.setDefault(TimeZone.getTimeZone("America/Edmonton")); // JVM timezone UTC-07:00 final String cnxnUrl = "jdbc:mysql://192.168.0.187:3307/mydb?useUnicode=true&characterEncoding=utf8" + "&serverTimezone=UTC"; try (Connection cnxn = DriverManager.getConnection(cnxnUrl, "root", "whatever")) { System.out.println(cnxn.getMetaData().getDriverVersion()); Statement st = cnxn.createStatement(); st.execute("CREATE TEMPORARY TABLE foo (id INT PRIMARY KEY, dt DATETIME)"); final LocalDateTime ldtSent = LocalDateTime.of(2018, 12, 1, 10, 0, 0); System.out.printf("LocalDateTime sent: %s%n", ldtSent.toString()); PreparedStatement ps = cnxn.prepareStatement("INSERT INTO foo (id, dt) VALUES (?,?)"); ps.setInt(1, 1); ps.setObject(2, ldtSent); ps.executeUpdate(); ResultSet rs = st.executeQuery("SELECT dt FROM foo WHERE id=1"); rs.next(); final LocalDateTime ldtRcvd = rs.getObject(1, LocalDateTime.class); System.out.printf("LocalDateTime rcvd: %s%n", ldtRcvd.toString()); } catch (Throwable ex) { ex.printStackTrace(System.err); } } } Output: mysql-connector-java-8.0.13 (Revision: 66459e9d39c8fd09767992bc592acd2053279be6) LocalDateTime sent: 2018-12-01T10:00 LocalDateTime rcvd: 2018-12-01T17:00 Wireshark shows that the value being sent by Connector/J has time value of '17:00', not '10:00': 0000 00 22 68 d2 c1 c4 40 2c f4 34 c0 d3 08 00 45 00 ."hÒÁÄ@,ô4ÀÓ..E. 0010 00 68 3d 55 40 00 80 06 3a 7c c0 a8 00 b3 c0 a8 .h=U@...:|À¨.³À¨ 0020 00 bb c0 ca 0c eb 3c 5d b3 a2 ea 6f b4 ec 50 18 .»ÀÊ.ë<]³¢êo´ìP. 0030 3e ea 7f 91 00 00 3c 00 00 00 03 49 4e 53 45 52 >ê....<....INSER 0040 54 20 49 4e 54 4f 20 66 6f 6f 20 28 69 64 2c 20 T INTO foo (id, 0050 64 74 29 20 56 41 4c 55 45 53 20 28 31 2c 27 32 dt) VALUES (1,'2 0060 30 31 38 2d 31 32 2d 30 31 20 31 37 3a 30 30 3a 018-12-01 17:00: 0070 30 30 2e 30 27 29 00.0') Both java.time.LocalDateTime and MySQL DATETIME columns are timezone-agnostic so this should not happen.