MySQL Bugs: #116788: ResultsetRowsStreaming hangs on large number of rows

Bug #116788	ResultsetRowsStreaming hangs on large number of rows
Submitted:	26 Nov 2024 20:22	Modified:	2 May 18:14
Reporter:	Raphael Azzolini	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	8.0.33, 9.0.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When using ResultsetRowsStreaming to query a large dataset, a critical issue may arise: the read operations can become stuck while waiting to receive bytes from the server. This delay will eventually cause the net_write_timeout to exceed its limit, resulting in a closed connection.

During testing, I encountered this problem with a table containing 1.2 billion rows. The ResultSet.next() method ceased to return results after reading 458.5 million rows on the client side.

I also observed that, although the connection is still open, the database's network activity drops when the result set gets stuck, meaning that it is not sending bytes to the client. 

Key observations:

1. The issue was consistently reproduced across various minor versions of Connector/J 8.0 and in Connector/J 9.0.
2. Notably, this problem does not occur in Connector/J 5.1. Using this older version, all 1.2 billion rows from the same table could be read without any issues.
3. Same issue happens when I try to set useReadAheadInput=false or useReadAheadInput=false&useUnbufferedInput=false
4. Based on these findings, it appears that this is a regression introduced in Connector/J 8.0.

When ResultSet.next() gets stuck and stops returning rows, the following stack trace is observed for the affected thread:

"main" #1 prio=5 os_prio=31 cpu=5889766.79ms elapsed=39386.67s tid=0x0000000128808200 nid=0x2803 runnable  [0x000000016d716000]
   java.lang.Thread.State: RUNNABLE
  at sun.nio.ch.Net.poll(java.base@17.0.12/Native Method)
  at sun.nio.ch.NioSocketImpl.park(java.base@17.0.12/NioSocketImpl.java:186)
  at sun.nio.ch.NioSocketImpl.park(java.base@17.0.12/NioSocketImpl.java:195)
  at sun.nio.ch.NioSocketImpl.implWrite(java.base@17.0.12/NioSocketImpl.java:420)
  at sun.nio.ch.NioSocketImpl.write(java.base@17.0.12/NioSocketImpl.java:445)
  at sun.nio.ch.NioSocketImpl$2.write(java.base@17.0.12/NioSocketImpl.java:831)
  at java.net.Socket$SocketOutputStream.write(java.base@17.0.12/Socket.java:1035)
  at sun.security.ssl.SSLSocketOutputRecord.flush(java.base@17.0.12/SSLSocketOutputRecord.java:271)
  at sun.security.ssl.OutputRecord.changeWriteCiphers(java.base@17.0.12/OutputRecord.java:224)
  at sun.security.ssl.KeyUpdate$KeyUpdateProducer.produce(java.base@17.0.12/KeyUpdate.java:323)
  at sun.security.ssl.KeyUpdate$KeyUpdateKickstartProducer.produce(java.base@17.0.12/KeyUpdate.java:171)
  at sun.security.ssl.SSLHandshake.kickstart(java.base@17.0.12/SSLHandshake.java:542)
  at sun.security.ssl.PostHandshakeContext.kickstart(java.base@17.0.12/PostHandshakeContext.java:69)
  at sun.security.ssl.TransportContext.kickstart(java.base@17.0.12/TransportContext.java:258)
  at sun.security.ssl.SSLSocketImpl.startHandshake(java.base@17.0.12/SSLSocketImpl.java:448)
  at sun.security.ssl.SSLSocketImpl.startHandshake(java.base@17.0.12/SSLSocketImpl.java:426)
  at sun.security.ssl.SSLSocketImpl.tryKeyUpdate(java.base@17.0.12/SSLSocketImpl.java:1553)
  at sun.security.ssl.SSLSocketImpl.decode(java.base@17.0.12/SSLSocketImpl.java:1525)
  at sun.security.ssl.SSLSocketImpl.readApplicationRecord(java.base@17.0.12/SSLSocketImpl.java:1484)
  at sun.security.ssl.SSLSocketImpl$AppInputStream.read(java.base@17.0.12/SSLSocketImpl.java:1069)
  at java.io.FilterInputStream.read(java.base@17.0.12/FilterInputStream.java:132)
  at com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:64)
  at com.mysql.cj.protocol.a.SimplePacketReader.readMessageLocal(SimplePacketReader.java:137)
  at com.mysql.cj.protocol.a.SimplePacketReader.readMessage(SimplePacketReader.java:102)
  at com.mysql.cj.protocol.a.SimplePacketReader.readMessage(SimplePacketReader.java:45)
  at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readMessage(TimeTrackingPacketReader.java:62)
  at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readMessage(TimeTrackingPacketReader.java:41)
  at com.mysql.cj.protocol.a.MultiPacketReader.readMessage(MultiPacketReader.java:66)
  at com.mysql.cj.protocol.a.MultiPacketReader.readMessage(MultiPacketReader.java:44)
  at com.mysql.cj.protocol.a.ResultsetRowReader.read(ResultsetRowReader.java:75)
  at com.mysql.cj.protocol.a.ResultsetRowReader.read(ResultsetRowReader.java:42)
  at com.mysql.cj.protocol.a.NativeProtocol.read(NativeProtocol.java:1648)
  at com.mysql.cj.protocol.a.result.ResultsetRowsStreaming.next(ResultsetRowsStreaming.java:194)
  at com.mysql.cj.protocol.a.result.ResultsetRowsStreaming.next(ResultsetRowsStreaming.java:62)
  at com.mysql.cj.jdbc.result.ResultSetImpl.next(ResultSetImpl.java:1813)
  - locked <0x0000000600006520> (a com.mysql.cj.jdbc.ConnectionImpl)

