Bug #118146 Inconsistent output when using MariaDB and MYSQL Connector, unexpected shutdown of MYSQL ResultSet object

• Submitted: 9 May 3:15
• Modified: 20 May 16:06
• Reporter: 策 吕
• Status: Not a Bug
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 9.2.0, 9.3.0
• OS: Any
• CPU Architecture: Any

[9 May 3:15] 策 吕

Description:
When I ran a test sample I found that the ResultSet was being closed unexpectedly in MYSQL, but this was not the case in MariaDB.
*****************************************************************

MYSQL Connector：
<dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>9.2.0</version>
</dependency>
-----------------------------------------------------------------
MariaDB Connector：
<dependency>
            <groupId>org.mariadb.jdbc</groupId>
            <artifactId>mariadb-java-client</artifactId>
            <version>3.5.3</version>
</dependency>
-----------------------------------------------------------------
/////////////////////////////////////////////////////////////////
MYSQL Output：
true
java.sql.SQLException: Operation not allowed for a result set of type ResultSet.TYPE_FORWARD_ONLY.
java.sql.SQLException: Operation not allowed for a result set of type ResultSet.TYPE_FORWARD_ONLY.
java.sql.SQLException: Operation not allowed for a result set of type ResultSet.TYPE_FORWARD_ONLY.
java.sql.BatchUpdateException: Data truncation: Out of range value for column 'Value1' at row 1
false
-1
-1
java.sql.SQLException: Generated keys not requested. You need to specify Statement.RETURN_GENERATED_KEYS to Statement.executeUpdate(), Statement.executeLargeUpdate() or Connection.prepareStatement().
java.sql.SQLException: Operation not allowed after ResultSet closed
java.sql.SQLException: Operation not allowed after ResultSet closed
Exception in thread "main" java.sql.SQLException: Operation not allowed after ResultSet closed
------------------------------------------------------------------
//////////////////////////////////////////////////////////////////
MariaDB Output：
true
java.sql.SQLException: (conn=16) Operation not permit on TYPE_FORWARD_ONLY resultSet
java.sql.SQLException: (conn=16) Operation not permit on TYPE_FORWARD_ONLY resultSet
java.sql.SQLException: (conn=16) Operation not permit on TYPE_FORWARD_ONLY resultSet
[main] WARN org.mariadb.jdbc.message.server.ErrorPacket - Error: 1264-22003: Out of range value for column 'Value1' at row 1
Exception in thread "main" java.sql.SQLSyntaxErrorException: (conn=16) max rows cannot be negative : asked for -2147483648
	at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:289)
	at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:354)
	at org.mariadb.jdbc.Statement.setMaxRows(Statement.java:292)
	at reportBug1.main(reportBug1.java:139)
java.sql.BatchUpdateException: (conn=16) Out of range value for column 'Value1' at row 1
false
-1
-1
java.sql.SQLException: Cannot return generated keys: query was not set with Statement.RETURN_GENERATED_KEYS
true
false
false
false

How to repeat:
import java.sql.*;

public class reportBug1 {

