[CONJ-487] No timeout exception on query with sleep(50) and querytimeout(1) Created: 2017-06-02  Updated: 2017-06-04  Resolved: 2017-06-04

Status: Closed
Project: MariaDB Connector/J
Component/s: JDBC 4.2 compatibility
Affects Version/s: 2.0.1
Fix Version/s: 1.6.1, 2.0.2

Type: Bug Priority: Major
Reporter: sfahren Assignee: Diego Dupin
Resolution: Fixed Votes: 0
Labels: None
Environment:

10.2.6-MariaDB


 Description   

I try to create a timeout exception with the current mariadb connector.
For this i create a select statement with the sleep value of 50 seconds but a querytimeout value of 1.

the query runs all the 50 seconds and has a return value - see log information "has next"

Is the handling from my side correct?

final String sql = "SELECT sleep(50)";
 
 
 
	        try (final PreparedStatement preparedStatement = dbCon.getPreparedStatement(sql)) {
 
	            logger.info("### Prepared Statement: " + preparedStatement);
 
 
 
	            preparedStatement.setQueryTimeout(1);
 
	            final ResultSet rowset = preparedStatement.executeQuery();
 
	            logger.info("### Finsih Statement: " + rowset.toString());
 
	            logger.info("### Has next: " + rowset.next());
 
	        } catch (final Exception e) {
 
	            logger.severe(e.getMessage());
 
	        }
 
	        
	        try (final Statement statement = dbCon.getStatement()) {
 
	            logger.info("### Statement: " + statement );
 
 
 
	            statement .setQueryTimeout(1);
 
	            final ResultSet rowset = statement .executeQuery(sql);
 
	            logger.info("### Finsih Statement: " + rowset.toString());
 
	            logger.info("### Has next: " + rowset.next());
 
	        } catch (final Exception e) {
 
	            logger.severe(e.getMessage());
 
	        }


Jun 02, 2017 9:42:48 AM com.mariadb.conj.QueryTimeoutIssue selectEntry
 
INFORMATION: ### Prepared Statement: sql : 'SELECT sleep(50)', parameters : []
 
Jun 02, 2017 9:43:38 AM com.mariadb.conj.QueryTimeoutIssue selectEntry
 
INFORMATION: ### Finsih Statement: org.mariadb.jdbc.internal.com.read.resultset.SelectResultSet@4629104a
 
Jun 02, 2017 9:43:38 AM com.mariadb.conj.QueryTimeoutIssue selectEntry
 
INFORMATION: ### Has next: true
 
Jun 02, 2017 9:43:38 AM com.mariadb.conj.QueryTimeoutIssue selectEntry
 
INFORMATION: ### Statement: org.mariadb.jdbc.MariaDbStatement@27f8302d
 
Jun 02, 2017 9:43:39 AM com.mariadb.conj.QueryTimeoutIssue selectEntry
 
INFORMATION: ### Finsih Statement: org.mariadb.jdbc.internal.com.read.resultset.SelectResultSet@4d76f3f8
 
Jun 02, 2017 9:43:39 AM com.mariadb.conj.QueryTimeoutIssue selectEntry
 
INFORMATION: ### Has next: true



 Comments   
Comment by Diego Dupin [ 2017-06-02 ]

Your tests are corrects, but SLEEP has a specificity : see documentation : If SLEEP() is interrupted, it returns 1, not an exception.
In fact, you can use all command but sleep ...

Comment by Diego Dupin [ 2017-06-02 ]

Sorry, i read text too quickly, it seems that PrepareStatement setReadonly doesn't take in account setTimeout ?!
checking now

Comment by sfahren [ 2017-06-02 ]

Thank you for the fast answer.
I tested with another database and get the correct exception:

class java.sql.SQLTimeoutException | (conn:80) (conn:80) Query execution was interrupted (max_statement_time exceeded)

What i also see was that the exception is only throw from Statement not from PreparedStatement.. (140MB table with 1.1 million entries, no indizies..)
Sorry for the request! Thank you very much for the help

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