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

Bug #101413	java.time.LocalDateTime cannot be cast to java.sql.Timestamp
Submitted:	2 Nov 2020 6:12	Modified:	18 Dec 2020 20:02
Reporter:	li leilei	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.22	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	e.LocalDateTime Timestamp

Description:
Use the parameter useserverprepstmts = true. When the 8.0.21 version is used, there is no exception. When the 8.0.22 version is used, the exception is converted。

exception message

java.lang.ClassCastException: java.time.LocalDateTime cannot be cast to java.sql.Timestamp
	at com.mysql.cj.jdbc.ServerPreparedStatement.setOneBatchedParameterSet(ServerPreparedStatement.java:761)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchedInserts(ClientPreparedStatement.java:712)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:426)
	at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:796)

How to repeat:
public class Mysql8JDbcTest {
    String url = "jdbc:mysql://192.168.56.101:3306/jdbctest?serverTimezone=Asia/Shanghai&rewriteBatchedStatements" +
            "=true&useServerPrepStmts=true";
    String sql = "insert into goods(createtime) values(?)";

    @Test
    void jdbc8021Test() {
        try (Connection con = DriverManager.getConnection(this.url, "root", "root");
             PreparedStatement ps = con.prepareStatement(this.sql);) {

       
            con.setAutoCommit(false);

            for (int i = 1; i <= 20000; i++) {
                ps.setObject(1, LocalDateTime.now());
                ps.addBatch();
                if (i % 500 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }

     
            con.commit();

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}

Hi li leilei,

Thank you for the report.
The problem is related to the recent fix for Bug#93444. Most probably it will be covered by the fix for Bug#95644.

Posted by developer:
 
Added the following entry to the Connector/J 8.0.23 changelog: 

"Storing a java.time.LoalDateTime object onto the server as a TIMESTAMP value using a batched PreparedStatement failed with the complaint that java.time.LocalDateTime could not be cast to java.sql.Timestamp. With this fix, the casting works again."

Posted by developer:
 
Corrected the changelog entry to the following: 

"Storing a java.time.LocalDateTime object onto the server as a TIMESTAMP value
using a batched PreparedStatement failed with the complaint that
java.time.LocalDateTime could not be cast to java.sql.Timestamp. With this
fix, the casting works again."