I also checked that the process is still active in the server:
mysql> select * from information_schema.processlist where db = 'mydb'\G
*************************** 1. row ***************************
     ID: 195
   USER: db_user
   HOST: *****
     DB: mydb
COMMAND: Query
   TIME: 43389
  STATE: Sending to client
   INFO: SELECT id_1,id_2,execute_count,state,updated_at FROM mydb.test_table

This issue significantly impacts the ability to query and process large datasets efficiently, potentially causing data retrieval operations to fail unexpectedly.

How to repeat:
1. Database is MySQL 8.0.39 with the following parameters
innodb_stats_transient_sample_pages = 100
max_allowed_packet = 1073741824
max_connect_errors = 100000
net_write_timeout = 28800
wait_timeout = 2400

2. I created the following table
CREATE TABLE test_table (id_1 varchar(100), id_2 varchar(100), execute_count bigint, state varchar(255), updated_at datetime, PRIMARY KEY (id_1, id_2));

3. Then I ran a script to insert random data in that table, 1.2 billion rows with a total size of around 600 GB.

4. Create a connection with the following properties
props.setProperty("zeroDateTimeBehavior", "CONVERT_TO_NULL");
props.setProperty("maxAllowedPacket ", "1073741824");
props.setProperty("useUnicode", "yes");
props.setProperty("characterEncoding", "UTF-8");
props.setProperty("socketTimeout", "3600000");
props.setProperty("netTimeoutForStreamingResults", "28800");
props.setProperty("sslMode", "VERIFY_IDENTITY");
props.setProperty("useSSL", "true");
props.setProperty("enabledTLSProtocols", "TLSv1.2,TLSv1.3");

5. Create the following PreparedStatement
PreparedStatement stmt = connection.prepareStatement(QUERY, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);
stmt.setQueryTimeout(0);

6. Iterate the result set until it gets stuck;
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
   ...
}

Hi,

I have reproduced this issue with 8.0.33 but I cannot reproduce it with 8.0.40. Can you retest with latest version of MySQL and connector as it looks like it works ok with 8.0.40.

Thanks

Hi,

Thanks for looking at the issue.

Did you mean 8.4.0? I can't find version 8.0.40.

You can find 8.0.40 on our site:
https://dev.mysql.com/downloads/mysql/

Hi MySQL Verification Team,

Sorry, when you said version 8.0.40, I thought it was the Connector/J version, not server version.

I tested the query in MySQL 8.0.40 with Connector/J 9.1.0, and the problem persists.

I think that it is a problem with Connector/J and not the MySQL server, because the query works fine on Connector/J 5.1.

Hi,

Apologies, my bad, I focused on server version didn't even check what version of connector I was using. I will retest with connector 9.1.0 asap while I have test setup live.

Thanks

Verified. Thank you for the report.

Hi,

We discovered that the root cause of this bug is related to TLS v1.3, as documented in https://bugs.openjdk.org/browse/JDK-8329548.

This issue does not occur in MySQL Connector/J 5.1 because its latest version (5.1.49) was built with JDK 1.8.0_241-b07. Oracle's Java 8 releases prior to 8u261 were shipped with JSSE implementations that only supported TLS up to version 1.2 (see https://dev.mysql.com/doc/connectors/en/connector-j-versions.html).