Bug #99495 Get dirty connection resultset
Submitted: 9 May 3:57 Modified: 3 Sep 23:05
Reporter: hcy john Email Updates:
Status: No Feedback Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.49 OS:Any
Assigned to: CPU Architecture:ARM

[9 May 3:57] hcy john
Description:
mysql-connector-java: 5.1.49
jdk-version:1.8

when happen CommunicationsException and MySQLTimeoutException simultaneously, the connection will get dirty connection resultset. i guess  CommunicationsException will be  overwritten by MySQLTimeoutException.

How to repeat:
Verification condition:
1. set sockettimeout  and querytimeout with close value
2. run multi thread to execute sql (ex. select sleep() from ...) with different where condition
3. when get result, compare their where condition, you will get the wrong result.
Contact me: hcy_xy@qq.com leile111@126.com 
Thanks.

Suggested fix:
maybe distinguish the real exception, and when CommunicationsException happens, force close the mysqlio
[25 May 5:38] hcy john
when set both the value of sockettimeout and querytimeout closely, then will get last connection's result
[3 Aug 23:05] Filipe Silva
Hi hcy john,

Thank you for this bug report.

Please provide Java code for a complete test case.

As far as I understand you setup the connection with `socketTimeout` and `setQueryTimeout()` with similar values and then run a query that takes longer than those, expecting an exception to be thrown, right? So, if you are getting either exception, what results are you comparing?

Also mind that Connector/J connection object is not thread safe, so, if you are using the same connection object concurrently you should get unexpected results.
[10 Aug 7:36] he he
Filipe Silva,thank you for your reply.I use connection pool to reproduce the case, such as dbcp2,druid,HikariCp...

the test code is using dbcp2, to reproduce the case ,you just need to change the url with your own jdbcurl. Thanks
maven dependency:
<dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.1.1</version>
</dependency>
<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
</dependency>

public class JdbcLocalDemo {
    static BasicDataSource basicDataSource = null;

    public static void main(String[] args) throws Exception {
        basicDataSource = new BasicDataSource();
        basicDataSource.setUrl("jdbc:mysql://127.0.0.1:3306/shopping?characterEncoding=UTF8&socketTimeout=1005&allowMulti");
        basicDataSource.setUsername("root");
        basicDataSource.setPassword("123456");
        basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        basicDataSource.setDefaultQueryTimeout(1000);
        basicDataSource.setTestOnBorrow(false);
        basicDataSource.setMaxTotal(10);
        basicDataSource.setMaxIdle(10);
        basicDataSource.setMinIdle(5);
        basicDataSource.setMinIdle(5);

        SqlRunnable r1 = new SqlRunnable("a");
        SqlRunnable r2 = new SqlRunnable("b");
        ExecutorService executorService = Executors.newFixedThreadPool(5);
        for (int i = 1; i <= 2000; ++i) {
            executorService.submit(r1);
            executorService.submit(r2);
        }
        executorService.shutdown();
        executorService.awaitTermination(1, TimeUnit.MINUTES);
    }

    private static class SqlRunnable implements Runnable {
        private final String value;

        private SqlRunnable(String value) {
            this.value = value;
        }

        @Override
        public void run() {
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            Connection connection = null;
            try {
                connection = basicDataSource.getConnection();
                preparedStatement = connection.prepareStatement("select \"" + value + "\" as v, sleep(?);");
                preparedStatement.setQueryTimeout(1);
                preparedStatement.setFetchSize(0);
                preparedStatement.setMaxRows(0);
                preparedStatement.setDouble(1, ThreadLocalRandom.current().nextInt(2000) / 1000.0);
                resultSet = preparedStatement.executeQuery();

                while (resultSet.next()) {
                    String v = resultSet.getString("v");
                    //当查到脏数据退出
                    if (!value.equals(v)) {
                        System.err.println("!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=!=: " + value);
                        System.exit(-1);
                    } else {
                        System.out.println("result equals");
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (resultSet != null) {
                    try {
                        resultSet.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (preparedStatement != null) {
                    try {
                        preparedStatement.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }
}
[4 Sep 1:00] Bugs System
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".