MySQL Bugs: #88205: select query via msyql connector doesn't return when max_execution

Bug #88205	select query via msyql connector doesn't return when max_execution_time is set
Submitted:	24 Oct 2017 13:22	Modified:	13 Nov 2017 10:57
Reporter:	Santosh Bheemarajaiah	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.38	OS:	Fedora (FC23)
Assigned to:		CPU Architecture:	Any
Tags:	JDBC hangs, max_execution_time

Description:
Our application runs on java and we user mysql jdbc driver to talk to mysql server. 

MYSQL server : 5.7.17-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
MYSQL connector v5.1.38: Platform Independent (Architecture Independent)

When we set max_execution_time on the server side, select queries made via the jdbc client will not return ( just hangs ) when the timeout expires. I could see the query being executed on the server side ( ran 'show processlist' from mysql command line client ). The query does get terminated when the max_execution_time expires ( I had set it to 15 sec for testing). But on the client side, the call to executeQuery() just doesn't return or throw an exception. I just hangs. I also tried with version v5.1.44. Same result. 

Note that the same query executed via command line mysql client does show an error when it terminates after the timeout.

Here is a sample code. T1,T2,T3,T4 are large tables with 50+ columns and 10s of thousands for rows. I wrote this query to artificially increase the query time. 

final String sql = "select T1.* from minutetunneltraffic T1, hourlyloss T2, minutenxtraffic T3, hourlytunneltraffic T4";

JDBCProperties jdbcProp = JDBCProperties.getInstance();
        StringBuilder urlBuilder = new StringBuilder("jdbc:mysql://");
        urlBuilder.
                append(jdbcProp.getHost()).
                append(":").
                append(jdbcProp.getPort()).
                append("/").
                append(jdbcProp.getDBName()).
                append(jdbcProp.getUrlOptions()).
                append("&zeroDateTimeBehavior=convertToNull").
                append("&jdbcCompliantTruncation=false").
                append("&cachePrepStmts=true").
                append("&prepStmtCacheSize=250").
                append("&prepStmtCacheSqlLimit=2048");

        try{
            Class.forName("com.mysql.jdbc.Driver");
            Connection con= DriverManager.getConnection(
                    urlBuilder.toString(),jdbcProp.getUser(),jdbcProp.getPassword());
            Statement stmt=con.createStatement();
            ResultSet rs=stmt.executeQuery(sql);
            while(rs.next())
                System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getString(3));
            con.close();
        }catch(Throwable e){
            System.out.println(e);
        }

How to repeat:
I have provided same code in the description. You can easily repeat this via any large table I believe.

Any update on this please.

Hello Santosh,

Thank you for the bug report..
I tried to reproduce the issue at my end using MySQL Connector / J 5.1.44, 38 and MySQL 5.7.20 but not seeing any issues.
Could you please provide exact steps(database/create table statements, tables data, full stack trace etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.

output:
run:
java.sql.SQLException: Query execution was interrupted, maximum statement execution time exceeded
BUILD SUCCESSFUL (total time: 15 seconds)

Hello John,

Thank you for your feedback.
I could not repeat the issue at our end using MySQL Connector / J 5.1.44, 38 and MySQL 5.7.17 and 20 but not seeing any issues.
If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Thanks,
Chiranjeevi.

Possible duplicate of https://bugs.mysql.com/bug.php?id=72331