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

[16 Nov 2020 9:38] Li Su
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 - -
[16 Nov 2020 11:42] Alexander Soklakov
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.
[16 Nov 2020 12:11] Li Su
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
[16 Nov 2020 12:26] Li Su
s/create table t(a datetime(3), b datetime(3);/create table t(a datetime(3), b datetime(3));/g
[19 Nov 2020 2:51] Li Su
hi Alexander Soklakov PTAL thx
[19 Nov 2020 12:20] MySQL Verification Team
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
[19 Nov 2020 12:21] MySQL Verification Team
- 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)