Bug #107215 ClassCastException: java.time.LocalDateTime cannot be cast to java.sql.Timestamp
Submitted: 5 May 2022 8:11 Modified: 14 Sep 2023 16:49
Reporter: Jan Kula Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.29 OS:Any
Assigned to: CPU Architecture:Any

[5 May 2022 8:11] Jan Kula
Description:
Regarding the change as noted in the changelog https://dev.mysql.com/doc/relnotes/connector-j/8.0/en/news-8-0-23.html:

Functionality Added or Changed -> Important -> "Also, with the implementation of the new mechanism, a getObject(columnIndex) call on a DATETIME column returns a LocalDateTime object now instead of a String. To receive a String like before, use getObject(columnIndex, String.class) instead."

-----

Am I missing something or this change breaks getDate method in JRE CachedRowSetImpl? After MySQL JDBC Connector upgrade to version >= 8.0.23 i get ClassCastException

Caused by: java.lang.ClassCastException: java.time.LocalDateTime cannot be cast to java.sql.Timestamp
	at com.sun.rowset.CachedRowSetImpl.getDate(CachedRowSetImpl.java:2141)
	at org.springframework.jdbc.support.rowset.ResultSetWrappingSqlRowSet.getDate(ResultSetWrappingSqlRowSet.java:232)
	at org.springframework.jdbc.support.rowset.ResultSetWrappingSqlRowSet.getDate(ResultSetWrappingSqlRowSet.java:245)

calling:

rowset.getDate("date");

How to repeat:
RowSetFactory factory = RowSetProvider.newFactory();
CachedRowSet rowset = factory.createCachedRowSet();
rowset.setUrl(url);
rowset.setUsername(username);
rowset.setPassword(password);
rowset.setCommand(sql); 
rowset.execute();

...

while (rowset.next()) {
    rowset.getDate("date");
}
[5 May 2022 9:51] MySQL Verification Team
Hello Jan Kula,

Thank you for the report.

regards,
Umesh
[24 May 2022 13:12] Jan Kula
Hello Umesh,
is there any progress in the issue? Is there any possible workaround?
[16 Jun 2022 22:40] Eric Schoen
Apologies if this is not the appropriate way to chime in on this issue. 

Like many developers, we are slow to move forward on Connector/J versions, and updates are typically driven by the need to remove vulnerabilities that appear in the National Vulnerability Database.  (for example: https://nvd.nist.gov/vuln/detail/CVE-2021-2471)

Accordingly, we recently jumped from version 8.0.16 to 8.0.29, and stumbled across this bug.  

Specifically: changing the return type of a DATETIME field is a critical breaking change.  I would strenuously argue that the severity level is S1.  The change made in 8.0.23 breaks any code that expects a DATETIME field to be a particular type, whether that type is a string, a java.sql.Timestamp (which is what we used to see), or any other java type.
[14 Sep 2023 16:49] Daniel So
Posted by developer:
 
Added the following entry to the Connector/J 8.2.0 changelog: 

"Using javax.sql.rowset.CachedRowSet#getDate() or javax.sql.rowset.CachedRowSet#getTimestamp() on DATETIME fields resulted in a ClassCastException. It was because the default return type of DATETIME fields by ResultSet.getObject() was java.time.LocalDateTime instead of java.sql.Timestamp. To prevent the exception, a new connection property, treatMysqlDatetimeAsTimestamp, now allows the return type of DATETIME by ResultSet.getObject() to be changed to java.sql.Timestamp."