Bug #72331 MAX_STATEMENT_TIME does not respond when using cursor fetch
Submitted: 13 Apr 2014 16:42 Modified: 15 Apr 2014 19:28
Reporter: Sadao Hiratsuka Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.7.4, 5.7.5 OS:Any
Assigned to: Filipe Silva CPU Architecture:Any
Tags: max_statement_time

[13 Apr 2014 16:42] Sadao Hiratsuka
Description:
MAX_STATEMENT_TIME does not respond when using cursor fetch.

In Connector/J, following settings are required.
- useCursorFetch = true
- setFetchSize > 0

It seems to be cancelled the query in server side,
but client is still waiting at SocketInputStream.socketRead0().

How to repeat:
package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Timeout {
	public static final String URL = "jdbc:mysql://k07sl6/tpcc?useCursorFetch=true";
	public static final String USER = "tpcc";
	public static final String PASS = "xxxx";
	public static final String STATEMENT = "SELECT MAX_STATEMENT_TIME=1000 1 FROM orders o INNER JOIN order_line ol ON o.o_id <= ol.ol_o_id;";

	public static void main(String[] args) {
		try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
				PreparedStatement pstmt = conn.prepareStatement(STATEMENT);) {

			pstmt.setFetchSize(100);

			try (ResultSet rs = pstmt.executeQuery();) {
				while (rs.next()) {
					System.out.println(rs.getInt(0));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

Suggested fix:
Sorry, I have no idea.
[15 Apr 2014 19:28] Sveta Smirnova
Thank you for the report.

Verified as described.
[15 Apr 2014 19:29] Sveta Smirnova
test case for Java 1.6 or older

Attachment: bug72331.java (text/x-java), 954 bytes.

[29 Dec 2015 13:22] Filipe Silva
Posted by developer:
 
The option MAX_STATEMENT_TIME=n is deprecated MySQL 5.7.8 but the same issue is observed when using the hint /*+ MAX_EXECUTION_TIME(1) */ that replaces it.