Ah, i just answered that on CONJ-715:
This is expected :

When streaming data, data are read row per row (or to be precise by bunch corresponding to fetch size).

See the red paragraph in https://mariadb.com/kb/en/about-mariadb-connector-j/#streaming-result-sets :

If another query is executed on the same connection when a streaming resultset has not been fully read, the connector will put the whole remaining streaming resultset in memory in order to execute the next query. This can lead to OutOfMemoryError if not handled.

The problem is if the same connection is used when streaming, to execute another command ( a Connection.isValid() in your case ), the connector will force the loading of streaming resultset, to read the new command result. The forced loading of streaming resultset might cause this OOM error.
I don't know Kafka Connect JDBC Source, but it would seems it use connection caching (pool?) and validate the connection before use (=> calling Connection.isValid while a streaming resultset was in progress. There is a problem somewhere, a connection must not have been return to pool if used.

Hi !
Thank you for your quick answer.
I'm investigating why Kafka Connect JDBC Source is calling isValid() when a statement is currently streaming.
https://github.com/confluentinc/kafka-connect-jdbc/blob/28052eb896a15a8ed7ee75424c51c9557b28f87d/src/main/java/io/confluent/connect/jdbc/util/CachedConnectionProvider.java

I will give you feedbacks.
Howevere the readFully is a bit dangerous, should we add a warn log or timeout to prevent OOM ?

Still, since you indicate that this seem to work with mysql driver, i'll check what they do, that's strange

There could be 2 others possible implementation :

• add option to dismiss remaining streaming result, and throw an error in streaming resultset to prevent OOM
• there is another streaming possibility that would never have issue, because streaming data are asked to server by bunch each time, but this solution is so slow it has never been a real solution.

I just checked with mysql driver.
setFetchSize only works with size Integer.MIN_VALUE , not following JDBC standard... and if another command is issued an error is thrown : "java.sql.SQLException: Streaming result set com.mysql.cj.protocol.a.result.ResultsetRowsStreaming@1750fbeb is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries."

That correspond to the first other options indicate above. I just wonder why it hasn't failed when you were using mysql driver

You're right. That's so strange ...
I've investigated and if I remove the isValid() call to connection the connector is running without issue.
I really think the isValid() call should not have any impact on a current ResultSet iteration ...

________________________________________________________________________________________________
Here is a simple java program to reproduce :

package test.aurora;

import java.sql.*;

public class SelectAccount {

public static void main(String[] args) throws SQLException {
String password = "pass";
String user = "user";

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3308/loyalty?useSSL=false&useCursorFetch=true", user, password);

PreparedStatement stmt = conn.prepareStatement("select .... FROM TABLE WHERE "); // BIG QUERY (80 millions rows)
// stmt.setFetchSize(Integer.MIN_VALUE);
stmt.setFetchSize(100);

// This code affect current ResultSet Iteration => OOM
new Thread(() -> {
try {
Thread.sleep(10000);
System.out.println("Start execute isValid");
// HERE => #isValid has big impact => cf. cmdProlog() => #readFully() of AbstractQueryProtocol
conn.isValid(1000);
/*
private void cmdPrologue() throws SQLException {

// load active result if any so buffer are clean for next query
if (activeStreamingResult != null)

*/
} catch (InterruptedException e) {
} catch (SQLException e)

}).start();

System.out.println("test before executeQuery");
try (ResultSet rs = stmt.executeQuery()) {
System.out.println("test before while");
while (rs.next())

}
}
}

Hello,
Do have any news concerning this issue, especially the method isValid() that has an impact on the current resultSet ?
Thanks

Streaming implementation is executing the whole command, driver just fetching some rows at a time in memory. This is by far the fastest implementation when streaming data. This means connection cannot be used for other command until finishing reading streaming results. Some server/driver use a different streaming implementation, using a cursor (MariaDB provided this functionnality in protocol), but this isn't implemented by our java driver.
I do not know many cases when using a cursor would be preferable, resulting in slow streaming.

MySQL driver works the same way, even throwing an exception when another commandis executed when streaming, where our driver load streaming resultset in memory, making previous streaming resultset workable, but can leed to OOM.

I think that is real solution there is that kafka make a connection.isValid() call where there is no real need there :
https://github.com/confluentinc/kafka-connect-jdbc/blob/574a90e42d6e3e7f86fe21de03e98038e764c395/src/main/java/io/confluent/connect/jdbc/source/JdbcSourceTask.java#L384