5.1-micro can not handle prepared statements with timestamps involving nanoseconds

[Philip Stoev]

5.1-micro can not handle prepared statements where a nanosecond-precision timestamp is sent to the server. Nanoseconds may be constructed by Java applications using the Timestamp class which operates in nanoseconds.

Test case:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.util.Calendar;

class b5 {
        public static void main(String[] args) {
                Connection conn;
                try {
                        Class.forName("com.mysql.jdbc.Driver").newInstance();
                        String url = "jdbc:mysql://127.0.0.1:3306/test?traceProtocol=true&profileSQL=true";
                        String userName = "test";
                        String userPassword = "";

                        conn = DriverManager.getConnection(url, userName, userPassword);

                        Statement stmt = conn.createStatement();

                        stmt.executeUpdate("DROP TABLE IF EXISTS t1");
                        stmt.executeUpdate("CREATE TABLE IF NOT EXISTS t1 (t_id int(10), test_date timestamp NOT NULL,primary key t_pk (t_id));");
                        stmt.executeUpdate("insert into t1 values (1,NOW());");

                        PreparedStatement pstmt = ((com.mysql.jdbc.Connection) conn).serverPrepareStatement("UPDATE t1 SET test_date=ADDDATE( ? , INTERVAL 1 YEAR) WHERE t_id=1;");
                        Timestamp ts = new Timestamp(System.currentTimeMillis());

                        ts.setNanos(99999999);
                        pstmt.setTimestamp(1, ts);

                        System.out.println("Updated rows: " + pstmt.executeUpdate());

                        ts.setNanos(999999999);
                        pstmt.setTimestamp(1, ts);
                        System.out.println("Timestamp is " + ts.toString());

                        System.out.println("Updated rows: " + pstmt.executeUpdate());

                } catch (Exception e) {
                        System.out.println("Exception: "+e);
                        e.printStackTrace();
                }
        }
}

[Philip Stoev (pstoev-askmonty)]

The test case results in:

Exception: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'test_date' at row 1
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'test_date' at row 1
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3601)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3535)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1989)
        at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1347)
        at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:845)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2415)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2333)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2318)
        at b5.main(b5.java:39)