[CONJ-531] Cancel of result set is incorrect/excesive Created: 2017-09-27  Updated: 2017-10-03  Resolved: 2017-10-03

Status: Closed
Project: MariaDB Connector/J
Component/s: Other
Affects Version/s: 2.0.3, 2.1.1, 1.6.4
Fix Version/s: 2.2.0, 1.7.0

Type: Bug Priority: Major
Reporter: Adrian Tarau Assignee: Diego Dupin
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by CONJ-315 Cannot cancel Streaming result sets Closed

 Description   

Starting with 1.6.X, when a statement is closed, it has a result set and streaming is enabled, a kill command is sent out. That killed our application since we have a large scale application with hundreds of processes. cancelCurrentQuery creates a new connection and sends a kill command. If a new connection is created for every select, there are thousands of sockets left in TIMED_WAIT and the server cannot create new connections.

This should be done in a dedicated, pooled connection. In addition to that, the code should check if the result set was already consumed and skip kill since there will be nothing to do anyway. In this case, even if a new connection is done, it will be done in 0.01% of the cases where the app abourts the ResultSet.

public void close() throws SQLException {
 
        lock.lock();
 
        try {
 
            closed = true;
 
 
 
            if (results.getFetchSize() != 0) {
 
                if (options.killFetchStmtOnClose) {
 
                    try {
 
                        protocol.cancelCurrentQuery();
 
                        skipMoreResults();
 
                    } catch (SQLException sqle) {
 
                        //eat exception
 
                    } catch (IOException sqle) {
 
                        //eat exception
 
                    }
 
                } else skipMoreResults();
 
            }
 
 
 
            results.close();
 
            protocol = null;
 
 
 
            if (connection == null || connection.pooledConnection == null
 
                    || connection.pooledConnection.statementEventListeners.isEmpty()) {
 
                return;
 
            }
 
            connection.pooledConnection.fireStatementClosed(this);
 
        } finally {
 
            lock.unlock();
 
        }
 
    }



 Comments   
Comment by Diego Dupin [ 2017-09-27 ]

Right, there has been confusion :

JDBC specification indicate : 

Closing a Statement object will close and invalidate any instances of ResultSet produced by that Statement object.

close() must not execute any KILL QUERY command, but must read the full result-set from the server in case there is some streaming resultset.

If there is a big streaming result, Statement.cancel() must handle that.

Current implementation doesn't handle those streaming resultset, since executing flag is then set to false : 

    public void cancel() throws SQLException {
 
        checkClose();
 
        try {
 
            if (!executing) return;
 
            protocol.cancelCurrentQuery();
 
        } catch (SQLException e) {
 
            logger.error("error cancelling query", e);
 
            ExceptionMapper.throwException(e, connection, this);
 
        }
 
    }

code must be changed for something like : 

    public void cancel() throws SQLException {
 
        checkClose();
 
        try {
 
            if (executing || (results.getFetchSize() != 0 && results.notFullyLoaded())) {
 
                protocol.cancelCurrentQuery();
 
            }
 
        } catch (SQLException e) {
 
            logger.error("error cancelling query", e);
 
            ExceptionMapper.throwException(e, connection, this);
 
        }
 
    }

Has you must have seen, you can set the option "killFetchStmtOnClose" to false as a workaround.

Comment by Diego Dupin [ 2017-10-03 ]

correction is available in 2.2.0-SNAPSHOT, 1.6.6-SNAPSHOT : 

<repositories>
 
    <repository>
 
        <id>sonatype-nexus-snapshots</id>
 
        <name>Sonatype Nexus Snapshots</name>
 
        <url>https://oss.sonatype.org/content/repositories/snapshots</url>
 
    </repository>
 
</repositories>
 
 
 
<dependencies>
 
    <dependency>
 
        <groupId>org.mariadb.jdbc</groupId>
 
        <artifactId>mariadb-java-client</artifactId>
 
        <version>2.2.0-SNAPSHOT</version>
 
    </dependency>
 
</dependencies>

Generated at Thu Feb 08 03:16:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.