MySQL Bugs: #110285: When using a CachedRowSet with a DATETIME field an exception is thrown

Bug #110285	When using a CachedRowSet with a DATETIME field an exception is thrown
Submitted:	6 Mar 2023 21:33	Modified:	14 Jun 2024 12:42
Reporter:	Moses Lecce	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	8.0.32	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Starting with version 8.0.23 and present in the latest version 8.0.32, Using a CachedRowSet (com.sun.rowset.CachedRowSetImpl) and calling getTimestamp() or getDate() will cause the following exception to be thrown:

Exception in thread "main" java.lang.ClassCastException: class java.time.LocalDateTime cannot be cast to class java.sql.Timestamp (java.time.LocalDateTime is in module java.base of loader 'bootstrap'; java.sql.Timestamp is in module java.sql of loader 'platform')        at java.sql.rowset/com.sun.rowset.CachedRowSetImpl.getDate(CachedRowSetImpl.java:2156)        at java.sql.rowset/com.sun.rowset.CachedRowSetImpl.getDate(CachedRowSetImpl.java:2697)

Note: This problem does not occur when using ResultSet directly.

How to repeat:
Consider the following code:

String mysql_host = "your_db_hostname";
String mysql_username = "your_mysql_username";
String mysql_password = "your_mysql_password";

String jdbcUrlString = "jdbc:mysql://"+mysql_host+"/aberrant?user="+mysql_username+"&password="+mysql_password+"&default_time_zone='America/Toronto'&connectionTimeZone=LOCAL&preserveInstants=true";

MysqlDataSource ds = new MysqlDataSource();
ds.setURL(jdbcUrlString);

String tmpTableCreate = "CREATE TEMPORARY TABLE IF NOT EXISTS tmp_table (`my_datecol` datetime DEFAULT NULL,`my_dateonly` date DEFAULT NULL, `my_ts` TIMESTAMP DEFAULT CURRENT_TIMESTAMP);";
String tmpTablePoplulate = "insert into tmp_table (my_datecol,my_dateonly) values (?,?)";
String query = "select my_datecol,my_dateonly,my_ts from tmp_table";

try (Connection conn = ds.getConnection();) {
   Statement stmt = conn.createStatement();
   stmt.executeUpdate(tmpTableCreate);

   try (PreparedStatement ps = conn.prepareStatement(tmpTablePoplulate);) {
	ps.setTimestamp(1,new Timestamp(date.getTime()));
	ps.setDate(2, new java.sql.Date(date.getTime()));
	ps.execute();
   }

   try (PreparedStatement ps = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);) {

       ps.execute();
       try (ResultSet rs = ps.getResultSet(); CachedRowSet crs = RowSetProvider.newFactory().createCachedRowSet();)
       {
            crs.populate(rs);
            if(crs.next())
            {
                Timestamp myTS = crs.getTimestamp("my_ts"); // this will throw an exception
            }
       }
   }
}

Suggested fix:
You can use crs.getObject("my_ts") instead of crs.getTimestamp() and there will not be a cast exception, though you will have an Object returned that you will need to cast or parse into the proper type

Added current version of Connector / J (though this issue began in 8.0.23)

Hello Moses Lecce,

Thank you for the bug report.
Please upgrade to latest version and report us back if issue persist even in latest version along with test case. Thank you.

Regards,
Ashwini Patil

Have updated Connector/J to com.mysql:mysql-connector-j:8.4.0 and am still experiencing the exception.

Exception in thread "main" java.lang.ClassCastException: class java.time.LocalDateTime cannot be cast to class java.sql.Timestamp (java.time.LocalDateTime is in module java.base of loader 'bootstrap'; java.sql.Timestamp is in module java.sql of loader 'platform')
        at java.sql.rowset/com.sun.rowset.CachedRowSetImpl.getDate(CachedRowSetImpl.java:2156)
        at java.sql.rowset/com.sun.rowset.CachedRowSetImpl.getDate(CachedRowSetImpl.java:2697)

Hello Moses Lecce,

Thank you for the feedback.
I tried to reproduce your issue on windows 11 with Connector/J 8.4.0 but I'm not seeing any issues at my end. 

Also, see Bug #107215 which is fixed in Connector/J 8.2.0 and later versions.

Regards,
Ashwini Patil