MySQL Bugs: #80522: Using useCursorFetch leads to data corruption in Connector/J for TIME type

Bug #80522	Using useCursorFetch leads to data corruption in Connector/J for TIME type
Submitted:	26 Feb 2016 9:17	Modified:	22 Mar 2016 2:10
Reporter:	Jakub W	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.38 (exists since 5.1.1)	OS:	Ubuntu
Assigned to:	Filipe Silva	CPU Architecture:	Any
Tags:	defaultFetchSize, time, timestamp, useCursorFetch

Description:
Bug is exposed only for connections with set defaultFetchSize and useCursorFetch=true.

Using combination of TIME and one of MEDIUMTEXT, TEXT, TINYTEXT, LONGTEXT, BLOB types in query results in corruption in returned timestamp values. Looks like there is some kind of correlation between other selected values and returned timestamp value - see example. 

It was introduced in Connector/J 5.1.x version, previous releases work fine for us.

Using database MySql 5.1.72 community, Ubuntu.

How to repeat:
First of all you have to set defaultFetchSize=xxx and useCursorFetch=true as connection parameters. Then create table which contains TIME type column and one of MEDIUMTEXT, TEXT, TINYTEXT, LONGTEXT, BLOB. Insert midnight ('00:00:00') into TIME column and some text. Execute query which returns both types. Read TIME value from RowSet using getTimestamp() method. Now observe returned timestamp values.

Example code which reproduces bug:

import java.sql.*;

public class Test {

    private Connection connection;

    public static void main(String...args) throws SQLException {
        new Test().go();
    }

    public void go() throws SQLException {
        // use defaultFetchSize and useCursorFetch
        connection = DriverManager.getConnection("jdbc:mysql://localhost/test?user=testuser&password=testpass&defaultFetchSize=500&useCursorFetch=true");

        // need to have one of MEDIUMTEXT, TEXT, TINYTEXT, LONGTEXT, BLOB type in table
        execute("CREATE TEMPORARY TABLE test (t TIME, s MEDIUMTEXT)");
        execute("INSERT INTO test VALUES (CAST('00:00:00' as TIME), 'Aften');"); // insert 00:00:00 timestamp

        selectAndPrint("select t, t, t from test;"); // OK - no TEXT type in query
        selectAndPrint("select t, t, s from test;"); // magic begins, returns 06:50:00.0
        selectAndPrint("select t, s, t from test;"); // 14:00:00.0
        selectAndPrint("select t, s, s from test;"); // 14:06:05.0
        selectAndPrint("select t, s, 1 from test;"); // 14:01:00.0
        selectAndPrint("select t, s, 2 from test;"); // 14:02:00.0
        selectAndPrint("select t, s, 3 from test;"); // 14:03:00.0

        connection.close();
    }

    private void execute(String statement) throws SQLException{
        connection.createStatement().execute(statement);
    }

    private void selectAndPrint(String sql) throws SQLException{
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        while(resultSet.next()){
            System.out.println(resultSet.getTimestamp(1));
        }
        statement.close();
    }
}

Suggested fix:
Released fixed Connector/J version including code fix (and good beer ;])

Verified as described with MySQL 5.6.27 . 
The problem output happens also without defaultFetchSize in connection string like below:

Statement statement = connection.createStatement();
statement.setFetchSize(1);
ResultSet resultSet = statement.executeQuery(sql);

Any non-zero argument to setFetchSize() triggers the problem.

Hi Guys,

can we expect maintenance release any soon? This is important issue for us.
Thanks.

--
Jakub

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

"For connections with useCursorFetch=true and fetch size set with defaultFetchSize or setFetchSize, if data from a TIME and a BLOG data column was selected together, corrupted value for the TIME data was returned."

Corrected the changelog entry to the following:

"For connections with useCursorFetch=true and fetch size set with defaultFetchSize or setFetchSize, if data from a TIME and a BLOB data column was selected together, corrupted value for the TIME data was returned."

You wrote

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

but the changelog does not contain any entries

Changes in MySQL Connector/J 5.1.39 (Not yet released)
The https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-1-39.html

Subscribe to the Connector/J notifications list (http://lists.mysql.com/java) to be informed as soon as the next release gets published.

Changelog entries will appear on https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-1-39.html soon after release of the version.