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

[2 Nov 2020 6:12] li leilei
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();
        }
    }
}
[2 Nov 2020 6:56] Alexander Soklakov
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.
[18 Dec 2020 20:02] Daniel So
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."
[11 Jan 15:13] Daniel So
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."