    public static void main(String[] args) throws SQLException {
        Connection con = null;
        Statement stmt = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        //con = DriverManager.getConnection("jdbc:mariadb://localhost:3306/testdb0?user=root&password=1234&useServerPrepStmts=true&rewriteBatchedStatements=true&createDatabaseIfNotExist=false&cacheCallableStmts=true&useBulkStmts=false");
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb0?user=root&password=1234&useServerPrepStmts=true&rewriteBatchedStatements=true&createDatabaseIfNotExist=false&cacheCallableStmts=true&useBulkStmts=false");
        stmt = con.createStatement(1003, 1007, 1);
        stmt.getQueryTimeout();
        stmt.setMaxRows(100);
        try {
            stmt.setFetchSize(50);
        } catch (Exception e) {
            System.out.println(e);
        }
        try {
            stmt.setFetchDirection(1001);
        } catch (Exception e) {
            System.out.println(e);
        }

        try {
            stmt.executeUpdate("DROP TABLE IF EXISTS table0_0;");
        } catch (Exception e) {
            System.out.println(e);
        }
        try {
            stmt.executeUpdate("CREATE TABLE table0_0 (Id DOUBLE PRIMARY KEY, Value0 TINYINT, Value1 DECIMAL);");
        } catch (Exception e) {
            System.out.println(e);
        }

        pstmt = con.prepareStatement("INSERT INTO table0_0 (Id, Value0, Value1) VALUES (?, ?, ?);");
        pstmt.setObject(1, 1.0);
        pstmt.setObject(2, Byte.MAX_VALUE);
        pstmt.setObject(3, Double.MIN_VALUE);
        try {
            pstmt.executeUpdate();
        } catch (Exception e) {
            System.out.println(e);
        }

        stmt.addBatch("INSERT INTO table0_0 (Id, Value0, Value1) VALUES (2.0, -128, 100.5);");
        stmt.addBatch("INSERT INTO table0_0 (Id, Value0, Value1) VALUES (3.0, 127, -200.25);");
        try {
            stmt.executeBatch();
        } catch (Exception e) {
            System.out.println(e);
        }

        try {
            rs = stmt.executeQuery("SELECT * FROM table0_0;");
        } catch (Exception e) {
            System.out.println(e);
        }
        System.out.println(rs.next());
        rs.getObject(1);
        rs.getObject("Value1");
        try {
            System.out.println(rs.previous());
        } catch (Exception e) {
            System.out.println(e);
        }
        try {
            rs.beforeFirst();
        } catch (Exception e) {
            System.out.println(e);
        }
        try {
            rs.afterLast();
        } catch (Exception e) {
            System.out.println(e);
        }

        stmt.setMaxFieldSize(1024);
        stmt.setEscapeProcessing(true);
        try {
            stmt.setFetchDirection(1002);
        } catch (Exception e) {
            System.out.println(e);
        }

        pstmt.clearParameters();
        pstmt.setObject(1, 4.0);
        pstmt.setObject(2, Byte.MIN_VALUE);
        pstmt.setObject(3, Double.MAX_VALUE);
        pstmt.addBatch();
        try {
            pstmt.executeBatch();
        } catch (Exception e) {
            System.out.println(e);
        }

        try {
            con.setAutoCommit(false);
        } catch (Exception e) {
            System.out.println(e);
        }
        Savepoint sp1 = con.setSavepoint("SP1");
        try {
            stmt.executeUpdate("UPDATE table0_0 SET Value0 = 0 WHERE Id = 1.0;");
        } catch (Exception e) {
            System.out.println(e);
        }
        try {
            con.rollback(sp1);
        } catch (Exception e) {
            System.out.println(e);
        }

        System.out.println(stmt.getMoreResults());
        System.out.println(stmt.getUpdateCount());
        System.out.println(stmt.getLargeUpdateCount());
        try {
            stmt.getGeneratedKeys();
        } catch (Exception e) {
            System.out.println(e);
        }

        try {
            rs.setFetchSize(10);
        } catch (Exception e) {
            System.out.println(e);
        }
        try {
            rs.setFetchDirection(1003);
        } catch (Exception e) {
            System.out.println(e);
        }
        System.out.println(rs.isFirst());
        System.out.println(rs.isLast());
        System.out.println(rs.isAfterLast());
        System.out.println(rs.isBeforeFirst());

        stmt.setMaxRows(Integer.MIN_VALUE);

        rs.close();
        pstmt.close();
        stmt.close();
        con.close();
    }
}

[9 May 7:37] MySQL Verification Team

Hello 策 吕,

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

regards,
Umesh

[20 May 16:06] Axyoan Marcelo

Posted by developer:
 
Hello 策 吕,

Thank you for your bug report, please have a look at the following fragment from ResultSet's documentation:
"A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed..."
You can find the documentation here: https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html

With that in mind, note that you have the following line in your testcase:
stmt.executeUpdate("UPDATE table0_0 SET Value0 = 0 WHERE Id = 1.0;");
And some lines below that, you do:
rs.isFirst()

Because you have called executeUpdate(), the ResultSet is automatically closed, and will throw an exception when calling rs.isFirst().

You may want to look into the property dontTrackOpenResources https://dev.mysql.com/doc/connector-j/en/connector-j-connp-props-statements.html#cj-conn-p... which, if set to true, would prevent the ResultSet from being closed in this case.

I hope you've found this information useful, this report will be closed as not a bug.

Regards,
Axyoan