Bug #99495 Get dirty connection resultset

• Submitted: 9 May 2020 3:57
• Modified: 3 Sep 2020 23:05
• Reporter: hcy john
• Status: No Feedback
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 5.1.49
• OS: Any
• CPU Architecture: ARM

[9 May 2020 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: [e-mail] [e-mail] 
Thanks.

Suggested fix:
maybe distinguish the real exception, and when CommunicationsException happens, force close the mysqlio

[25 May 2020 5:38] hcy john

when set both the value of sockettimeout and querytimeout closely, then will get last connection's result

[3 Aug 2020 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 2020 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 2020 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".