| Bug #109025 | Zero-based values in Date fields in Java Connector version 8.X throws exception | ||
|---|---|---|---|
| Submitted: | 8 Nov 2022 11:38 | Modified: | 16 Jan 2023 13:54 |
| Reporter: | Ivan Kucher | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 8.X, 8.0.31 | OS: | MacOS |
| Assigned to: | CPU Architecture: | Any | |
[8 Nov 2022 12:34]
MySQL Verification Team
Hello Ivan Kucher, Thank you for the report and feedback. regards, Umesh
[10 Jan 2023 20:16]
Filipe Silva
Posted by developer: This is a behavior we definitely don't want to replicate anymore. It makes no sense to return date values to the client application when the ones stored in the database are totally different, and this is not even a case of differences in Time Zone settings which could justify adjusting temporal values. It's a fact that Java does not support dates with zero values and the result was those weird shifts if we didn't use a non-lenient calendar, thus it's not possible to return such values a true Date objects, but you have to option to return the originally stored values as string (rs.getString(col)) and then work from there.
[16 Jan 2023 13:45]
Ivan Kucher
Thank you for your answer. But I still can't understand then why is it possible to have such DATE rows with zero-based values in the MySQL database and in the same time NOT possible to handle them using MySQL connector of version 8.X.
[16 Jan 2023 13:52]
Ivan Kucher
Then there is incompatibility between MySQL server and MySQL connector, since you have an option NO_ZERO_IN_DATE (https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_in_date), and at the same time if I will turn off NO_ZERO_IN_DATE option, I will have an issues with the connector. So this option brakes the logic.
[16 Jan 2023 13:54]
Ivan Kucher
Absolutely the same option you still have in the MySQL connector of version 8.X https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_zero_in_date
Description: Previously our applications had mysql java connector of version 5.1.49 and in the database some of our fields has type Date and values 1999-00-00 or 2000-00-00. Our MySQL version is 5.7. After executing SELECT query connector maps date values with 0-based MONTHs or DAYs to the previous year. For example: Having 1999-00-00 after executing SELECT through my-ibatis-2-spring:1.1.0 in the result we receive 1998-11-30. Having 2000-00-00 after executing SELECT through my-ibatis-2-spring:1.1.0 in the result we receive 1999-11-30. We decided to upgrade Connector to the newest version 8.0.31. Executing the same query for the same result set now throw an Exception with the following stack trace: Caused by: com.mysql.cj.exceptions.WrongArgumentException: MONTH at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105) at com.mysql.cj.result.SqlDateValueFactory.localCreateFromDate(SqlDateValueFactory.java:86) at com.mysql.cj.result.SqlDateValueFactory.localCreateFromDate(SqlDateValueFactory.java:51) at com.mysql.cj.result.AbstractDateTimeValueFactory.createFromDate(AbstractDateTimeValueFactory.java:69) at com.mysql.cj.protocol.a.MysqlTextValueDecoder.decodeDate(MysqlTextValueDecoder.java:77) Caused by: java.sql.SQLException: MONTH at com.mysql.cj.protocol.result.AbstractResultsetRow.decodeAndCreateReturnValue(AbstractResultsetRow.java:92) at com.mysql.cj.protocol.result.AbstractResultsetRow.getValueFromBytes(AbstractResultsetRow.java:243) at com.mysql.cj.protocol.a.result.ByteArrayRow.getValue(ByteArrayRow.java:91) ... 129 common frames omitted Caused by: java.lang.IllegalArgumentException: MONTH at java.util.GregorianCalendar.computeTime(GregorianCalendar.java:2644) at java.util.Calendar.updateTime(Calendar.java:3395) at java.util.Calendar.getTimeInMillis(Calendar.java:1782) Caused by: com.mysql.cj.exceptions.WrongArgumentException: MONTH at com.mysql.cj.result.SqlDateValueFactory.localCreateFromDate(SqlDateValueFactory.java:83) ... 135 common frames omitted Going through the mysql-connector source code I found out that in SqlDateValueFactory.class in the version 8.0.31 in the method you have one line this.cal.set(idate.getYear(), idate.getMonth() - 1, idate.getDay()); which is basically decrease month value, so if we had 0, after executing this.cal.set() you pass -1 value. This cause Calendar to throw an Exception. @Override public Date localCreateFromDate(InternalDate idate) { synchronized (this.cal) { try { if (idate.isZero()) { throw new DataReadException(Messages.getString("ResultSet.InvalidZeroDate")); } this.cal.clear(); this.cal.set(idate.getYear(), idate.getMonth() - 1, idate.getDay()); long ms = this.cal.getTimeInMillis(); return new Date(ms); } catch (IllegalArgumentException e) { throw ExceptionFactory.createException(WrongArgumentException.class, e.getMessage(), e); } } } Is it possible somehow to fix it? We was expecting mysql-connector of version 8.0.31 have the same behaviour as of version 5.1.49. How to repeat: MySQL version 5.7. Connector version 8.0.31 Java version 8 Having table with field of type Date and value 1999-00-00. After executing SELECT through my-ibatis-2-spring:1.1.0 in the result we receive an exception Caused by: com.mysql.cj.exceptions.WrongArgumentException: MONTH Suggested fix: MySQL version 5.7. Connector version 8.0.31 Java version 8 Having table with field of type Date and value 1999-00-00. After executing SELECT through my-ibatis-2-spring:1.1.0 in the result we receive the same value as in the version 5.1.49.