MySQL Bugs: #107215: ClassCastException: java.time.LocalDateTime cannot be cast to java.sql.Timestamp

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

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");
}

Hello Jan Kula,

Thank you for the report.

regards,
Umesh

Hello Umesh,
is there any progress in the issue? Is there any possible workaround?

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.

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."