MySQL Bugs: #101618: Got corruption datetime value by prepared statement with CONCUR

Bug #101618	Got corruption datetime value by prepared statement with CONCUR_UPDATABLE
Submitted:	16 Nov 2020 9:38	Modified:	19 Nov 2020 12:20
Reporter:	Li Su	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.49	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Got corruption DateTime value by prepared statement with CONCUR_UPDATABLE

How to repeat:
0. init a table with two datetime column and write a test row

mysql -uroot -pabc -h0.0.0.0 -P3307 test

> create table t(a datetime(3), b datetime(3);
> insert into t(a, b) select '2011-01-01 01:01:01.121', '2011-01-01 01:01:01.121';

1. config jdbc url use binary protocol

    @Bean
    public DataSource getDataSource() throws ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
        dataSourceBuilder.driverClassName("com.mysql.jdbc.Driver");
        dataSourceBuilder.url("jdbc:mysql://127.0.0.1:3307/test?useServerPrepStmts=true");
        dataSourceBuilder.username("root");
        dataSourceBuilder.password("abc");
        return dataSourceBuilder.build();
    }

2. write test to query table by ResultSet.CONCUR_UPDATABLE

@Component
public class Test {

    @Resource
    private DataSource dataSource;

    @Resource
    private JdbcTemplate jdbcTemplate;

    @PostConstruct
    public void test() throws SQLException {
        Connection connection = dataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("select * from t", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            System.out.println(resultSet.getDate(2));
        }
    }
}

expect result:

2011-01-01

but got

0471-12-11

Suggested fix:
I think it must be mistake to comment those 3 line:

https://github.com/mysql/mysql-connector-j/blob/release/5.1/src/com/mysql/jdbc/MysqlIO.jav...

                    //if (length > 7) {
                    //    nanos = (int)binaryData.readLong();
                    //}

uncomments them should fix this bug - -

Hi Li Su,

Please try the latest c/J version to ensure that the problem still exists, 5.1.35 is 6 years old already.

thanks, Alexander Soklakov

8.0.x works well

but for the latest 5.0.x version --- 5.1.49(Apr 28, 2020) still got wrong result

s/create table t(a datetime(3), b datetime(3);/create table t(a datetime(3), b datetime(3));/g

hi Alexander Soklakov PTAL thx

Hello Li Su,

Thank you for the report and feedback.
Observed that C/J 5.1.49 is affected(no issues with C/J 8.0.22).

regards,
Umesh

- C/J 8.0.22 - not affected
ant -f C:\\Work\\MySQLJava\\Bug101618 -Dnb.internal.action.name=run run
init:
deps-jar:
Created dir: C:\Work\MySQLJava\Bug101618\build
Updating property file: C:\Work\MySQLJava\Bug101618\build\built-jar.properties
Created dir: C:\Work\MySQLJava\Bug101618\build\classes
Created dir: C:\Work\MySQLJava\Bug101618\build\empty
Created dir: C:\Work\MySQLJava\Bug101618\build\generated-sources\ap-source-output
Compiling 1 source file to C:\Work\MySQLJava\Bug101618\build\classes
compile:
run:
JDK version: 11.0.3+12-LTS
MySQL Server: 8.0.22
Driver Name & Ver: MySQL Connector/Jmysql-connector-java-8.0.22 (Revision: d64b664fa93e81296a377de031b8123a67e6def2)
2011-01-01
17:47:01 IST 2020, duration: 0, connection-id: 57076, statement-id: 0, resultset-id: -1,	at Bug101618.main(Bug101618.java:20)]
BUILD SUCCESSFUL (total time: 2 seconds)

- C/J 5.1.49 affected

ant -f C:\\Work\\MySQLJava\\Bug101618 -Dnb.internal.action.name=run run
init:
Deleting: C:\Work\MySQLJava\Bug101618\build\built-jar.properties
deps-jar:
Updating property file: C:\Work\MySQLJava\Bug101618\build\built-jar.properties
compile:
run:
Thu Nov 19 17:49:35 IST 2020 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
JDK version: 11.0.3+12-LTS
MySQL Server: 8.0.22
Driver Name & Ver: MySQL Connector Javamysql-connector-java-5.1.49 ( Revision: ad86f36e100e104cd926c6b81c8cab9565750116 )
0471-12-11
BUILD SUCCESSFUL (total time: 1 second)