MySQL Bugs: #82235: Latest Workbench latest JDBC driver WHERE is convertToNull in Workbench options?

Bug #82235	Latest Workbench latest JDBC driver WHERE is convertToNull in Workbench options?
Submitted:	14 Jul 2016 21:23	Modified:	15 Aug 2016 11:09
Reporter:	ROBERT G PARIS	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / J	Severity:	S5 (Performance)
Version:	5.1.35	OS:	Windows (Microsoft Windows 10 Pro)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	WBBugReporter

Description:
----[For better reports, please attach the log file after submitting. You can find it in C:\Users\rgpar\AppData\Roaming\MySQL\Workbench\log\wb.log]

WHERE is convertToNull in Workbench options?

Get ERROR
SQLException: Cannot convert value '0000-00-00 00:00:00' from column 5 to TIMESTAMP.
SQLState: S1009
VendorError: 0

and online I find:
matthias42 2012-03-03 20:28:42 UTC
Please see "zeroDateTimeBehavior" option of the mysql-jdbc driver. Setting it to "convertToNull" should fix the symptoms of your problem - reporting dates of this type as null.

How to repeat:
Use Sakila database "fresh out of box", WorkBench "fresh out of box".
From Matthews book MySQL and Java Developers Guide" use the following java code:

import java.sql.*;
class Hello {
    Connection connection;

    public Hello() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
    }

    public static void main(String[] args) throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
        Hello hello = new Hello();
        hello.connectToDB();
        hello.executeSQL();

    }

    private void displaySQLErrors(SQLException e) {
        System.out.println("SQLException: " + e.getMessage());
        System.out.println("SQLState: " + e.getSQLState());
        System.out.println("VendorError: " + e.getErrorCode());
    }

    public void connectToDB() {
        try {
            connection = DriverManager.getConnection("jdbc:mysql://localhost/sakila?user=xxxxxx&password=xxxxxxxx");
        }
        catch (SQLException e) {
            displaySQLErrors(e);
        }

    }

    public void executeSQL() {
        try {
            Statement statement = connection.createStatement();

            ResultSet rs = statement.executeQuery("select count(*) from actor");
            while (rs.next()) {
                System.out.println(rs.getTime(1));
            }
            rs.close();
            statement.close();
            connection.close();
        }
        catch (SQLException e) {
            displaySQLErrors(e);
        }
    }

}

WorkBench Log file

Attachment: wb.log (application/octet-stream, text), 11.85 KiB.

mysql-connector-java-5.1.35-bin is being used

Hello Bob Paris,

Thank you for the bug report.
I tried to reproduce the issue at my end using MySQL Connector / J 5.1.39 but not seeing any issues in convertToNull.
As in your test case you are not using connection sting properties (zeroDateTimeBehavior=convertToNull) to convert null values and you are trying to convert int value to time.

Thanks,
Chiranjeevi.

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".