MySQL Bugs: #56479: getTimestamp throws exception

Bug #56479	getTimestamp throws exception
Submitted:	2 Sep 2010 3:55	Modified:	21 Apr 2016 19:37
Reporter:	Di-Shi Sun	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.13	OS:	Linux (CentOS 5.2)
Assigned to:	Filipe Silva	CPU Architecture:	Any

Description:
When we tried to get a TIMESTAMP column from a table, it threw an exception. 

java.sql.SQLException: Cannot convert value '2010-09-01 22:53:46' from column 2 to TIMESTAMP.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
        at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:1339)
        at com.mysql.jdbc.ByteArrayRow.getTimestampFast(ByteArrayRow.java:129)
        at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:6754)
        at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:6073)
        at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:6111)
        at TimestampException.main(TimestampException.java:32)
Caused by: java.lang.NullPointerException
        at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:1052)
        ... 5 more

According to bug reports, it should have been fixed in 5.1.5. But it is still there.

How to repeat:
mysql> CREATE TABLE TESTTABLE(ID INT PRIMARY KEY,Updated TIMESTAMP);
mysql> INSERT INTO TESTTABLE(ID,Updated) VALUES (1,"2010-09-02 01:00:00");                                                                     mysql> INSERT INTO TESTTABLE(ID,Updated) VALUES (2,"2010-09-02 02:00:00");                                                                     mysql> INSERT INTO TESTTABLE(ID,Updated) VALUES (3,"2010-09-02 03:00:00");                                                                     mysql> INSERT INTO TESTTABLE(ID,Updated) VALUES (4,"2010-09-02 04:00:00");                                                                     

$ cat TimestampException.java 
import java.sql.*;

public class TimestampException {
    public static void main(String[] args) {
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;

        try {
            String userName = "root";
            String password = "root";
            String url = "jdbc:mysql://localhost/test";
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, userName, password);
            System.out.println("Database connection established");

            statement = conn.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE
            );

            rs = statement.executeQuery("SELECT * FROM TESTTABLE");
            while (rs.next()) {
                System.out.println(rs.getTimestamp("Updated"));
                System.out.println("Trying to insert a new row");
                rs.moveToInsertRow();
                System.out.println("Trying to update timstamp");
                rs.updateTimestamp("Updated",new java.sql.Timestamp((new java.util.Date()).getTime()));
                System.out.println("Now trying to retrive the timstamp");
                System.out.println(rs.getTimestamp("Updated"));
            }
            rs.close();
        }
        catch(Exception e) {
            e.printStackTrace();
        }
        finally {
            if(conn != null) {
                try {
                    conn.close();
                    System.out.println("Database connection terminated");
                }
                catch(Exception e) { /* ignore close errors */ }
            }
        }
    }
}

A work around is to call getString("Updated") instead of rs.getTimestamp("Updated") and then parse the string value into a Date object.

Di-Shi, can you please try the latest driver (5.1.17 at the time) and report back if the error still persists.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi Di-Shi,

Verified with latest sources. Result set doesn't contain metadata when last rs.getTimestamp("Updated") is called, that causes NPE.

Added the following entry to the MySQL Connector/J 5.1.39 changelog:

"Calling getTimestamp() on a timestamp column resulted in a java.sql.SQLException (Cannot convert value ... to TIMESTAMP). That was deal to the missing metadata for each row in the ResultSet, which is now restored with this fix."

Corrected the changelog entry for the bug to the following:

"Calling getTimestamp() on a timestamp column resulted in a java.sql.SQLException (Cannot convert value ... to TIMESTAMP). That was due to the missing metadata for each row in the ResultSet. This fix ensures that the metadata is no longer missing."