Bug #118360 A potential bugs in Mysql Connector/J

• Submitted: 4 Jun 14:50
• Modified: 3 Jul 14:41
• Reporter: 策 吕
• Status: Duplicate
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 9.0.0
• OS: Any
• CPU Architecture: Any

[4 Jun 14:50] 策 吕

Description:
When the createStatement() method is called, set resultSetHoldability to 2 (ResultSet.CLOSE_CURSORS_AT_COMMIT), using System.out.println(con.getHoldability()) and System.out.println(stmt.getResultSetHoldability()) produced inconsistent output.

Expected: 2 2
Actual: 2 1

How to repeat:
import java.sql.*;
import java.sql.ResultSet;

public class test2OMbug {
    public static void main(String[] args) {
        try{
            Connection con = null;
            Statement stmt = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            String url1 = "jdbc:mysql://localhost:3306/testdb0?user=root&password=1234&useServerPrepStmts=false";
            String url2 = "jdbc:oceanbase://49.52.27.61:2881/test?user=root@test&password=1234&useServerPrepStmts=false";
            con = DriverManager.getConnection(url1);

            stmt = con.createStatement(1005, 1008, ResultSet.CLOSE_CURSORS_AT_COMMIT); // ResultSet.CLOSE_CURSORS_AT_COMMIT -> 2
            stmt.executeUpdate("DROP TABLE IF EXISTS table2_3;");
            stmt.executeUpdate("CREATE TABLE table2_3 (Id TINYINT PRIMARY KEY, Value0 TEXT(5));");
            pstmt = con.prepareStatement("INSERT INTO table2_3 (Id, Value0) VALUES (?, ?)");
            pstmt.setObject(1, Byte.MAX_VALUE);
            pstmt.setObject(2, "abcde");
            pstmt.addBatch();
            pstmt.setObject(1, Byte.MIN_VALUE);
            pstmt.setObject(2, "ABCDE");
            pstmt.addBatch();
            try{
                pstmt.executeBatch();
            } catch (Exception e) {
                System.out.println(e);
            }
            rs = stmt.executeQuery("SELECT * FROM table2_3 WHERE Id = " + Byte.MAX_VALUE);

            con.setAutoCommit(false);
            con.commit();
            rs.next();
            System.out.println(stmt.getResultSetType());
            System.out.println(stmt.getResultSetConcurrency());
            System.out.println(con.getHoldability());
            System.out.println(stmt.getResultSetHoldability());
            con.close();
        } catch (Exception e) {
            System.out.println(e);
        }

    }
}

[4 Jun 14:51] MySQL Verification Team

Hello 策 吕,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh

[4 Jun 14:54] 策 吕

As I am using stmt = con.createStatement(1005, 1008, ResultSet.CLOSE_CURSORS_AT_COMMIT);

Therefore get stmt's should be resultSetHoldability should be ResultSet.CLOSE_CURSORS_AT_COMMIT, so System.out.println(con.getHoldability()) output is 2, System.out.println(stmt.getResultSetHoldability()) output should also be 2 (ResultSet.CLOSE_CURSORS_AT_COMMIT)

[3 Jul 14:40] Axyoan Marcelo

Posted by developer:
 
Hi 策 吕,
Thank you for you report. The inconsistencies you mentioned are indeed a bug, and is being tracked by the following report: Bug#44791
As such, this report will be closed as a duplicate. Please note, that in your test case, you try to create a Statement with ResultSet.CLOSE_CURSORS_AT_COMMIT. Connector/J only supports ResultSet.HOLD_CURSORS_OVER_COMMIT, so even if you try to use a different resultSetHoldability, the driver won't apply these changes. However, doing this won't throw an exception unless the connection property `pedantic` is set to "true". Indeed, if you set the connection property "pedantic" to "true", you will get the following exception running your testcase:
java.sql.SQLException: HOLD_CUSRORS_OVER_COMMIT is only supported holdability level.
I hope this cleared up any confusion.
Regards,
Axyoan